OIP-04122 Bind variable not full enabled error message - OO4O and Wizards(Archived)

I am trying to use VB for Applications to open an OraSesion in Excel.
I am using Oracle 8.0.5 and OO4O 2.3
When executing the following script I get a run time error message. "OIP-04122 Bind variable not full enabled error message". Debug points to this line as the culprit.
OraDatabase.Parameters.Add "upper_lmt", "1", ORAPARM_INPUT
Does the error occur because the OO40 version 2.3 does not support bind variables. Or does the fault lie elseware.
thanks, James
Sub var_parms()
Dim OraSession As Object
Dim OraDatabase As Object
Dim OraDynaset As Object
Dim flds() As Object
Dim fldcount As Integer
Set OraSession = CreateObject("OracleInProcServer.XOraSession")
Set OraDatabase = OraSession.OpenDatabase("", "scott/tiger", ORADYN_DEFAULT)
OraDatabase.Parameters.Add "upper_lmt", "1", ORAPARM_INPUT
OraDatabase.Parameters("upper_lmt").ServerType = ORATYPE_NUMBER
Set OraDynaset = OraDatabase.CreateDynaset _
("SELECT * FROM au_week_number WHERE week_num = :upper_lmt", ORADYN_DEFAULT)
fldcount = OraDynaset.fields.Count
ReDim flds(0 To fldcount - 1)
For Colnum = 0 To fldcount - 1
Set flds(Colnum) = OraDynaset.fields(Colnum)
Next
For Colnum = 0 To OraDynaset.fields.Count - 1
ActiveSheet.Cells(1, Colnum + 1) = flds(Colnum).Name
Next
For Rownum = 2 To OraDynaset.RecordCount + 1
For Colnum = 0 To fldcount - 1
ActiveSheet.Cells(Rownum, Colnum + 1) = flds(Colnum).Value
Next
OraDynaset.DbMoveNext
Next
Range("A1:A1").Select
End Sub

Related

OIP-04151: Internal error getting ROWID

Hi,
Does anyone encounter this error message?
I am having a problem when I insert a spatial record using OO4O. The error pops up during the Dynaset.Update method call. The error message is "OIP-04151: Internal error getting ROWID". Here is the code:
Public Function InsertSpatialRecord(sWorkspace As String, sObjectType As String, sTablename As String, sIDField As String, _
vrFields As Variant, vrValues As Variant, vrGeom As Variant) As Boolean
Dim sField As String, sValue As String
Dim vrGeometry() As Double
Dim vrAttributes() As String
Dim i As Integer
Dim blResult As Boolean
Dim oraSession As oraSession
Dim oraDB As OraDatabase
Dim oraDS As OraDynaset
Dim oraSql As OraSqlStmt
Dim sdoPoint As OraObject
Dim sdoGeom As OraObject
Dim sdoElemInfo As OraCollection
Dim sdoOrdinates As OraCollection
' Connect to Oracle
Set oraDB = ConnectToOracle("HOST", "user/password")
Set sdoGeom = oraDB.CreateOraObject("MDSYS.SDO_GEOMETRY")
Set sdoElemInfo = oraDB.CreateOraObject("MDSYS.SDO_ELEM_INFO_ARRAY")
Set sdoOrdinates = oraDB.CreateOraObject("MDSYS.SDO_ORDINATE_ARRAY")
oraDB.ExecuteSQL ("begin dbms_wm.gotoworkspace('arnold');end;")
oraDB.BeginTrans
Set oraDS = oraDB.CreateDynaset("SELECT * FROM " & sTablename & " WHERE ROWNUM < 2", 0)
oraDS.MoveLast
oraDS.AddNew
' Loop through the Fields and insert the Attributes
For i = 0 To UBound(vrFields)
sField = UCase(vrFields(i))
If IsEmpty(vrValues(i)) Then
oraDS.Fields(sField).value = Null
Else
sValue = vrValues(i)
oraDS.Fields(sField).value = sValue
End If
Next
sdoGeom.Item(1).value = 2002
sdoGeom.Item(2).value = 32768
sdoGeom.Item(3).value = Null
sdoElemInfo.Append (1)
sdoElemInfo.Append (2)
sdoElemInfo.Append (1)
For i = 0 To UBound(vrGeom)
ReDim Preserve vrGeometry(i)
sdoOrdinates.Append (vrGeom(i))
Next
sdoGeom.Item(4) = sdoElemInfo
sdoGeom.Item(5) = sdoOrdinates
oraDS.Fields("Geom") = sdoGeom
oraDS.Update ' HERE IS WHERE THE ERROR POPS UP
' If the record is inserted properly then commit transaction
If oraDS.RecordCount = 1 Then
oraDB.CommitTrans
blResult = True
Else
oraDB.Rollback
blResult = False
End If
oraDB.Close
Set oraDB = Nothing
If blResult = True Then
InsertSpatialRecord = True
Else
InsertSpatialRecord = False
End If
End Function
All the parameters being passed to the function are valid. It has no error during the creation of the sdo_geometry object. Only when it comes to updating the dynaset, the error appears.
I have searched through the web, but there is no clear explanation as to why this error appears. I'm not sure if this is a bug on the OO4O and/or Spatial libraries.
Please advise. Thank you.
Regards,
Arnold

