Re: Is it possible using stored procedure like this way???

From: dodo <dominique_hung_at_hotmail.com>
Date: Wed, 4 Aug 2004 09:37:26 +0200
Message-ID: <ceq3nm$94s$1@sunnews.cern.ch>

First off, thank you to have a look my question.

Here is the ASP code

---

---

<%@ Language=VBScript %>

<%
Dim partName, Comm, con,param

set con = Server.CreateObject("ADODB.Connection")

con.ConnectionString = "DRIVER={Microsoft ODBC for Oracle};SERVER=ora92; UID=test;PWD=test"
con.Open()

Set Comm = Server.CreateObject("ADODB.Command")

Comm.ActiveConnection = con
With Comm
 .CommandText = "{CALL PkgPartition.add(?)}"  .CommandType = 1 '1=adCmdText
End With
Set param = Comm.CreateParameter("paramPartName", 8, 1,100,ASPTest) Comm.Parameters.Append param
Comm.Execute ,,128
Response.Write ("<p> The partitionName is " & Comm.Parameters("paramPartName"))
con.Close
%>

Error message

---

---

Microsoft OLE DB Provider for ODBC Drivers (0x80040E14) [Microsoft][ODBC driver for Oracle][Oracle]ORA-20004: /partition.asp, line 23

Package code

---

---

CREATE OR REPLACE PACKAGE PkgPartition IS  FUNCTION createPartition(paramPartName IN VARCHAR2) RETURN NUMBER;  PROCEDURE add(paramPartName IN VARCHAR2); END PkgPartition;
/

show errors;
/

CREATE OR REPLACE PACKAGE BODY PkgPartition IS  FUNCTION createPartition(paramPartName IN VARCHAR2) RETURN NUMBER IS

  partId NUMBER;
  partNb NUMBER;

 BEGIN
  SELECT COUNT(*) INTO partNb FROM Partition   WHERE Partition.partitionName = paramPartName;

  IF (partNb = 0)
  THEN
   INSERT INTO Partition VALUES (IdSeq.nextval, paramPartName);    SELECT IdSeq.currval INTO partId FROM dual;    COMMIT;
  ELSE
   RAISE_APPLICATION_ERROR(-20002,'partitionName ' || paramPartName || ' is already used!!!');

   partId := -1;
  END IF;   RETURN partId;
 END;  PROCEDURE add(paramPartName IN VARCHAR2) IS

  partId NUMBER;

 BEGIN
  partId := createPartition(paramPartName);   DBMS_OUTPUT.PUT_LINE('create a new partition --> partionId: ' || partId || ' partitionName: ' || paramPartName);
 END;
END PkgPartition;
/

"tojo" <Tojo_at_hotmail.com> ???
news:MPG.1b7aa3a9927e6db59896f9_at_news.t-online.de ???...

> In article <ceogn2$a9u$1_at_sunnews.cern.ch>, dominique_hung_at_hotmail.com
> says...
> > Hi there,
> >
> > I create a package Pkg which contents one procedure ProX(paramIn)  and
> > one function FunY(paramIn). The ProX will get a value by calling FunY.
> > In fact the FunY will insert "paramIn" into table and return an Id.
> > Normally, with PL/SQL, this Id will show on the screen.
> > If I use following syntax in ASP:
> >
> > objCmd.CommandText = "{call Pkg.ProX(?)}"
> >
> > I  always get error message:
> > Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
> > [Microsoft][ODBC driver for Oracle][Oracle]ORA-20004:...
> >
> >
> > Coule someone tell me why?
> Are you binding the parameter correctly? It would help if you posted the
> complete error message and your code.
>
> -- Tom

Received on Wed Aug 04 2004 - 02:37:26 CDT