-
hi,
I am trying to pass the table name dynamicaly to the query and want to fetch the records.
I went through the forum database and got this solution
(((
You cannot use tablenames as variables in cursors. In 8.1.6 you can use cursor variables with dynamic select statements.
declare
type refcur_ty is ref cursor;
refcur refcur_ty;
emprec emp%rowtype;
tablename varchar2(30);
begin
tablename := 'EMP';
open refcur for 'select * from '||tablename;
fetch refcur into emprec;
close refcur;
dbms_output.put_line(emprec.ename);
end;
)))
This solution is working but outputs only the first record, the other records are not display, could you help me.
Badrinath
-
You have to get all the records in the cursor via a loop:
open refcur for 'select * from '||tablename;
LOOP
fetch refcur into emprec;
EXIT WHEN refcur%NOTFOUND;
dbms_output.put_line(emprec.ename);
END LOOP;
close refcur;
end;
Jeff Hunter
-
Thanxs,
It's working now
Badrinath
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|