Crystal 10 and Oracle 10i Stored Procedure problem 2

[Pluck]

The following package and stored procedure works without any problems

create or replace PACKAGE Test_Package2
AS TYPE Test_Type IS REF CURSOR;
END Test_Package2;

create or replace PROCEDURE eam_get_position_reports (
Report_Cursor IN OUT eam_get_position_reports_pkg.rpt_type,
Account_Code IN cs_fund.ACCT_CD%TYPE)
AS
BEGIN

OPEN Report_Cursor FOR
select f.acct_typ_cd,
f.inactive,
f.allow_buy,
f.allow_sell,
f.acct_name,
f.crrncy_cd
from cs_fund f
where f.acct_cd = Account_Code;
END eam_get_position_reports;

When I create a new report I see the stored procedure and
the parameter works as expected. life is good
-----------------------------------------------------

Now I need to add one more table. The following works when running in sql developer, Crystal sees the procedure but it blows. ora 20003 oru 10036 is invalid and cannot be described

The package is identical other than a different name so I included here

create or replace PACKAGE eam_get_position_reports_pkg2
AS TYPE rpt_type IS REF CURSOR;
END eam_get_position_reports_pkg2;

create or replace PROCEDURE eam_get_position_reports2 (
Report_Cursor IN OUT eam_get_position_reports_pkg2.rpt_type,
Account_Code IN cs_fund.ACCT_CD%TYPE)
AS
BEGIN

OPEN Report_Cursor FOR
select f.acct_cd,
f.acct_name,
f.crrncy_cd,
p.mkt_val_sod,
p.qty_sod,
p.mkt_val_intraday,
p.qty_intraday
from cs_fund f, cs_position p
where p.acct_cd = f.acct_cd
and f.acct_cd = Account_Code ;
END eam_get_position_reports2;

-----

I have actually had Crystal look at it and they don't have a clue. Does anyone have a simple Oracle stored procedure that goes against more than one table that works. I have used sql for years and this is driving my bonkers arghhhh

if someone does have an example I can plagerize please send to pluckon@carolina.rr.com

Thanks a LOT

 

[satinsilhouette]

I am wondering if the f.acct_cd being equal to two things in the where clause is what CR is hung up on. Usually you have to do a union or something to get crystal to take it.

Thanks so much!
satinsilhouette

 

[synapsevampire]

Satin: One is the join, the other is a parameter passed to the SP, the code looks fine.

Perhaps the difficulty is the connectivity being used, they should try changing from ODBC to Native or vice versa. They didn't bother to state how they were connecting...

-k

 

[Pluck]

Actually CR got back to me with the answer - There were 2 issues - here is the package and sp and they work fine - thanks for getting back to me

SQL> create or replace package test_package_tables
2 as
3 TYPE test_type_record is RECORD
4 (acct_cd CRD.cs_fund.acct_cd%type,
5 acct_name CRD.cs_fund.acct_name%type,
6 mkt_val_sod number (18,4),
7 qty_sod number (18,4) ) ;
8 TYPE test_type is ref cursor return test_type_record ;
9 end test_package_tables;
10 /

Package created.

SQL> create or replace procedure test_procedure_tables(
2 test_cursor in out test_package_tables.test_type,
3 test_parameter1 in cs_fund.acct_cd%type)
4 is
5 begin
6 open test_cursor for select cs_fund.acct_cd,
7 cs_fund.acct_name,
8 cs_position.mkt_val_sod,
9 cs_position.qty_sod
10 from cs_fund ,
11 cs_position
12 where cs_fund.acct_cd = cs_position.acct_cd
13 and cs_fund.acct_cd = test_parameter1;
14 end test_procedure_tables;
15 /

 

[synapsevampire]

Ahhh, didn't define the record in the package, sorry, it's been a while since I built SPs in Oracle.

-k