Call oracle stored procedure from ASP VBSCRIPT with input and returned parameters

Question

I connect to an oracle database from an ASP-VBSCRIPT website and want to execute a stored procedure which accepts input parameters as well as returning some. The stored procedure is the following:

 p_std_currency.get_currency_details(input1,input2,input3,input4,output1,output2)

Input 1-4 are the parameters (3rd is date) sent to the procedure whereas output1-2 are the fields/items to where the returned parameters are sent. I have tried to adjust the above into following ASP VBsript with no success:

Set oConn_send = Server.CreateObject ( "ADODB.Connection" )
sConnectString_send = "Provider=MSDAORA.1;Data Source=demodbas;User Id=user Password=pas;" 
oConn_send.Open sConnectString_send
oConn_send.ActiveConnection = oConn_send
oConn_send.CommandType = adCmdStoredProc

oConn_send.Properties("PLSQLRSet") = TRUE

oConn_send.CommandText = "p_std_currency.get_currency_details"

oConn_send.Parameters.Append oConn_send.CreateParameter(9,10,"01-JAN-10",1,crate_value,crate_id_no)
Set objSearch = oConn_send.Execute

Response.Write crate_value
Response.Write crate_id_no

Any ideas? Thanks.

Answer 1

I can see at least these three problems:

• You must call CreateParameter once per parameter, i.e. 6 times.

• You must use ADODB.Command, not ADODB.Connection, to send the command.

• The output parameters can be retrieved using cmd.Parameters(0) and cmd.Parameters(1).

I recommend to have a look at this example: http://support.microsoft.com/kb/164485/en-us. It is for SQL Server, but should also work with Oracle.