VB & OO4O ERROR - OO4O and Wizards(Archived)

Following code run in vb no problem:
sql = "Select * from Table_name for update"
set OraDynaset = OraDatabase.CreateDynaset(sql, 0&)
But run exe the error message is "OIP-04117 Not an updatable set"
Help Me!


running loops in VBscript

hi everyone
I am naresh,
i have written the following code in vbscript
Sub test
Dim tkgoDB,msConnect,gsUser,gsPassword,gsInstance,moRS
gsUser=" "
gsInstance=" "
Set tkgoDB =CreateObject("ADODB.Connection")
msConnect = "UID=" & apps & ";PWD=" & apps & ";CONNECTSTRING=" & dev01
tkgoDB.Open "Driver={Microsoft ODBC for Oracle};" & msConnect
Do While True
Set moRS=tkgoDB.execute("SELECT apps.aa_scheduled_pkg.start_job REQUEST_ID FROM dual")
msRequest = moRS("REQUEST_ID")
End Do
End Sub
while i am typing in command prompt as WScript testcode1.vbs it is giving error as Expected statement at 16
I am new to this Vscript
Please helpme
Any can any one know how to execute vbscript code through command prompt...
Thank you In advance.. 
Your question seems unrelated to .NET Stored Procedures which this forum deals with.
For that matter, it seems unrelated to Oracle as a vbs file that contains nothing but the following also errors with the same:
Do While True
End Do
Also, I dont see that your code would ever break out of the loop.
Anyway, to answer your question, you run a vbscript file from a command prompt simply by typing the name of it, and heres a simple example of a vbs file using odbc to select from emp.
Hope it helps,
set con = createobject("adodb.connection")
con.open "dsn=orcl;uid=scott;pwd=tiger"
set rs = createobject("adodb.recordset")
rs.open "select ename from emp",con
while not rs.eof
rslt = rslt & rs.fields(0).value & " "
msgbox rslt

Calling Oracle Package Function from Visual Basic

Oracle Client 8.04
Oracle ODBC Driver 8.00.04
VB 6.0
Windows 2000
I'm stumped here. I want to have a Oracle stored procedure run a
query and return a result set which I can assign to a recordset
object in VB. Based on things I've read here and on MS's site,
here's what I've done:
In the Oracle Schema Manager under the Packages folder I created
the following package:
FUNCTION mycur RETURN test_cur;
END test;
and under the Package Body folder created:
FUNCTION mycur RETURN test_cur
c_return test_cur;
OPEN c_return FOR
SELECT * FROM table_A;
RETURN c_return;
CLOSE c_return;
END mycur;
END test;
They both compile without errors and in Oracle SQL Worksheet I
can enter the following:
variable x refcursor;
execute :x :=test.mycur;
print x;
and the query results are displayed as expected.
The problem is trying to get the result back into a VB recordset
In VB 6.0 I have done this:
Dim RS As ADODB.Recordset
Dim Conn As ADODB.Connection
Dim sConnection As String
Dim sSQL As String
sSQL = "{call test.mycur}"
sConnection = "Provider=MSDASQL;UID=" & sUserID & ";PWD=" &
sPassword & ";Driver={Microsoft ODBC for Oracle}; Server=" &
sInstance & ";"
Conn.Open sConnection
RS.CursorLocation = adUseClient
RS.Open sSQL, Conn, adOpenForwardOnly, adLockOptimistic,
adCmdStoredProc ' or adCmdText
but get:
?err.Number -2147217900
?err.Source Microsoft OLE DB Provider for ODBC Drivers
?err.Description [Microsoft][ODBC driver for Oracle]Syntax error
or access violation
The problem is not with the connection or permissions, since the
query works fine when I just use the select statement in the
package function as the string, instead of calling the function
in the package (eg sSQL = "Select * from table_A") and can
process the resulting recordset in VB.
I've also tried variations using:
Set RS = Conn.Execute("{call test.mycur}")
or using a Command object something like:
Dim com As ADODB.Command
Set com = New ADODB.Command
With Conn
.ConnectionString = sConnection
.CursorLocation = adUseClient
End With
With com
.ActiveConnection = Conn
.CommandText = sSQL
.CommandType = adCmdText
End With
Set RS.Source = com
But still get the same errors. Any help is appreciated. Also, in
my package body, is it necessary to explicitly close the cursor,
or does the function just exit when it executes the return and
not ever hit the close statement?
Ed Holloman
We use VB6, Oracle 8.1.6, MS OLE DB Provider and MS Tran Server
to call an Oracle Package that returns a REF CURSOR to VB as
1) We use Provider=MSDAORA.1 in connect string.
2) We set up, but do not open, an ADO Command Object for the
Oracle Package.StoredProcedure, using the connect string.
3) We then set up an ADO Recordset and use the Command Object
when opening the recordset.
The cursor in the Oracle Package.StoredProcedure is set up this
OPEN prm_cur FOR
As a result we do not explicitly close the cursor.
Hope this helps.... 
Also try using the Oracle ODBC driver rather than the MS one.
It supports this syntax while some versions of the MS driver do
I have this working in a production application.
You will need:
Oracle Client 8.05
Oracle DBMS 8.05
MDAC 2.5
Use the OLE DB Provider
See this URL for more information:
i don't know if you got your answer, but i work with VB and
the procedure in the DB should have the cursor like you writen
in your mail.
to call a procedure in Oracle and get the data back
into a recordset you shuld use a Command object like this:
Dim conn As ADODB.Connection
Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset
Set conn = CreateObject("adodb.connection")
Set cmd = CreateObject("adodb.command")
Set rs = CreateObject("adodb.recordset")
With conn
.ConnectionString = ""
.CursorLocation = adUseClient
End With
With cmd
.ActiveConnection = conn
.CommandText = "proc.fun"
.CommandType = adCmdStoredProc
End With
'Then you set the rs to the command
Set rs = cmd.Execute
Set conn = Nothing
Set rs = Nothing
Set cmd = Nothing

