Run oracle script using java code

Question

I wrote a program to run oracle (oracle11g) script using java code but when i m run program it gives error:

ORA-00900:"invalid SQL statement".

Queries are:

execute dbms_metadata.set_transform_param (dbms_metadata.session_transform,'STORAGE',false);
execute dbms_metadata.set_transform_param (dbms_metadata.session_transform,'TABLESPACE',false);
execute dbms_metadata.set_transform_param (dbms_metadata.session_transform,'SEGMENT_ATTRIBUTES', false);
execute dbms_metadata.set_transform_param (dbms_metadata.session_transform,'REF_CONSTRAINTS', false);
execute dbms_metadata.set_transform_param (dbms_metadata.session_transform,'CONSTRAINTS', false);

Code:

String Query = "execute dbms_metadata.set_transform_param (dbms_metadata.session_transform,'STORAGE',false);"
Statement st = con.createStatement();
st.executeUpdate(Query);

To ask the obvious: Can you execute that query from your Oracle console without error? My guess is that you can't. — Tim Biegeleisen
– Tim Biegeleisen, Commented Nov 23, 2015 at 9:47
No I can run script using oracle console and also getting same error when i execute script using oracle11g developer tool but when i run script then it run without any error. — santosh
– santosh, Commented Nov 23, 2015 at 9:55
EXECUTE is SQL*Plus command, I am not sure about Java. Also, you need to execute them in a PL/SQL block. — Lalit Kumar B
– Lalit Kumar B, Commented Nov 23, 2015 at 10:01
I can easily execute this command in sql*plus but i want to execute it with java. — santosh
– santosh, Commented Nov 23, 2015 at 10:02

are · Accepted Answer · 2015-11-23 10:40:25Z

2

I think you need call the procedure via anonymous block in java use begin ... end instead of execute

String Query = "begin dbms_metadata.set_transform_param (dbms_metadata.session_transform,'STORAGE',false); end;"
Statement st = con.createStatement();
st.executeUpdate(Query);

answered Nov 23, 2015 at 10:40

are

2,6153 gold badges24 silver badges27 bronze badges

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

2 Comments

ocarlsen Over a year ago

Did it work for you? I am seeing

java.sql.SQLException: ORA-06550: line 1, column 12: PLS-00103: Encountered the symbol "DBMS_METADATA" when expecting one of the following:     := . ( @ % ; The symbol ":=" was substituted for "DBMS_METADATA" to continue.

are Over a year ago

Connected to Oracle Database 11g Express Edition Release 11.2.0.2.0    SQL> begin dbms_metadata.set_transform_param (dbms_metadata.session_transform,'STORAGE',false); end;   2  /   PL/SQL procedure successfully completed   SQL> show errors; No errors   SQL>

Collectives™ on Stack Overflow

Run oracle script using java code

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related