ASP/OO4O Problem: Unexpect results from package

Discussion in 'ASP General' started by CJM, Aug 31, 2006.

  1. CJM

    CJM Guest

    Repeated for the benefit of m.p.i.asp.general, which I forgot to include in
    the original posting...


    "CJM" <> wrote in message
    news:...
    > [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
    > ) ;
    >
     
    CJM, Aug 31, 2006
    #1
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Monique Y. Mudama
    Replies:
    1
    Views:
    463
    Monique Y. Mudama
    Jun 28, 2005
  2. lovecreatesbeauty
    Replies:
    8
    Views:
    1,703
    Old Wolf
    Sep 12, 2005
  3. CJM
    Replies:
    1
    Views:
    104
  4. CJM
    Replies:
    2
    Views:
    141
  5. CJM
    Replies:
    7
    Views:
    580
Loading...

Share This Page