0

Need help in passing dynamic variable (table name) in cursor in Oracle stored procedure.

My stored procedure:

CREATE OR REPLACE PROCEDURE ABCDEF
    (TBL_NAME IN VARCHAR)
IS 

CURSOR CUR IS SELECT * FROM TABLEA 

BEGIN

FOR rec
IN CUR
LOOP
.
.
.
END

I NEED THIS TABLEA in cursor to be replaced by TBL_NAME variable. I tried to make the cursor statement as executable statement but it didn't help me.

Suggestions, please

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user3723562
  • 73
  • 1
  • 2
  • 15
  • 1
    Possible duplicate of: http://stackoverflow.com/questions/15786953/cursor-for-loop-with-dynamic-sql-statement Also See: https://asktom.oracle.com/pls/apex/f?p=100:11:5045785413478::::P11_QUESTION_ID:1288401763279 – xQbert Dec 31 '14 at 14:13
  • partially same but the solution still doesn't work for the procedure example above – user3723562 Dec 31 '14 at 14:27
  • Typically: `sql_text varchar2;cur sys_refcursor; begin open cur for sql_text; end;` – user2672165 Dec 31 '14 at 14:40

1 Answers1

1
Made this working using part of the solution above. Thanks for the suggestions. 

CREATE OR REPLACE PROCEDURE ABCDEF
(
   TBL_NAME IN VARCHAR
) IS 

TYPE curtype IS REF CURSOR;
cur curtype;
column1 number;
column2 number;

cursor sql := 'Select * from ' ||tbl_name;

Begin
open cur for cursor_sql;
fetch cur into column1,column2;

loop
.
.
.
close cur;
End
user3723562
  • 73
  • 1
  • 2
  • 15