ORA-06550/PLS-00306: package stored proc with out ref cursor

Discussion of open issues, suggestions and bugs regarding Entity Framework support in ADO.NET Data providers
Post Reply
r.m.saddler
Posts: 33
Joined: Thu 20 May 2010 08:57
Location: Wales, UK.

ORA-06550/PLS-00306: package stored proc with out ref cursor

Post by r.m.saddler » Mon 28 Jun 2010 10:11

Hi, I am getting the following exception when trying to call a simple stored procedure that returns a ref cursor in an out parameter. Here is the SP:

PROCEDURE isPasswordValid (
i_UserID IN UWS_USERS.userid%TYPE,
i_Password IN UWS_USERS.PASSWORD%TYPE,
o_rs OUT UWS_SCHEMA.UWS_TYPES.recordset) AS
-- declarations
encrypted_password UWS_USERS.PASSWORD%TYPE;
db_password UWS_USERS.PASSWORD%TYPE;
vResult VARCHAR2 (5);
BEGIN
encrypted_password := uws_schema.uws_tools.encrypt (i_Password);

SELECT PASSWORD
INTO db_password
FROM UWS_USERS
WHERE userid = i_UserID;

IF (encrypted_password = db_password) THEN
vResult := 'True';
ELSE
vResult := 'False';
END IF;

OPEN o_rs FOR
SELECT vResult
FROM DUAL;
EXCEPTION
WHEN OTHERS THEN
OPEN o_rs FOR
SELECT 'False'
FROM DUAL;
END isPasswordValid;

and here is the exception:

{"ORA-06550: line 2, column 3:
PLS-00306: wrong number or types of arguments in call to 'ISPASSWORDVALID'
ORA-06550: line 2, column 3:
PL/SQL: Statement ignored"}

I added the SP call to my edml by dragging it on in the normal way.

I have looked at the instructions here http://www.devart.com/dotconnect/oracle ... rsors.html for calling stored procs that return ref cursors but most of the manual editing of the xml file wasn't necessary as nearly all the 'missing' bits were already present - I assume these notes need updating for the latest release (I am using 5.70.140.0). The only thing I did have to add was:

devart:ResultSetParameterName="o_rs"

Doing this however resulted in a different exception:

"The data reader is incompatible with the specified 'HR.Web.Data.IspasswordvalidResult'. A member of the type, 'False', does not have a corresponding column in the data reader with the same name."

I am doing something wrong? This seems like such basic functionality?

For reference the Storage Model Schema entry looks like this (before my manual edit):






and the Conceptual Model looks like this:










And the Mapping looks like this:



Many thanks,

Rob.

r.m.saddler
Posts: 33
Joined: Thu 20 May 2010 08:57
Location: Wales, UK.

Post by r.m.saddler » Mon 28 Jun 2010 10:22

Oh... OK... Just dawned on me. I have aliased the column in the select statement as so:

OPEN o_rs FOR
SELECT vResult "IsValid"
FROM DUAL;

Then, using the Model Explorer, rename the property in the complex return type to IsValid too.

Everything then works as expected.

Just a quick question though - will the Result Set Parameter Name be added automatically in a future release?

Cheers,

Rob.

Shalex
Site Admin
Posts: 8245
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Tue 29 Jun 2010 15:52

The Values Types property in Entity Developer's Method Editor can be set only manually at the moment. We will investigate the possibility of automatical population of the Value Types property. For more information, please refer to our blog article: http://www.devart.com/blogs/dotconnect/?p=5.

r.m.saddler
Posts: 33
Joined: Thu 20 May 2010 08:57
Location: Wales, UK.

Post by r.m.saddler » Tue 29 Jun 2010 15:54

OK, that's great - thanks for the update.

Post Reply