| Author |
Message
|
| vickas |
 Posted: Tue Feb 28, 2017 3:20 am Post subject: passing string(more than 2k) to stored procedure on oracle |
 |
|
Centurion
Joined: 18 Aug 2013
Posts: 126
|
Hie guys ..
I get the below exceptions when passing a string more than 2000 characters to the stored procedure from the flow .
so Child SQL exception 22001 [unixODBC][IBM][ODBC Oracle Wire Protocol driver]String data, right truncated. Error in parameter 1
so Child SQL exception 32 [unixODBC][IBM][ODBC 20101 driver]1816073
the below is the simple DB procedure on oracle which is defined as below
which takes the input string and returns the same
| Code: |
create or replace
PROCEDURE Test(inpstr IN CLOB, outstr OUT CLOB, error_message OUT CHARACTER)
IS
BEGIN
outstr := inpstr ;
DBMS_OUTPUT.PUT_LINE(outstr);
EXCEPTION
WHEN OTHERS THEN
error_message := SQLERRM;
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END; |
I get the above mentioned exception ONLY when the input string("Input") is more than 2000 characters.
here is the esql code
| Code: |
CALL Fetchtest(Input,Output,error_message)
CREATE PROCEDURE Fetchtest(IN inpstr CHARACTER, OUT outstr CHARACTER ,OUT error_message CHARACTER)
LANGUAGE DATABASE
EXTERNAL NAME "TEST"; |
ODBCINI configuration
| Code: |
;# Oracle stanza
[TEST]
Driver=/opt/ibm/mqsi/9.0.0.1/ODBC/V7.0/lib/UKora26.so
Description=DataDirect 7.0 ODBC Oracle Wire Protocol
HostName=10.10.10.10
PortNumber=1521
ServiceName=DBSN
CatalogOptions=0
EnableStaticCursorsForLongData=0
ApplicationUsingThreads=1
EnableDescribeParam=1
OptimizePrepare=1
WorkArounds=536870912
ProcedureRetResults=1
ColumnSizeAsCharacter=1
LoginTimeout=0 |
here is the environment
| Quote: |
IIB version - 9.0.0.2
MQ version - 7.5
OS - RHEL 6.5 |
is there any setting to be done on odbc ini file ?
is it a limitation in IIB ?
pls help me , how can we pass more than 2k chars length to procedure ?
[/b] |
|
| Back to top |
|
 |
| Vitor |
| Posted: Tue Feb 28, 2017 5:45 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
You're cheating slightly - the stored procedure isn't using strings, the parameters are CLOB which the DataDirect drivers don't support.
Change the stored procedure to use VARCHAR and it will work to the maximum size of a VARCHAR in Oracle.
If you need to pass a string longer than that through a stored procedure, change your design and/or use a different ETL tool than IIB.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| adubya |
| Posted: Tue Feb 28, 2017 6:15 am Post subject: |
|
|
Partisan
Joined: 25 Aug 2011
Posts: 377
Location: GU12, UK
|
|
| Back to top |
|
|
| Vitor |
| Posted: Tue Feb 28, 2017 6:30 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
I'm fairly sure that you can't map a character to a CLOB in a stored procedure, and if you use a CLOB in a ESQL DB statement it maps to a BLOB not a CHARACTER.
Having said that, I was fairly sure going drinking with @mqjeff wouldn't result in getting so drunk I'd have to show the cab driver my hotel key so he knew where to take me and I was seriously mistaken about that.
For the record (and before my most worthy associate says anything) I did not fall over drunk on the sidewalk. That tree pulled out in front of me without warning and I collided with the bench trying to avoid it, losing my footing in the process.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| mqjeff |
| Posted: Tue Feb 28, 2017 6:33 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
_________________
chmod -R ugo-wx / |
|
| Back to top |
|
|
| adubya |
| Posted: Tue Feb 28, 2017 6:33 am Post subject: |
|
|
Partisan
Joined: 25 Aug 2011
Posts: 377
Location: GU12, UK
|
I can't vouch for drinking sessions with mqjeff 
But the mapping table I linked to shows CLOBs are implicitly cast to ESQL CHAR types when referenced in ESQL calls.
_________________
Independent Middleware Consultant
andy@knownentity.com |
|
| Back to top |
|
|
| vickas |
| Posted: Tue Feb 28, 2017 8:38 pm Post subject: |
|
|
Centurion
Joined: 18 Aug 2013
Posts: 126
|
| Vitor wrote: |
You're cheating slightly - the stored procedure isn't using strings, the parameters are CLOB which the DataDirect drivers don't support.
. |
I have changed the input parameters of the DB stored procedure to VARCHAR and passed input as character from ESQL
I also tried the other way around of passing Blob from ESQL to the DB stored procedure while changing the input parameters to CLOB
in both the cases , i get the same exception .
can you please throw some light |
|
| Back to top |
|
|
| mqjeff |
| Posted: Wed Mar 01, 2017 5:00 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
The error is saying that the data you're sending in is too long for the field in use.
"String data, right truncated. Error in parameter 1" means "the string you sent in as parameter 1 was truncated from the right'.
It's possible that the db is expecting a ccsid that uses only single byte characters and you are sending in multibyte characters, so while the string looks like the right size, the db is counting the length wrong.
Regardless, take a user and odbc trace. Then ask the db admins to look at what they are seeing on their side - and make sure that your definition matches the stored procedure you're calling...
_________________
chmod -R ugo-wx / |
|
| Back to top |
|
|
|
|
