Re: Should be sumply but...

From: Gord W <gord_at_swcsconsulting.com>
Date: Tue, 7 Nov 2000 11:58:29 -0700
Message-ID: <diYN5.42$r91.590@client>

Here's some sample code which should help... Included you will find

1. Package Definition
2. Package Body Definition
3. Sample ASP Code accessing the package
   • Package Definition CREATE OR REPLACE PACKAGE Pkg_Divisions AS TYPE t_cursor IS REF CURSOR ;

  PROCEDURE sp_GetDivision (pDivisionID IN NUMBER,

                     io_cursor IN OUT t_cursor);

  END Pkg_Divisions;

• Package Body Definition

CREATE OR REPLACE PACKAGE BODY Pkg_Divisions AS PROCEDURE sp_GetDivision (pDivisionID IN NUMBER,

                     io_cursor IN OUT t_cursor) IS d_cursor t_cursor;

    iNumChildren NUMBER;
    BEGIN

      SELECT
        COUNT(fk_DivisionID)
      INTO
        iNumChildren
      FROM
        ROLETITLES
      WHERE
        fk_DivisionID = pDivisionID;
      OPEN d_cursor FOR
        SELECT
          d.DivisionID,
          d.DivisionName,
          d.GraphicOSName,
          d.Priority,
          iNumChildren
      FROM
        DIVISIONS d
      WHERE
        d.DivisionID = pDivisionID;

    io_cursor := d_cursor;
  END sp_GetDivision;
End Pkg_Divisions

• ASP Code

<% Response.Buffer = true %>

<%

'Connect to the database using Microsoft OLEDB provider (Others will work too)

 Dim Conn
 Set Conn = Server.CreateObject("ADODB.Connection")  Conn.ConnectionTimeout = 60
 Conn.Open "Provider=MSDAORA.1;Data Source=;User ID=;Password="

 ' Extract the filters from the Request object  Dim strDivisionID, blnAllowDeletes
  strDivisionID = 1 ' You can get this through the request object or the querystring object etc.

 ' Call the sproc for our recordset, and  Dim cmd, rs, aRS, prmDivisionID, sSQL, iRowCount  set cmd = Server.CreateObject("ADODB.Command")  set rs = Server.CreateObject("ADODB.Recordset")  aRS = null ' we typically return the recordset into an array, but you could handle it in the native recordset if you like  sSQL = "{Call Pkg_Divisions.sp_GetDivision(?, {resultset 0, io_cursor})}"  with cmd
.ActiveConnection = conn
.CommandType = adCmdText
.CommandText = sSQL

  set prmDivisionID = .CreateParameter("pDivisionID", adInteger, adParamInput,,strDivisionID)

  with .Parameters
   .Append prmDivisionID
  end with
 end with

 with rs
.CursorLocation = AdUseClient
.CacheSize = 10
.Open cmd, , adOpenForwardOnly, adLockReadOnly

  if (.State = adStateOpen) then
   if (NOT .EOF) then
    aRS = .GetRows
    iRowCount = .RecordCount
   end if

   .Close
  end if
 end with

 with cmd.Parameters
.Delete "pDivisionID"

 end with

 set prmDivisionID = nothing

 set cmd.ActiveConnection = nothing
 set cmd = nothing
 set rs = nothing

%>

Now you can work with the recordset

Hope this helps,

Gord
'"rdf" <deficis_at_show.it> wrote in message news:8u8get$g5e$1_at_nslave3.tin.it...> Hi, > this is my question:
> How can I execute a oracle procedure (with input and output parameters)  from

> asp script ?
> I tried with th ado connection object: Conn.Execute("myprocedure"), but I
> received a 'Catastrofic Failure' error.
> Thanks.
>
>

Received on Tue Nov 07 2000 - 12:58:29 CST