Calling results from Oracle API into ASP

[Ferrian]

I am currently working with a colleague trying to get a return from a PL/SQL API into ASP. The API is called 'authenticate_api' and the part I need is coded in the PL/SQL as:

FUNCTION get_access(i_username IN VARCHAR2,
                    i_password IN VARCHAR2) RETURN VARCHAR2;
   c_approved  CONSTANT VARCHAR2(10) := 'APPROVED';
   c_denied    CONSTANT VARCHAR2(10) := 'DENIED';
   c_suspended CONSTANT VARCHAR2(10) := 'SUSPENDED';
   c_expired   CONSTANT VARCHAR2(10) := 'EXPIRED';

In the ASP code I am using the following to try and call the API function:

Set rs = Server.CreateObject ("adodb.Recordset")
Set cmd = Server.CreateObject ("adodb.Command")
		
cmd.Parameters.Append cmd.CreateParameter("b_username",adVarChar, adParamInput, 255, session("username"))
cmd.Parameters.Append cmd.CreateParameter("b_password",adVarChar, adParamInput, 32, strPassword)
cmd.Parameters.Append cmd.CreateParameter("b_return",adVarChar, adParamReturnValue, 32, api_return)
		
strsql = "Begin ? = authenticate_api.get_access(?, ?) End "
				
cmd.CommandText = strsql
cmd.CommandType = adCmdText
cmd.ActiveConnection = portal_app
Set rs = cmd.Execute
		
response.write("Api Return: " & api_return)
		
set rs = nothing
set cmd = nothing

const adParamInput =2, const adParamReturnValue =4, which I believe are correct.

Anybody got any ideas of what I'm doing wrong, or even where I can look for more help on this subject? I've been looking around for quite a while now and am getting frustrated!

Thanks all.

 

[Ferrian]

Nevermind, apologies, I wasn't thinking, forgot to add in connection settings.
Thanks anyone who looked into this!