I am trying to create Oracle PL/SQL procedures and execute them via Oracle JDBC (thin driver). Here is the full PL/SQL script:
begin
for i in (select owner, constraint_name, table_name from all_constraints where owner = 'SCHEMA' and status = 'ENABLED') LOOP
execute immediate 'alter table SCHEMA.'||i.table_name||' disable constraint SCHEMA.'||i.constraint_name||'';
end loop;
end;
/
begin
for i in (select table_name from all_tables where owner = 'SCHEMA') LOOP
execute immediate 'truncate table SCHEMA.'||i.table_name||'';
end loop;
end;
/
begin
for i in (select owner, constraint_name, table_name from all_constraints where owner = 'SCHEMA' and status = 'DISABLED') LOOP
execute immediate 'alter table SCHEMA.'||i.table_name||' enable constraint SCHEMA.'||i.constraint_name||'';
end loop;
end;
/
In java I am splitting on '/' so each begin end block is executed in a separa开发者_Python百科te statement. The java code to execute the statement is:
CallableStatement c = dbc.getConnection().prepareCall(sqlStatement);
c.executeUpdate();
I'm receiving the following error:
java.sql.SQLSyntaxErrorException: ORA-00933: SQL command not properly ended
ORA-06512: at line 3
How do I format this and execute the PL/SQL in JDBC?
Updated: To clarify, all three statements are executed without the '/' delimiter that is split on.
Updated: The oracle server is the following version: Oracle Database 11g Release 11.2.0.1.0 - 64bit Production
In the "enable /diable" constraint you shouldn't add the schema name (your'SCHEMA).
From the manual: ALTER TABLE
Your example:
begin
for i in (select owner, constraint_name, table_name
from all_constraints
where owner = 'SCHEMA'
and status = 'ENABLED')
loop
execute immediate 'alter table SCHEMA.' || i.table_name ||
' disable constraint ' || i.constraint_name;
end loop;
end;
Test Query
select ac.constraint_name, ac.table_name, ac.status, ac.owner
from all_constraints ac
where ac.owner = 'HR'
and ac.constraint_name = 'EMP_SALARY_MIN'
Result
CONSTRAINT_NAME TABLE_NAME STATUS OWNER
------------------------------ ------------------------------ -------- --------------------------------------------------------------------------------
EMP_SALARY_MIN EMPLOYEES ENABLED HR
Correct dynamic sql
begin
execute immediate 'alter table HR.EMPLOYEES disable constraint EMP_SALARY_MIN';
end;
Previous query result
CONSTRAINT_NAME TABLE_NAME STATUS OWNER
------------------------------ ------------------------------ -------- --------------------------------------------------------------------------------
EMP_SALARY_MIN EMPLOYEES DISABLED HR
精彩评论