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