Search code examples
oracle-databaseplsqlflyway

PL/SQL inline functions are not supported by Flyway?


Since Oracle 12c it is possible to write inline functions in an SQL query, consider Inline Functions However, apparently it does not work when using this syntax in flyway scripts. As far as I understand the Flyway documentation, this kind of usage was not considered, consider Flyway documentation: The "SQL Script Syntax" section. When I try to use it I keep getting

ORA-06553: PLS-103: Encountered the symbol "end-of-file" when expecting one of the following: := ; not null default character

So, do I understand it right that I can't use inline PL/SQL functions with Flyway?

Here is my script:

CREATE OR REPLACE VIEW TEST_VIEW AS
WITH
FUNCTION testfunction (input IN NUMBER) RETURN VARCHAR2
IS
BEGIN
  return 'fffff';
END;
SELECT
  testfunction(2) AS TEST_COLUMN
FROM dual;
/

Solution

  • Can you use an anonymous PL/SQL block to create the view?

    --Create view with dynamic SQL to avoid Flyway parser bug.
    begin
        execute immediate q'[
    CREATE OR REPLACE VIEW TEST_VIEW AS
    WITH
    FUNCTION testfunction (input IN NUMBER) RETURN VARCHAR2
    IS
    BEGIN
      return 'fffff';
    END;
    SELECT
      testfunction(2) AS TEST_COLUMN
    FROM dual
    ]';
    end;
    /