I'm trying to call a package/procedure in oracle (from an ASP page) which
requires a number of parameters. I have got this working using OO40 but
unfortunately the transaction rollback function doesnt seem to do much.
So I'm now trying to use ADO instead (in the hope that ADO transactions will
work), however I'm getting the above error. My initial searches havent
turned up any suitable suggestions - there appear to be many reasons why
this error might occur - none of which I have found so far fit my situation.
Can anyone suggest where I am going wrong? Better still, can anyone post a
working code snippet that I can use as a model? I already have another [very
similar] function which calls a different package/procedure which works
fine.
Thanks in advance...
CJM
Code Snippets:
PROCEDURE AddSerialToHistory2(sSerialNo in varchar2, sPartNo in varchar2,
sSequenceNo in number,
sShopOrderNo in varchar2, sLineNo in varchar2, sRelNo in varchar2,
sSuperiorSerialNo in varchar2,
sSuperiorPartNo in varchar2, sOrderType in varchar2, sHistoryPurpose in
varchar2, sCurrentPosition in varchar2,
iResult Out number)
IS
sDesc Varchar2(100) := 'Received into stock against Shop Order ' ||
sShopOrderNo || ', ' || sLineNo || ', ' || sRelNo;
dtDate Date := CURRENT_DATE;
Begin
Insert Into IFSAPP.PART_SERIAL_HISTORY_TAB
(Part_No, Serial_No, Sequence_No, Order_No, Line_No, Release_No,
Transaction_Date,
RowVersion, Transaction_Description, Order_Type, History_Purpose,
Current_Position, User_Created,
Part_Ownership)
Values
(sPartNo, sSerialNo, sSequenceNo, sShopOrderNo, sLineNo, sRelNo,
dtDate, dtDate, sDesc,
sOrderType, sHistoryPurpose, sCurrentPosition, 'IFSAPP', 'COMPANY
OWNED');
If SQL%ROWCOUNT = 1 Then
iResult := 0;
--Commit;
Else
iResult := 1;
--Rollback;
End If;
END AddSerialToHistory2;
Function AddHistory (sSerialNo, sPartNo, sShopOrderNo, sLineNo, sRelNo,
sSuperiorSerialNo, sSuperiorPartNo)
Dim iResult2
Dim oParam
With oCmd
.CommandType=adCmdText
Set oParam = .CreateParameter("sSerialNo", adVarchar, adParamInput, 50,
sSerialNo)
.Parameters.Append oParam
Set oParam = .CreateParameter("sPartNo", adVarchar, adParamInput, 50,
sPartNo)
.Parameters.Append oParam
'Set oParam = .CreateParameter("sSequenceNo", adSmallInt, adParamInput,
50, 1)
'.Parameters.Append oParam
Set oParam = .CreateParameter("sShopOrderNo", adVarchar, adParamInput,
50, sShopOrderNo)
.Parameters.Append oParam
Set oParam = .CreateParameter("sLineNo", adVarchar, adParamInput, 50,
sLineNo)
.Parameters.Append oParam
Set oParam = .CreateParameter("sRelNo", adVarchar, adParamInput, 50,
sRelNo)
.Parameters.Append oParam
Set oParam = .CreateParameter("sSuperiorSerialNo", adVarchar,
adParamInput, 50, sSuperiorSerialNo)
.Parameters.Append oParam
Set oParam = .CreateParameter("sSuperiorPartNo", adVarchar, adParamInput,
50, sSuperiorPartNo)
.Parameters.Append oParam
'Set oParam = .CreateParameter("sHistoryPurpose", adVarchar,
adParamInput, 50, "INFO")
'.Parameters.Append oParam
'Set oParam = .CreateParameter("sCurrentPosition", adVarchar,
adParamInput, 50, "InInventory")
'.Parameters.Append oParam
Set oParam = .CreateParameter("iResult", adDecimal, adParamReturnValue)
.Parameters.Append oParam
'Insert row into SNE (Shop Order)
.CommandText="{Call ADDROWS.AddSerialToCatalog(?, ?, 1, ?, ?, ?, ?, ?,
'INFO', 'InInventory', ?)}"
.Execute() '<======= this is where
the error occurs
AddHistory = .Parameters("iResult").Value
.Parameters.Delete "sSerialNo"
.Parameters.Delete "sPartNo"
.Parameters.Delete "sSequenceNo"
.Parameters.Delete "sShopOrderNo"
.Parameters.Delete "sLineNo"
.Parameters.Delete "sRelNo"
.Parameters.Delete "sSuperiorSerialNo"
.Parameters.Delete "sSuperiorPartNo"
.Parameters.Delete "sHistoryPurpose"
.Parameters.Delete "sCurrentPosition"
.Parameters.Delete "iResult"
End With
End Function 10 19723
Can anyone suggest where I am going wrong? Better still, can anyone post a
working code snippet that I can use as a model? I already have another [very
similar] function which calls a different package/procedure which works
fine.
Thanks in advance...
CJM
I am willing to bet that this is an ADO issue in terms of how you are
passing arguments. I would suggest getting a SQL*PLUS session (this is
probably installed on the web server under the oracle directories) in
the database, and manually running the procedure through that. If it
works there, you have a mapping issue. Try something like ...
variable iResult number
exec AddSerialToHistory2('test',
'test',
1,
'test',
'test',
'test',
'test',
'test',
'test',
'test',
'test',
:iResult);
print iResult
....in the SQLPLUS session I suggested.
Regards,
Steve
CJM wrote:
I'm trying to call a package/procedure in oracle (from an ASP page) which
requires a number of parameters. I have got this working using OO40 but
unfortunately the transaction rollback function doesnt seem to do much.
Doesn't seem to do much ... means precisely what?
So I'm now trying to use ADO instead (in the hope that ADO transactions will
work), however I'm getting the above error.
And that error, presumably, came with a line number. Use it to track
down the offending line.
--
Puget Sound Oracle Users Group
Steve Howard wrote:
Can anyone suggest where I am going wrong? Better still, can anyone post a
working code snippet that I can use as a model? I already have another [very
similar] function which calls a different package/procedure which works
fine.
Thanks in advance...
CJM
I also noticed the procedure you are calling is not the one for which
you have provided the definition. Are you sure they are the same?
"Steve Howard" <st**********@gmail.comwrote in message
news:11**********************@p79g2000cwp.googlegr oups.com...
>
I also noticed the procedure you are calling is not the one for which
you have provided the definition. Are you sure they are the same?
Sorry, I cut and paste the wrong function! It doesnt make much difference,
since they are near identical.
Update: I'n the meantime, I've re-created the package call from first
principles, and it appears to be working. I'm not sure where the error lay,
but clearly there was one in there some where.
What is more, having moved away from OO4O to ADO, I now have transactions
working! Hurray to Microsoft! I'm not sure why transactiosn didnt work in
OO4O but it's academic now.
Thanks for your help Steve.
CJM
"DA Morgan" <da******@psoug.orgwrote in message
news:11***************@bubbleator.drizzle.com...
CJM wrote:
>I'm trying to call a package/procedure in oracle (from an ASP page) which requires a number of parameters. I have got this working using OO40 but unfortunately the transaction rollback function doesnt seem to do much.
Doesn't seem to do much ... means precisely what?
Unfortunately it means exactly what it says... I issued transactional
commands against a database object which neither returned an error or did
anything useful. Specifically, I couldnt rollback series of transactions; se
my earlier thread 'OO4O Transactions: Updates not being rolled=back'.
>So I'm now trying to use ADO instead (in the hope that ADO transactions will work), however I'm getting the above error.
And that error, presumably, came with a line number. Use it to track
down the offending line.
The offending line was marked (perhaps not clearly enough) in my code
snippet. Not surprisingly, it was at the point were the oracle package was
called.
Anyway, I've finally got it all working, and thankfully I have transactional
control through ADO (see my reply to Steve).
Thanks for your efforts anyway.
CJM
CJM wrote:
>Doesn't seem to do much ... means precisely what?
Unfortunately it means exactly what it says... I issued transactional
commands against a database object which neither returned an error or did
anything useful. Specifically, I couldnt rollback series of transactions; se
my earlier thread 'OO4O Transactions: Updates not being rolled=back'.
Perhaps your driver, being Microsoft technology, is autocommitting so
there is nothing to roll back.
Try this simple test.
INSERT ONE ROW
ROLLBACK;
COMMIT;
Is the row committed or rolled back?
--
Daniel Morgan
University of Washington
Puget Sound Oracle Users Group
CJM wrote:
"Steve Howard" <st**********@gmail.comwrote in message
Sorry, I cut and paste the wrong function! It doesnt make much difference,
since they are near identical.
CJM
It would make all the difference, actually, if they are only "near"
identical.
Look at the following...
/************************************************** ********************************************
....create a simple procedure that receives two arguments and returns
one as an OUT parameter to the caller...
SQLCREATE OR REPLACE PROCEDURE ADDSERIALTOHISTORY2 (p1 in number,
2 p2 in number,
3 iResult Out
number) IS
4 BEGIN
5 iResult := p1 * p2;
6 END;
7 /
Procedure created.
....and then run a simple vbs script that executes this procedure and
echoes the results to the screen...
SQL$type oracle_stored_proc.vbs
Set rs = CreateObject("ADODB.Recordset")
Set con = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
strsid = "test10g"
strUser = "rep"
strpassword = "rep"
con.Open "Provider=MSDAORA;" _
& "Password=rep;" _
& "User ID=rep;" _
& "Data Source=test10g;" _
& "Persist Security Info=True"
Set ocmd = CreateObject("ADODB.Command")
ocmd.ActiveConnection = con
ocmd.CommandType = 4
ocmd.CommandText = "AddSerialToHistory2"
ocmd.Parameters.Append ocmd.CreateParameter(, 3, 1)
ocmd.Parameters.Append ocmd.CreateParameter(, 3, 1)
ocmd.Parameters.Append ocmd.CreateParameter(, 3, 2)
ocmd.Parameters.Append ocmd.CreateParameter(, 4, 2)
ocmd(0) = 2
ocmd(1) = 2
ocmd.Execute
wscript.echo ocmd(2)
SQL$cscript oracle_stored_proc.vbs
Microsoft (R) Windows Script Host Version 5.6
Copyright (C) Microsoft Corporation 1996-2001. All rights reserved.
4
....and then add a parameter to the command object (uncomment the fourth
one in the example above), for which there is not a corresponding
parameter in the oracle procedure definition...
SQL$type oracle_stored_proc.vbs
Set rs = CreateObject("ADODB.Recordset")
Set con = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
strsid = "test10g"
strUser = "rep"
strpassword = "rep"
con.Open "Provider=MSDAORA;" _
& "Password=rep;" _
& "User ID=rep;" _
& "Data Source=test10g;" _
& "Persist Security Info=True"
Set ocmd = CreateObject("ADODB.Command")
ocmd.ActiveConnection = con
ocmd.CommandType = 4
ocmd.CommandText = "AddSerialToHistory2"
ocmd.Parameters.Append ocmd.CreateParameter(, 3, 1)
ocmd.Parameters.Append ocmd.CreateParameter(, 3, 1)
ocmd.Parameters.Append ocmd.CreateParameter(, 3, 2)
ocmd.Parameters.Append ocmd.CreateParameter(, 4, 2)
ocmd(0) = 2
ocmd(1) = 2
ocmd.Execute
wscript.echo ocmd(2)
....and voila, instant exception your script threw...
SQL$cscript oracle_stored_proc.vbs
Microsoft (R) Windows Script Host Version 5.6
Copyright (C) Microsoft Corporation 1996-2001. All rights reserved.
C:\SCRIPTS\vbs\oracle_stored_proc.vbs(27, 1) Microsoft OLE DB Provider
for Oracl
e: ORA-01036: illegal variable name/number
SQL>
************************************************** ************************************************** *
The moral of the story is, if you ever get that error again, count your
parameters, in both the caller and the oracle procedure.
Regards,
Steve
"Steve Howard" <st**********@gmail.comwrote in message
news:11**********************@h48g2000cwc.googlegr oups.com...
>
It would make all the difference, actually, if they are only "near"
identical.
Ok, obviously it would make a difference. What I was trying to say (but
glossing-over) was that the error was not with the general approach, because
the other similar routines work fine. It was with the specifics of the this
example, where I had presumably mis-typed something or had made a
syntactical mistake. When I recreated the code from scratch (using the same
knowledge and approach) it worked.
Going by your example (thanks, btw), I can only assume that the problem was
a similar mismatch between the parameters object and the procedure call.
Thanks
Chris
CJM wrote:
Ok, obviously it would make a difference. What I was trying to say (but
glossing-over) was that the error was not with the general approach, because
the other similar routines work fine. It was with the specifics of the this
example, where I had presumably mis-typed something or had made a
syntactical mistake. When I recreated the code from scratch (using the same
knowledge and approach) it worked.
I've done a *lot* worse :)
Regards,
Steve
"DA Morgan" <da******@psoug.orgwrote in message
news:11***************@bubbleator.drizzle.com...
CJM wrote:
Perhaps your driver, being Microsoft technology, is autocommitting so
there is nothing to roll back.
Initially, I was using OO4O which is a middleware layer from Oracle. Of
course, this will autocommit be default so there would be nothing to
rollback.
But I set the database object's Autocommit property to false and explicitly
started and rolled-back the transactions to no avail...
Try this simple test.
INSERT ONE ROW
ROLLBACK;
COMMIT;
Is the row committed or rolled back?
--
I tried something similar along the lines of:
AutoCommit = false
DB.BeginTrans
Insert Single Row
Rollback
Although OO4O never objected to the Rollback (ie no errors), it simply didnt
work - the rows were inserted anyway.
I moved across to ADO, and apart from the expected re-coding (although both
ADO and OO4O are very similar in approach), I had no problems at all.
Unfortunately, I'm using the MS ODBC For Oracle drivers, which have a
chequered history, so I would like to 'upgrade' to a better solution; either
a MS OLEDB driver or ideally the Oracle OLEDB driver which is included in
the same package as OO4O. Unfortunately, it doesnt seemt to have installed
correctly - at least, I can't see any Oracle OLEDB drivers shown in the ODBC
Administrator (the instant client and full client are listed though).
CJM This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Galina |
last post by:
Hello
I have created a trigger not to let records with certain fields null
to be inserted into the table:
BEGIN
IF :new.RUL_CODE is null or
:new.FES_UINS_CODE is null or
:new.FES_UIO_CALOCC is...
|
by: Park Yeon Jo |
last post by:
About Error : ORA-12514: TNS:listener could not resolve SERVICE_NAME
given in connect descriptor
I installed Oracle 8.1.7 on Windows XP Professional.
and I wanto connect to that server...
|
by: bdj |
last post by:
Hello!
I have at set of tnsnames.ora. I wich to make an union, e.g. a single file
of it.
How can I do that easy?
Greetings
Bjørn
|
by: mpatel6 |
last post by:
I had this error in alert log and my instance was down, anybody can help me?
Errors in file /u01/app/oracle/admin/sotstest/bdump/sotstest_p004_626740.trc:
ORA-07445: exception encountered: core...
|
by: kalpanaanand |
last post by:
I am new to Oracle 9i client. i tried to log in but i am getting this error.
this is my listenerfile
# LISTENER.ORA Network Configuration File: C:\ProgramFiles\oracle\NETWORK\ADMIN\listener.ora
#...
|
by: mivey4 |
last post by:
Hi,
First off, I am aware that this is a very heavily documented error and I have done my homework for throughly researching probable causes before deciding to post my problem here. At this point,...
|
by: Nitvar |
last post by:
When i am trying to connect to oracle it is giving me these two errors
ORA-12224: TNS:no listener
ORA-01034: ORACLE not available
I am new to oracle ,plz help
I am using solaris 5.6 and...
|
by: nabh4u |
last post by:
HI everyone,
I an getting the following errors when i execute my procedure.
SQL> exec PROCEDURE_NAME
BEGIN PROCEDURE_NAME; END;
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error:...
|
by: basmgokul |
last post by:
I am using oracle 10g in windows vista..
when starting DB it throws an error
Errors in file c:\database\udump\practice_ora_440.trc:
ORA-00704: bootstrap process failure
ORA-39700: database...
|
by: michael ngong |
last post by:
michael.john@gmx.at (Michael John) wrote in message news:<90cc4edd.0306230900.28075193@posting.google.com>...
MIchael
I you stated the OS and platform that could make it easier to address your...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
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...
|
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...
|
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
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
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,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
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...
| |