Problem Retrieving RecordSet Item from Oracle7 with OraOLEDB.Oracle

I have to connect to a Oracle7 DB from an VB application, the machine running the VB Program will have Oracle 8.1.7 & 8.1.7 ODBC latest patch installed. (due to other things running).
I can connect OK, and retrieve a recordset as expected with the syntax in the below sample code;
However as soon as I do;
myVar = objADORecordSet.Fields.Item("MY_FIELD").Value
I get things like ???||||||||?| instead of SOME_NAME
If i switch to a oracle8 DB the problem goes away immediately
I would really appreciate any help you can give me!
Thanks in Advance
'* Declare All Variables and Objects *'
Dim objADOConnection As Object
Dim objADORecordSet As Object
Dim myVar As Variant
Dim I As Long
Dim strConnectionString As String
'* Instantiate Objects and Variables *'
Set objADORecordSet = CreateObject("ADODB.RecordSet")
Set objADOConnection = CreateObject("ADODB.Connection")
'* Open AODB Connection, Get Records *'
strConnectionString = "Provider=OraOLEDB.Oracle;Data Source=ORACLE7;User ID=MY_USER;Password=MY_PASSWORD"
objADOConnection.Open (strConnectionString)
Set objADORecordSet = objADOConnection.Execute("SELECT MY_FIELD FROM MY_TABLE")
If Not objADORecordSet.EOF Then
I = 0
Do While Not (objADORecordSet.EOF Or objADORecordSet.bof)
myVar = objADORecordSet.Fields.Item("MY_FIELD").Value '*** I get things like ???||||||||?| instead of SOME_NAME
MsgBox "What Did We Find ??: " & myVar
I = I + 1
MsgBox "Number of Records: " & I
End If
'* Destroy Objects *'
Set objADORecordSet = Nothing
Set objADOConnection = Nothing

Long Query

