Here is a copy of my oracle stored proc that takes 2 inputs and outputs one record. The vbscript is below the stored proc. I am getting an access violation on Set rs = cmdStoredProc.Execute() . I am struggling trying to figure out what the problem is. Has anyone seen this and can guide me on the resolution?
'******************************************************************************************
'oracle stored proc
'create or replace
'PROCEDURE "IASP_INTOTEST"
'( startnum IN NUMBER
' ,rowcount IN NUMBER
' ,v_count OUT VARCHAR2
')
'AS
'begin
' Insert Into iastorage.iaItemStorage( ItemID, StorageDeviceId )
' Select ItemId, 3 From iastorage.Device1Items Where ItemId between startnum and startnum + rowcount - 1;
' commit;
' Delete iastorage.Device1Items where ItemID in (Select ItemId from iastorage.iaItemStorage where storageDeviceId = 3);
' v_count := SQL%ROWCOUNT;
'commit;
'end;
'******************************************************************************************
Dim strCon
'irftst
strConIRF = "Driver={Microsoft ODBC for Oracle}; " & _
"CONNECTSTRING=(DESCRIPTION=" & _
"(ADDRESS=(PROTOCOL=TCP)" & _
"(HOST=tst)(PORT=1521))" & _
"(CONNECT_DATA=(SERVICE_NAME=test))); uid=test;pwd=test;"
Dim oCon: Set oCon = WScript.CreateObject("ADODB.Connection")
Dim oRs: Set oRs = WScript.CreateObject("ADODB.Recordset")
oCon.Open strConIRF
Const adVarChar = 200
Const adInteger = 3
Const adParamOutput = 2
Const adParamInput = 1
Const recordSize = 200
inputTest1 = "3718896"
inputTest2 = "2"
'Set cmdStoredProc = New ADODB.Command
Set cmdStoredProc = wscript.CreateObject("ADODB.Command")
Set cmdStoredProc.ActiveConnection = oCon
cmdStoredProc.CommandText = "IAFOLDER.IASP_INTOTEST"
cmdStoredProc.CommandType = 4 'defines cmd type as stored proc
'cmdStoredProc.Parameters.Refresh
cmdStoredProc.Parameters.Append cmdStoredProc.CreateParameter("inputTest1",adInteger,adParamInput,recordSize,inputTest1)
cmdStoredProc.Parameters.Append cmdStoredProc.CreateParameter("inputTest2",adInteger,adParamInput,recordSize,inputTest2)
cmdStoredProc.Parameters.Append cmdStoredProc.CreateParameter("out1",adInteger,adParamOutput,recordSize)
cmdStoredProc.Parameters.Append cmdStoredProc.CreateParameter("out2",adInteger,adParamOutput,recordSize)
'cmdStoredProc.Execute()
Set rs = cmdStoredProc.Execute()
'Dim sresult AS String = Ctype(cmdStoredProc.Parameters("out1").value(),String)
num = 0
'Do While Not IsEmpty(cmdStoredProc.Parameters(num))
Do While num <> 6
If Not IsNull(cmdStoredProc.Parameters(num)) Then
WScript.Echo cmdStoredProc.Parameters(num)
Else
WScript.Echo "NULL Value"
End if
num = num+1
loop
oCon.Close
'******************************************************************************************
'oracle stored proc
'create or replace
'PROCEDURE "IASP_INTOTEST"
'( startnum IN NUMBER
' ,rowcount IN NUMBER
' ,v_count OUT VARCHAR2
')
'AS
'begin
' Insert Into iastorage.iaItemStorage( ItemID, StorageDeviceId )
' Select ItemId, 3 From iastorage.Device1Items Where ItemId between startnum and startnum + rowcount - 1;
' commit;
' Delete iastorage.Device1Items where ItemID in (Select ItemId from iastorage.iaItemStorage where storageDeviceId = 3);
' v_count := SQL%ROWCOUNT;
'commit;
'end;
'******************************************************************************************
Dim strCon
'irftst
strConIRF = "Driver={Microsoft ODBC for Oracle}; " & _
"CONNECTSTRING=(DESCRIPTION=" & _
"(ADDRESS=(PROTOCOL=TCP)" & _
"(HOST=tst)(PORT=1521))" & _
"(CONNECT_DATA=(SERVICE_NAME=test))); uid=test;pwd=test;"
Dim oCon: Set oCon = WScript.CreateObject("ADODB.Connection")
Dim oRs: Set oRs = WScript.CreateObject("ADODB.Recordset")
oCon.Open strConIRF
Const adVarChar = 200
Const adInteger = 3
Const adParamOutput = 2
Const adParamInput = 1
Const recordSize = 200
inputTest1 = "3718896"
inputTest2 = "2"
'Set cmdStoredProc = New ADODB.Command
Set cmdStoredProc = wscript.CreateObject("ADODB.Command")
Set cmdStoredProc.ActiveConnection = oCon
cmdStoredProc.CommandText = "IAFOLDER.IASP_INTOTEST"
cmdStoredProc.CommandType = 4 'defines cmd type as stored proc
'cmdStoredProc.Parameters.Refresh
cmdStoredProc.Parameters.Append cmdStoredProc.CreateParameter("inputTest1",adInteger,adParamInput,recordSize,inputTest1)
cmdStoredProc.Parameters.Append cmdStoredProc.CreateParameter("inputTest2",adInteger,adParamInput,recordSize,inputTest2)
cmdStoredProc.Parameters.Append cmdStoredProc.CreateParameter("out1",adInteger,adParamOutput,recordSize)
cmdStoredProc.Parameters.Append cmdStoredProc.CreateParameter("out2",adInteger,adParamOutput,recordSize)
'cmdStoredProc.Execute()
Set rs = cmdStoredProc.Execute()
'Dim sresult AS String = Ctype(cmdStoredProc.Parameters("out1").value(),String)
num = 0
'Do While Not IsEmpty(cmdStoredProc.Parameters(num))
Do While num <> 6
If Not IsNull(cmdStoredProc.Parameters(num)) Then
WScript.Echo cmdStoredProc.Parameters(num)
Else
WScript.Echo "NULL Value"
End if
num = num+1
loop
oCon.Close