| Author |
Message
|
| spahrson |
 Posted: Fri Feb 18, 2011 2:07 am Post subject: Problems with Broker 7, Oracle 11g and CLOB Datatype... |
 |
|
Newbie
Joined: 18 Feb 2011
Posts: 6
|
Hi.
i tried to insert a CLOB in Oracle 11g over
- 1: INSERT Statement
- 2: PASSTROUGH INSERT Statement
- 3: EXTERNAL PROCEDURE / STORED PROCEDURE
- 4: PASSTROUGH STORED PROCEDURE CALL
We use DataDirect v6 ODBC Driver! Standart Oracle connection params...
The following happens for my cases:
- 1 + 2: Exception: [IBM][ODBC Oracle Wire Protocol driver][Oracle]ORA-03146: Invalid buffer length for TTC field
- 3 + 4: No Exceptions. CLOB Param is null/empty!
We updatet from 7.0.0.1 to 7.0.0.2. Under 7.0.0.1 it worked with case 4 and 500KB Messages.
If I change CLOB Type to VARChAR2 it works with smaller messages...
mqsicvp says:
| Code: |
databaseProviderVersion = 11.01.0000 Oracle 11.1.0.0.0
driverVersion = 06.00.0214 (B0147, U0063)
driverOdbcVersion = 03.52
driverManagerVersion = 3.52.0000
driverManagerOdbcVersion = 03.52.0000
databaseProviderName = Oracle
datasourceServerName = xxxxxxxxxx
databaseName = N/A
odbcDatasourceName = IMBDB
driverName = UKora24.so
supportsStoredProcedures = Yes
procedureTerm = PL/SQL
accessibleTables = Yes
accessibleProcedures = Yes
identifierQuote = "
specialCharacters = None
describeParameter = Yes
schemaTerm = User Name
tableTerm = Table
sqlSubqueries = 31
activeEnvironments = 0
maxDriverConnections = 0
maxCatalogNameLength = 128
maxColumnNameLength = 30
maxSchemaNameLength = 30
maxStatementLength = 0
maxTableNameLength = 30
supportsDecimalType = Yes
supportsDateType = No
supportsTimeType = No
supportsTimeStampType = Yes
supportsIntervalType = No
supportsAbsFunction = Yes
supportsAcosFunction = No
supportsAsinFunction = No
supportsAtanFunction = No
supportsAtan2Function = No
supportsCeilingFunction = Yes
supportsCosFunction = Yes
supportsCotFunction = No
supportsDegreesFunction = No
supportsExpFunction = Yes
supportsFloorFunction = Yes
supportsLogFunction = Yes
supportsLog10Function = Yes
supportsModFunction = Yes
supportsPiFunction = No
supportsPowerFunction = Yes
supportsRadiansFunction = No
supportsRandFunction = No
supportsRoundFunction = Yes
supportsSignFunction = Yes
supportsSinFunction = Yes
supportsSqrtFunction = Yes
supportsTanFunction = Yes
supportsTruncateFunction = Yes
supportsConcatFunction = Yes
supportsInsertFunction = Yes
supportsLcaseFunction = Yes
supportsLeftFunction = Yes
supportsLengthFunction = Yes
supportsLTrimFunction = Yes
supportsPositionFunction = No
supportsRepeatFunction = Yes
supportsReplaceFunction = Yes
supportsRightFunction = Yes
supportsRTrimFunction = Yes
supportsSpaceFunction = Yes
supportsSubstringFunction = Yes
supportsUcaseFunction = Yes
supportsExtractFunction = No
supportsCaseExpression = No
supportsCastFunction = No
supportsCoalesceFunction = No
supportsNullIfFunction = No
supportsConvertFunction = Yes
supportsSumFunction = Yes
supportsMaxFunction = Yes
supportsMinFunction = Yes
supportsCountFunction = Yes
supportsBetweenPredicate = Yes
supportsExistsPredicate = Yes
supportsInPredicate = Yes
supportsLikePredicate = Yes
supportsNullPredicate = Yes
supportsNotNullPredicate = Yes
supportsLikeEscapeClause = Yes
supportsClobType = No
supportsBlobType = No
charDatatypeName = CHAR
varCharDatatypeName = VARCHAR2
longVarCharDatatypeName = CLOB
clobDatatypeName = N/A
timeStampDatatypeName = TIMESTAMP
binaryDatatypeName = RAW
varBinaryDatatypeName = RAW
longVarBinaryDatatypeName = BLOB
blobDatatypeName = N/A
intDatatypeName = NUMBER
doubleDatatypeName = BINARY_DOUBLE
varCharMaxLength = 4000
longVarCharMaxLength = 2147483647
clobMaxLength = 0
varBinaryMaxLength = 2000
longVarBinaryMaxLength = 2147483647
blobMaxLength = 0
timeStampMaxLength = 29
identifierCase = Upper
escapeCharacter = \
longVarCharDatatype = -1
clobDatatype = 0
longVarBinaryDatatype = -4
blobDatatype = 0
BIP8273I: The following datatypes and functions are not natively supported by da tasource 'IMBDB' using this ODBC driver: Unsupported datatypes: 'DATE, TIME, INT ERVAL, CLOB, BLOB' Unsupported functions: 'ACOS, ASIN, ATAN, ATAN2, COT, DEGREES , PI, RADIANS, RAND, POSITION, EXTRACT, CASE, CAST, COALESCE, NULLIF'
Examine the specific datatypes and functions not supported natively by this data source using this ODBC driver.
When using these datatypes and functions within ESQL, the associated data proces sing is done within WebSphere Message Broker rather than being processed by the database provider.
|
Some ideas? I don't really understand the mqsicvp results? Are this database or broker settings? |
|
| Back to top |
|
 |
