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
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.
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
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
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
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)
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
