Call Oracle Stored Procedure from Visual Basic using ADO 2.6 - ADODB
Command object.

I need the CreateParameter parameter values to call the following
stored procedure in Oracle 7:

CREATE OR REPLACE PROCEDURE DB1.SET_VALUES(x IN OUT VARCHAR) as
y varchar2(500) := upper('all');
[rest of sp logic snipped]

Thanks,
S

Dear sdunn-ga,

Your answer should look somewhat like this:

'-------------------------------------------------------------------------
Dim x
'Store the value of the input parameter in x (in this example, I'll
'assign "abc" to it
x = "abc"
'Store your database connection string here
Dim strConnection
strConnection = ""
'Other variables
Dim objConnection, objRecordSet, objCommand, objNameParam1
Set objConnection = Server.CreateObject("ADODB.Connection")
Set objRecordSet = Server.CreateObject("ADODB.RecordSet")
Set objCommand = Server.CreateObject("ADODB.Command")
Set objNameParam1 = .CreateParameter("x", adBSTR, adParamInputOutput,
,x)
With objConnection
	.ConnectionString = strOraConnection
	.Open
End with
With objCommand
.ActiveConnection = objConnection 
.CommandType = adCmdText
.CommandText = "{call DB1.SET_VALUES(?,{resultset 10000, x})}"  
.Parameters.Append objNameParam1
Set objRecordSet = .Execute()
End With
'Display error message is error occurs
If Err.number <> 0 Then
	Response.Clear
	DisplayErrorOnly(Err.Description)
	Response.End
End If
'Get output of Stored Procedure in a variable...
x = objCommand.Parameters("x").Value
'-------------------------------------------------------------------------

This code snippet is a modification of an actual code that I used in
Visual Basic to connect to Oracle and run a stored procedure.  You may
or may not have to make a few changes to this, and I presume you know
VB well enough to be able to do that.

Thanks for using Google Answers!

Regards,
gopalkamat-ga

---

Clarification of Answer by gopalkamat-ga on 23 Jul 2002 13:14 PDT

Sdunn-ga,

The question that you had asked was "I need the CreateParameter
parameter values to call the stored procedure".  From the comment put
in by you, it is clear that there was an issue with the Oracle Stored
Procedure, and not the answer given by me.

I believe I deserved a better rating.  Thanks for using Google
Answers.

regards,
gopalkamat-ga

A good, thourough answer, but not the resolution to my problem. As I
later found, the issue was with handling Oracle's VARCHAR datatype. I
changed it to VARCHAR2 and it worked.

Thanks,
Scott

I have an article on this subject at:

http://www.15seconds.com/issue/000810.htm

It goes step-by-step on how to call a stored procedure. You may find it helpful.

Al

That covers everything. My problem wasn't so much how to do it, but
what the problem was with my specific instance. Turns out the issue
was with handling the Oracle datatype VARCHAR. I changed it to
VARCHAR2 and it worked.