Calling an Oracle function from an ASP page

sparky753 · February 26th, 2012, 04:24 PM

What is the syntax for calling an Oracle function from a Classic ASP page?

This is how I call the function:

Code:

strSQL = "Call PkgName.ProcessMyPaymentTransaction (1234567,'Credit',0,'4111111111111111','06','14',0  .10,6000677,NULL,'Y')"
Conn.Execute(strSQL)

But I get an error message:

Code:

Microsoft OLE DB Provider for Oracle error '80004005' 

ORA-06576: not a valid function or procedure name 

/Secure1/pymtprocess.asp, line 28

Any help would be greatly appreciated!! Thanks
Sean

disel2010 · February 27th, 2012, 04:54 AM

Hi,

it's been a long time since I've done that, but looking at some old code it's basically
> creating an ADO command
> add the stored procedure call to this ADO command
> add each parameter to this ADO command

Here's a small example to get you started...

Code:

	Set cmdItem = Server.CreateObject("ADODB.Command")
	With cmdItem 
		.CommandText = "{call mySchema.myPkg.myProc(?,?)}"
		.CommandType = adCmdText
		.ActiveConnection = conn
		.Prepared = True
		Set prmItem = .CreateParameter("",adNumeric,adParamInput)
		Set prmUser = .CreateParameter("",adVarchar,adParamInput,255)
		.Parameters.Append(prmItem)
		.Parameters.Append(prmUser)
	End With

	With cmdItem 
		.Parameters(0).Value = 1
		.Parameters(1).Value = "USER1"
		Set objRs  = .Execute
	End With

	'...

Hope this helps

sparky753 · February 28th, 2012, 01:01 PM

Thanks for getting back to me. Do you think I'm getting that error message because I didn't specify the schema name? I noticed in your call, you are doing this:

Code:

call mySchema.myPkg.myProc(arg1, arg2, arg3)

I'm currently just calling it this way:

Code:

call myPkg.myProc(arg1, arg2, arg3)

I know your approach, using an ADO command and parameters, is way better than my simple approach but I'm just going to try the simple way to see if I can at least call the function successfully and then switch to the way you've done it.

Thanks again.

disel2010 · February 29th, 2012, 05:34 AM

Hi,

on second read I noticed that you're trying to call an Oracle function, not a procedure. Your errror could thus be the result of not handling the return type...

This sounds like you
- either need to add an additional Oracle parameter object for the return value...

Code:

cmdItem.Parameters.Append("returnval", OracleType.Number).Direction = ParameterDirection.Output;

- or if you have a simple return type, you may also want to do this the really easy way and just call it like this:

Code:

strSQL = "SELECT PkgName.ProcessMyPaymentTransaction(1234567,'Credit',0,'4111111111111111','06','14',0.10,6000677,NULL,'Y') FROM dual"
Set oRS = Conn.Execute(strSQL)

Hope this helps

sparky753 · March 1st, 2012, 12:18 AM

Thanks a bunch! You're definitely steering me in the right direction. My function does have a return value which is a table containing the following:
label VARCHAR2(100),
value VARCHAR2(600)

Would I use this?

Code:

.Parameters.Append("returnval", OracleType.Cursor).Direction = ParameterDirection.Output;

sparky753 · March 1st, 2012, 12:58 AM

Now I get this error message:

ADODB.Command error '800a0bb9'

Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.

Here is my code so far:

Code:

Set cmdItem = Server.CreateObject("ADODB.Command")
	With cmdItem 
		.CommandText = "{myschema.mypkg.myproc (?,?,?,?,?,?)}"
		.CommandType = adCmdStoredProc
		.ActiveConnection = ORConn
		.Prepared = True
		Set param1 = .CreateParameter("",adNumeric,adParamInput)
		Set param2 = .CreateParameter("",adVarchar,adParamInput,255)
		Set param3 = .CreateParameter("",adNumeric,adParamInput)
		Set param4 = .CreateParameter("",adVarchar,adParamInput,255)
		Set param5 = .CreateParameter("",adVarchar,adParamInput,255)
		Set param6 = .CreateParameter("",adVarchar,adParamInput,255)
			
		
		.Parameters.Append(param1)
		.Parameters.Append(param2)
		.Parameters.Append(param3)
		.Parameters.Append(param4)
		.Parameters.Append(param5)
		.Parameters.Append(param6)
		.Parameters.Append("returnval", OracleType.Cursor).Direction = ParameterDirection.Output
	End With

	With cmdItem 

		.Parameters(0).Value = 1234567
		.Parameters(1).Value = "Credit"
		.Parameters(2).Value = 0
		.Parameters(3).Value = "4111111111111111"
		.Parameters(4).Value = "06"
		.Parameters(5).Value = "14"
		
		Set objRs  = .Execute
	End With

Do I need to set the parameter for the return value too? for instance,

Code:

   Set param7 = .CreateParameter("",adVarchar,adParamInput,255)