Re: Stored Procedures

["Jeff" <jsouthworth_at_dsitopdog.com>]
|
| Help. I'm trying to create a procedure that returns values from a
| select. I would like to pass the procedure an account number, then
| pull back Name and address info based on the number. I am having a
| terrible time trying to get this accomplished. Any help is greatly
| appreciated.

create a stored procedure that passes back a REF CURSOR through the argument list of the stored procedure. an example from the OCI manual for Oracle 7.3 contains the following sample code:

   create or replace package oci11pkg as

           type rectype is record (cname ocicolu.cname%type,
                                   clength ocicolu.clength%type,
                                   colid ocicolu.colid%type);
           type ctype is ref cursor return rectype;
            
           procedure oci11proc(
                   curs      in out  ctype,
                   tabname in varchar2);
   

   end;
   /    

   create or replace package body oci11pkg as     

           procedure oci11proc(
                   curs in out ctype,
                   tabname in varchar2
   
           ) is
    
           begin
    
                   open curs for 
                   select cname, clength, colid 
                   from ocicolu
                   where tname = tabname;
                   
    
           end;
    

   end;
   /

I have used this technique successfully in one of my applications, but it doesn't seem as if there are great many people who actually take advantage of this.

my application used this in order to grab result sets from a stored procedure and use the data in an application written in C.    

-Bjørn

-- 
 Bjørn Borud <borud_at_guardian.no>       | "The Net interprets censorship 
 <URL:http://www.pvv.unit.no/~borud/>  | as damage and routes around it."
 UNIX person, one of "them"            |         - John Gilmore