I am using the following code to create a table:
If PVar.OutputFormat.Oracle Then
Dim connection As New OracleConnection
Dim command As New OracleCommand
Try
connection.Close()
connection.UserId = gsPVarMgr.SQLServerUserName
connection.Server = gsPVarMgr.SQLServerName
connection.Password = gsPVarMgr.SQLServerPassword
connection.Open()
command.Connection = connection
command.CommandType = CommandType.Text
command.CommandText = "CREATE TABLE " & GetDBNameForOracle(CurrOutputDBName, myTableName) & " ("
Dim first As Boolean = True
For Each mycol In myTable.Columns
Dim ColumnType As String = ""
Dim ColumnName As String = ""
If first Then first = False Else ColumnName &= ","
ColumnName &= """" & mycol.ColumnName & """"
Select Case mycol.DataType.Name
Case "Single"
ColumnType = "BINARY_FLOAT"
Case "Double"
ColumnType = "BINARY_DOUBLE"
Case "String"
ColumnType = "VARCHAR2(255)"
Case "Int32"
ColumnType = "LONG"
Case "Boolean"
ColumnType = "LONG"
Case "Object"
ColumnType = "BLOB"
Case "DateTime", "Date"
ColumnType = "DATE"
Case Else
myEventMessage.OnAM("Bad type for column in output database", AMessageType.StandardError)
connection.Close()
Return
End Select
command.CommandText &= ColumnName & " " & ColumnType
Next
command.CommandText &= ")"
command.ExecuteNonQuery()
connection.Close()
Return
When I try using loader, to load the table, I get the following error:
LoadTime: 20-Jul-05 1:36:36 PM CurrTime: 20-Jul-05 3:26:57 PM Error: System.InvalidOperationException: Table contains field with data type that does not supported by OracleLoader.
at CoreLab.Oracle.OracleLoader.Open()
at AURORAServer.clsdbOutputMain.WriteTable(DataTable mytable, String mytablename) in C:\VBNet\AURORAXMPSolution\AURORAServer\clsdbOutputMain.vb:line 641
Code is the following (error occurs at loader.open :
If PVar.OutputFormat.Oracle Then
Dim connection As New OracleConnection
Dim column As New OracleLoaderColumn
Dim loader As New OracleLoader
Try
Dim myOracleTableName As String = GetDBNameForOracle(CurrOutputDBName, mytable.TableName)
connection.Close()
connection.UserId = gsPVarMgr.SQLServerUserName
connection.Server = gsPVarMgr.SQLServerName
connection.Password = gsPVarMgr.SQLServerPassword
connection.Open()
loader.Connection = connection
loader.TableName = myOracleTableName
If Not HoldLoaderCols.Contains(myOracleTableName) Then
loader.CreateColumns()
For Each mycol As OracleLoaderColumn In loader.Columns
mycol.Name = "`" & mycol.Name & "`"
Next
HoldLoaderCols.Add(myOracleTableName, loader.Columns)
Else
For Each myloadercol As OracleLoaderColumn In HoldLoaderCols.Item(myOracleTableName)
loader.Columns.Add(myloadercol)
Next
End If
loader.Open()
Dim myColFlag() As Boolean
ReDim myColFlag(mytable.Columns.Count - 1)
Dim ii As Integer = 0
For Each mycol As OracleLoaderColumn In loader.Columns
If mycol.OracleDbType = OracleDbType.Float Then
myColFlag(ii) = True
Else
myColFlag(ii) = False
End If
ii += 1
Next
For Each myrow As DataRow In mytable.Rows
Dim i As Integer = 0
For Each mycol As DataColumn In mytable.Columns
If myColFlag(i) Then
If Not IsDBNull(myrow.Item(i)) Then
If Single.IsInfinity(myrow.Item(i)) Then
myrow.Item(i) = DBNull.Value
End If
End If
End If
loader.SetValue(i, myrow.Item(i))
i += 1
Next
loader.NextRow()
Next
loader.Close()
connection.Close()
Catch e As Exception
myEventMessage.OnAM(e.ToString, AMessageType.StandardError)
connection.Close()
End Try
Return
End If
Bug in 3.1?
Direct path loading in Oracle does not support LONG, BLOB types.
OracleLoader supports the following types:
OracleLoader supports the following types:
Code: Select all
OracleDbType.VarChar
OracleDbType.Char
OracleDbType.Integer
OracleDbType.Double
OracleDbType.Date
OracleDbType.Number-
Warren Winter
But,
Oracle does not support type Integer!!
From Oracles documentation:
Table 2-1 Built-in Datatype Summary
Code Datatype Description
1 VARCHAR2(size [BYTE | CHAR]) Variable-length character string having maximum length size bytes or characters. Maximum size is 4000 bytes or characters, and minimum is 1 byte or 1 character. You must specify size for VARCHAR2.
BYTE indicates that the column will have byte length semantics; CHAR indicates that the column will have character semantics.
1 NVARCHAR2(size) Variable-length Unicode character string having maximum length size characters. The number of bytes can be up to two times size for AL16UTF16 encoding and three times size for UTF8 encoding. Maximum size is determined by the national character set definition, with an upper limit of 4000 bytes. You must specify size for NVARCHAR2.
2 NUMBER[(precision [, scale]]) Number having precision p and scale s. The precision p can range from 1 to 38. The scale s can range from -84 to 127.
8 LONG Character data of variable length up to 2 gigabytes, or 231 -1 bytes. Provided for backward compatibility.
12 DATE Valid date range from January 1, 4712 BC to December 31, 9999 AD. The default format is determined explicitly by the NLS_DATE_FORMAT parameter or implicitly by the NLS_TERRITORY parameter. The size is fixed at 7 bytes. This datatype contains the datetime fields YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND. It does not have fractional seconds or a time zone.
21 BINARY_FLOAT 32-bit floating point number. This datatype requires 5 bytes, including the length byte.
22 BINARY_DOUBLE 64-bit floating point number. This datatype requires 9 bytes, including the length byte.
180 TIMESTAMP [(fractional_seconds)] Year, month, and day values of date, as well as hour, minute, and second values of time, where fractional_seconds_precision is the number of digits in the fractional part of the SECOND datetime field. Accepted values of fractional_seconds_precision are 0 to 9. The default is 6. The default format is determined explicitly by the NLS_DATE_FORMAT parameter or implicitly by the NLS_TERRITORY parameter. The sizes varies from 7 to 11 bytes, depending on the precision. This datatype contains the datetime fields YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND. It contains fractional seconds but does not have a time zone.
181 TIMESTAMP [(fractional_seconds)] WITH TIME ZONE All values of TIMESTAMP as well as time zone displacement value, where fractional_seconds_precision is the number of digits in the fractional part of the SECOND datetime field. Accepted values are 0 to 9. The default is 6. The default format is determined explicitly by the NLS_DATE_FORMAT parameter or implicitly by the NLS_TERRITORY parameter. The size is fixed at 13 bytes. This datatype contains the datetime fields YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, TIMEZONE_HOUR, and TIMEZONE_MINUTE. It has fractional seconds and an explicit time zone.
231 TIMESTAMP [(fractional_seconds)] WITH LOCAL TIME ZONE All values of TIMESTAMP WITH TIME ZONE, with the following exceptions:
Data is normalized to the database time zone when it is stored in the database.
When the data is retrieved, users see the data in the session time zone.
The default format is determined explicitly by the NLS_DATE_FORMAT parameter or implicitly by the NLS_TERRITORY parameter. The sizes varies from 7 to 11 bytes, depending on the precision.
182 INTERVAL YEAR [(year_precision)] TO MONTH Stores a period of time in years and months, where year_precision is the number of digits in the YEAR datetime field. Accepted values are 0 to 9. The default is 2. The size is fixed at 5 bytes.
183 INTERVAL DAY [(day_precision)] TO SECOND [(fractional_seconds)] Stores a period of time in days, hours, minutes, and seconds, where
day_precision is the maximum number of digits in the DAY datetime field. Accepted values are 0 to 9. The default is 2.
fractional_seconds_precision is the number of digits in the fractional part of the SECOND field. Accepted values are 0 to 9. The default is 6.
The size is fixed at 11 bytes.
23 RAW(size) Raw binary data of length size bytes. Maximum size is 2000 bytes. You must specify size for a RAW value.
24 LONG RAW Raw binary data of variable length up to 2 gigabytes.
69 ROWID Base 64 string representing the unique address of a row in its table. This datatype is primarily for values returned by the ROWID pseudocolumn.
208 UROWID [(size)] Base 64 string representing the logical address of a row of an index-organized table. The optional size is the size of a column of type UROWID. The maximum size and default is 4000 bytes.
96 CHAR [(size [BYTE | CHAR])] Fixed-length character data of length size bytes. Maximum size is 2000 bytes or characters. Default and minimum size is 1 byte.
BYTE and CHAR have the same semantics as for VARCHAR2.
96 NCHAR[(size)] Fixed-length character data of length size characters. The number of bytes can be up to two times size for AL16UTF16 encoding and three times size for UTF8 encoding. Maximum size is determined by the national character set definition, with an upper limit of 2000 bytes. Default and minimum size is 1 character.
112 CLOB A character large object containing single-byte or multibyte characters. Both fixed-width and variable-width character sets are supported, both using the database character set. Maximum size is (4 gigabytes - 1) * (database block size).
112 NCLOB A character large object containing Unicode characters. Both fixed-width and variable-width character sets are supported, both using the database national character set. Maximum size is (4 gigabytes - 1) * (database block size). Stores national character set data.
113 BLOB A binary large object. Maximum size is (4 gigabytes - 1) * (database block size).
114 BFILE Contains a locator to a large binary file stored outside the database. Enables byte stream I/O access to external LOBs residing on the database server. Maximum size is 4 gigabytes.
Oracle does not support type Integer!!
From Oracles documentation:
Table 2-1 Built-in Datatype Summary
Code Datatype Description
1 VARCHAR2(size [BYTE | CHAR]) Variable-length character string having maximum length size bytes or characters. Maximum size is 4000 bytes or characters, and minimum is 1 byte or 1 character. You must specify size for VARCHAR2.
BYTE indicates that the column will have byte length semantics; CHAR indicates that the column will have character semantics.
1 NVARCHAR2(size) Variable-length Unicode character string having maximum length size characters. The number of bytes can be up to two times size for AL16UTF16 encoding and three times size for UTF8 encoding. Maximum size is determined by the national character set definition, with an upper limit of 4000 bytes. You must specify size for NVARCHAR2.
2 NUMBER[(precision [, scale]]) Number having precision p and scale s. The precision p can range from 1 to 38. The scale s can range from -84 to 127.
8 LONG Character data of variable length up to 2 gigabytes, or 231 -1 bytes. Provided for backward compatibility.
12 DATE Valid date range from January 1, 4712 BC to December 31, 9999 AD. The default format is determined explicitly by the NLS_DATE_FORMAT parameter or implicitly by the NLS_TERRITORY parameter. The size is fixed at 7 bytes. This datatype contains the datetime fields YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND. It does not have fractional seconds or a time zone.
21 BINARY_FLOAT 32-bit floating point number. This datatype requires 5 bytes, including the length byte.
22 BINARY_DOUBLE 64-bit floating point number. This datatype requires 9 bytes, including the length byte.
180 TIMESTAMP [(fractional_seconds)] Year, month, and day values of date, as well as hour, minute, and second values of time, where fractional_seconds_precision is the number of digits in the fractional part of the SECOND datetime field. Accepted values of fractional_seconds_precision are 0 to 9. The default is 6. The default format is determined explicitly by the NLS_DATE_FORMAT parameter or implicitly by the NLS_TERRITORY parameter. The sizes varies from 7 to 11 bytes, depending on the precision. This datatype contains the datetime fields YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND. It contains fractional seconds but does not have a time zone.
181 TIMESTAMP [(fractional_seconds)] WITH TIME ZONE All values of TIMESTAMP as well as time zone displacement value, where fractional_seconds_precision is the number of digits in the fractional part of the SECOND datetime field. Accepted values are 0 to 9. The default is 6. The default format is determined explicitly by the NLS_DATE_FORMAT parameter or implicitly by the NLS_TERRITORY parameter. The size is fixed at 13 bytes. This datatype contains the datetime fields YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, TIMEZONE_HOUR, and TIMEZONE_MINUTE. It has fractional seconds and an explicit time zone.
231 TIMESTAMP [(fractional_seconds)] WITH LOCAL TIME ZONE All values of TIMESTAMP WITH TIME ZONE, with the following exceptions:
Data is normalized to the database time zone when it is stored in the database.
When the data is retrieved, users see the data in the session time zone.
The default format is determined explicitly by the NLS_DATE_FORMAT parameter or implicitly by the NLS_TERRITORY parameter. The sizes varies from 7 to 11 bytes, depending on the precision.
182 INTERVAL YEAR [(year_precision)] TO MONTH Stores a period of time in years and months, where year_precision is the number of digits in the YEAR datetime field. Accepted values are 0 to 9. The default is 2. The size is fixed at 5 bytes.
183 INTERVAL DAY [(day_precision)] TO SECOND [(fractional_seconds)] Stores a period of time in days, hours, minutes, and seconds, where
day_precision is the maximum number of digits in the DAY datetime field. Accepted values are 0 to 9. The default is 2.
fractional_seconds_precision is the number of digits in the fractional part of the SECOND field. Accepted values are 0 to 9. The default is 6.
The size is fixed at 11 bytes.
23 RAW(size) Raw binary data of length size bytes. Maximum size is 2000 bytes. You must specify size for a RAW value.
24 LONG RAW Raw binary data of variable length up to 2 gigabytes.
69 ROWID Base 64 string representing the unique address of a row in its table. This datatype is primarily for values returned by the ROWID pseudocolumn.
208 UROWID [(size)] Base 64 string representing the logical address of a row of an index-organized table. The optional size is the size of a column of type UROWID. The maximum size and default is 4000 bytes.
96 CHAR [(size [BYTE | CHAR])] Fixed-length character data of length size bytes. Maximum size is 2000 bytes or characters. Default and minimum size is 1 byte.
BYTE and CHAR have the same semantics as for VARCHAR2.
96 NCHAR[(size)] Fixed-length character data of length size characters. The number of bytes can be up to two times size for AL16UTF16 encoding and three times size for UTF8 encoding. Maximum size is determined by the national character set definition, with an upper limit of 2000 bytes. Default and minimum size is 1 character.
112 CLOB A character large object containing single-byte or multibyte characters. Both fixed-width and variable-width character sets are supported, both using the database character set. Maximum size is (4 gigabytes - 1) * (database block size).
112 NCLOB A character large object containing Unicode characters. Both fixed-width and variable-width character sets are supported, both using the database national character set. Maximum size is (4 gigabytes - 1) * (database block size). Stores national character set data.
113 BLOB A binary large object. Maximum size is (4 gigabytes - 1) * (database block size).
114 BFILE Contains a locator to a large binary file stored outside the database. Enables byte stream I/O access to external LOBs residing on the database server. Maximum size is 4 gigabytes.