Unable to execute anonymous PLSQL block having variable declaration in Java Springboot

Question

I have set up all other configurations correctly hence keeping this post short. I have the following method which basically reads in from the file where I have place the logic. I am able to execute SQLs that do not have "DECLARE" keyword; but the moment I parameterize my script all hell breaks loose. How can I get around the problem? I wish to script all plug and play components. Thus, I would not prefer to hardcode queries or write 100s of lines of code.

@Override
public void setupInfrastructure() 
{
   Resource resource = new ClassPathResource("static/setup_infra.sql");
   ResourceDatabasePopulator databasePopulator = new ResourceDatabasePopulator(resource);
   databasePopulator.execute(sourceDataSourceConfiguration.sourceDataSource());
}

DECLARE
    C INT;
BEGIN
    SELECT COUNT(1) INTO C FROM USER_TABLES WHERE TABLE_NAME = 'SOME_TABLE_LOADTEST';

    IF C = 1 THEN
        EXECUTE IMMEDIATE 'DROP TABLE SOME_TABLE_LOADTEST';
        EXECUTE IMMEDIATE 'CREATE TABLE SOME_TABLE_LOADTEST AS (SELECT * FROM SOME_TABLE WHERE 1=0)';
    ELSE
        EXECUTE IMMEDIATE 'CREATE TABLE SOME_TABLE_LOADTEST AS (SELECT * FROM SOME_TABLE WHERE 1=0)';
    END IF;

    SELECT COUNT(1) INTO C FROM USER_TABLES WHERE TABLE_NAME = 'SOME_TABLE_TRACKER';

    IF C = 1 THEN
        EXECUTE IMMEDIATE 'DROP TABLE SOME_TABLE_TRACKER';
        EXECUTE IMMEDIATE 'CREATE TABLE SOME_TABLE_TRACKER AS (SELECT EMPLID, SEQNO FROM SOME_TABLE WHERE 1=0)';
    ELSE
        EXECUTE IMMEDIATE 'CREATE TABLE SOME_TABLE_TRACKER AS (SELECT EMPLID, SEQNO FROM SOME_TABLE WHERE 1=0)';
    END IF;
END;

"... all hell breaks loose. How can I get around the problem?" >> which problem? If it is about hell, perhaps you need to talk to the priest. If it is something about Oracle/Java, perhaps you should specify error/problem you got. — Littlefoot
– Littlefoot, Commented Feb 20, 2022 at 19:40
I don't know Java, but it sounds as though the question is how to execute PL/SQL blocks, and not really to do with variable declaration. — William Robertson
– William Robertson, Commented Feb 20, 2022 at 20:19
"Instead of building a robust application using industry standard practices I would prefer to write a series of brittle scripts executing dynamic SQL, for the lulz" — APC
– APC, Commented Feb 20, 2022 at 22:43

Arkadiusz Łukasiewicz · Accepted Answer · 2022-02-20 22:10:22Z

1

Add this line in your code.

databasePopulator.setSeparator(ScriptUtils.EOF_STATEMENT_SEPARATOR);

Copy value of constant
ScriptUtils.EOF_STATEMENT_SEPARATOR(^^^ END OF SCRIPT ^^^)
and put it in your sql file in last line.

Note1. ResourceDatabasePopulator is not made to execute plsql block. The Solution is a workaround. It's a generic tool for populating a database. And in my opinion, it should be used only for automated tests.

Note2. If you want to execute sql script and pl/sql you should use separate populators.

answered Feb 20, 2022 at 22:10

Arkadiusz Łukasiewicz

6,3761 gold badge13 silver badges17 bronze badges

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

Collectives™ on Stack Overflow

Unable to execute anonymous PLSQL block having variable declaration in Java Springboot

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related