topic Re: [resolved] Talend TOracle componet Error. in Talend Studio

[resolved] Talend TOracle componet Error.

Anonymous — Sat, 16 Nov 2024 12:42:33 GMT

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

Re: [resolved] Talend TOracle componet Error.

Anonymous — Thu, 01 Sep 2011 22:39:27 GMT

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

Re: [resolved] Talend TOracle componet Error.

David_Beaty — Fri, 02 Sep 2011 08:18:55 GMT

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.

Re: [resolved] Talend TOracle componet Error.

Anonymous — Fri, 02 Sep 2011 15:47:24 GMT

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

Re: [resolved] Talend TOracle componet Error.

Anonymous — Fri, 02 Sep 2011 17:09:52 GMT

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

Re: [resolved] Talend TOracle componet Error.

Anonymous — Mon, 05 Sep 2011 03:02:53 GMT

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

Re: [resolved] Talend TOracle componet Error.

Anonymous — Wed, 07 Sep 2011 15:58:03 GMT

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

Re: [resolved] Talend TOracle componet Error.

Anonymous — Wed, 07 Sep 2011 16:09:52 GMT

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

Re: [resolved] Talend TOracle componet Error.

Anonymous — Wed, 07 Sep 2011 20:33:34 GMT

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)

Re: [resolved] Talend TOracle componet Error.

Anonymous — Thu, 08 Sep 2011 01:49:35 GMT

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

Re: [resolved] Talend TOracle componet Error.

Anonymous — Thu, 08 Sep 2011 16:28:10 GMT

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:

Re: [resolved] Talend TOracle componet Error.

Anonymous — Fri, 09 Sep 2011 03:21:04 GMT

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

Re: [resolved] Talend TOracle componet Error.

Anonymous — Fri, 09 Sep 2011 14:03:09 GMT

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