Re: PL/SQL Problem/Question

From: Timothy Taylor <ttaylor_at_us.oracle.com>
Date: 1997/06/03
Message-ID: <33946D2D.5229@us.oracle.com>#1/1

snip
> >
> >PROCEDURE test1 (mcc_rec IN OUT mco_contract_control%ROWTYPE)
> >IS
> >BEGIN
> >SELECT *
> >INTO mcc_rec
> >FROM mco_contract_control
> >WHERE mco_contract_control.mco_cntrct_num = mco_cntrct_num;
> >
> >END;
> >
 

>
> I spent a lot of time trying to do the same type of thing in ProC. I
> contacted Oracle support and they first told me it could be done but then
> they couldn't get it to work. The problem is that the Pro? languages don't
> seem able to pass records or tables between the Pro? code and a stored
> procedure.
>
> I have since learned that it should work if the stored procedure is in a
> package and the record or table is declared there. I haven't tried it yet
> so I can't guarantee that it will work, but it's worth a try.
>

The bad news: You are correct, you can't return a PL/SQL record to a Pro*C structure.
The good news: You can accomplish most of what you want by using PL/SQL cursor variable. Here's the basic outline (I'm making this up from memory so don't be surprised if it doesn't compile...):

package MyPkg is

        type t_CurType is ref cursor return emp%ROWTYPE;

	procedure GetAllEmps (p_Cursor in out t_CurType);
	procedure FindEmps (p_Cursor in out t_CurType, p_Name in varchar2);

end MyPkg;

package body MyPkg is         

	procedure GetAllEmps (p_Cursor in out t_CurType) is
	begin
		open p_Cursor for select * from emp;
	end GetAllEmps;

	procedure FindEmps (p_Cursor in out t_CurType, p_Name in varchar2) is
	begin
		open p_Cursor for select * from emps where ename = p_Name;
	end FindEmps;

end MyPkg;

in Pro*C you need to define a cursor variable, allocate the cursor (a cursor variable is essentially a pointer to a cursor), define a PL/SQL record variable, call the procedure that opens the cursor, loop over the resulting rows, and close the cursor. I can't remember the syntax for all of the Pro*C stuff. Email me privately and I can find it somewhere.

Tim Received on Tue Jun 03 1997 - 00:00:00 CDT