RAW datatype in EXCEL using OO4O

Hi all.
I would like to display a result of "select saddr from v$session where rownum < 2"
to EXCEL sheet and I want to use OO4O.
The data type of saddr in v$session is RAW.
Is there any good solution to display oracle "RAW datatype"?
To solve this question, I have tried two method - OLEDB provider and OO4O.
When I used OLEDB, the result is nice,but the other is not.
Please help me.
Thank in advance.
Please see below.
1) method #1 : using OLEDB provider
Dim sConn As String
Dim oConn As ADODB.Connection
Dim oRs As ADODB.Recordset
Dim cntCol, cntRow As Long
Dim resData() As Variant
Dim i, j As Long
Dim startTime, endTime As Date
startTime = timer
sConn = "Provider=MSDAORA;Data Source=" & gTargetServer & ";User ID=" & gUserID & "; Password=" & gPasswd
Set oConn = New ADODB.Connection
oConn.Open sConn
Set oRs = New ADODB.Recordset
oRs.Open gSqlString, oConn, adOpenStatic, adCmdText
endTime = timer
If Not (oRs.BOF Or oRs.EOF) Then
Do While Not (oRs.BOF Or oRs.EOF)
i = i + 1
oRs.MoveNext
Loop
oRs.MoveFirst
cntCol = oRs.Fields.Count
cntRow = i
ReDim resData(cntRow, cntCol - 1)
'======== Column Title ==========
For j = 0 To oRs.Fields.Count - 1
resData(0, j) = oRs.Fields(j).Name
'Debug.Print oRs.Fields(j).Name, oRs.Fields(j).Type
If oRs.Fields(j).Type = 204 Then
'Debug.Print "Attribute=" & oRs.Fields(j).Attributes
End If
Next j
i = 1
Dim varLogo As Variant
Dim varChunk As Variant
Dim by() As Byte
Dim k As Integer
Dim hexaString As String
Do While Not (oRs.BOF Or oRs.EOF)
For j = 0 To cntCol - 1
If oRs.Fields(j).Type = 204 Then
If oRs.Fields(j).Value & "" = "" Then
resData(i, j) = oRs.Fields(j).Value
Else
hexaString = ""
by = oRs.Fields(j).Value
For k = 0 To UBound(by)
hexaString = hexaString & Right("0" & Hex(by(k)), 2)
Next k
'resData(i, j) = "'" & hexaString
resData(i, j) = hexaString
End If Else
resData(i, j) = oRs.Fields(j).Value
End If
Next j
oRs.MoveNext
i = i + 1
Loop
Else
ReDim resData(2, oRs.Fields.Count - 1)
For j = 0 To oRs.Fields.Count - 1
resData(0, j) = oRs.Fields(j).Name
Next j
resData(1, 0) = "There is no record"
End If
oRs.Close
oConn.Close
Set oRs = Nothing
Set oConn = Nothing
endTime = timer
getResultSet = resData
The result is right.
SADDR
C0000000614ADDF0
2) method #1 : using OO4O
Dim oSession As Object
Dim oDatabase As Object
Dim oRs, tRs As Object
Dim cntCol, cntRow As Long
Dim rowNum, colNum As Long
Dim startTime, endTime As Date
Dim sraw() As Byte
Dim k As Integer
Dim hexaString As String
startTime = timer
Set oSession = CreateObject("OracleInProcServer.XOraSession")
Set oDatabase = oSession.DbOpenDatabase(gTargetServer, gUserID & "/" & gPasswd, 0&)
Set oRs = oDatabase.DbCreateDynaset(gSqlString, 0&)
endTime = timer
cntCol = oRs.Fields.Count
cntRow = oRs.RecordCount
ReDim resData(cntRow, cntCol)
For colNum = 0 To cntCol - 1
resData(0, colNum) = oRs.Fields(colNum).Name
Next colNum
For rowNum = 1 To cntRow
For colNum = 0 To cntCol - 1
Select Case oRs.Fields(colNum).Type
Case 11
hexaString = ""
sraw = oRs.Fields(colNum).Value
'Debug.Print sraw
For k = 0 To UBound(sraw)
hexaString = hexaString & Right("0" & Hex(sraw(k)), 2)
Next k
resData(rowNum, colNum) = hexaString
Case Else
resData(rowNum, colNum) = oRs.Fields(colNum).Value
End Select
Next colNum
oRs.MoveNext
Next rowNum
Set oRs = Nothing
Set oDatabase = Nothing
Set oSession = Nothing
getResultSet = resData
==> The result is wrong:
SADDR
FDFF0000000061004A00D66B

