Ref Cursor Oracle 9i and Crystal Report 1

[TheEntertainer]

Hello,

I've not even got as far as crystal! I'm just trying to create a simple package with which to report off. This is what I've got so far:

CREATE OR REPLACE PACKAGE Test_Package
AS TYPE Test_Type IS REF CURSOR RETURN table%ROWTYPE;
PROCEDURE Test_Procedure (
Test_Cursor IN OUT Test_Type,
Test_Parameter IN table.yearcode%TYPE
);
END Test_Package;

it says it compiled fine

THEN

CREATE OR REPLACE PACKAGE BODY Test_Package
AS
PROCEDURE Test_Procedure (
Test_Cursor IN OUT Test_Type,
Test_Parameter IN table.yearcode%TYPE
) IS
BEGIN
OPEN Test_Cursor FOR
SELECT *
FROM table
WHERE table.yearcode = Test_Parameter;
END Test_Procedure;
END Test_Package;
/

this also compiles successfully

Then to execute the stored procedure to see if it works, you're supposed to enter the following:

declare
test_cursor test_package.test_type;
resultset test_cursor%rowtype;
begin

test_procedure(test_cursor, '06');
if not test_cursor%isopen then
dbms_output.put_line('the cursor is not open');
else
dbms_output.put_line('the cursor is open');
end if;
fetch test_cursor into resultset;
while test_cursor%found loop
dbms_output.put_line(resultset.yearcode);
dbms_output.put_line(resultset.First_name);
dbms_output.put_line(resultset.Last_name);
dbms_output.put_line(resultset.calendarperiod);
fetch test_cursor into resultset;
end loop;
end;

but it keeps saying:

test_procedure(test_cursor, '06');
*
ERROR at line 5:
ORA-06550: line 5, column 1:
PLS-00201: identifier 'TEST_PROCEDURE' must be declared
ORA-06550: line 5, column 1:
PL/SQL: Statement ignored

What I'm I doing wrong?

This is vitual a copy of a document from crystal showing how to create a ref cursor to report from in crystal. I've even tried copying and pasting the code with the exception of changing the table references so that it looks at my table and it will just not work!

Thanks for any help

 

[stefanhei]

Your procedure is inside a package, so you have to use

...
Test_Package.test_procedure(test_cursor, '06');
...

to call it.

Stefan

 

[TheEntertainer]

Stefan,

Thanks for that, I've been trying to figure it out and just couldn't. You'd think whoever wrote it would have tested it and then they would come up with the same error. Anyway it working now, it even returning data into Crystal.

Just one question for anyone. This seems very complicate just to get a record set, package, package body and procedure etc is this standard or is there a simplier way?

Thanks again.