execute immediate problum

[umesh049]

Hello,

I want to delete all the date of all the table in a scheman.

but i got error at execute immediate statement.

can any body help me.

thanks

DECLARE
 query varchar2(700);
 CURSOR cu_idx IS
         SELECT *
         FROM   user_tables;
 BEGIN
     FOR cur_rec IN cu_idx LOOP
 query:= 'delete from ' || upper(cur_rec.table_name) || ';'   ;
 execute immediate query;
 END LOOP;
 END;

DECLARE
*
ERROR at line 1:
ORA-00911: invalid character
ORA-06512: at line 9

[debasisdas]

Please try the following code

DECLARE
 query varchar2(700);
 CURSOR cu_idx IS
         SELECT *
         FROM   user_tables;
 BEGIN
     FOR cur_rec IN cu_idx LOOP
 query:= 'delete from ' || upper(cur_rec.table_name)   ;
 execute immediate query;
 END LOOP;
 END;

[arterakesh]

hi there,

try this just change the syntax to

 
query:= 'delete from '''|| upper(cur_rec.table_name) || ''''   ;
 
 

regards,

Rakesh Arte

Hello,

I want to delete all the date of all the table in a scheman.

but i got error at execute immediate statement.

can any body help me.

thanks

DECLARE
 query varchar2(700);
 CURSOR cu_idx IS
         SELECT *
         FROM   user_tables;
 BEGIN
     FOR cur_rec IN cu_idx LOOP
 query:= 'delete from ' || upper(cur_rec.table_name) || ';'   ;
 execute immediate query;
 END LOOP;
 END;

DECLARE
*
ERROR at line 1:
ORA-00911: invalid character
ORA-06512: at line 9

[skkydiver]

Hello,

I want to delete all the date of all the table in a scheman.

but i got error at execute immediate statement.

can any body help me.

thanks

DECLARE
 query varchar2(700);
 CURSOR cu_idx IS
         SELECT *
         FROM   user_tables;
 BEGIN
     FOR cur_rec IN cu_idx LOOP
 query:= 'delete from ' || upper(cur_rec.table_name) || ';'   ;
 execute immediate query;
 END LOOP;
 END;

DECLARE
*
ERROR at line 1:
ORA-00911: invalid character
ORA-06512: at line 9

Hi, not sure if you solved this problem but in case you have not I might suggest the following as possible reasons for Oracles error as the syntax looks good to me:

- Lack of Delete privs on the tables

As an aside to your specific question, if the tables that you are deleting are large tables - you should consider executing COMMIT inside your loop every 1000 of records to avoid "blowing" your rollback segments.

[Dave44]

Hi, not sure if you solved this problem but in case you have not I might suggest the following as possible reasons for Oracles error as the syntax looks good to me:

- Lack of Delete privs on the tables

As an aside to your specific question, if the tables that you are deleting are large tables - you should consider executing COMMIT inside your loop every 1000 of records to avoid "blowing" your rollback segments.

Debasisdas is correct skkydiver, there is one too many ; in the statement. we can reproduce this nicely

Elapsed: 00:00:00.03
[143]dave@MYORADB> drop table t;
 
Table dropped.
 
Elapsed: 00:00:00.01
[143]dave@MYORADB> 
[143]dave@MYORADB> create table t (col1  varchar2(50));
 
Table created.
 
Elapsed: 00:00:00.01
[143]dave@MYORADB> 
[143]dave@MYORADB> declare code varchar2(4000);
  2  begin
  3    code := 'delete from t';
  4    execute immediate code;
  5  end;
  6  /
 
PL/SQL procedure successfully completed.
 
Elapsed: 00:00:00.00
[143]dave@MYORADB> 
[143]dave@MYORADB> declare code varchar2(4000);
  2  begin
  3    code := 'delete from t;';
  4    execute immediate code;
  5  end;
  6  /
declare code varchar2(4000);
*
ERROR at line 1:
ORA-00911: invalid character
ORA-06512: at line 4
 

now if ,for example, it was an unnamed block we would need the semi colons in there.

[143]dave@MYORADB> declare code varchar2(4000);
  2  begin
  3    code := 'begin delete from t; end;';
  4    execute immediate code;
  5  end;
  6  /
 
PL/SQL procedure successfully completed.
 
Elapsed: 00:00:00.01
 

[subashsavji]

Hello,

I want to delete all the date of all the table in a scheman.

but i got error at execute immediate statement.

can any body help me.

thanks

DECLARE
 query varchar2(700);
 CURSOR cu_idx IS
         SELECT *
         FROM   user_tables;
 BEGIN
     FOR cur_rec IN cu_idx LOOP
 query:= 'delete from ' || upper(cur_rec.table_name) || ';'   ;
 execute immediate query;
 END LOOP;
 END;

DECLARE
*
ERROR at line 1:
ORA-00911: invalid character
ORA-06512: at line 9

 
CREATE OR REPLACE PROCEDURE delete_all_rows
   (p_tab_name IN VARCHAR2, p_rows_del OUT NUMBER)        
 IS
   cursor_name   INTEGER;
 BEGIN
   cursor_name := DBMS_SQL.OPEN_CURSOR;
   DBMS_SQL.PARSE(cursor_name, 'DELETE FROM '||p_tab_name,
               DBMS_SQL.NATIVE );
   p_rows_del := DBMS_SQL.EXECUTE (cursor_name);
   DBMS_SQL.CLOSE_CURSOR(cursor_name);
END;
/
 
VARIABLE deleted NUMBER
EXECUTE  delete_all_rows('employees', :deleted)
PRINT deleted