call an Oracle Stored Procedure

call an Oracle Stored Procedure

--------------------------------------------------------------------------------

Description : Folks, contrary to popular belief there are many ways to call stored procedures from an ASP page. I've tried it with Oracle (the only REAL RDBMS ;-) and it works.

Assume you have a procedure like this one below, And that it has been already created On the
Oracle database. This procedure doesn't return anything, but that doesn't change anything!
STEP #1:
/******STORED PROCEDURE On ORACLE DATABASE************/
/*====================================================*/
create Or Replace procedure test_me
Is
w_count integer;
begin
insert into TEST values ('Surya was here');
--commit it
commit;
end;
/*****End OF STORED PROCEDURE****/

STEP # 2:
+++++++++
I assume you have tested it from sql*plus by running the
following statements:

/************TEST THE STORED PROCEDURE FROM SQL*PLUS******/
SQL> execute test_me

PL/SQL procedure successfully completed.

SQL>
/***************End OF TESTING THE STORED PROC************/

STEP# 3:
++++++++
/*****CALLING A STORED PROCEDURE FROM ASP******************/

1. USING THE CONNECTION OBJECT

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...

%>

Thats it!