Repeated for the benefit of m.p.i.asp.general, which I forgot to include in
the original posting...
"CJM" <cjmnews04_at_REMOVEMEyahoo.co.uk> wrote in message
news:4lo3f8F2shqtU1_at_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 - 08:56:59 CDT
