CODEWITHSUNDEEP
oracle-databasestored-procedurescursor
user3723562
user3723562

Reputation: 73

Dynamic Variable in Cursor in Oracle stored procedure

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

Upvotes: 0

Views: 1049

Answers (1)

user3723562
user3723562

Reputation: 73

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

Upvotes: 1

Related Questions