CODEWITHSUNDEEP
javasqloracle-databasespring-bootplsql
Suryadeep
Suryadeep

Reputation: 39

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

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;

Upvotes: 0

Views: 392

Answers (1)

Arkadiusz Łukasiewicz
Arkadiusz Łukasiewicz

Reputation: 6346

  1. Add this line in your code.

databasePopulator.setSeparator(ScriptUtils.EOF_STATEMENT_SEPARATOR);

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

Upvotes: 1

Related Questions