Re: Returning parameters from an Oracle Procedure (ado & vb6).


<dnaumowicz@xxxxxxxxx> wrote in message
news:1164833751.856412.184160@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Hello,

I have an Oracle procedure defined as (this works in SQLPlus):
procedure fleet_prc( incriteria IN varchar2,
eng OUT varchar2,
engdt OUT varchar2,
trn OUT varchar2,
trndt OUT varchar2)


I've tried to access it via
Set cmdStoredProc = New ADODB.Command
Set cmdStoredProc.ActiveConnection = cnnOracle
cmdStoredProc.CommandType = adCmdText
strCommandText = "{call prc.fleet_prc(?,?,?,?,?)}"


cmdStoredProc.CommandText = strCommandText
cmdStoredProc.Parameters.Append
cmdStoredProc.CreateParameter("incriteria", adVarChar, adParamInput,
17, strVIN)
cmdStoredProc.Parameters.Append
cmdStoredProc.CreateParameter("eng", adVarChar, adParamOutput, 255)
cmdStoredProc.Parameters.Append
cmdStoredProc.CreateParameter("engdt", adVarChar, adParamOutput, 255)
cmdStoredProc.Parameters.Append
cmdStoredProc.CreateParameter("trn", adVarChar, adParamOutput, 255)
cmdStoredProc.Parameters.Append
cmdStoredProc.CreateParameter("trndt", adVarChar, adParamOutput, 255)


Set rsEmp = New ADODB.Recordset
Set rsEmp = cmdStoredProc.Execute()


However, at this point I receive the error:
[Microsoft][ODBC driver for Oracle]Syntax error or access


my connection string is:
Provider=MSDASQL.1;Extended
Properties="DRIVER={Microsoft ODBC for
Oracle};UID=****;PWD=***;SERVER=***;"


Any and all help would be appreciated!!!!


Thanks,


David


All you need is the procName, no '?' or braces are needed...
strCommandText = "prc.fleet_prc"
and use...
cmdStoredProc.CommandType = adCmdStoredProc

Also why mess with a recordset. Read the out values using a construct
similar to the following... [Warning Air Code!]

Private mProcParamEng As ADODB.Parameter
Set mProcParamEng = cmdStoredProc.CreateParameter( _
"eng", adVarChar, adParamOutput, 255)
cmdStoredProc.Parameters.Append mProcParamEng
mProcParamEng.Value = 0
....
cmdStoredProc.Execute
MyEng = mProcParamEng.Value

hth
-ralph



.

Relevant Pages

  • Returning parameters from an Oracle Procedure (ado & vb6).
    ... procedure fleet_prc(incriteria IN varchar2, ... cmdStoredProc.CreateParameter("incriteria", adVarChar, adParamInput, ... cmdStoredProc.CreateParameter("engdt", adVarChar, adParamOutput, 255) ... Set rsEmp = New ADODB.Recordset ...
    (microsoft.public.data.ado)
  • Why error 2147217904 expects parameter when parm is supplied?
    ... Set objInputAuditItemParm = objCom.CreateParameter("@AuditItem", ... adVarChar, adParamInput, 50, trim) ... Set objOutputFoundFlag = objCom.CreateParameter("@FoundFlag", ... objCom.CreateParameter("@OutputStatusCode", advarChar, adParamOutput, ...
    (microsoft.public.scripting.vbscript)
  • Re: Why error 2147217904 expects parameter when parm is supplied?
    ... Set objInputAuditItemParm = objCom.CreateParameter("@AuditItem", ... adVarChar, adParamInput, 50, trim) ... Set objOutputFoundFlag = objCom.CreateParameter("@FoundFlag", ... objCom.CreateParameter("@OutputStatusCode", advarChar, adParamOutput, ...
    (microsoft.public.scripting.vbscript)
  • Re: Update- Anweisung in einer Funktion (SQL SERVER 2005)
    ... .CreateParameter("@NextNr", adVarChar, adParamOutput, 4) ... So klappt es jetzt auch mit meiner Textausgabe! ... wie ich dass anstellen kann und dann ist es ...
    (microsoft.public.de.sqlserver)
  • Re: Stored procedure Output Parameter headache
    ... .Parameters.Append .CreateParameter("RETURN_VALUE", adInteger, adParamReturnValue) ... .Parameters.Append .CreateParameter("@SSN", adVarChar, adParamInput, 9, txtSSN) ... .Parameters.Append .CreateParameter("@LastName", adVarChar, adParamInput, 25, txtLName) ... .Parameters.Append .CreateParameter("@Verified", adInteger, adParamOutput, 0) ...
    (microsoft.public.inetserver.asp.db)