Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Oracle: Parser fails on CREATE VIEW ... AS WITH <PL/SQL> #2048

Closed
Sajith1988V opened this issue Jun 21, 2018 · 24 comments
Closed

Oracle: Parser fails on CREATE VIEW ... AS WITH <PL/SQL> #2048

Sajith1988V opened this issue Jun 21, 2018 · 24 comments

Comments

@Sajith1988V
Copy link

@Sajith1988V Sajith1988V commented Jun 21, 2018

Which version and edition of Flyway are you using?

Using Flyway Flyway Community Edition 5.1.1 by Boxfuse

If this is not the latest version, can you reproduce the issue with the latest one as well?

(Many bugs are fixed in newer releases and upgrading will often resolve the issue)

Which client are you using? (Command-line, Java API, Maven plugin, Gradle plugin)

Command-line

Which database are you using (type & version)?

Oracle 12.2.1, Ojdbc 8

Which operating system are you using?

Windows & Unix

What did you do?

(Please include the content causing the issue, any relevant configuration settings, the SQL statement that failed (if relevant) and the command you ran.)

CREATE OR REPLACE VIEW VIEW1 AS
WITH
FUNCTION Test_funct (
ARG1 IN VARCHAR2,
ARG2 IN VARCHAR2
) RETURN NUMBER AS
V_O_AMT NUMBER(8,2);
BEGIN
.
.
.
.
This is a new feature of Oracle 12 C to allow functions/procedures inside a with clause. I am trying to create a view out of this with clause query. This executes successfully when I run the script from SQL* Plus or SQL Developer. But this fails when executed via Flyway 5.1.1 Open source

What did you expect to see?

View created successfully.

What did you see instead?

Encountered the below error

**SQL State : 65000
Error Code : 6553
Message : ORA-06553: PLS-103: Encountered the symbol "end-of-file" when expecting one of the following:

:= ; not null default character**

@axelfontaine
Copy link
Contributor

@axelfontaine axelfontaine commented Jun 21, 2018

Please share the full statement Flyway fails to parse correctly, so we can make sure you case is covered.

@Sajith1988V
Copy link
Author

@Sajith1988V Sajith1988V commented Jun 21, 2018

Here is the full view query:

CREATE OR REPLACE VIEW view1 AS
    WITH
-- Total Amount Function    
        FUNCTION WITH_TOT_AMT (
            arg1   IN VARCHAR2,
            ARG2   IN VARCHAR2
        ) RETURN NUMBER AS
            V_O_AMT   NUMBER(8,2);
        BEGIN
            SELECT
            col1
            INTO V_O_AMT
            FROM
                table1 A,
                table2 B
            WHERE
              <filters>

            RETURN V_O_AMT;
        END;

-- Total quantity function 

        FUNCTION WITH_TOT_QTY (
            ARG1   IN VARCHAR2,
            ARG2   IN VARCHAR2
        ) RETURN NUMBER IS
            V_O_QTY   NUMBER(8,2);
        BEGIN
            SELECT
                          col1
            INTO V_O_QTY
            FROM
                table1 A,
                table2 C
            WHERE
               <filters>


            RETURN V_O_QTY;
        END;

--Main query 

    SELECT
       col1, col2, col3
        (
            SELECT
              col1, col2, col3
            FROM
                tt1 A,
                tt2 B
            WHERE
               <filters>

        );
        /

@Sajith1988V
Copy link
Author

@Sajith1988V Sajith1988V commented Jun 21, 2018

Please keep us posted once this is fixed in the next milestone.

@axelfontaine
Copy link
Contributor

@axelfontaine axelfontaine commented Jun 26, 2018

I can't seem to find documentation for this syntax anywhere, including at https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/CREATE-VIEW.html

Can you point me to the official docs regarding this?

@axelfontaine
Copy link
Contributor

@axelfontaine axelfontaine commented Jul 4, 2018

Any news?

@Sajith1988V
Copy link
Author

@Sajith1988V Sajith1988V commented Jul 6, 2018

This view was created while executed via sql plus and sql developer.

@axelfontaine
Copy link
Contributor

@axelfontaine axelfontaine commented Jul 6, 2018

But where is this documented as official Oracle syntax though?

@Sajith1988V
Copy link
Author

@Sajith1988V Sajith1988V commented Jul 10, 2018

Any updates???

@apexbine
Copy link

@apexbine apexbine commented Jul 10, 2018

