Talend community forum / [resolved] connecting to a distant oracle server

fantom · 2012-05-14 14:08:37

Hi,
I have a problem extracting some data from a distant oracle 9i database, actually the metadata configuration is good but when it comes to schema it's all messed up, I've chosen repository option in both schema and query type but it still doesn't work even if I used "retrieve schema", and after executing the job (oracleinput ---> tmap -----> excel ) it shows this error :

Exception in component tOracleInput_1
java.sql.SQLException: ORA-00911: invalid character

at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:590)
at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1973)
at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteDescribe(TTC7Protocol.java:850)
at oracle.jdbc.driver.OracleStatement.doExecuteQuery(OracleStatement.java:2599)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2963)
at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:653)
at exemple.toad_0_1.TOAD.tOracleInput_1Process(TOAD.java:651)
at exemple.toad_0_1.TOAD.runJobInTOS(TOAD.java:1073)
at exemple.toad_0_1.TOAD.main(TOAD.java:941)

in the first image : I try to connect to the oracle9i database, which it works successfuly.
in the second image : I execute my query so I can extract the data I want.
in the third image : I retrieve the schema from the database and choose only one which is SYSADM (because the table is so full of many columns that talend crushes each time after retrieving them all) .
in the fourth image: I go back to edit schema and add 2 columns ( those I want to extract).
in the fifth image : shown all the configuration used to extract.
and after executing the job it generates the error already mentioned.
any help please! I'd bo so grateful.

image 1 : http://www.freeimagehosting.net/p99m3
image 2 : http://www.freeimagehosting.net/mpbf8
image 3 : http://www.freeimagehosting.net/2sz3m
image 4 : http://www.freeimagehosting.net/ekiba
image 5 : http://www.freeimagehosting.net/a9kx9

Last edited by fantom (2012-05-14 14:19:46)

pedro · 2012-05-15 05:44:50

Hi

I'm not sure, but I think the query of tOracleInput may be not correct.

Code:

"select \"CASE_ID\".\"BUSINESS_UNIT\" from sysadm.PS_RC_CASE where \"CASE_ID\"='220416.0' "

There is an extra semicolon in your query.

Regards,
Pedro

fantom · 2012-05-15 11:29:18

indeed,you're right, the query worked and was able to extract data from the table in the DB .but still I don't understand the fact that this very query works with or without the semicolon in the talend sql builder whereas it doesn't when it comes to attach it (when talend adds the double quotes ).
I'm so grateful, thanks for your time and your help.
fantom.

pedro · 2012-05-15 11:41:27

Hi

The query in DB components, in fact, is stored into a String variable which will be execute by executeQuery() method later on. It is decided by JAVA JDBC. So just don't type semicolon in the end of 'Query' textfield...

Regards,
Pedro

fantom · 2012-05-15 12:47:18

Thanks for your attention and your advice.
best regards,
fantom

Announcement

Unanswered posts

#1 2012-05-14 14:08:37

[resolved] connecting to a distant oracle server

#2 2012-05-15 05:44:50

Re: [resolved] connecting to a distant oracle server

Code:

#3 2012-05-15 11:29:18

Re: [resolved] connecting to a distant oracle server

#4 2012-05-15 11:41:27

Re: [resolved] connecting to a distant oracle server

#5 2012-05-15 12:47:18

Re: [resolved] connecting to a distant oracle server

Board footer