Dynamic Variable in Cursor in Oracle stored procedure

Question

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

Possible duplicate of: stackoverflow.com/questions/15786953/… Also See: asktom.oracle.com/pls/apex/… — xQbert
– xQbert, Commented Dec 31, 2014 at 14:13
partially same but the solution still doesn't work for the procedure example above — user3723562
– user3723562, Commented Dec 31, 2014 at 14:27
Typically: sql_text varchar2;cur sys_refcursor; begin open cur for sql_text; end; — user2672165
– user2672165, Commented Dec 31, 2014 at 14:40

user3723562 · Accepted Answer · 2014-12-31 16:39:10Z

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

answered Dec 31, 2014 at 16:39

user3723562

731 gold badge3 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

xQbert Over a year ago

Applaud the self effort in taking the hints and apply them.

Collectives™ on Stack Overflow

Dynamic Variable in Cursor in Oracle stored procedure

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related