Home » SQL & PL/SQL » SQL & PL/SQL » Problem with the procedure which is using dbms_sql
Problem with the procedure which is using dbms_sql [message #218130] Tue, 06 February 2007 23:14 Go to next message
world.apps
Messages: 70
Registered: January 2007
Location: Hyderabad
Member

SQL> ed
Wrote file afiedt.buf

  1  CREATE OR REPLACE PROCEDURE delete_all_rows
  2     (p_tab_name IN VARCHAR2, p_rows_del OUT NUMBER)
  3  IS
  4    cursor_name INTEGER;
  5  BEGIN
  6   cursor_name:=DBMS_SQL.OPEN_CURSOR;
  7   DBMS_SQL.PARSE(cursor_name,'DELETE FROM'||p_tab_name,
  8                               DBMS_SQL.NATIVE);
  9   p_rows_del:=DBMS_SQL.EXECUTE(cursor_name);
 10   DBMS_SQL.CLOSE_CURSOR(cursor_name);
 11* END;
SQL> /

Procedure created.

SQL> set serveroutput on
SQL> /

Procedure created.

SQL> show user
USER is "SCOTT"
SQL> select * from nani;

       NUM NAME       ROW_NUM
---------- ---------- ------------------
         1 CHITTI
         2 RAVI

SQL> variable deleted number
SQL> EXECUTE delete_all_rows('NANI',:deleted)
BEGIN delete_all_rows('NANI',:deleted); END;

*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "SYS.DBMS_SYS_SQL", line 826
ORA-06512: at "SYS.DBMS_SQL", line 32
ORA-06512: at "SCOTT.DELETE_ALL_ROWS", line 7
ORA-06512: at line 1



Thanks in advance
Re: Problem with the procedure which is using dbms_sql [message #218155 is a reply to message #218130] Wed, 07 February 2007 00:46 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
If you use dynamic sql, then, while developing, always make sure you display the query to be executed.
Then you would notice you forgot a space.
Re: Problem with the procedure which is using dbms_sql [message #218156 is a reply to message #218130] Wed, 07 February 2007 00:47 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
try to give a blank space after "FROM"
Quote:
DBMS_SQL.PARSE(cursor_name,'DELETE FROM'||p_tab_name,
8 DBMS_SQL.NATIVE);

By
Vamsi
Re: Problem with the procedure which is using dbms_sql [message #218160 is a reply to message #218156] Wed, 07 February 2007 00:59 Go to previous message
world.apps
Messages: 70
Registered: January 2007
Location: Hyderabad
Member
Hi,

I got it.

Thanks
Previous Topic: Date function
Next Topic: Getting Error when using to_char function
Goto Forum:
  


Current Time: Mon Dec 05 12:45:55 CST 2016

Total time taken to generate the page: 0.18900 seconds