Oracle-L: Re: calling Oracle stored procedures from ASP/ADO

From: Jeff Herrick <jherrick_at_igs.net>
Date: Wed, 27 Nov 2002 14:03:39 -0800
Message-ID: <F001.0050E30D.20021127140339@fatcity.com>

Hi Jared,

The following will work assuming you're using the Oracle OLE DB Provider. Note that this is _not_ the same thing as OO40 - Oracle Objects for OLE .... or as I like to call it Oracle Oily Objects =8-)

I had a little trouble getting it going until I hard-coded the enumerated values for datatype and command type. I'm not an ASP whiz but it seems I'm probably just missing an include for some ADO manifests

Cheers

Connected to:
Oracle8i Personal Edition Release 8.1.7.0.0 - Production JServer Release 8.1.7.0.0 - Production

SQL> create table test_table (field1 varchar2(2000));

Table created.

SQL> create or replace procedure test_proc (param in varchar2) as   2 begin
  3 insert into test_Table values (param);   4 end;
  5 /

ASP Code ----- begins

<html>
<body>
<form action="default.asp" method="get">

Your name: <input type="text" name="fname" size="20">

<input type="submit" value="Submit">
</form>
<%

set objConn = server.createObject("adodb.Connection") ObjConn.ConnectionString="Provider=OraOLEDB.Oracle.1;Persist Security Info=False;User ID=username;Password=password;Data Source=ORCL" objConn.open

dim fname
fname=Request.QueryString("fname")
If fname<>"" Then

      set rs = Server.CreateObject("ADODB.command")
	  set para = Server.CreateObject("ADODB.Parameter")

	  para.Type=200
	  para.Size=20
	  para.Direction=1
	  para.Value=fname

	  rs.CommandText = "Test_Proc"
	  rs.CommandType = 4
	  rs.ActiveConnection = objConn
	  rs.Parameters.Append para
	  rs.execute

	  set rs = nothing
	  set para = nothing
	  objConn.close
      set objConn = nothing
      Response.Write("Hello " & fname & "!<br />")
      Response.Write("How are you today?")

End If
%>
</body>
</html>

HTH Jeff Herrick

ps...good site is http://www.w3schools.com/ado/default.asp

On Wed, 27 Nov 2002 Jared.Still_at_radisys.com wrote:

> Does anyone have a good example of this?
>
> We have a developer here that has been unable to
> figure out how to do this properly. Numerous examples
> from support.microsoft.com are apparently no help.
>
> A particular problem seems to be declaring the args
> properly, as he consistently gets and 'invalid type on parameter'
> error from ODBC.
>
> Thanks,
>
> Jared
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author:
> INET: Jared.Still_at_radisys.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jeff Herrick
  INET: jherrick_at_igs.net

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Wed Nov 27 2002 - 16:03:39 CST