Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

how to use referential cursor in this context

807875Mar 7 2011 — edited Mar 7 2011
Hello Everybody I want to create a procedure and i am able to create it however it is not working the way i want it to be
kindly suggest the better way.
CREATE OR REPLACE PROCEDURE UPDATE_COMMAND(V_TABLE_NAME VARCHAR2, V_COLUMN_NAME VARCHAR2)
AS

V_SQL CLOB;
V_SQL_1 CLOB;
TYPE V_SQL_2 IS REF CURSOR;
V_SQL_22 V_SQL_2;
V_SQL_23 VARCHAR2(2000);
A clob;

BEGIN

V_SQL :='UPDATE '||V_TABLE_NAME ||'SET '||V_COLUMN_NAME ||' = ';

V_SQL_23 := 'SELECT'|| V_COLUMN_NAME|| ' FROM ' ||V_TABLE_NAME;

OPEN V_SQL_22 FOR V_SQL_23;

loop
   FETCH V_SQL_22 INTO A;
 
  V_SQL_1 :=V_SQL | |V_COLUMN_NAME;

   dbms_output.put_line(v_sql_1);
   END LOOP;
   
   CLOSE V_SQL_22;
   
   END;
first of all i don't know how to reference to this v_coluimn_name which i am using just after the fetch command.

when i execute procedure it gives error

ORA-06550: line 1, column 22:
PLS-00357: Table,View Or Sequence reference 'QM_PRODUCT' not allowed in this context
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
This post has been answered by BluShadow on Mar 7 2011
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Apr 4 2011
Added on Mar 7 2011
6 comments
149 views