oracle stored proc and parameter

[scrsadmin]

I am try to call a oracle stored procedure and pass it one parameter. I keep getting an error
argument wrong type, are out of acceptable range, or are in conflict with another.

If anyone can help i would appreciate any help. This is a test of what i need to start off with. I need to be able to call the stored proc and pass it one parameter. After i figure out how to get that to work i want to expand passing more parameters but i figure once i can get one to work adding others will be easy. I work best with samples of working code if you have any. Thanks in advance for any help

The stored proc looks like this and has one input called inputTest1 that I'm trying to pass it from the vbscript;

create or replace
PROCEDURE SP_TEST3
( inputTest1 IN VARCHAR2) AS
BEGIN
insert into mjatest (inputTest1,test2,test3) values (inputTest1,'3333','!@#$%');
END SP_TEST3;


My vbscript look like this:


teststrCon = "Driver={Microsoft ODBC for Oracle}; " & _
"CONNECTSTRING=(DESCRIPTION=" & _
"(ADDRESS=(PROTOCOL=TCP)" & _
"(HOST=somehost)(PORT=1212))" & _
"(CONNECT_DATA=(SERVICE_NAME=SID_NAME))); uid=xxx;pwd=xxx;"


Dim oCon
Set oCon = WScript.CreateObject("ADODB.Connection")

Dim oRs

oCon.Open teststrCon

Set cmdStoredProc = WScript.CreateObject("ADODB.Command")
Set cmdStoredProc.ActiveConnection = oCon

cmdStoredProc.CommandText = "SP_Test3"

cmdStoredProc.CommandType = 4 'defines cmd type as stored proc
cmdStoredProc.Parameters.Refresh
cmdStoredProc.Parameters.Append cmdStoredProc.CreateParameter("inputTest1",adVarChar,adParamInput,20,inputTest1)

cmdStoredProc.Execute

oCon.Close

 

[PHV]

You havent any inputTest1 variable populated in your script!
Tip: Use the Option Explicit instruction.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[scrsadmin]

In my orginal code it does have inputTest1 variable. I must have missed it during the copy and pastin to this forum. Here is the code but im still not sure what is wrong!

Dim oCon
Set oCon = WScript.CreateObject("ADODB.Connection")

Dim oRs
inputTest1 = "testtest"

oCon.Open teststrCon

Set cmdStoredProc = WScript.CreateObject("ADODB.Command")
Set cmdStoredProc.ActiveConnection = oCon

cmdStoredProc.CommandText = "SP_Test3"

cmdStoredProc.CommandType = 4 'defines cmd type as stored proc
cmdStoredProc.Parameters.Refresh
cmdStoredProc.Parameters.Append cmdStoredProc.CreateParameter("inputTest1",adVarChar,adParamInput,20,inputTest1)

cmdStoredProc.Execute

oCon.Close

 

[scrsadmin]

Can someone help me with this issue. i keep getting argument wrong type, are out of acceptable range, or are in conflict with another. I looked at every sample i could find and can't seem to find what is wrong.

 

[tsuji]

>cmdStoredProc.Parameters.Append cmdStoredProc.CreateParameter("inputTest1",adVarChar,adParamInput,20,inputTest1)
To start with, do this (like what you did with adCmdStoredProc).
[tt]cmdStoredProc.Parameters.Append cmdStoredProc.CreateParameter("inputTest1",200,1,20,inputTest1)[/tt]

 

[scrsadmin]

tsuji,
I tried that and now getting this error.
(32, 1) Microsoft OLE DB Provider for ODBC Drivers: [Microsoft][ODBC driver for Oracle][Oracle]ORA-20003:

 

[tsuji]

>I tried that
There is nothing to try there. If you hadn't defined the constants, define them. If you now have them in place, keep them.

The error obviously now means you've to check the connection string and the connection object.

 

[scrsadmin]

I know the connection string works as I can run a similar script that only calls an oracle stored procedure without parameters and it works fine. So if I change this script to call a stored proc with out parameters it works fine it only has a problem once I started to add a call to a stored proc that has input parameters. What can I do to resolve this?