Expert: Srini Nagarajan - 11/29/2004

Question
Hi,

I am trying to call a stored procedure using ASP and I got the following error at the execute:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC driver for Oracle][Oracle]ORA-20004:
/dps/intake/CaseAdmitEdit.asp, line 217

Set CMD = Server.CreateObject("ADODB.Command")
CMD.ActiveConnection   = objConnect
CMD.CommandText   = "SP_CREATE"
CMD.CommandType   = adCmdStoredProc
CMD.Parameters.Append  CMD.CreateParameter("p_HomeCd",adVarChar, adParamInput, 4, tHomeCd)
CMD.Parameters.Append  CMD.CreateParameter("p_RetValue", adVarChar, adParamOutput, 300)CMD.Execute

What can be wrong?

Answer
Hi

Here is the simple steps to use oracle store proc in asp

You can execute stored procedures which perform Oracle Server side tasks and return you a recordset. You can only use this method if
your stored procedure doesn't return any OUTPUT values.
<%    Set Conn = Server.CreateObject("ADODB.Connection")

     Conn.execute "test_me",-1,4
%>
  Note that -1 means no count of total number of records is
   required. If you want to get the count, substitute count
  with some integer variable

  Note that 4 means it is a stored procedure. By using the
  actual number -1 and 4, you don't need the server side
  include ADOVBS.INC  ;-)

  The above would do the job on the database and return
  back to you without returning any recordsets.

  Alternatively, you could:

<%   Set rs = conn.execute("test_me",w_count,4)   %>

  W_count is the number of records affected. If your stored
  procedure were to return a query result, it is returned
  within your recordset (rs). This method is useful with Stored procs
  which return results of an SQL query


2. USING THE COMMAND OBJECT

<%
   Set Conn = Server.CreateObject("ADODB.Connection")
   Set Comm = Server.CreateObject("ADODB.Command")

   Set comm.ActiveConnection = conn
   comm.commandtype=4   

    '(or use adCmdStoredProc instead of 4, but then you would have to
    'include the ADOVBS.INC. Its upto you

   comm.commandtext = "test_me"

   comm.execute
    'or
   Set rs = comm.execute()
%>

STEP# 4
+++++++++
/************PASSING INPUT/OUTPUT PARAMETERS**************************/
<%
   'If your stored procedure accepts IN parameters and returns OUT parameters
   'here's how to go about it

   set param = comm.Parameters
    param.append comm.createparameter("Input",3,1)
   param.append comm.createparameter("Output",3,2)
   'Note that 3 = adInteger for the datatype
   'Note that 1=adParamInput and 2=adParamOutput for parameter direction
   'Pass the input value
   comm("Input") = "...."

   OR

   set param = comm.createparameter("InPut",3,1)
   set param = comm.createparameter("OutPut",3,2)
   comm.parameters.append param
   'Pass the input value
   comm("Input") = "...."

   'Execute after setting the parameters
   comm.execute()

   'If your stored procedure returns OUT parameters, here's how to get it

   
   Out_1 = comm("Output")
   'and so on...

%>


Happy Programming!

-Sri