Home » SQL & PL/SQL » SQL & PL/SQL » procedure result inserting into file now want to as result set  () 1 Vote
procedure result inserting into file now want to as result set [message #252684] Thu, 19 July 2007 11:35 Go to next message
madan30
Messages: 2
Registered: July 2007
Location: Pune
Junior Member
Hi,
the current Stored Procedure is writting data to One file line by line.But now i want this procedure to be called by one application there result should be return(same data which written to file should be return to application).So how to modify this query please help:
here is the query.....
Re: procedure result inserting into file now want to as result set [message #252837 is a reply to message #252684] Fri, 20 July 2007 04:02 Go to previous messageGo to next message
user82
Messages: 49
Registered: June 2006
Location: Romania
Member
Try to modify your procedure into a function that returns a cursor:

CREATE OR REPLACE FUNCTION Pr_Get_Grid_Latest RETURN <cursor_type_variable>
........
........
........
RETURN c_scode;
Re: procedure result inserting into file now want to as result set [message #252904 is a reply to message #252837] Fri, 20 July 2007 10:05 Go to previous messageGo to next message
madan30
Messages: 2
Registered: July 2007
Location: Pune
Junior Member
didn't get the thing please explain the code
Re: procedure result inserting into file now want to as result set [message #252972 is a reply to message #252684] Sat, 21 July 2007 02:13 Go to previous messageGo to next message
user82
Messages: 49
Registered: June 2006
Location: Romania
Member
First of all, i have no time to guide you, i just give some hints and you may Google for functions and ref cursors....

So , if you want the procedure to return some result set in an application, i think the procedure should be a function, and the function should return a ref cursor. At first you should have a ref cursor type (so i suggest you to write an package...), try something like this:

create or replace package My_package is
type CRS is ref cursor;
FUNCTION Pr_Get_Grid_Latest return CRS
-------------------------------------

create or replace package body My_package is

FUNCTION Pr_Get_Grid_Latest return CRS is
begin
/*

you code here

*/
RETURN c_scode;
end;

end My_package


for more details try to Google !!!


Re: procedure result inserting into file now want to as result set [message #253021 is a reply to message #252684] Sat, 21 July 2007 09:39 Go to previous message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
user82,
I do have not the idea too how you would propagate the result (function applied to rows in a cursor) into cursor (stored SQL statement).
You could construct the cursor from the current one; then you would not need any code (loops through cursor), just the updated cursor definition.

However (as the first shot) I would use collection as a return value, making this function pipelined.

madan30: Search for Pipelined Function in PL/SQL User's Guide and Reference. Also read about collection types.
Previous Topic: Difference of between clause in number&varchar2 fields
Next Topic: utl_file
Goto Forum:
  


Current Time: Wed Dec 07 14:42:47 CST 2016

Total time taken to generate the page: 0.08703 seconds