How to create a .gms file(GAMS) in excel vba using oracle stored Procedure.

hi
I have written the code.
Please tell me how to create a .gms file. I am able to read the clob data but not able to write the clob into a file and saving the file as a .gms
Please help me. Refer the code below:
Private Function ClobReading(Ccode As String, Sid As String)
Dim OraSession As Object 'Declare variables as OLE Objects
Dim OraDatabase As Object
Dim OraDynaset As Object
Dim objItem As Object
Dim strname As String
Dim buffer As Variant
Dim buf As String
'Dim rs As ADO.Recordset
Const ClobDestPath = "C:\"
Dim ArrCursorOutput(0) As String ' This array will actually be used for wrting the recordset to the excel workbook
ArrCursorOutput(0) = "gams"
Set OraSession = CreateObject("OracleInProcServer.XOraSession")
Set OraDatabase = OraSession.OpenDatabase("db_SERVER", "final/owner_6789", 0)
OraDatabase.Parameters.Add "CountryCode", Ccode, ORAPARM_INPUT
OraDatabase.Parameters("CountryCode").ServerType = ORATYPE_VARCHAR2
OraDatabase.Parameters.Add "scenarioid", Sid, ORAPARM_INPUT
OraDatabase.Parameters("scenarioid").ServerType = ORATYPE_VARCHAR2
OraDatabase.Parameters.Add "gams", 0, ORAPARM_OUTPUT
OraDatabase.Parameters("gams").ServerType = ORATYPE_CLOB
OraDatabase.Parameters.Add "status", 0, ORAPARM_OUTPUT
OraDatabase.Parameters("status").ServerType = ORATYPE_VARCHAR2
Set OraSQLStmt = OraDatabase.CreateSql("begin scenario_pck.get_gams_file(:CountryCode,:scenarioid,:gams,:status); end;", ORASQL_FAILEXEC)
Set rs= OraDatabase.Parameters("gams").Value
'OrderDynaset.Save
'Get a free file number
'FNum = FreeFile
'Read entire CLOB value, buffer must be a Variant
'AmountRead = OrderDynaset.Read(buffer)
'buf is string holding the buffer
' Create ADO stream object
Set mstream = New ADODB.Stream
' Set the character set for you particular type of text data
mstream.Charset = "us-ascii"
' Set it to a text file type
mstream.Type = adTypeText
' Open it
mstream.Open
----------------------------------------getting an error at this line-------------------------
' This writes the text file from the clob field to the buffer
mstream.WriteText 'rs.Fields(0).Value
' This saves the stream to a file on disk
'mstream.SaveToFile ClobDestPath & rs.Fields("FIELD").Value
mstream.SaveToFile ClobDestPath, adSaveCreateOverWrite
End Function

Create SQL in VBA

