Home » SQL & PL/SQL » SQL & PL/SQL » How to replce fetch in Refcursor (oracle 10g)
How to replce fetch in Refcursor [message #419823] Tue, 25 August 2009 13:09 Go to next message
bond007
Messages: 58
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 #419829 is a reply to message #419823] Tue, 25 August 2009 13:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Without fetching c.EMP_NM is meaningless.

Quote:
My requiremnt is the variable v should be...

This can't be a requirement.

Regards
Michel

[Updated on: Tue, 25 August 2009 13:29]

Report message to a moderator

Re: How to replce fetch in Refcursor [message #419831 is a reply to message #419823] Tue, 25 August 2009 13:36 Go to previous messageGo to next message
bond007
Messages: 58
Registered: March 2009
Member
Sorry .
There are more than 12 coloumns in the tables . 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.
Re: How to replce fetch in Refcursor [message #419835 is a reply to message #419831] Tue, 25 August 2009 13:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't be so lazy.

Kyte recently published a new Reason #13134213 Not to Use SELECT *

Regards
Michel
Re: How to replce fetch in Refcursor [message #419836 is a reply to message #419823] Tue, 25 August 2009 13:53 Go to previous message
BlackSwan
Messages: 25032
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.
Previous Topic: How to create procedure
Next Topic: Problem with compiling function (merged 3)
Goto Forum:
  


Current Time: Fri Dec 02 12:10:56 CST 2016

Total time taken to generate the page: 0.13213 seconds