From: Marty Frongillo <mfrongillo@onwardtech.com>
Subject: Calling a stored procedure from Active Server Pages
Date: 1997/12/12
Message-ID: <881954440.1327890443@dejanews.com>#1/1
To: gkiwanuka@onwardtech.com
X-Http-User-Agent: Mozilla/4.02 [en] (WinNT; I)
X-Originating-IP-Addr: 146.115.133.166 (pm3.onwardtech.com)
Organization: Deja News Posting Service
X-Authenticated-Sender: Marty Frongillo <mfrongillo@onwardtech.com>
X-Article-Creation-Date: Fri Dec 12 19:20:40 1997 GMT
Reply-To: mfrongillo@ownardtech.com
Newsgroups: comp.databases.oracle.server



I'm trying to call a stored procedure from an ASP.  Because the
stored procedure returns an array, I've included the procedure as part
of a package (see exerpt below).  I trying to figure out how to call
the package.procedure from within the ASP.  The ASP below does not work
as I believe it can't figure out how to resolve the package name.
Does anyone have any hints or sugguestions.


Here's an exerpt from the ASP.

 Set cmd = Server.CreateObject("ADODB.Command")
 Set cmd.ActiveConnection = Conn
 cmd.CommandText = "seetra_info.get_seetra_info"
 cmd.CommandType = adCmdStoredProc

In ProC, this would be called

      EXEC SQL EXECUTE
	BEGIN seetra_info.get_seetra_info (:temp_buf, :num_ret,
					   :emailaddrs, :companynames);
        END;

      END-EXEC;

My sample ProC applications works fine; calling the stored procedure
and returning the expected results.

Here's an exerpt from the package

CREATE OR REPLACE PACKAGE seetra_info AS
   TYPE email_array IS TABLE OF VARCHAR2(128)
      INDEX BY BINARY_INTEGER;
   TYPE company_array IS TABLE OF VARCHAR2(128)
      INDEX BY BINARY_INTEGER;
   PROCEDURE get_seetra_info(
      name           IN CHAR,
      numret         IN OUT integer,
      email          OUT email_array,
      company        OUT company_array);
   END seetra_info;
/

CREATE OR REPLACE PACKAGE BODY seetra_info AS
   array_limit    NUMBER;
   companyname    CHAR (128);
   emailaddr      CHAR (128);
   internal       BOOLEAN;
   userid         NUMBER;

   ...

   PROCEDURE get_seetra_info(name         IN CHAR,
                             numret       IN OUT INTEGER,
                             email        OUT email_array,
                             company      OUT company_array) IS
   BEGIN
          ...
   END get_seetra_info;

END seetra_info;


We are using Oracle 7.1.6 on Unix and IIS 3.x on NT.


email replies preferred.



Thanks Marty

-------------------==== Posted via Deja News ====-----------------------
      http://www.dejanews.com/     Search, Read, Post to Usenet