| Author |
Message
|
| ganeshkj |
 Posted: Wed Jun 16, 2004 2:38 am Post subject: Inserting Large XML into Oracle database - Urgent |
 |
|
Novice
Joined: 02 Jun 2004
Posts: 13
Location: Bangalore
|
Hi,
I am inserting large XML (more than 4 K in size) to Oracle 9.0 and above database from MQSI 2.1 using the database node. It is showing the following error message: "[DataDirect][ODBC Oracle driver][Oracle]ORA-01461: can bind a LONG value only for insert into a LONG column".
While inserting smaller sized XML, it is getting inserted successfully.
The full message is displayed below. I heard from somewhere that there is a limitation of 4 K. I tried changing the XMLTYPE datatype to CLOB in Oracle, and the data is getting inserted successfully.
But this will affect lots of other applications. So is there any way i can achieve the result while keeping the datatype as XMLTYPE in oracle.
I am using the normal oracle driver supplier along with MQSI.
- <DatabaseException>
<File>F:\build\S210_P\src\DataFlowEngine\ImbOdbc.cpp</File>
<Line>156</Line>
<Function>ImbOdbcHandle::checkRcInner</Function>
<Type />
<Name />
<Label />
<Text>Root SQL exception</Text>
<Catalog>WMQIv210</Catalog>
<Severity>3</Severity>
<Number>2321</Number>
- <Insert>
<Type>2</Type>
<Text>-1</Text>
</Insert>
- <DatabaseException>
<File>F:\build\S210_P\src\DataFlowEngine\ImbOdbc.cpp</File>
<Line>258</Line>
<Function>ImbOdbcHandle::checkRcInner</Function>
<Type />
<Name />
<Label />
<Text>Child SQL exception</Text>
<Catalog>WMQIv210</Catalog>
<Severity>3</Severity>
<Number>2322</Number>
- <Insert>
<Type>5</Type>
<Text>HY000</Text>
</Insert>
- <Insert>
<Type>2</Type>
<Text>1461</Text>
</Insert>
- <Insert>
<Type>5</Type>
<Text>[DataDirect][ODBC Oracle driver][Oracle]ORA-01461: can bind a LONG value only for insert into a LONG column</Text>
</Insert>
</DatabaseException>
</DatabaseException>
</RecoverableException>
</RecoverableException>
Please let me know any other information is required.
Thanx and regards
Ganesh |
|
| Back to top |
|
 |
| JLRowe |
| Posted: Wed Jun 16, 2004 2:46 am Post subject: |
|
|
Yatiri
Joined: 25 May 2002
Posts: 664
Location: South East London
|
| I had a similar problem, and (as I remember) we got round it by binding the XML string to an oracle variable first. Try searching metalink. |
|
| Back to top |
|
|
| ganeshkj |
| Posted: Wed Jun 16, 2004 3:11 am Post subject: |
|
|
Novice
Joined: 02 Jun 2004
Posts: 13
Location: Bangalore
|
Hi,
Did not clearly understand by your statement " got round it by binding the XML string to an oracle variable first" ?
Does it mean that i need to use a stored procedure in Oracle and call from MQSI ?
Thnx and regards
Ganesh |
|
| Back to top |
|
|
| JLRowe |
| Posted: Wed Jun 16, 2004 4:57 am Post subject: |
|
|
Yatiri
Joined: 25 May 2002
Posts: 664
Location: South East London
|
| I mean you need 2 SQL statements, the first to set a PL/SQL variable, and the second to run the INSERT containing the value as a variable. It's something to do with a restriction in the ODBC driver. |
|
| Back to top |
|
|
| ganeshkj |
| Posted: Wed Jun 16, 2004 9:29 am Post subject: |
|
|
Novice
Joined: 02 Jun 2004
Posts: 13
Location: Bangalore
|
Hi,
Currently I have the following in the flow:
Insert statement as
Insert into Database.Tablename(Field1, XMLfield2) Values (value1, XMLValue);
Where XMLValue is a variable defined in the ESQL to hold the XML data and is passed using the ESQL Insert statement.
I am not clear about how to set a PL/SQL variable. Also when i use the Insert statement how to use the set variable in the Insert statement?
Are you suggesting to use PASSTHRU ?
Request you to provide more details as i am not clear about your suggestion as i am new to this MQSI 
Thanx alot for the pointers already given.
Rgds
Ganesh |
|
| Back to top |
|
|
| Lisa |
| Posted: Wed Jun 16, 2004 10:40 am Post subject: XML |
|
|
Master
Joined: 07 Jun 2002
Posts: 287
Location: NJ
|
Please refer to your Oracle programming guide manual for PL/SQL samples. You may also have PL/SQL samples in one of the Oracle directories.
Lisa |
|
| Back to top |
|
|
| ganeshkj |
| Posted: Thu Jun 17, 2004 12:40 am Post subject: |
|
|
Novice
Joined: 02 Jun 2004
Posts: 13
Location: Bangalore
|
Hi,
Here i am facing problem while inserting from MQSI using ESQL...How will it help from reading Oracle PL/SQL books ? Could you plz suggest ?
Mr. Rowe, could you provide more clarity to your soln plz ?
Thnx and regards
Ganesh |
|
| Back to top |
|
|
| JLRowe |
| Posted: Thu Jun 17, 2004 1:01 am Post subject: |
|
|
Yatiri
Joined: 25 May 2002
Posts: 664
Location: South East London
|
*I don't exactly remember the solution*
... And reading the manuals is generally considered a good thing to do when you hit a problem.
You're either going to have to find an oracle person to help you, or read up on it yourself. |
|
| Back to top |
|
|
| ganeshkj |
| Posted: Thu Jun 17, 2004 2:19 am Post subject: |
|
|
Novice
Joined: 02 Jun 2004
Posts: 13
Location: Bangalore
|
Thnx...
Let me figure out what could be the problem....
Anyway i have alternate solutions which could be considered..Instead of MQSI doing the insert, need to look at whether this can be replaced with MDBs which will definitely.
I repeatedly asked because you already faced and solved.
Anyway,
Rgds
Ganesh |
|
| Back to top |
|
|
|
|
