Re: beginner: packaging queries?

On Apr 10, 8:46 am, "matt" <reflectio..._at_gmail.com> wrote:
> are procedures and functions the only types of queries that I can put
> into a package?
>
> I have painstakingly created a couple of plsql db queries and saved
> them as *.sql files. Is there a simple way to create a package out
> of these two plsql queries or do I need to re-write them as stored
> procedures/functions? I'm dreading having to translate these into
> sprocs/functions because they contain cursors that i don't yet know
> how to implement.
>
> Thanks.

Procedures and functions are *NOT* queries. They are procedural constructs.
They can return a REF CURSOR, but this is more or less a kludge needed by Mickeysoft products.
Queries can be packaged when you make sure you convert the statement to a cursor in the package *specification*. Associate a TYPE definition to it.
Like this
package mypack is
cursor mycur is
select * from emp;

end;

outside the package you would
define a variable of mypack.mycur%rowtype; open mycur;
fetch mycur into myvar;

etc.

Hth

--
Sybrand Bakker
Senior Oracle DBA