|
|
|
|
|
|
| Author |
Message |
macca
*nix forums beginner
Joined: 19 May 2005
Posts: 9
|
 Posted: Thu Jul 20, 2006 9:32 am Post subject:
Error while calling an Oracle Stored Procedure from VB using ADO
|
|
|
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 |
|
| Back to top |
|
 |
Charles Hooper
*nix forums addict
Joined: 09 Jul 2006
Posts: 51
|
| Posted: Thu Jul 20, 2006 11:25 am Post subject:
Re: Error while calling an Oracle Stored Procedure from VB using ADO
|
|
|
macca wrote:
| Quote: | 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. |
|
| Back to top |
|
|
macca
*nix forums beginner
Joined: 19 May 2005
Posts: 9
|
| Posted: Thu Jul 20, 2006 1:24 pm Post subject:
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:
| Quote: | 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. |
|
|
| Back to top |
|
|
Charles Hooper
*nix forums addict
Joined: 09 Jul 2006
Posts: 51
|
| Posted: Thu Jul 20, 2006 1:52 pm Post subject:
Re: Error while calling an Oracle Stored Procedure from VB using ADO
|
|
|
macca wrote:
| Quote: | 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. |
|
| Back to top |
|
|
macca
*nix forums beginner
Joined: 19 May 2005
Posts: 9
|
| Posted: Thu Jul 20, 2006 2:28 pm Post subject:
Re: Error while calling an Oracle Stored Procedure from VB using ADO
|
|
|
already tried that rpovider but VB threw a different error message...
80004005
Unspecified Error
not very helpful
Charles Hooper wrote:
| Quote: | 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. |
|
|
| Back to top |
|
|
Charles Hooper
*nix forums addict
Joined: 09 Jul 2006
Posts: 51
|
| Posted: Thu Jul 20, 2006 3:25 pm Post subject:
Re: Error while calling an Oracle Stored Procedure from VB using ADO
|
|
|
macca wrote:
| Quote: | 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. |
|
| Back to top |
|
|
Google
|
|
| Back to top |
|
|
|
|
The time now is Mon Dec 01, 2008 8:34 pm | All times are GMT
|
|
Mortgage | Debt Help | Free Advertising | Books | Mortgage Calculator
|
|
Copyright © 2004-2005 DeniX Solutions SRL
|
|
|
|
Other DeniX Solutions sites:
Unix/Linux blog |
electronics forum |
medicine forum |
science forum |
|
|
Privacy Policy
|
Powered by phpBB © 2001, 2005 phpBB Group
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
Forum index
»
Databases
»
Oracle
»
Server