Hi,
I wan to create a create sql in VBA. I read a some sites but I haven't found why my script doesn't work.
This is my script:
Sub Create_Temp_Table()
Dim username
Dim password
Dim sid
Dim objSession
Dim objdatabase
Dim OraDynaset
Dim oraSqlStmt
Dim strSQL
Dim stdout
Dim i
Set stdout = WScript.stdout
username = ""
password = ""
SERVICE_NAME = "TEST"
' connect to database
stdout.WriteLine "Connecting to database . . ."
Set objSession = CreateObject("OracleInProcServer.XOraSession")
Set objdatabase = objSession.OpenDatabase(SERVICE_NAME, username & "/" & password, 0)
strSQL = "DROP TABLE TTT_DATEPAR"
strSQL = "COMMIT"
strSQL = "CREATE TABLE TTT_DATEPAR(STARTDATE date,ENDDATE date)"
strSQL = "COMMIT"
strSQL = "INSERT INTO TTT_DATEPAR VALUES (FROMDATTE, TODATE)"
strSQL = "COMMIT"
' This command takes a long time to execute when doing ranks/order bys
Set OraDynaset = objdatabase.DBCreateDynaset(strSQL, 0&)
Do While OraDynaset.EOF = False
Loop
Set OraDynaset = Nothing
Set objSession = Nothing
objdatabase.Close
Set objdatabase = Nothing
End Sub
The error is at line
Set objSession = CreateObject("OracleInProcServer.XOraSession")
Can somebody help me? thx 
Please describe the environment in which you trying to make this run. (What OS version and edition, Oracle Client package and version, VBA host app, etc.)
Tyrus wrote:
The error is at line
Set objSession = CreateObject("OracleInProcServer.XOraSession")What error? 
I have created another scrip:
Sub Create_table()
Dim OraSession As OraSession
Dim OraDatabase As OraDatabase
Dim OraDynaset As OraDynaset
+' Use OO4O+
Set objSession = CreateObject("OracleInProcServer.XOraSession")
Set objdatabase = objSession.OpenDatabase("XE", "SYSTEM/BCG", 0&)
Set OraDynaset = OraDatabase.CreateDynaset("DROP TABLE TTT_DATEPAR", 0&)
Set OraDynaset = OraDatabase.CreateDynaset("CREATE TABLE TTT_DATEPAR(STARTDATE date,ENDDATE date)", 0&)
Set OraDynaset = OraDatabase.CreateDynaset("INSERT INTO  TTT_DATEPAR VALUES ('01-JAN-2010', '01-JUN-2010')", 0&)
Set OraDynaset = objdatabase.DBCreateDynaset(Sql, 0)
Set objSession = Nothing
Set objdatabase = Nothing
End Sub
My OS is Windows7 with Office 2010. I have installed Oracle client 11.2.0 and Oracle Express Edition 10gE. The database name is XE (default name). The error is:
Run-time error '91': Object variable or With block variable not set with debug on
Set OraDynaset = OraDatabase.CreateDynaset("DROP TABLE TTT_DATEPAR", 0&)
I wrote also this command and I get the same error:
Set OraDynaset = OraDatabase.CreateDynaset("select table_name from all_tables", 0&)
The script model is this one
http://download.oracle.com/docs/cd/E11882_01/win.112/e17727/clients.htm#CIHBHJDF
thx 
i tried now other methods. Like this one from http://www.orafaq.com/wiki/OO4O_FAQ
rowcount = OraDatabase.ExecuteSQL("create table X (A date)")
rowcount = OraDatabase.ExecuteSQL("insert into X values (sysdate)")
OraSession.CommitTrans ' or OraSession.Rollback
Error run time 424 Object required.
the only script that does something is this one:
Sub GetEmployees()
+' Use OO4O+
Set objSession = CreateObject("OracleInProcServer.XOraSession")
Set objdatabase = objSession.OpenDatabase("XE", "SYSTEM/BCG", 0)
Sql1 = "DROP TABLE TTT_DATEPAR"
Sql2 = "CREATE TABLE TTT_DATEPAR(STARTDATE date,ENDDATE date)"
Sql3 = "INSERT INTO  TTT_DATEPAR VALUES ('01-AUG-2010', '01-JAN-2011')"
Set OraDynaset = objdatabase.DBCreateDynaset(Sql1, 1)
Set OraDynaset = objdatabase.DBCreateDynaset(Sql2, 0)
Set OraDynaset = objdatabase.DBCreateDynaset(Sql3, 0)
Set objdatabase = Nothing
End Sub
Does anyone know which command should i use for create, insert and drop? Is it DBCreateDynase or OraDatabase.ExecuteSQL
thx 
found the solution using this post Insert Range into table using vba
This is my script:
Sub test1()
Dim OraDatabase As Object
Dim OraDynaSet As Object
Dim OraSession As Object
Set OraSession = CreateObject("OracleInProcServer.XOraSession")
Set OraDatabase = OraSession.OpenDatabase("XE", "SYSTEM/BCG", 0)
OraDatabase.ExecuteSQL "DROP TABLE TTT_DATEPAR"
OraDatabase.ExecuteSQL "CREATE TABLE TTT_DATEPAR(STARTDATE date,ENDDATE date)"
OraDatabase.ExecuteSQL "INSERT INTO TTT_DATEPAR VALUES ('01-JUN-2010', '01-JAN-2011')"
OraDatabase.Close
'OraSession.Close
Set OraDynaSet = Nothing
Set OraDatabase = Nothing
Set OraSession = Nothing
End Sub 
Tyrus wrote:
Run-time error '91': Object variable or With block variable not set with debug on
Set OraDynaset = OraDatabase.CreateDynaset("DROP TABLE TTT_DATEPAR", 0&)Seems you forgot to set OraDatabase variable (in previous line objdatabase was set).
Add option explicit to your code to help avoid such mistakes.

