Re: Error while calling an Oracle Stored Procedure from VB using ADO

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 20 Jul 2006 08:25:03 -0700
Message-ID: <1153409103.114475.168040@75g2000cwc.googlegroups.com>

macca wrote:
> Charles Hooper wrote:
> > macca wrote:
> > > > Before the .Parameters.Append lines. This may not be the only error in
> > > > your code, but it should help.
> > > Thanks for the feedback. I tried that but it's still the same. Any
> > > other suggestions?
> >
> > Try modifying the connection string:
> > Provider=OraOLEDB.Oracle
> >
> > Charles Hooper
> > PC Support Specialist
> > K&M Machine-Fabricating, Inc.
> already tried that rpovider but VB threw a different error message...
>
> 80004005
> Unspecified Error
>
> not very helpful
>

Take a look at the code examples here:
http://www.oracle.com/technology/sample_code/tech/windows/ole_db/oledb8/index.html

In specific, "Returning multiple recordsets from a stored procedure", the Form_Load event of the frmPerformance form.

A little test:
Dim db as ADODB.Connection
Dim cmdSQL As New ADODB.Command
Dim parSQLParameter As New ADODB.Parameter Dim snpDataSQL As New ADODB.Recordset
Dim strSQL as String

Set db = New ADODB.Connection
db.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=" & strOracleSID & ";User ID=" & strUserName & ";Password=" & strPassword & ";ChunkSize=1000;"

db.ConnectionTimeout = 40
db.CursorLocation = adUseClient
db.Open

strSQL = "SELECT" & vbCrLf
strSQL = strSQL & "  SQL_TEXT" & vbCrLf
strSQL = strSQL & "FROM" & vbCrLf
strSQL = strSQL & "  V$SQLTEXT" & vbCrLf
strSQL = strSQL & "WHERE" & vbCrLf
strSQL = strSQL & "  HASH_VALUE= ?" & vbCrLf

strSQL = strSQL & "ORDER BY" & vbCrLf
strSQL = strSQL & " PIECE"
cmdSQL.CommandText = strSQL
cmdSQL.CommandType = adCmdText

cmdSQL.Prepared = True
Set parSQLParameter = cmdSQL.CreateParameter("hash", adDouble, adParamInput, 16, 3969931980#)
cmdSQL.Parameters.Append parSQLParameter

cmdSQL.ActiveConnection = dbVMFG
Set snpDataSQL = cmdSQL.Execute

'Specify a different hash to retrieve:
cmdSQL("hash") = 3969930000#
Set snpDataSQL = cmdSQL.Execute
strSQL = ""
Do While Not (snpDataSQL.EOF)

    strSQL = strSQL & snpDataSQL("sql_text")     snpDataSQL.MoveNext
Loop

If you specify the correct Oracle SID, user name, and password, do you still receive an unspecified error message when executing the code above? Specify an actual hash value, does it retrieve the correct SQL statement from the database?

If you can make the above work, try adapting it to work with your stored procedure. Consult the Oracle publish code example to help you adapt the example above.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Thu Jul 20 2006 - 10:25:03 CDT