0

I'm trying to clear all tables in the current schema by using the following sql: How to truncate all user tables?

set serveroutput on;
declare
begin
for c1 in (select y1.table_name, y1.constraint_name from user_constraints y1, user_tables x1 where x1.table_name = y1.table_name order by y1.r_constraint_name nulls last) loop
begin
    dbms_output.put_line('alter table '||c1.table_name||' disable constraint '||c1.constraint_name || ';');
    execute immediate  ('alter table '||c1.table_name||' disable constraint '||c1.constraint_name);
end;
end loop;
for t1 in (select table_name from user_tables) loop
begin
    dbms_output.put_line('truncate table '||t1.table_name || ';');    
    execute immediate ('truncate table '||t1.table_name);
end;
end loop;
for c2 in (select y2.table_name, y2.constraint_name from user_constraints y2, user_tables x2 where x2.table_name = y2.table_name order by y2.r_constraint_name nulls first) loop
begin
    dbms_output.put_line('alter table '||c2.table_name||' enable constraint '||c2.constraint_name || ';');        
    execute immediate ('alter table '||c2.table_name||' enable constraint '||c2.constraint_name);
end;
end loop;
end;

Using the SQLDeveloper ist works fine, but using JDBC it doesn't.

@Test
public void test() throws DBZugriffsFehler, ClassNotFoundException, SQLException, JAXBException, IOException {
try{
    JPAQLManagement.INSTANCE.loadXML("some path");
    JPAQLQuery query = JPAQLManagement.INSTANCE.getQuery("TabellenLeeren");

    System.out.println(query.getQueryString());

    Class.forName("oracle.jdbc.driver.OracleDriver");
    Connection connection = null;
    connection = DriverManager.getConnection(
            "foo", "lalala",
            "blabla");
    Statement statement = connection.createStatement();
    statement.execute(query.getQueryString());
}catch(Exception e){
        e.printStackTrace();
    }
}

Console:

set serveroutput on;
declare
begin
for c1 in (select y1.table_name, y1.constraint_name from user_constraints y1, user_tables x1 where x1.table_name = y1.table_name order by y1.r_constraint_name nulls last) loop
begin
    dbms_output.put_line('alter table '||c1.table_name||' disable constraint '||c1.constraint_name || ';');
    execute immediate  ('alter table '||c1.table_name||' disable constraint '||c1.constraint_name);
end;
end loop;
for t1 in (select table_name from user_tables) loop
begin
    dbms_output.put_line('truncate table '||t1.table_name || ';');    
    execute immediate ('truncate table '||t1.table_name);
end;
end loop;
for c2 in (select y2.table_name, y2.constraint_name from user_constraints y2, user_tables x2 where x2.table_name = y2.table_name order by y2.r_constraint_name nulls first) loop
begin
    dbms_output.put_line('alter table '||c2.table_name||' enable constraint '||c2.constraint_name || ';');        
    execute immediate ('alter table '||c2.table_name||' enable constraint '||c2.constraint_name);
end;
end loop;
end;

java.sql.SQLSyntaxErrorException: ORA-00922: Fehlende oder ungültige Option

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:399)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1017)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:655)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:249)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:566)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:202)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:45)
at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:933)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1075)
at oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:1718)
at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1678)
at oracle.jdbc.driver.OracleStatementWrapper.execute(OracleStatementWrapper.java:332)
at de.gema.solis.dbaccess.Sandbox.test(Sandbox.java:33)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:78)
at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:57)
at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:86)
at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:459)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:678)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:382)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:192)
Community
  • 1
  • 1
  • 4
    You can't use `set serveroutput on;` in SQL. That is a statement only supported by various SQL clients (SQL\*Plus or SQL Developer), but is not a valid SQL statement. –  Feb 07 '17 at 16:24
  • That was it! Thank you @a_horse_with_no_name – Daniel Huber Feb 10 '17 at 12:23

0 Answers0