topic SQL XML Oracle - tOracleInput won't retrieve data in Talend Studio

SQL XML Oracle - tOracleInput won't retrieve data

Anonymous — Tue, 25 Jul 2017 16:35:02 GMT

Hello Everyone,

I have a very simple query from an Oracle DB aiming at returning a single XML node:

"
select xmlelement(\"edition\", edition_id) as xml
from table1
where isbn_unhyphenated = '978311036762'
"

I want to write the result of the query in an XML-file. The result should be:

My tOracleInput should return the above result as a document to a tFileOutputXML which ought to save it on my drive then.

Yet I get the following error:

Below you will find the settings of my components:

I use Talend 6.4.1.

DB connectivity issues are out of question - checked and tested. The problem starts happening when I want to save XML as a document.

Please advice what am I doing wrong in here.

Best!

Re: SQL XML Oracle - tOracleInput won't retrieve data

Anonymous — Fri, 28 Jul 2017 10:59:15 GMT

Hello,

DB connectivity issues are out of question - checked and tested. The problem starts happening when I want to save XML as a document.

You need to convert the resulting String to a Document using the tConvertType component.

On tFileOutputXML component, check the 'incoming is a document' box.

Best regards

Sabrina

Re: SQL XML Oracle - tOracleInput won't retrieve data

Anonymous — Fri, 28 Jul 2017 12:01:23 GMT

Thanks for the answer. Unfortunately it does not help.I got the following error:

Exception in component tFileOutputXML_1 ([job])
java.lang.NullPointerException
   at [project].[job].tOracleInput_2Process(Books_KupDb_Test.java:1054)
   at [project].[job].runJobInTOS(Books_KupDb_Test.java:1508)
   at [project].[job].main(Books_KupDb_Test.java:1327)

So I went to basics to check if I get anything from tOracleInput at all. To my surprise I stated the string is empty after I put in an SQL query that resulted in an XML. On the other hand, when I used a simple query returning a single column, the output was correct.

The output that I check directly in Oracle SQL Developer is correct - I get the expected XML (as described in the first post).

Hence a question arises: Does tOracleInput have any problems with SQL queries, that deliver XMLs?

This query delivers nothing when executed via tOracleInput:

"
select xmlelement(\"edition\", edition_id) as xml
from [table]
where isbn_unhyphenated = '978311036762'
"

The expected output is: <edition>430424</edition>

And this one is OK:

select edition_id
from [table]
where isbn_unhyphenated = '978311036762'

Re: SQL XML Oracle - tOracleInput won't retrieve data

Anonymous — Thu, 10 Aug 2017 09:34:49 GMT

Hello Everyone,

this one has still not been solved Anyone any ideas, please?

Re: SQL XML Oracle - tOracleInput won't retrieve data

Anonymous — Mon, 14 Aug 2017 07:42:19 GMT

Hello,

Could you please post your job setting screenshots into forum?

Best regards

Sabrina

Re: SQL XML Oracle - tOracleInput won't retrieve data

Anonymous — Thu, 17 Aug 2017 11:48:25 GMT

Hi Sabrina,

here is the complete documentation:

01_Job_overview - there are two test scenarios: save as txt and save as xml (incoming document).

The first scenario: save as txt has two subscenarios:

a) retrieve via normal SQL

b) retrieve via xmlelement

ad a) the query gets executed, everything works perfectly, the result is saved into the set path - this makes me say there are no connection problems;

ad b) no errors messages, a non-empty txt-file is saved (1kb), but there is no visible data in it;

The second scenario - save as xml (incoming document) after you had been retrieved as string - transform from string to a document on the way. Again, there are two subscenarios, same as in the first scenario:

a) retrieve via normal SQL

b) retrieve via xmlelement

ad a) error message

Exception in component tFileOutputXML_1 (tmp)
java.lang.NullPointerException
   at submissionchecker_20170720.tmp_0_1.tmp.tOracleInput_1Process(tmp.java:930)
   at submissionchecker_20170720.tmp_0_1.tmp.runJobInTOS(tmp.java:1384)
   at submissionchecker_20170720.tmp_0_1.tmp.main(tmp.java:1203)

No file created

ad b) there is an error:

an empty file gets created (understandable...)

You have settings of individual components in attachments 02 to 04. 05 and 06 shows you what is Oracle output of the query.

Please let me know if an additional info is needed.

Best,

Jakub

01_Job_overview.PNG
02_tOracleInput_output_settings.PNG
03_tConvertType_settings.PNG
04_tFileOutputXML_settings.PNG
05_oracle_sql_result_view.PNG.jpg

Re: SQL XML Oracle - tOracleInput won't retrieve data

Anonymous — Thu, 17 Aug 2017 16:20:02 GMT

I think there's a problem with XMLType and Oracle ...

If you find a solution, i'm really interested.

You can try to use in advanced setting the option "Convert XMLType to Java Type (juste when extract return a NODE)" . . .
But you have to modify a bit your SQL like :

select extract(xmlelement(\"edition\", COLUMN ), 'edition') as xml from table where ...

And in your schema the dbtype must be XMLTYPE and talend type String ...

I try it but fail =(

Edit :

PS : If the purpose of your job is to convert database data to XML, there is other way to do this in talend

Re: SQL XML Oracle - tOracleInput won't retrieve data

Anonymous — Fri, 23 Mar 2018 09:39:40 GMT

Hi,

any progress on that one? I still have issues with exporting XML data directly from an Oracle SQL database via tOracleInput...

Best,

Jakub