ASP/OO4O Problem: Unexpect results from package

From: CJM <cjmnews04_at_REMOVEMEyahoo.co.uk>
Date: Thu, 31 Aug 2006 13:36:56 +0100
Message-ID: <4lo3f8F2shqtU1@individual.net>

[Apologies for the premature posting previous to this one]

I'm working on my first Oracle DB, so bear with me...

I have a couple of validation routines which are both returning the same result regardless of the inputs. One checks if a Serial No already exists, the other checks if a Part No is valid.

Currently there are no Serial Nos in the system, so this check shouldnt fail. And I get a response to indicate that the Part No is valid regardless of whether it is or not.

I strongly suspect that I'm made the same mistake in each case, but since I'm not getting any errors, I can't see where.

In both cases, the PL/SQL procedures return a value of 1, whereas if I run the PL/SQL in SQL Developer with suitable values inserted I get the expected results.

[I actually dont know how to output the value of iResult to the screen in SQL Developer, so I removed the 'INTO iResult' to return a row via the conventional method- so strictly speaking I'm not testing the exact same code. Out of interest, how would I run this code and then output the value of iResult?]

I know the problem will be something silly, but I simply dont know enough to spot it.

Thanks in advance.

CJM

>>>>>>>>>>>>>>>>>>>>>>>>

ASP Snippets:

>>>>>>>>>>>>>>>>>>>>>>>>

Function SerialExists (sSerialNo, sPartNo)  Dim iResult, bResult

 With oDB
.Parameters.Add "sSerialNo", sSerialNo, ORAPARM_INPUT
.Parameters ("sSerialNo").ServerType = ORATYPE_VARCHAR2

.Parameters.Add "sPartNo", sPartNo, ORAPARM_INPUT
.Parameters ("sPartNo").ServerType = ORATYPE_VARCHAR2

.Parameters.Add "iResult", 0, ORAPARM_OUTPUT
.Parameters ("iResult").ServerType = ORATYPE_NUMBER

  iResult = oDB.ExecuteSQL("Begin VALIDATION_PKG.SerialExists(:sSerialNo, :sPartNo, :iResult); end;")

  If iResult > 0 then bResult = true Else bResult = false   response.Write bResult & "<BR>"

  SerialExists = bResult

.Parameters.Remove "sSerialNo"
.Parameters.Remove "sPartNo"
.Parameters.Remove "iResult"

 End With
End Function

Function IsValidPartNo(sPartNo)
 Dim iResult, bResult

 With oDB
.Parameters.Add "sPartNo", sPartNo, ORAPARM_INPUT
.Parameters ("sPartNo").ServerType = ORATYPE_VARCHAR2

.Parameters.Add "iResult", 0, ORAPARM_OUTPUT
.Parameters ("iResult").ServerType = ORATYPE_NUMBER

  iResult = oDB.ExecuteSQL("Begin VALIDATION_PKG.IsValidPartNo(:sPartNo, :iResult); end;")

  If iResult > 0 then bResult = true Else bResult = false

  Response.Write bResult & "<BR>"
  IsValidPartNo = bResult

.Parameters.Remove "sPartNo"
.Parameters.Remove "iResult"

 End With
End Function

 'check that SerialNo/PartNo not used
 If SerialExists(sSerialNo, sPartNo) then iError = iError + 2

 'check for valid partnos
 If Not IsValidPartNo(sPartNo) then iError = iError + 4

>>>>>>>>>>>>>>>>>>>>>>

Package Specification:

>>>>>>>>>>>>>>>>>>>>>>

CREATE OR REPLACE
PACKAGE "VALIDATION_PKG" AS
  PROCEDURE SerialExists(sSerialNo in varchar2, sPartNo in varchar2, iResult Out number);
  PROCEDURE IsValidPartNo(sPartNo in varchar2, iResult Out number); END; CREATE OR REPLACE
PACKAGE BODY "VALIDATION_PKG" AS
   PROCEDURE SerialExists(sSerialNo in varchar2, sPartNo in varchar2, iResult Out number)

   IS
   BEGIN
    Select Count(*)
    into iResult
    from Part_Serial_Catalog_Tab --note: should be IFSAPP.Part_Serial_Catalog_Tab on live system

    where Serial_No = sSerialNo
    and Part_No = sPartNo;
   END;   PROCEDURE IsValidPartNo(sPartNo in varchar2, iResult Out number)   IS
  BEGIN
    Select Count(*)
    into iResult
    from IFSAPP.Inventory_Part_Tab
    where Part_No = sPartNo;
  END;
END;

>>>>>>>>>>>>>>>>>>>>>>>>>>>>

Tables (trimmed)

>>>>>>>>>>>>>>>>>>>>>>>>>>

  CREATE TABLE "SNE"."PART_SERIAL_CATALOG_TAB"    ( "PART_NO" VARCHAR2(25 BYTE) NOT NULL ENABLE,  "SERIAL_NO" VARCHAR2(50 BYTE) NOT NULL ENABLE,   CONSTRAINT "PART_SERIAL_CATALOG_PK" PRIMARY KEY ("PART_NO", "SERIAL_NO") ENABLE
   ) ;

  CREATE INDEX "SNE"."PART_SERIAL_CATALOG_RENAMED_IX" ON "SNE"."PART_SERIAL_CATALOG_TAB" ("PART_NO", "RENAMED_TO_SERIAL_NO")   ;

  CREATE TABLE "IFSAPP"."INVENTORY_PART_TAB"    ( "PART_NO" VARCHAR2(25 BYTE) NOT NULL ENABLE,  "CONTRACT" VARCHAR2(5 BYTE) NOT NULL ENABLE,   CONSTRAINT "INVENTORY_PART_PK" PRIMARY KEY ("PART_NO", "CONTRACT") ENABLE    ) ; Received on Thu Aug 31 2006 - 07:36:56 CDT