Error using Cursors

Question

Hi

I have a set of SQL server scripts that I ran thru a migration tool to make them Oracle (10G) compatible. The stored procedure is implemented using cusors as it returns a table in SQL server. I am getting the error below

ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'SP_COURSE_GET' ORA-06550: line 1, column 7: PL/SQL: Statement ignored

Below is my stored procedure..

CREATE OR REPLACE PROCEDURE SP_COURSE_GET
(
course_id IN INT DEFAULT NULL,
RCT1 IN OUT GLOBALPKG.RCT1
)
AS
BEGIN

OPEN RCT1 FOR
SELECT
A .*,
B.test_name,
B.test_status
FROM COURSES A,
TESTS B
WHERE A.xtest_id = B.test_id (+) and (A.course_id = SP_COURSE_GET.course_id);
RETURN;
END;

And here is the associated package..

CREATE OR REPLACE PACKAGE GLOBALPKG
AS
TYPE RCT1 IS REF CURSOR;
TRANCOUNT INTEGER := 0;
IDENTITY INTEGER;
END;

Can an expert please shed some light as to why I am getting this error? Thanks in advance or your advice

Cheers

Answer 1

Sorry, no Oracle experts here. But I'd call the parameter something other than 'RCT1' just to be safe ...

Answer 2

assuming GLOBALPKG.RCT1 is a cursor, it only needs to be an OUT cursor, not an in out cursor.

From what you posted it looks like you are either not supplying both parameters to the proc when you call it, or one or more of them is of the wrong type.
course_id should be an int (of course) and RCT1 will depend on the language you are calling it from

IN c#, using the MS Oracle driver for example, you would set the OracleType to OracleType.Cursor and the Direction to ParameterDirection.Output

hth.
#