[resolved] Talend TOracle componet Error.

One Star

[resolved] Talend TOracle componet Error.

Hi,
I am trying to connect Oracle data base. when i am running a job I am getting this error. Please find bellow. Could you please suggest me.
Exception in component tOracleInput_1
java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist
at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:91)
at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:133)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:206)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:455)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:413)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:1034)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:183)
at oracle.jdbc.driver.T4CStatement.executeForDescribe(T4CStatement.java:780)
at oracle.jdbc.driver.T4CStatement.executeMaybeDescribe(T4CStatement.java:855)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1186)
at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:1377)
at oracle.jdbc.driver.OracleStatementWrapper.executeQuery(OracleStatementWrapper.java:387)
at salesforce.test_0_1.Test.tOracleInput_1Process(Test.java:1745)
at salesforce.test_0_1.Test.tSetProxy_1Process(Test.java:1338)
at salesforce.test_0_1.Test.tFileInputProperties_1Process(Test.java:1230)
at salesforce.test_0_1.Test.runJobInTOS(Test.java:2507)
at salesforce.test_0_1.Test.main(Test.java:2157)
disconnected
Thank you,
Praveen

Accepted Solutions
One Star

Re: [resolved] Talend TOracle componet Error.

Thank you for your suggestion. Toracle input component after using the schema it's working.
Thank you for you help.
Praveen

All Replies
One Star

Re: [resolved] Talend TOracle componet Error.

ORA-00942: table or view does not exist....
Look at your query does it point to the correct schema database and table?
Cheers
One Star

Re: [resolved] Talend TOracle componet Error.

We've had this before, for us the problem was when the table we reading from ( tOracleInput ) wasnt owned by the user we connected to the oracle server as, by default it prefixes the schema on the front of the table.
So you have to edit the select statement and remove schema name or replace it with the actual schema it belongs to.
Eg
select * from logon_user.table
becomes
select * from actual_table_owner.table
...assuming the logon_user has the correct select grants for the table in question.
One Star

Re: [resolved] Talend TOracle componet Error.

Thank you for your suggestion. Toracle input component after using the schema it's working.
Thank you for you help.
Praveen
One Star

Re: [resolved] Talend TOracle componet Error.

I am also not able to written the records in Oracle databse, Can you please suggest me what are the things i need to check in Toracle component ?
Thank you,
Praveen
Community Manager

Re: [resolved] Talend TOracle componet Error.

I am also not able to written the records in Oracle databse, Can you please suggest me what are the things i need to check in Toracle component ?
Thank you,
Praveen

If you are using a tOracleConnection to create the db connection in the job, you should a tOracleCommint to commit the changes at the end of job.
Best regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: [resolved] Talend TOracle componet Error.

Hi Shong,
still I am not able to insert the records in oracle DB. Can you please help me on this. Please find this print screen.
Thank you,
Praveen
Community Manager

Re: [resolved] Talend TOracle componet Error.

Hi
Please upload a screenshot of job, are there some error message thrown on the console? For testing, check the option 'die on error' on tOracleOutput component, so that the job will stop and throw the java exception once it meet an error.
Best regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: [resolved] Talend TOracle componet Error.

Hi Shong,
Thanks for quick responce, we are using property file decoded use name , password, DB schema and table names.
In mapping we are using Toracle out component. Also, our Oracle table columns do not allow nulls. Only the key works if processed by itself. Once additional columns that are not nullable are added, the job errors out with data type errors. Please find this Print screen. Ig you have data type conversion information for going from DB2 to Oracle, could you please provide me a link to that information.
Thanks

PS. Here's the error. Still trying to downsize the image to be able to upload....I am not able to up load print screen, I will mail you...is it okay?
Exception in component tOracleOutput_1
java.sql.SQLSyntaxErrorException: ORA-01747: invalid user.table.column, table.column, or column specification
at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:91)
at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:133)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:206)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:455)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:413)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:1034)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:194)
at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:953)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1222)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3387)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3468)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeUpdate(OraclePreparedStatementWrapper.java:1350)
at salesforce.oracle_job_0_1.ORACLE_JOB.tAS400Input_1Process(ORACLE_JOB.java:2102)
at salesforce.oracle_job_0_1.ORACLE_JOB.tSetProxy_1Process(ORACLE_JOB.java:3165)
at salesforce.oracle_job_0_1.ORACLE_JOB.tFileInputProperties_1Process(ORACLE_JOB.java:3057)
at salesforce.oracle_job_0_1.ORACLE_JOB.runJobInTOS(ORACLE_JOB.java:3879)
at salesforce.oracle_job_0_1.ORACLE_JOB.main(ORACLE_JOB.java:3306)
Community Manager

Re: [resolved] Talend TOracle componet Error.

Hi
First, there is a small problem on job design, there is no a tOracleConnection in the job, hence, you don't need to add a tOracleCommit. The job looks like:
Job properties files--main-->Load properties File to context
|
onsubjobok
|
proxy info
|
onsubjobok
|
tAs400Input-->tMap-->tOracleOutput
Exception in component tOracleOutput_1
java.sql.SQLSyntaxErrorException: ORA-01747: invalid user.table.column, table.column, or column specification

There are two reasons could cause this error:
1. Oracle database table field contains the Oracle keywords, for example, contains desc.
2. The column does not exist in the table.
Best regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: [resolved] Talend TOracle componet Error.

Hi Shong,
I Build the job same way as you said,
Job properties files--main-->Load properties File to context
|
onsubjobok
|
proxy info
|
onsubjobok
|
tAs400Input-->tMap-->tOracleOutput

But still the problem is not sloved. I am thinking on data types. Can you please find this data types:
Community Manager

Re: [resolved] Talend TOracle componet Error.

Hi
What's the error message thrown on tOracleOutput component now? The size of third pic is small, I can't see it clear.
Best regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: [resolved] Talend TOracle componet Error.

Hi Shong,
I thought the issue is related data types. I resolved the issue.
Thank you for your help.
Praveen