Devart Forums

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.

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.

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.

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