Re: Returning results from a Stored procedure

A copy of this was sent to Pinne <pinne_at_pegit.se> (if that email address didn't require changing) On Mon, 25 Oct 1999 23:10:21 +0200, you wrote:

>Thomas Kyte wrote:
>
>> A copy of this was sent to Pinne <pinne_at_pegit.se>
>> (if that email address didn't require changing)
>> On Sun, 24 Oct 1999 21:57:05 +0200, you wrote:
>>
>> >Are these types of statement possible from within an SP:
>> >SELECT col1, col2 FROM Table1;
>>
>> >Is there another way to return result-sets from SP:s
>> >
>> >/Regards
>>
>> See the URL in my signature. I have a short article on returning result sets
>> from stored procedures (a HOWTO)
>>
>
>I tried what You described and it works fine.
>However I would like to do it all from within ONE procedure, ie.
>declare the cursor type(if the type declaration is neccessary), open it and
>print it.
>Do i really need to declare the cursor as Package type, return it and THEN
>print.it.
>I tried to put it all in one but failed (could be a syntax/typo thing, and yes
>I'm new on Oracle)
>

It is a chicken/egg type of problem.

You need to have a prototype on the procedure like:

create or replace procedure some_procedure( X in out CURSOR_TYPE )

but you want to defer declaring the CURSOR_TYPE until you are in the procedure. But cursor_type must be a known type in order to compile the procedure. The procedure cannot be compiled without the type -- hence, the type must be known when (before) you compile the procedure.

I prefer to *never* use procedures or functions in PL/SQL except for trivial examples. It is always suggested to use a package. This is because of the way dependencies and invalidations happen. If you use procedures, every time you recompile a procedure, every other procedure that refers to it will be invalidated and eventually recompiled. If you use a PACAKGE, you break this dependency. If you recompile a package body -- NOTHING else becomes invalid. It is only when you change the package SPEC that other objects might become invalid.

I would always code:

create or replace package my_pkg
as

   type myCursor is ref cursor;

   procedure my_procedure( x in out myCursor ); end;
/

create or replace package body my_pkg
as

procedure my_procedure( x in out myCursor ) is
begin

   open x for ....;
end;

end;
/

that way, when I fix my code in my_procedure -- nothing else is affected. only if I change the interface to my_procedure (by rebuilding my_pkg) will other objects be affected. It is more modular, it is more efficient, it is more flexible.

If you really really really want to use a standalone procedure, you'll need to create one package/database that has your global types in it. All procedures can then use the same types but you will need to define those types in a package spec.

>I can accomplish the same thing in an SQL-server SP by simply
>CREATE PROCEDURE select_something
>SELECT col1, col2 FROM someting
>(Don't know what's done explicitly in the background though)
>/Cheers

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Tue Oct 26 1999 - 07:49:45 CDT