Executing a script file

[felipenoris]

Hi! I've been using dpiStmt_execute successfully to execute a PL/SQL script like this one:

DECLARE
    acct_amount NUMBER(12,2);
    acct_id     CONSTANT NUMBER(4,0) := 2;

BEGIN
   SELECT AMOUNT INTO acct_amount FROM TB_ACCOUNTS
      WHERE ID = acct_id
      FOR UPDATE OF AMOUNT;

   UPDATE TB_ACCOUNTS SET AMOUNT = 10.0 WHERE ID = acct_id;

   COMMIT;
END;

However, I can't use it to execute something like

CREATE TABLE TB_FIRST ( ... ) ;
CREATE TABLE TB_SECOND( ... ) ;
...

This stackoverflow answer suggests parsing the script file looking for ; delimiters. Is this the best solution?

[cjbj]

dpiStmt_execute sends a single statement to the DB for processing. The PL/SQL block is a single statement. Each CREATE is also a single statement.

SQL scripts are a tool feature.

You may be able to rewrite some scripts to make use EXECUTE IMMEDIATE inside a PL/SQL block. Or abandon SQL scripts altogether and use your own format that your app likes. Or do the parsing you mentioned; see how @anthony-tuininga did it in https://github.com/oracle/python-cx_Oracle/blob/master/samples/SampleEnv.py The parsing is easier if you don't try to be as flexible as SQL*Plus is.

[felipenoris]

Oh, I see. Thanks!