Oracle PL/SQL way to conditionally execute a statement based on the results of an environment variable - DB pipelines and Flyway

Question

I'm using flyway Placeholders to conditionally execute statements against specific environments

We have a pipeline check before prod that looks at the schema history tables of QA/Test etc. to make sure that every script ran successfully against every previous environment - and fails the pipeline if it hasn't.

This is a requirement passed down by management.

So we unfortunately cannot use ShouldExecute because it will just not execute the whole script.

If we use ShouldExecute - that script will be missing from the schema history table in QA/Test if it is intended to only run against Prod.

So the script itself needs to have that conditional built into it so it can run successfully regardless of whether the statement inside executes.

I thought that maybe the below would work:

IF ${environment} = 'test' THEN      
    ALTER TABLE hr.job_history ADD (employee_name VARCHAR2(20 BYTE));    
END IF;

which gives

SQL State  : 42000
Error Code : 900
Message    : ORA-00900: invalid SQL statement

Because the above statement only works inside of a function/procedure - is there an easy way to do this?

I looked at this thread but I don't know of way to adapt the script for my use case.

p3consulting · Accepted Answer · 2024-02-10 10:30:21Z

0

If what you want to achieve is conditional compilation, the expression should use the $IF ... $THEN ... $ELSE ... $END; syntax, which means that either the code in the $THEN, either the code in the $ELSE clause will be compiled. Doc: https://www.oracle.com/technetwork/database/features/plsql/overview/plsql-conditional-compilation-133587.pdf The variables are passed using session variable PLSQL_CCFlags, so the variables driven by Flyway should be injected in a prelude where you define it.

answered Feb 10, 2024 at 10:30

p3consulting

5,4212 gold badges14 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Oracle PL/SQL way to conditionally execute a statement based on the results of an environment variable - DB pipelines and Flyway

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related