VB6.0 SP4
I maked code have long query over than 16382 bytes.
strLongQuery = "SELECT ......" 'Over than 16382 bytes
Rs.Open strLongQuery, Cn, adOpenForwardOnly, adLockReadOnly, adCmdText
But, I catched ADO error code '8007000e'.
Is this matter OraOLEDB's specification?
Or, Have I way of avoidance?
By the way, I tryed using MSDAORA.
And, It worked.
Give my best regards.
Sorry, I cannot write English well.
As I understand your problem is that you are trying to fetch some large amount of data
using Ora OLEDB provider. And you get this error.
Is your data LOB type ?
Can you send your code snippet.
Thank you , Jagriti.
I don't use LOB data.
The following is sample code occur error '8007000e'.
Dim Cn As New ADODB.Connection
Dim Rs As New ADODB.Recordset
Dim strQuery As String
Dim i As Long
strQuery = "SELECT 'AA' FROM DUAL WHERE 1 = 1"
For i = 1 To 1635
strQuery = strQuery & " AND 1 = 1"
Next i
Cn.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=XXX;User ID=XXX;Password=XXX;"
Rs.Open strQuery, Cn, adOpenForwardOnly, adLockReadOnly, adCmdText
Set Rs = Nothing
Set Cn = Nothing
Length of "strQuery" is 16383 byte.
On trial, change "SELECT 'A' ...", it work. -- 16382 byte
Best regards.
hideki nomoto
I tried your code and it works fine even when I increase the for loop size to 2000.
The only difference I see is you are using OraOLEDB while I am using OraOLEDB 8.1.7 which you can
download from otn site.
I cannot do it easily,
because exsiting system.
But, I will try to update OraOLEDB 8.1.7.
Thank you, Chandar.

Error calling procedures with numeric parameters from VB/ADO

I'm getting an error when calling a procedure with numeric parameter, as follow:
VB 5.0 Code:
Dim Conexao As New ADODB.Connection
Dim Comando As New ADODB.Command
With Conexao
.ConnectionString = "DSN=bddd;UID=user;PWD=password"
.CursorLocation = adUseClient
End With
With Comando
.ActiveConnection = Conexao
.CommandText = "{ call teste (?, ?) }"
.Parameters.Append .CreateParameter("par1", adNumeric, adParamInputOutput)
.Parameters.Append .CreateParameter("par2", adVarChar, adParamInputOutput, 15)
.Parameters(0).Value = 1
.Parameters(1).Value = "e"
.CommandType = adCmdStoredProc
End With
Error Message:
Run Time Error: -2147217891 (80040e1d)
Method 'Execute' of object '_Command' failed
version 8.1.6 running on a UNIX server
VB 5.0 with SP 3
ADO 2.1
MS ODBC Driver for Oracle
Other problem is happen whe I try to open Recordsets in tables with numeric fields. In this case, I'm geting the following error:
Run=time error: -2147217887 (80040e21)
Method 'Value' of object 'Field' failed
VB Code:
Dim Dados As New ADODB.Recordset
Dados.CursorLocation = adUseClient
Dados.Properties("Preserve On Commit") = True
Dados.Properties("Preserve On Abort") = True
Dados.Open "SELECT nome, nro as total FROM tabteste order by nro", Conexao
While Not Dados.EOF
Me.Print Dados.Fields("nome").Name & " - " & Dados.Fields("nome").Value
Me.Print Dados.Fields("total").Name & " - " & Dados.Fields("total").Value '<-- Error happens here!
I tryed to change the ODBC Driver, always with the same result.
1) Can you give the SQL you use to create the procedure 'teste'?
2) When you say that you've tried changing the ODBC driver, I assume you've tried the Oracle ODBC driver as well as the Microsoft ODBC for Oracle driver.
3) In your second example, is it possible that the value in 'total' is NULL? As I recall, that would be an error.
Justin Cave
ODBC Development 
Hi, Justin! Let's go! :-)
procedure teste (
par1 in out number ,
par2 in out varchar2 ) is
par1 := par1 + 10;
par2 := Par2 | | '- Teste';
2) Your supposition is correct. Both drivers return the same result.
3) Yes, the column 'nro' accept NULL values. But the problem happen even with not null values. And in the case of the procedures with numeric parameters, what can I do? I can't use NVL function on the procedure calling, for example.
Thanks a lot!