UPDATE...RETURNING...INTO: ORA-24369 and ORA-22060

Hello,
array binding with "UPDATE...RETURNING...INTO :outArray" does not work for me as expected:
1. When the UPDATE does not update any row, then command.executeNonQuery() throws exception ORA-22060 (argument [{0}] is an invalid or uninitialized number).
2. When the number of rows updated does not equal the length of the input arrays (fewer or more rows), then executeNonQuery() throws exception ORA-24369 (required callbacks not registered for one or more bind handles).
Has anybody used the RETURNING...INTO clause successfully with array binding?
Thanks,
Armin
Public Function BulkUpdateNoteReturnArray() As Integer
Dim rowCount As Integer
Dim sqlStmt As String
Dim command As New OracleCommand
command.BindByName = True
'Arrays
Dim versions() As Integer = {1, 1, 1}
Dim userIds() As Decimal = {101, 102, 103}
Dim noteTexts() As String = {"Updated Note1 " & Now(), _
"Updated Note2 " & Now(), _
"Updated Note3 " & Now()}
'Returned array
Dim outArrayName As String = "noteId"
sqlStmt = "UPDATE SGNote " _
+ " SET NoteText = :noteText " _
+ " WHERE UserIdentity = :userId " _
+ " AND Version = :version " _
+ " RETURNING Identity INTO :noteId "
'Add parameter arrays
Dim noteTextsParam As New OracleParameter("noteText", OracleDbType.Varchar2)
noteTextsParam.Direction = ParameterDirection.Input
noteTextsParam.Value = noteTexts
command.Parameters.Add(noteTextsParam)
Dim userIdsParam As New OracleParameter("userId", OracleDbType.Decimal)
userIdsParam.Direction = ParameterDirection.Input
userIdsParam.Value = userIds
command.Parameters.Add(userIdsParam)
Dim versionsParam As New OracleParameter("version", OracleDbType.Int32)
versionsParam.Direction = ParameterDirection.Input
versionsParam.Value = versions
command.Parameters.Add(versionsParam)
Dim noteIdsParam As New OracleParameter(outArrayName, OracleDbType.Decimal)
noteIdsParam.Direction = ParameterDirection.Output
command.Parameters.Add(noteIdsParam)
command.ArrayBindCount = versions.Length
command.CommandText = sqlStmt
command.CommandType = CommandType.Text
Dim connection As OracleConnection
connection = New OracleConnection("Pooling=False;User ID=ARSI;Password=ARSI;Data Source=EXIGO1;")
command.Connection = connection
command.Connection.Open()
command.ExecuteNonQuery()
'"ORA-24369: required callbacks not registered for one or more bind handles"
Dim outParameter As OracleParameter
'OracleDecimal[] newSalary = (OracleDecimal[])newSal.Value;
' Dim valuesPtr As Decimal()
' Dim valuesArray() As Decimal
' Dim objectPtr As Object()
outParameter = command.Parameters(outArrayName)
' 'valuesPtr = CType(outParameter.Value, Decimal())
' 'valuesArray = CType(outParameter.Value, Decimal())
' objectPtr = CType(outParameter.Value, Object())
rowCount = outParameter.ArrayBindStatus.Length 'valuesArray.Length
command.Connection.Close()
command.Connection.Dispose()
command.Dispose()
Return rowCount
End Function

Categories

Resources