115 lines
3.0 KiB
Plaintext
115 lines
3.0 KiB
Plaintext
|
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!
|