473,390 Members | 1,124 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,390 software developers and data experts.

ASP/OO4O Problem: Unexpect results from package

CJM
Repeated for the benefit of m.p.i.asp.general, which I forgot to include in
the original posting...
"CJM" <cj*******@REMOVEMEyahoo.co.ukwrote in message
news:4l************@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
) ;

Aug 31 '06 #1
0 1525

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Julia Sats | last post by:
Hi, I use in VB6 OO4O for connecting to Oracle, run procedures, sql queryes and so on. Now I am going to move to VB.Net. And I found that Oracle developer ODP.Net Please give advice do I need...
0
by: shamel | last post by:
Hi, I'm just starting to use OO4O on a project and I do not know a lot about Oracle. I wrote a standard Windows DLL using VC++ 6. In this DLL, I have a database class (DbOO4OClass) that use...
3
by: tfs | last post by:
I have a page that is running a DTS package and takes anywhere from 1 minute to 20 minutes. When it comes back it displays in my textbox the results. The problem is that on the long packages,...
6
by: Page Horton | last post by:
I have a ASP.NET (VB) web application running. The DTS package is suppose to generates a flat file to a file path (aka: \\myStation\outputFiles\). When it runs it generates the following error on...
1
by: CJM | last post by:
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...
2
by: CJM | last post by:
I'm using OO4O via ASP to manipulate an Oracle 10g database (v10.1.0.4). At the start of each vbScript section I'm starting off the transaction as follows: oDB.AutoCommit = False...
8
by: CJM | last post by:
I have a working web application (ASP) which links to an Oracle 10g DB via OO4O. I'm trying to port it to either of two test servers, but in fact, I can't get it to work with either - 'Unable to...
27
by: comp.lang.tcl | last post by:
My TCL proc, XML_GET_ALL_ELEMENT_ATTRS, is supposed to convert an XML file into a TCL list as follows: attr1 {val1} attr2 {val2} ... attrN {valN} This is the TCL code that does this: set...
0
by: Steven Samuel Cole | last post by:
Hi Stephane, thanks for your reply! :-) I do not get any notification or warning or whatever from dpkg, all output I get when running # sudo dpkg -i python-<package name>_0.0.1-4927-1_all.deb...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.