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

[CODE=oracle]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.t able_name) || ';' ;
execute immediate query;
END LOOP;
END;[/CODE]


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

[debasisdas]

Please try the following code

[CODE=oracle]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.t able_name) ;
execute immediate query;
END LOOP;
END;[/CODE]

[arterakesh]

hi there,

try this just change the syntax to

[code=oracle]

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

[/code]

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

[CODE=oracle]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.t able_name) || ';' ;
execute immediate query;
END LOOP;
END;[/CODE]


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

[CODE=oracle]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.t able_name) || ';' ;
execute immediate query;
END LOOP;
END;[/CODE]


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

[CODE=oracle]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.t able_name) || ';' ;
execute immediate query;
END LOOP;
END;[/CODE]


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