PLS-00306 when calling an Oracle package procedure through a private synonym that returns a cursor.

Home -> Community -> Usenet -> c.d.o.server -> PLS-00306 when calling an Oracle package procedure through a private synonym that returns a cursor.

From: Roel Schreurs <schreurs_roel_at_hotmail.com>
Date: 20 Dec 2004 23:35:44 -0800
Message-ID: <321ebdef.0412202335.1ebbe1a8@posting.google.com>

Hi All,

I am using ADODB to retrieve data from an Oracle 8.1.7. server. I have a package that contains a procedure that returns a REF CURSOR. In my client code I use a ADODB.Command object to execute the procedure and a ADODB.RecordSet object to access the results.

Now I want to use a private synonym to be able to access the package from another schema. This fails with the following error: PLS-00306: wrong number or types of arguments in call to '<Procedure>'.

A function in the package can be executed through the synonym without problems.
An SQL script (see 6.) succeeds when executed using a SQL tool (Toad).

Below are the steps to reproduce this behaviour.

I suppose this has something to do with the way the OraOLEDB provider treats the cursor that is returned as an out parameter.

I have posted this to microsoft.public.data.ado before, since this has something to do with the cooperation between oracle and ado, but no reactions there...

Any help would be appreciated.

Roel Schreurs

Package
CREATE OR REPLACE PACKAGE TestPackage AS

FUNCTION GetValue
RETURN VARCHAR2; TYPE ValueRecType IS RECORD

( Value VARCHAR2(10));

TYPE ValueCurType IS REF CURSOR RETURN ValueRecType;

PROCEDURE GetValueCur
( cur OUT ValueCurType);

END TestPackage;

2. Package body

CREATE OR REPLACE PACKAGE BODY TestPackage AS

    FUNCTION GetValue
    RETURN VARCHAR2
IS
BEGIN
     RETURN 'Succesful!';
END;     PROCEDURE GetValueCur
( cur OUT ValueCurType)
IS
BEGIN
   OPEN cur FOR
  SELECT 'Succesful!' AS "Value"
  FROM DUAL;
END; END TestPackage;

3. Synonym

create synonym TestSynonym for TestPackage

4. Demo ASP that fails. Please adjust <data source>, <user> and <password>.
(includes commented-out alternatives that work.)

<% Option Explicit
Response.Expires = 0%>
<html>

<body>

<center>
<h1>Synonym tester</h1>
<%

    Dim objConnection
    Dim objCommand
    Dim objRecordset
    Dim DBError

Set objConnection = Server.CreateObject("ADODB.Connection") With objConnection

        .ConnectionString = "Provider=OraOLEDB.Oracle;" & _
            "Data Source=" & "<data source>" & ";" & _
            "User ID=" & "<user>" & ";" & _
            "Password=" & "<password>" & ";" & _
            "PLSQLRSet=1;enlist=false;"
        .Open

End With

Set objCommand = Server.CreateObject("ADODB.Command") Set objRecordset = Server.CreateObject("ADODB.RecordSet")

With objCommand

        Set .ActiveConnection = objConnection
        .CommandType = 1
 'These three will work.
        '.CommandText = "SELECT testpackage.GetValue AS Value from
dual"
        '.CommandText = "{call testpackage.GetValueCur}"
        '.CommandText = "SELECT testsynonym.GetValue AS Value from

dual"
'This one will fail.

        .CommandText = "{call testsynonym.GetValueCur}"
        Set objRecordset = .Execute()

End With

Response.Write "Selected Value: " &
objRecordSet.Fields("Value").Value
%>

</center>
</body>
</html>

5. Error message

OraOLEDB (0x80040E14)
ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'GETVALUECUR' ORA-06550: line 1, column 7: PL/SQL: Statement ignored
Test.asp, line 42

6. SQL Script that works fine.

BEGIN
DECLARE ValueRec TestSynonym.ValueRecType;

         ValueCur TestSynonym.ValueCurType; BEGIN
  TestSynonym.GetValueCur(ValueCur);
  FETCH ValueCur INTO ValueRec;
  DBMS_OUTPUT.PUT_LINE('Selected value: ' || ValueRec.Value); END;
END;

Received on Tue Dec 21 2004 - 01:35:44 CST