| mqjeff |
| Posted: Fri Feb 18, 2011 2:12 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
Did you review the ESQL->ODBC data type mappings?
What ESQL type of data are you trying to put into a CLOB field? |
|
| Back to top |
|
|
| spahrson |
| Posted: Fri Feb 18, 2011 2:17 am Post subject: |
|
|
Newbie
Joined: 18 Feb 2011
Posts: 6
|
The ESQL data type is CHARACTER.
In InfoCenter I found the following under Supported databases->ODBC support->Notes
| Code: |
5:Each broker system requires only the Client SDK; install the Dynamic Server on the system on which you create databases.
Large Objects (LOBs) are not supported.
|
CLOBs are not supported? Why did id work under 7.0.0.1? hap?
So does i have to implement this over JDBC? |
|
| Back to top |
|
|
| mgk |
| Posted: Fri Feb 18, 2011 3:56 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1647
|
The note below, (5) only applies to Informix. CLOBs are supported for Oracle and map to ESQL Character and can be used from ESQL. Please raise a PMR if you are still having problems as this should work. However, please check the "DescribeParam" option is set in the Odbc ini first.
Regards,
_________________
MGK
The postings I make on this site are my own and don't necessarily represent IBM's positions, strategies or opinions. |
|
| Back to top |
|
|
| spahrson |
| Posted: Thu Feb 24, 2011 12:54 am Post subject: |
|
|
Newbie
Joined: 18 Feb 2011
Posts: 6
|
we'll raise a pmr...  |
|
| Back to top |
|
|
| lalitdowlani |
| Posted: Sun Jun 26, 2011 12:08 am Post subject: Message Broker Oracle Stored Procedure CLOB issue |
|
|
Novice
Joined: 21 Feb 2011
Posts: 15
|
Hi
I am facing the same issue . Please let me know whether u rasied any PMR and please share the details of same |
|
| Back to top |
|
|
| mqjeff |
| Posted: Sun Jun 26, 2011 4:10 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
Open a PMR yourself. *include a link to this thread* in the PMR text.
Do all of this AFTER completing the necessary mustgather so that you can demonstrate the problem. Presuming you can actually repeat the issue. |
|
| Back to top |
|
|
| fwsld |
| Posted: Tue Jun 28, 2011 11:53 pm Post subject: |
|
|
Newbie
Joined: 13 Jul 2006
Posts: 5
|
I had this same problem when trying to store full messages in an Oracle database table as BLOB's.
Here is the real problem. If you want to pass any data into a field with a size greater then the default SQL datatype limit, then the ODBC Oracle driver chunks it up under the covers. That is it inserts the maximum amount of data possible, then selects the newly inserted record and appends the maximum amount of data possible. This loop continues until all the data has been updated. Or an error occurs.
To allow this you must grant the user that is performing the insert, select and update privileges for the database table, as well as insert obviously. This is working for me in Broker 7 Fp2 inserting to both BLOB and CLOB fields. |
|
| Back to top |
|
|
| gamawmb |
| Posted: Thu Sep 18, 2014 9:51 pm Post subject: |
|
|
Newbie
Joined: 29 Jul 2014
Posts: 4
|
| Thanks fwsld, same solution is working in Broker 8 Fp 1 connected to Oracle 12c |
|
| Back to top |
|
|
|
|
