PL/SQL inline functions are not supported by Flyway?

Question

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;
/

I read about it already. That did not help. I added a test script to my post. With SQL Developer it runs, by flyway it throws the mentioned exception. — Ewgenij Sokolovski, Nov 12 '19 at 09:41
You need to have a `/` *on the next line* after every PL/SQL program block, which is basically every block that ends with `end;`. But in this case, the `/` should not be needed, since the function is inlined in the view, and the statement only ends after `from dual`. Seems like this is indeed a Flyway shortcoming. I think Flyway is trying to break up the script in separate statements, but fails on this view. — GolezTrol, Nov 12 '19 at 09:46
I think it is the same issue like here with liquibase [liquibase issue](http://leakfromjavaheap.blogspot.com/2014/05/liquibase-and-plsql-with-oracle-db.html) — Ewgenij Sokolovski, Nov 12 '19 at 10:06
Hmmm, I thought Flyway's USP was that it handles SQL as is, wrangling ["unmodified SQL files generated by your native tools containing all kinds of complexity"](https://stackoverflow.com/a/39052447/146325). Perhaps @axelfontaine will notice this question and comment. — APC, Nov 12 '19 at 15:16

score 1 · Accepted Answer · answered Nov 13 '19 at 02:37

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;
/

PL/SQL inline functions are not supported by Flyway?

1 Answers1