Calling Oracle Stored Procedure

Hi all

I am trying to call oracle stored procedure from SRSS 2005. I am using the syntax { Call s_test_rcur()} .

I am getting following error.

Any suggestions?

Thanks in advance

Mvr

An error occurred while retrieving the parameters in the query.
ORA-00911: invalid character
ORA-06512: at "SYS.DBMS_UTILITY", line 68
ORA-06512: at line 1

ADDITIONAL INFORMATION:

ORA-00911: invalid character
ORA-06512: at "SYS.DBMS_UTILITY", line 68
ORA-06512: at line 1
(System.Data.OracleClient)

Here is the Oracel stored proc.

TYPE rc_test IS REF CURSOR;

PROCEDURE s_test_rcur (
po_test_rc OUT rc_test, -- returns a record set
po_error OUT INTEGER
)
IS

BEGIN
g_err_level := 1;

OPEN po_test_rc FOR
SELECT a.ssn_id,
TO_CHAR (a.acad_yr) || TO_CHAR (a.acad_yr + 1) acad_yr,
RPAD (NVL (last_name, ' '), 30, ' ') last_name,
RPAD (NVL (first_name, ' '), 30, ' ') first_name,
NVL (middle_initial, ' ') middle_initial
from test_table
order by last_name;

po_error := 0;
EXCEPTION
WHEN OTHERS
THEN
po_error := -1;
g_error_code := SQLCODE;
g_error_msg :=
'Err level :' ||
TO_CHAR (g_err_level) ||
' ' ||
SUBSTR (SQLERRM, 1, 250);

END;

You may want to read the following related thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=356162&SiteID=1

Basically, you need to write a wrapper stored procedure that has only one OUT REF cursor and no other OUT parameters.

-- Robert