Re: ASP + Oracle7 + stored procedure

From: Joel Minton <jminton_at_nowhere.com>
Date: Mon, 22 Mar 1999 08:43:52 -0800
Message-ID: <36F67347.86B58105@nowhere.com>

You do not do it the same way as calling SQL statements. Here is an example of how we call stored procedures from asp:

SET Conn = Server.CreateObject("ADODB.Connection")  Conn.Open "DSN=" + Session("dsn") + ";UID=" + Session("userid") + ";PWD=" + Session("userpass") + ";"
 set cmd = Server.CreateObject("ADODB.Command")  set cmd.ActiveConnection = Conn
 ' Specify the name of the stored procedure you wish to call

 cmd.CommandText = "update_master"
 cmd.CommandType = adCmdStoredProc
 cmd.Parameters.Append cmd.CreateParameter("CASE_NO_V", adDouble,

adParamInput, 0)
 cmd.Parameters("CASE_NO_V") = CaseNum

...Assign other passed in values
cmd.Parameters.Append cmd.CreateParameter("RETCODE", adDouble, adParamOutput) ' Return value

Email me with any questions at joel.minton_at_octel.com.

Joel

A J Hirst wrote:

> Hi folks, hope you can help.
> Using ADO component to connect to Oracle via ODBC
> I have no problems issuing select/insert commands in the .asp file and
> know how to work through the recordset that is returned BUT now I want
> to invoke stored Oracle procedures and functions. If I change the sql
> string that I have to
> "execute <procedure_name>", or "select <function_name>" then I get an
> error to the effect that the execute statement is illegal or the
> table/view does not exist. There is no such table/view, I know, but a
> valid function_name exists and I can use that from SQL*Plus command
> line. Also, if I had a function that returned an integer as opposed to
> a recordset, could I just 'catch' it in a normal variable or do I have
> to use a recordset object?
> Thanks for reading this.
Received on Mon Mar 22 1999 - 10:43:52 CST