Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Error while calling an Oracle Stored Procedure from VB using ADO
Thanks for the feedback. I tried that but it's still the same. Any
other suggestions?
Charles Hooper wrote:
> macca wrote:
> > Hi all, hope you can help me out. I'm getting the following error when
> > trying to call an oracle stored proc from vb using ado
> >
> > ORA-01060 array binds or executes not allowed
> >
> > Does nayone know what needs to be done to correct this? the definition
> > of the sp and vb code is shown below
> >
> > PROCEDURE allocate_account_number_ref (i_number_to_allocate IN NUMBER
> > ,i_sort_code IN VARCHAR2
> > ,i_account_type IN VARCHAR2
> > ,i_issued_to IN VARCHAR2
> > ,i_issued_by IN VARCHAR2
> > ,o_account_numbers OUT reftype);
> >
> > Private Sub UserForm_Initialize()
> >
> > Dim Conn As String
> >
> > Set cn = New ADODB.Connection
> >
> > userId = "xxx"
> >
> > password = "xxx"
> >
> > server = "xxx"
> >
> > On Error GoTo 0
> >
> > Set cn = New ADODB.Connection
> >
> > cn.ConnectionString = "Provider=MSDAORA.1;" & _
> > "Data Source=" & server & ";" & _
> > "User ID=" & userId & ";" & _
> > "Password=" & password & ";" & _
> > "PLSQLRSet=1;"
> >
> > cn.CursorLocation = adUseClient
> > cn.Open
> >
> > qSql = "{call cap_pkg.allocate_account_number({resultset 1,
> > o_account_numbers},?,?,?,?,?)}"
> >
> >
> > Set cpw1 = New ADODB.Command
> >
> >
> > With cpw1
> > .ActiveConnection = cn
> > .CommandType = adCmdText
> > .CommandText = qSql
> >
> >
> > .Parameters.Append .CreateParameter("i_number_to_allocate",
> > adNumeric, adParamInput, , 1)
> >
> > .Parameters.Append .CreateParameter("i_sort_code", adVarChar,
> > adParamInput, 8, "309713")
> >
> > .Parameters.Append .CreateParameter("i_account_type", adVarChar,
> > adParamInput, 3, "001")
> >
> > .Parameters.Append .CreateParameter("i_issued_to", adVarChar,
> > adParamInput, 35, "visualbasic")
> >
> > .Parameters.Append .CreateParameter("i_issued_by", adVarChar,
> > adParamInput, 35, "visualbasic")
> >
> > End With
> >
> > Set rs = New ADODB.Recordset
> > Set rs = cpw1.Execute
> > MsgBox "Complete"
> > rs.Close
> > End Sub
>
> Try placing:
> cpw1.Prepared = True
>
> Before the .Parameters.Append lines. This may not be the only error in
> your code, but it should help.
>
> Charles Hooper
> PC Support Specialist
> K&M Machine-Fabricating, Inc.
Received on Thu Jul 20 2006 - 08:24:55 CDT