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

Flyway parsing bug: unable to decrease block depth below 0 in version 6.4.2 #2884

Closed
t-kwasniewski opened this issue Jul 20, 2020 · 2 comments

Comments

@t-kwasniewski
Copy link

t-kwasniewski commented Jul 20, 2020

Which version and edition of Flyway are you using?

6.4.2

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)

I have checked also version 6.5.1 and there still was this error

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

Java API

Which database are you using (type & version)?

Oracle 19

Which operating system are you using?

Linux and Windows

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.)

In v6.2.4 i was having an error "unable to decrease block depth below 0" when i try to execute script which contains create package, procedure and dbms_scheluder.create_job.
Example code:

CREATE OR REPLACE PACKAGE some_jobs AS
    PROCEDURE some_procedure;
END some_jobs;

CREATE OR REPLACE PACKAGE BODY some_jobs AS

    PROCEDURE some_procedure IS
      CURSOR C_TO_CLOSE
      IS
        --some select

    BEGIN
      FOR row IN C_TO_CLOSE
      LOOP
       --update
      END LOOP;
    END;
END;

BEGIN
 DBMS_SCHEDULER.create_job (
    job_name        => 'some_procedure_job',
    job_type        => 'STORED_PROCEDURE',
    job_action      => 'some_jobs.some_procedure',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'FREQ=DAILY; BYHOUR=1',
    end_date        => NULL,
    enabled         => TRUE,
    comments        => 'some job');
END;

Workaround from issue #2715 (downgrade to v6.2.0) works.
But same issue exists in version 6.5.2.

@Lyeeedar Lyeeedar self-assigned this Jul 20, 2020
@Lyeeedar Lyeeedar added this to the Flyway 6.5.2 milestone Jul 20, 2020
@Lyeeedar
Copy link
Contributor

Lyeeedar commented Jul 20, 2020

If you add a / after the end of the package and the package body, Flyway will parse them correctly again.

In Flyway 6.2.0 this parsed by accident, as packages were not being correctly separated out which would in some cases mean that the sql was invalid when executed.
Fixing that bug has meant that packages need to explicitly be deliminated by a / to be detected properly. If I'm not mistaken this is the same behaviour that SQL*Plus exhibits isnt it?

@t-kwasniewski
Copy link
Author

t-kwasniewski commented Jul 21, 2020

Thank you for very fast answer. Adding / helped. Now it works corretly.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

2 participants