package body for oracle 8i

[Guest_imported]

As the size of formula is too big, i cannot write the whole formula in SCR itself, so i thought of making use of stored procedures to filter the records. Even here 'coz of DIANA size limitation and the error is 'program too large error code PLS-00123'. I read in Oracle documentation that there is no size limit if package body is used. I created package body and when i tried to add this thru data explorer in SCR, the error with a dialog box is wrong number or types of arguments in call to....
If anybody knows 'bout this pl. help me. Thanks in advance.

 

[MalcolmW]

Crystal Decisions site has Ask Crystal (Jeeves' girlfriend) - there is a good doc with sample code that comes up using key words Oracle Stored Procedure. Malcolm Wynden
I'm for sale at

http://wynden.net

malcolm@wynden.net

 

[Guest_imported]

I got samples for Stored Procedures from Jeeves gal friend, but she didn't tell me how to do this using Oracle Package body.

 

[MalcolmW]

http://support.crystaldecisions.com/communityCS/TechnicalPapers/scr_oracle_stored_procedures.pdf.asp

has several examples, including stuff like
CREATE OR REPLACE PACKAGE Test_Package
AS TYPE Test_Type IS REF CURSOR RETURN Test_Table%ROWTYPE;
END Test_Package;
Note the differences between strongly and weakly bound REF CURSORs in Oracle packages.
Is that what you're looking for? Malcolm Wynden
I'm for sale at

http://wynden.net

malcolm@wynden.net

 

[Guest_imported]

Thanks Malcolm, I read the document and it says use with native connection in CR8.5. The SCR version which i have is 8.0, so i think with this version its not possible to make reports using Package Body.

 

[Guest_imported]

This is what i did

==> package

create or replace package pat
as type test_type is ref cursor return report_dictque%rowtype;
procedure testprocedure(test_cursor in out test_type,
test_Parameter in number);

end pat;

==> package body

create or replace package body pat
as procedure testprocedure(test_cursor in out test_type,
test_parameter in number) is
begin
open test_cursor for
select * from report_dictque;
end testprocedure;
end pat;

==> this works fine
declare test_cursor pat.test_type;
resultset test_cursor%rowtype;
begin
pat.testprocedure(test_cursor, 12);
fetch test_cursor into resultset;
dbms_output.put_line(resultset.filmdateend);
end;
/

When i tried add this procedure thru data explorer in SCR, it pop ups with an error message "ODBC error : [Merant][ODBC ORACLE driver]...............
...........
ORA-06550: line 1, column 8:
PL/SQL : Statement ignored"

I am using the driver Cror815.dll as mentioned in the document.

Thanks

Satya