How to replce fetch in Refcursor [message #419823] |
Tue, 25 August 2009 13:09  |
bond007
Messages: 64 Registered: March 2009
|
Member |
|
|
Hi experts,
need your help in a ref cursor. The existing code is a big one and not explanatory to you all . So I am giving one sample code which can fix my problem .
There is a table
CREATE TABLE "EMP_TAB"
( "EMP_NM" VARCHAR2(20),
"EMPID" NUMBER(3,0),
"MNG_NM" VARCHAR2(20),
"MNGID" NUMBER(3,0)
);
There is procedure
create or replace procedure test1 is
type cur_type is ref cursor;
c cur_type;
v varchar2(400);
emp_name varchar2(400);
begin
v :='select emp_nm from emp_tab';
open c for v;
loop
FETCH c INTO emp_name;
EXIT WHEN c%NOTFOUND;
dbms_output.put_line(emp_name) ;
end loop;
close c;
end;
/
show errors
My requiremnt is the variable v should be
v :='select * from emp_tab';
insted of of fetch can we write c.EMP_NM
like the following way
create or replace procedure test1 is
type cur_type is ref cursor;
c cur_type;
v varchar2(400);
emp_name varchar2(400);
begin
v :='select emp_nm from emp_tab';
open c for v;
loop
dbms_output.put_line(c.emp_nm) ;
end loop;
close c;
end;
/
show errors
|
|
|
|
|
|
Re: How to replce fetch in Refcursor [message #419836 is a reply to message #419823] |
Tue, 25 August 2009 13:53  |
 |
BlackSwan
Messages: 26766 Registered: January 2009 Location: SoCal
|
Senior Member |
|
|
>My requirement is, Insteed of writng each coloumn and putting all the columns in the fetch clause , is there any other way to code it
This is an ideal solution when you desire to enter bugs into an application long after it has been released.
When you attempt this folly, at some point in the future a new column will be added & your code will break.
|
|
|