I'm interested, too!
Here are more links:
https://oracle-base.com/articles/12c/with-clause-enhancements-12cr1#functions
https://docs.oracle.com/database/121/SQLRF/statements_10002.htm#BABJFIDC

Are there any workarounds for this? (Something more elegant than execute immediate ...)

@apexbine
Copy link

@apexbine apexbine commented Jul 11, 2018

By the way, @Sajith1988V, the date for the milestone 5.2.0 is August 28, 2018.

@Sajith1988V
Copy link
Author

@Sajith1988V Sajith1988V commented Aug 1, 2018

Hi,
Is there a possibility to expedite the fix for this issue?

@axelfontaine axelfontaine changed the title Oracle view creation fails while execute the SQL script via Flyway Oracle: Parser fails on CREATE VIEW ... AS WITH <PL/SQL> Aug 10, 2018
axelfontaine added a commit to flyway/flywaydb.org that referenced this issue Aug 10, 2018
@axelfontaine
Copy link
Contributor

@axelfontaine axelfontaine commented Aug 10, 2018

Fixed. This was a weird one. For some reason when using Oracle's JDBC driver, and unlike all other PL/SQL statements no trailing ; is allowed. We have now added a special workaround for this.

@Sajith1988V
Copy link
Author

@Sajith1988V Sajith1988V commented Aug 14, 2018

Please provide which version of flyway has this fix. I am unable to find any latest version.

@axelfontaine
Copy link
Contributor

@axelfontaine axelfontaine commented Aug 14, 2018

@Sajith1988V We only offer pre-release binaries to paying Pro or Enterprise customers.

If this is not an option for you, you can simply build from source yourself or wait until the next release.

@Sajith1988V
Copy link
Author

@Sajith1988V Sajith1988V commented Aug 14, 2018

Ok Axel, I will try to build from my source.
By the way may I know when is the next release planned for?

@Sajith1988V
Copy link
Author

@Sajith1988V Sajith1988V commented Aug 30, 2018

HI Axel,
May I know if the fix for this is released in a new version? I do not see any latest updates to download.

@pputerla
Copy link

@pputerla pputerla commented Sep 21, 2018

Hi @axelfontaine ,

I've noticed that you have effectively disabled CE/Pro support for Oracle 12.1 (d915b86)

Was that deliberately?
I was wondering - do you have some policy on that? I mean we'd only like to know what to expect.
You know what I mean...

@Sajith1988V
Copy link
Author

@Sajith1988V Sajith1988V commented Sep 21, 2018

@pputerla,
Is this the reason the view creation failed for me?

@axelfontaine
Copy link
Contributor

@axelfontaine axelfontaine commented Sep 21, 2018

@pputerla Yes, that is deliberate. See #2147

The policy is: Oracle version still covered by Oracle Premier Support?

  • Yes: Supported in all editions of Flyway
  • No: Supported in Flyway Enterprise Edition only

@Sajith1988V No.

@pputerla
Copy link

@pputerla pputerla commented Sep 21, 2018

#2147: That's what I call a policy :) thanks!

@Sajith1988V
Copy link
Author

@Sajith1988V Sajith1988V commented Sep 21, 2018

@axelfontaine when is the next version of flyway be released that supports my view creation syntax? Please expedite. I do not see any upgraded version yet to download. Also I do not have the privilege to build flyway with your code.

You had replied me a month back saying

"We only offer pre-release binaries to paying Pro or Enterprise customers.

If this is not an option for you, you can simply build from source yourself or wait until the next release."

@jakubek2085
Copy link

@jakubek2085 jakubek2085 commented Aug 1, 2019

Thanks to this fix, I cannot migrate a view which does not contain WITH FUNCTION|PROCEDURE, but a CTE (common table expression). Needs fixing.

@pauxus
Copy link
Contributor

@pauxus pauxus commented Aug 1, 2019

Yes, we encountered the same problem right now as well.

The regexp needs to be less generic:

"^CREATE(\\s+OR\\s+REPLACE)?(\\s+(NON)?EDITIONABLE)?\\s+(VIEW)\\s+.*\\s+AS\\s+WITH\\s+(FUNCTION|PROCEDURE).*");

pauxus added a commit to pauxus/flyway that referenced this issue Aug 1, 2019
Fix flyway#2455:  Replace the regexp of the fix for flyway#2048 with a tighter one

Note that there might still be corner cases where this fails, but this at least fixes most scenarios.
dohrayme pushed a commit to dohrayme/flyway that referenced this issue Feb 3, 2020
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
6 participants