August 31st, 2006, 02:45 PM
|
| |
 ASP/OO4O Problem: Unexpect results from package
Repeated for the benefit of m.p.i.asp.general, which I forgot to include in
the original posting...
"CJM" <cjmnews04@REMOVEMEyahoo.co.ukwrote in message
news:4lo3f8F2shqtU1@individual.net... Quote:
[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
>
> Quote: Quote: |
>>>>>>>>>>>>>>>>>>>>>>>>>
| | ASP Snippets: Quote: Quote: |
>>>>>>>>>>>>>>>>>>>>>>>>>
| | 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;
> Quote: Quote: |
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
| | Tables (trimmed) Quote: Quote: |
>>>>>>>>>>>>>>>>>>>>>>>>>>>
| | >
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
) ;
>
| |
