By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,441 Members | 1,831 Online
Bytes IT Community

Home Questions Articles Browse Topics Latest Top Members FAQ

home > topics > oracle database > questions > execute immediate problum
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,441 IT Pros & Developers. It's quick & easy.

execute immediate problum

P: 17
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

Expand|Select|Wrap|Line Numbers
  1. DECLARE
  2.  query varchar2(700);
  3.  CURSOR cu_idx IS
  4.          SELECT *
  5.          FROM   user_tables;
  6.  BEGIN
  7.      FOR cur_rec IN cu_idx LOOP
  8.  query:= 'delete from ' || upper(cur_rec.table_name) || ';'   ;
  9.  execute immediate query;
  10.  END LOOP;
  11.  END;

DECLARE
*
ERROR at line 1:
ORA-00911: invalid character
ORA-06512: at line 9
Dec 24 '07 #1
Share this Question
Share on Google+
5 Replies
debasisdas
Expert 5K+
P: 8,127
Please try the following code

Expand|Select|Wrap|Line Numbers
  1. DECLARE
  2.  query varchar2(700);
  3.  CURSOR cu_idx IS
  4.          SELECT *
  5.          FROM   user_tables;
  6.  BEGIN
  7.      FOR cur_rec IN cu_idx LOOP
  8.  query:= 'delete from ' || upper(cur_rec.table_name)   ;
  9.  execute immediate query;
  10.  END LOOP;
  11.  END;
Dec 24 '07 #2
P: 1
hi there,

try this just change the syntax to

Expand|Select|Wrap|Line Numbers
  1.  
  2. query:= 'delete from '''|| upper(cur_rec.table_name) || ''''   ;
  3.  
  4.  
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

Expand|Select|Wrap|Line Numbers
  1. DECLARE
  2.  query varchar2(700);
  3.  CURSOR cu_idx IS
  4.          SELECT *
  5.          FROM   user_tables;
  6.  BEGIN
  7.      FOR cur_rec IN cu_idx LOOP
  8.  query:= 'delete from ' || upper(cur_rec.table_name) || ';'   ;
  9.  execute immediate query;
  10.  END LOOP;
  11.  END;

DECLARE
*
ERROR at line 1:
ORA-00911: invalid character
ORA-06512: at line 9
Jan 1 '08 #3
P: 4
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

Expand|Select|Wrap|Line Numbers
  1. DECLARE
  2.  query varchar2(700);
  3.  CURSOR cu_idx IS
  4.          SELECT *
  5.          FROM   user_tables;
  6.  BEGIN
  7.      FOR cur_rec IN cu_idx LOOP
  8.  query:= 'delete from ' || upper(cur_rec.table_name) || ';'   ;
  9.  execute immediate query;
  10.  END LOOP;
  11.  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.
Jan 12 '08 #4
100+
P: 153
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
Expand|Select|Wrap|Line Numbers
  1. Elapsed: 00:00:00.03
  2. [143]dave@MYORADB> drop table t;
  3.  
  4. Table dropped.
  5.  
  6. Elapsed: 00:00:00.01
  7. [143]dave@MYORADB> 
  8. [143]dave@MYORADB> create table t (col1  varchar2(50));
  9.  
  10. Table created.
  11.  
  12. Elapsed: 00:00:00.01
  13. [143]dave@MYORADB> 
  14. [143]dave@MYORADB> declare code varchar2(4000);
  15.   2  begin
  16.   3    code := 'delete from t';
  17.   4    execute immediate code;
  18.   5  end;
  19.   6  /
  20.  
  21. PL/SQL procedure successfully completed.
  22.  
  23. Elapsed: 00:00:00.00
  24. [143]dave@MYORADB> 
  25. [143]dave@MYORADB> declare code varchar2(4000);
  26.   2  begin
  27.   3    code := 'delete from t;';
  28.   4    execute immediate code;
  29.   5  end;
  30.   6  /
  31. declare code varchar2(4000);
  32. *
  33. ERROR at line 1:
  34. ORA-00911: invalid character
  35. ORA-06512: at line 4
  36.  
now if ,for example, it was an unnamed block we would need the semi colons in there.
Expand|Select|Wrap|Line Numbers
  1. [143]dave@MYORADB> declare code varchar2(4000);
  2.   2  begin
  3.   3    code := 'begin delete from t; end;';
  4.   4    execute immediate code;
  5.   5  end;
  6.   6  /
  7.  
  8. PL/SQL procedure successfully completed.
  9.  
  10. Elapsed: 00:00:00.01
  11.  
Jan 12 '08 #5
P: 93
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

Expand|Select|Wrap|Line Numbers
  1. DECLARE
  2.  query varchar2(700);
  3.  CURSOR cu_idx IS
  4.          SELECT *
  5.          FROM   user_tables;
  6.  BEGIN
  7.      FOR cur_rec IN cu_idx LOOP
  8.  query:= 'delete from ' || upper(cur_rec.table_name) || ';'   ;
  9.  execute immediate query;
  10.  END LOOP;
  11.  END;

DECLARE
*
ERROR at line 1:
ORA-00911: invalid character
ORA-06512: at line 9
Expand|Select|Wrap|Line Numbers
  1.  
  2. CREATE OR REPLACE PROCEDURE delete_all_rows
  3.    (p_tab_name IN VARCHAR2, p_rows_del OUT NUMBER)        
  4.  IS
  5.    cursor_name   INTEGER;
  6.  BEGIN
  7.    cursor_name := DBMS_SQL.OPEN_CURSOR;
  8.    DBMS_SQL.PARSE(cursor_name, 'DELETE FROM '||p_tab_name,
  9.                DBMS_SQL.NATIVE );
  10.    p_rows_del := DBMS_SQL.EXECUTE (cursor_name);
  11.    DBMS_SQL.CLOSE_CURSOR(cursor_name);
  12. END;
  13. /
  14.  
  15. VARIABLE deleted NUMBER
  16. EXECUTE  delete_all_rows('employees', :deleted)
  17. PRINT deleted
  18.  
Feb 8 '08 #6

Post your reply

Sign in to post your reply or Sign up for a free account.

Question stats

  • viewed: 7103
  • replies: 5
  • date asked: Dec 24 '07
BYTES.COM © 2019 About Bytes | Advertise on Bytes | Privacy Policy | Manage Cookies
Sitemap | Oracle Database Answers Sitemap | Oracle Database Insights Sitemap

Follow us to get the Latest Bytes Updates