get data in cursor from another stored proc 1

[CraigBest]

Hi Folks

I have a situation where I need to call a stored proc in another package from my own stored proc to validate a value. The foreign stored proc accepts a value parameter to be validated, and returns a cursor with a single varchar value indicating valid / not valid.

I am unsure of how to handle this on my end. I know I have to call the proc by qualifying the package name.proc name (no problem there) and I need to pass the value to the foreign proc as a parameter. But how do I define the cursor returned in my proc? How do I then read the value out of the proc (I assume a Fetch statement of some sort)?

And finally I need to call this validation proc over and over again, so I assume each time I need to call it I have to close the cursor, and them re-open it to get the new value when it is called or there will be an wrror. Is that right?

Would very much appreciate a bit of direction. Thanks!



CraigHartz

 

[SantaMufasa]

Craig,

Could you just clarify the purpose of using a CURSOR instead of a single-valued returning argument?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[CraigBest]

Hi Dave

I agree that would be the better method, but it is a pre-existing canned procedure they would like me to use for the validation, and I don't think it was originally intended to be accessed from another procedure.

CraigHartz

 

[SantaMufasa]

I know that this probably sounds like an obvious question, but have you already tried a standard "OPEN...FETCH...CLOSE" with the returning CURSOR?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[Dagon]

create or replace package pcurs is
  type t_cursor is ref cursor;
  PROCEDURE retcurs(p_val in number,
                    p_curs OUT t_cursor);
end;

create or replace package BODY pcurs is
  PROCEDURE retcurs(p_val in number,
                    p_curs OUT t_cursor) is
  begin
    open p_curs for select p_val from dual;
  end;
end;

declare
  l_curs pcurs.t_cursor;
  l_res  number;
begin
  pcurs.retcurs(1, l_curs);
  fetch l_curs into l_res;
  dbms_output.put_line(l_res);
  close l_curs;
end;

or use the generic sys_refcursor type:

declare
  l_curs sys_refcursor;
  l_res  number;
begin
  pcurs.retcurs(1, l_curs);
  fetch l_curs into l_res;
  dbms_output.put_line(l_res);
  close l_curs;
end;

 

[CraigBest]

Yeah, I think that'll work. Thanks!

CraigHartz

 

[CraigBest]

Dave - I guess that's the problem I was having, trying to figure out how to format the Open, Fetch, Close of the foreign procedure. Dagon's example seems to answer the question, and I'll try it to see if it brings me the result I'm looking for. Otherwise, I'll be back



CraigHartz