Re: Creating Datawindows in PB based on Oracle Stored Procedures

The first thing seems to be the way the stored procedure is declared. The general rule is that the package header should contain the cursor declaration and the stored procedure declaration, as you have done, but the procedure declaration in the package header must only declare the reference cursor as a parameter. Don't declare the other parameters. When you CREATE the procedure you should then declare the parameters but they must appear BEFORE the reference cursor declaration.

The second thing to look for is to make sure that the SELECT statement matches the RECORD exactly, otherwise you'll get the error you mentioned. You can test this by having the reference cursor return the table %ROWTYPE instead of declaring a record and then having a SELECT *, just to see if it works OK.

Also, the easiest way to test this through SQL*PLUS is to declare a variable as follows:

variable c refcursor
Then execute the procedure using EXECUTE <proc> (<any parameters, :c) The :c represents the refcursor variable you have just declared. Then use 'print c to' show the results

Hope this helps

Regards

Michael

>You need to create an anonymous PL/SQL block to test this in SQL*Plus.
>Something like:
>
>set serverout on
>declare
> cvar PACK_MYPORT_OFFICIER.C_MYPORT_OFFICIER ;
> crec PACK_MYPORT_OFFICIER.R_MYPORT_OFFICIER ;
>begin
> P_MYPORT_OFFICIER(100, cvar);
> fetch cvar into crec;
>
> while cvar%found loop
> dbms_output.put_line(crec.comapny);
> fetch cvar into crec;
> end loop;
>end;
>
>
>HTH
>
>Rob
>In article <6l72sh$d4r$1_at_nnrp1.dejanews.com>,
> vyeddanapalli_at_my-dejanews.com wrote:
>>
>> I need to create a datawindow in Powerbuilder that would show a resultset
>> from a stored procedure in Oracle with parameters being passed from
>> powerbuilder as retrieval argument.We have created a Package and a stored
>> procedure in Oracle so that the result set can be viewed in a DataWindow
 in
>> Powerbuilder. We created an Oracle Package first; a result set
 structure
>> is defined in the package, as well as the Reference Cursor, which is set
 to
>> return a result set of the type defined in the same package. The stored
>> procedure uses the reference cursor as an IN OUT, as well as an IN
 prameter
>> that is being passed to the procedure. When I execute the
 procedure from
>> SQLPLUS I get an error message saying: "Wrong number or types of
 arguments
>> in call to the procedure" . I'm not sure what the problem is. I
>> followed the structure for creating the package and the stored procedure
 as
>> suggested by the Powersoft faxline document ID = 44553. The Package and
>> Procedure code is as follows:
 Create OR Replace Package PACK_MYPORT_OFFICIER
>> ISTYPE R_MYPORT_OFFICIER IS RECORD(company account.company%type,city
>> account.city%type,postal account.postal%type,cust_overall_stat
>> account_shadow.cust_overall_stat%type,comp_indiv_indicator
>> account_shadow.comp_indiv_indicator%type,address_1
 account.address_1%type,
>> state_prov account.state_prov%type,phone_num phone.phone_num%type,
>> sales_vol account.sales_vol%type,last_call call_item.call_date%type,
>> next_call call_item_shadow.follow_up_date%type,name
 employee.name%type );
>> TYPE C_MYPORT_OFFICIER IS REF CURSORRETURN R_MYPORT_OFFICIER ;
PROCEDURE P_MYPORT_OFFICIER (EMP_NUM IN employee.employee%type, OUTREC IN OUT
>> C_MYPORT_OFFICIER);END PACK_MYPORT_OFFICIER;/
CREATE OR REPLACE PROCEDURE
>> P_MYPORT_OFFICIER(EMP_NUM IN employee.employee%type,OUTREC IN OUT
>> PACK_MYPORT_OFFICIER.C_MYPORT_OFFICIER ) ASBEGINOPEN OUTREC FOR Select
>> ...........;END P_MYPORT_OFFICIER ;/
>>
>> -----== Posted via Deja News, The Leader in Internet Discussion ==-----
>> http://www.dejanews.com/ Now offering spam-free web-based newsreading
>>
>
>
>-----== Posted via Deja News, The Leader in Internet Discussion ==-----
>http://www.dejanews.com/ Now offering spam-free web-based newsreading
Received on Fri Jun 05 1998 - 00:00:00 CEST