| Author |
Message
|
| sieijish |
 Posted: Mon Oct 24, 2005 1:32 am Post subject: ODBC String error error invoking Oracle Stored procedure |
 |
|
Acolyte
Joined: 29 Nov 2004
Posts: 67
Location: London
|
I have defined an esql procedure to invoke an oracle stored procedure. CREATE PROCEDURE archiveOraProc( IN pFields CHARACTER, IN pValues CHARACTER) EXTERNAL NAME "AUDITDB_SCHEMA.AUDIT_INSERT"; When I invoke this procedure from the compute module's main method, I am getting the following error when passing string parameters of length >2000 characters
(0x01000000):DatabaseException = (
(0x03000000):File = '/build/S500_P/src/DataFlowEngine/ImbOdbc.
cpp'
(0x03000000):Line = 366
(0x03000000):Function = 'ImbOdbcHandle::checkRcInner'
(0x03000000):Type = ''
(0x03000000):Name = ''
(0x03000000):Label = ''
(0x03000000):Text = 'Child SQL exception'
(0x03000000):Catalog = 'BIPv500'
(0x03000000):Severity = 3
(0x03000000):Number = 2322
(0x01000000):Insert = (
(0x03000000):Type = 5
(0x03000000):Text = '22001'
)
(0x01000000):Insert = (
(0x03000000):Type = 2
(0x03000000):Text = '0'
)
(0x01000000):Insert = (
(0x03000000):Type = 5
(0x03000000):Text = '[DataDirect][ODBC Oracle driver]String data
, right truncated. Error in parameter 2.'
)
It works fine with parameters less than 2000. If I execute the proc with the same parameters outside of broker, it is going through which shows my procedure is fine. Has anyone faced this error before? I am at wbimb5.0 + fp06 |
|
| Back to top |
|
 |
| mgk |
| Posted: Wed Nov 02, 2005 1:11 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
Hi,
From what you say, I would expect this to work fine, so I suggest you raise a PMR
_________________
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 |
|
|
| sieijish |
| Posted: Wed Nov 02, 2005 1:20 am Post subject: |
|
|
Acolyte
Joined: 29 Nov 2004
Posts: 67
Location: London
|
| Yes. IBM has confirmed that this is a known issue when invoking oracle stored procedures with parameter declared as varchar2. Workaround is to use CLOB data type. |
|
| Back to top |
|
|
| sknrt1 |
| Posted: Wed Nov 02, 2005 2:14 pm Post subject: |
|
|
Apprentice
Joined: 22 Jan 2003
Posts: 39
Location: USA
|
VARCHAR2 in the oracle stored proc can accept upto 4000 characters when called from Broker, above that need to use CLOB. Actuall PL/SQL can accept upto 32k of data for varchar2, but SQL can only accept 4000 char's. Broker ODBC driver falls into SQL category.
There are few thing need to be considered when StoredProc field is declared as CLOB
1. Broker can't pass null to this field (ie: when called from ESQL the value passed should not be null)
2. using CLOB within storedproc, is a overhead on performance, because oracle uses temp memory/space to cache
So, it would be good to have 2 different storedproc's one with Varchar2 and other with CLOB. Do a check on length of data and call the appropriated procedure (<=4000 or >4000), if the data is null use empty char to pass in.
Thanks |
|
| Back to top |
|
|
| sieijish |
| Posted: Thu Nov 03, 2005 1:24 am Post subject: |
|
|
Acolyte
Joined: 29 Nov 2004
Posts: 67
Location: London
|
| Broker ODBC driver was failing at 2000 chars. So it wasn't an SQL limitation. I am pressing for a fix from IBM as I want to use Varchar2 itself for strings upto 4000. |
|
| Back to top |
|
|
| sknrt1 |
| Posted: Thu Nov 03, 2005 8:19 am Post subject: |
|
|
Apprentice
Joined: 22 Jan 2003
Posts: 39
Location: USA
|
What's the odbc driver being used?
We have an application implemented to use Varchar2 upto 4000 char's.
And here is the configuration in place:
AIX v5.3, WBIMB v5, CSD-06, Oracle-9.2.0.x
Driver: UKor818.so (DataDirect 410 Oracle 8 Driver)
it would be interesting to have more details about 2000 char limitation, like version of oracle, driver ....
Thanks |
|
| Back to top |
|
|
| sieijish |
| Posted: Thu Nov 03, 2005 8:32 am Post subject: |
|
|
Acolyte
Joined: 29 Nov 2004
Posts: 67
Location: London
|
AIX 5.2 + ML6
WBIMB 5 + CSD06
ODBC dirver is the same shipped with wbimb - UKor818.so
Oracle 10g (this could be the issue as officially IBM hasn't announced 10g support for broker yet...)
Don't know why they are delaying it now that 10g support is already announced for MQ.5.3 Fp11 |
|
| Back to top |
|
|
|
|
