How to include multiple commands in a JDBC SQL query to Oracle 10g?

Question

I'm completely new to Oracle (I come from MySQL and MSSQL) and am novice at JDBC. One of my table creation queries looks like this:

CREATE TABLE  "LISTS" 
   ("ID" NUMBER NOT NULL ENABLE, 
    "NAME" VARCHAR2(1000) NOT NULL ENABLE, 
    "DOMAIN_ID" NUMBER NOT NULL ENABLE, 
     CONSTRAINT "LISTS_PK" PRIMARY KEY ("ID") ENABLE
   )
/

CREATE OR REPLACE TRIGGER  "BI_LISTS" 
  before insert on "LISTS"               
  for each row  
begin   
    select "LISTS_SEQ".nextval into :NEW.ID from dual; 
end; 

/
ALTER TRIGGER  "BI_LISTS" ENABLE
/

When I try to connection.createStatement().execute() this query, I get java.sql.SQLSyntaxErrorException: ORA-00922: missing or invalid option. If I remove the slashes, I get the same. If I try replacing them with semicolons, I get java.sql.SQLSyntaxErrorException: ORA-00911: invalid character.

Is it not possible to include multiple commands in one query in JDBC and/or Oracle? Or am I just missing some kind of syntax to separate them?

score 3 · Answer 1 · answered Nov 10 '11 at 16:29

3

For oracle if you include your sql between BEGIN and END; it should work.

Ex:

BEGIN
    CREATE TABLE  "LISTS" 
       ("ID" NUMBER NOT NULL ENABLE, 
        "NAME" VARCHAR2(1000) NOT NULL ENABLE, 
        "DOMAIN_ID" NUMBER NOT NULL ENABLE, 
        CONSTRAINT "LISTS_PK" PRIMARY KEY ("ID") ENABLE
       )
    ;

    CREATE OR REPLACE TRIGGER  "BI_LISTS" 
      before insert on "LISTS"               
      for each row  
    begin   
        select "LISTS_SEQ".nextval into :NEW.ID from dual; 
    end; 
    ;

    ALTER TRIGGER  "BI_LISTS" ENABLE;
END;

answered Nov 10 '11 at 16:29

Dave

618
6
17

I think this is only applicable for UPDATE/INSERT/DELETE statements- I couldn't get it to work when my SQL had CREATE/DROP statements – J. Dimeo Nov 25 '13 at 22:11
The following worked for me: `String wa = """DECLARE v_command VARCHAR2(32767); BEGIN v_command :='CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED $clsName AS $clsDef'; EXECUTE IMMEDIATE v_command; END;""" jdbcTemplate.execute(wa)` – Dave Nov 26 '13 at 14:51
Also note create statements are DDL whereas Update(etc) statements are DML. I wouldn't recommend aggregating DML in PLSQL but rather doing batch execution. – Dave Nov 26 '13 at 14:59

score 2 · Accepted Answer · answered Dec 09 '10 at 23:25

2

Each of those are separate statements. Issue one at a time via separate Statement objects via Connection#createStatement() or via multiple SQL calls to Statement#execute(String).

Conversely, what is your reason for wanting them in one delineated statement?

answered Dec 09 '10 at 23:25

Jé Queue

10,359
13
53
61

It's not so important that they are one statement (I'd separated them out into three strings as a workaround before posting), I just wanted to see if I was missing something and/or doing it wrong. Thanks! – Craig Kovatch Dec 09 '10 at 23:30
You can trick JDBC with some Oracles-specific syntax, but JDBC's object model really is designed to pass statement-level commands over to the DB as each would potentially have responses/exceptions. – Jé Queue Dec 09 '10 at 23:34
Makes sense =) Mostly I was confused that the sql file that Oracle/Apex itself spit out came back as invalid syntax when I tried to run it back through Apex or through JDBC. – Craig Kovatch Dec 09 '10 at 23:36

How to include multiple commands in a JDBC SQL query to Oracle 10g?

2 Answers2

Linked