workflow fails when adding data from one db to another in oracle

Question

checked in sql developer to confirm table existence and also granted insert, select to the table. help pls!

workflow log -

Severity Timestamp Node Thread Message Code Message INFO 11/2/2017 10:40:59 PM node01_XX 9140 LM_36435 Starting execution of workflow [wf_employee_employee] in folder [training] last saved by user [Administrator]. INFO 11/2/2017 10:40:59 PM node01_XX9140 LM_44206 Workflow wf_employee_employee started with run id [24], run instance name [], run type [Concurrent Run Disabled]. INFO 11/2/2017 10:40:59 PM node01_XX 9140 LM_44195 Workflow [wf_employee_employee] service level [SLPriority:5,SLDispatchWaitTime:1800]. INFO 11/2/2017 10:40:59 PM node01_XX 9140 LM_44253 Workflow started. Clients will be notified INFO 11/2/2017 10:40:59 PM node01_XX 9140 LM_36330 Start task instance [Start]: Execution started. INFO 11/2/2017 10:40:59 PM node01_XX 9140 LM_36318 Start task instance [Start]: Execution succeeded. INFO 11/2/2017 10:40:59 PM node01_XX 9140 LM_36505 Link [Start --> s_M_Employee_Employee]: empty expression string, evaluated to TRUE. INFO 11/2/2017 10:40:59 PM node01_XX 9140 LM_36388 Session task instance [s_M_Employee_Employee] is waiting to be started. INFO 11/2/2017 10:40:59 PM node01_XX 10416 LM_36682 Session task instance [s_M_Employee_Employee]: started a process with pid [1240] on node [node01_XX]. INFO 11/2/2017 10:40:59 PM node01_XX 10416 LM_36330 Session task instance [s_M_Employee_Employee]: Execution started. INFO 11/2/2017 10:40:59 PM node01_XX 6588 LM_36488 Session task instance [s_M_Employee_Employee] : [TM_6793 Fetching initialization properties from the Integration Service. : (Thu Nov 02 22:40:59 2017)] INFO 11/2/2017 10:40:59 PM node01_XX 6588 LM_36488 Session task instance [s_M_Employee_Employee] : [DISP_20305 The [Preparer] DTM with process id [1240] is running on node [node01_XX]. : (Thu Nov 02 22:40:59 2017)] INFO 11/2/2017 10:40:59 PM node01_XX 6588 LM_36488 Session task instance [s_M_Employee_Employee] : [PETL_24036 Beginning the prepare phase for the session.] INFO 11/2/2017 10:40:59 PM node01_XX 6588 LM_36488 Session task instance [s_M_Employee_Employee] : [TM_6721 Started [Connect to Repository].] INFO 11/2/2017 10:40:59 PM node01_XX 6588 LM_36488 Session task instance [s_M_Employee_Employee] : [TM_6722 Finished [Connect to Repository]. It took [0.0635259] seconds.] INFO 11/2/2017 10:40:59 PM node01_XX6588 LM_36488 Session task instance [s_M_Employee_Employee] : [TM_6794 Connected to repository [infa_training] in domain [Domain_XX] as user [Administrator] in security domain [Native].] INFO 11/2/2017 10:40:59 PM node01_XX 6588 LM_36488 Session task instance [s_M_Employee_Employee] : [TM_6721 Started [Fetch Session from Repository].] INFO 11/2/2017 10:40:59 PM node01_XX6588 LM_36488 Session task instance [s_M_Employee_Employee] : [TM_6722 Finished [Fetch Session from Repository]. It took [0.062696] seconds.] INFO 11/2/2017 10:40:59 PM node01_XX 6588 LM_36488 Session task instance [s_M_Employee_Employee] : [TM_6721 Started [Partition Group Formation].] INFO 11/2/2017 10:40:59 PM node01_XX 6588 LM_36488 Session task instance [s_M_Employee_Employee] : [TM_6722 Finished [Partition Group Formation]. It took [0.00900602] seconds.] INFO 11/2/2017 10:40:59 PM node01_XX 6588 LM_36488 Session task instance [s_M_Employee_Employee] : [PETL_24037 Finished the prepare phase for the session.] INFO 11/2/2017 10:40:59 PM node01_XX 6588 LM_36488 Session task instance [s_M_Employee_Employee] : [TM_6792 Notifying the Integration Service that the prepare phase has been completed. : (Thu Nov 02 22:40:59 2017)] INFO 11/2/2017 10:40:59 PM node01_XX 6588 LM_36488 Session task instance [s_M_Employee_Employee] : [TM_6228 Writing session output to log file [C:\Informatica\961\server\infa_shared\SessLogs\s_M_Employee_Employee.log].] ERROR 11/2/2017 10:41:01 PM node01_XX 15248 LM_36320 Session task instance [s_M_Employee_Employee]: Execution failed. WARNING 11/2/2017 10:41:01 PM node01_XX 15248 LM_36331 Session task instance [s_M_Employee_Employee] failed and its "fail parent if this task fails" setting is turned on. So, Workflow [wf_employee_employee] will be failed. ERROR 11/2/2017 10:41:02 PM node01_XX 9164 LM_36320 Workflow [wf_employee_employee]: Execution failed.

session log -

INSERT INTO EMPLOYEES_TEST(EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) INFO 11/2/2017 10:40:59 PM node01_XX WRITER_1__1 WRT_8124 Target Table EMPLOYEES_TEST :SQL UPDATE statement: UPDATE EMPLOYEES_TEST SET FIRST_NAME = ?, LAST_NAME = ?, EMAIL = ?, PHONE_NUMBER = ?, HIRE_DATE = ?, JOB_ID = ?, SALARY = ?, COMMISSION_PCT = ?, MANAGER_ID = ?, DEPARTMENT_ID = ? WHERE EMPLOYEE_ID = ? INFO 11/2/2017 10:40:59 PM node01_XX WRITER_1__1 WRT_8124 Target Table EMPLOYEES_TEST :SQL DELETE statement: DELETE FROM EMPLOYEES_TEST WHERE EMPLOYEE_ID = ? INFO 11/2/2017 10:40:59 PM node01_XX WRITER_1__1 WRT_8270 Target connection group #1 consists of target(s) [EMPLOYEES_TEST] INFO 11/2/2017 10:40:59 PM node01_XX WRITER_1__1 WRT_8003 Writer initialization complete. INFO 11/2/2017 10:40:59 PM node01_XXREADER_1_1_1 BLKR_16007 Reader run started. INFO 11/2/2017 10:40:59 PM node01_XX WRITER_1__1 WRT_8005 Writer run started. INFO 11/2/2017 10:40:59 PM node01_XX WRITER_1__1 WRT_8158

*****START LOAD SESSION*****

Load Start Time: Thu Nov 02 22:40:59 2017

Target tables:

 EMPLOYEES_TEST

INFO 11/2/2017 10:40:59 PM node01_XX READER_1_1_1 RR_4010 SQ instance [SQ_EMPLOYEES1] SQL Query [SELECT EMPLOYEES.EMPLOYEE_ID, EMPLOYEES.FIRST_NAME, EMPLOYEES.LAST_NAME, EMPLOYEES.EMAIL, EMPLOYEES.PHONE_NUMBER, EMPLOYEES.HIRE_DATE, EMPLOYEES.JOB_ID, EMPLOYEES.SALARY, EMPLOYEES.COMMISSION_PCT, EMPLOYEES.MANAGER_ID, EMPLOYEES.DEPARTMENT_ID FROM EMPLOYEES] INFO 11/2/2017 10:40:59 PM node01_XX READER_1_1_1 RR_4049 RR_4049 SQL Query issued to database : (Thu Nov 02 22:40:59 2017) INFO 11/2/2017 10:40:59 PM node01_XX READER_1_1_1 RR_4050 RR_4050 First row returned from database to reader : (Thu Nov 02 22:40:59 2017) INFO 11/2/2017 10:40:59 PM node01_XX READER_1_1_1 BLKR_16019 Read [107] rows, read [0] error rows for source table [EMPLOYEES] instance name [EMPLOYEES1] INFO 11/2/2017 10:40:59 PM node01_XX READER_1_1_1 BLKR_16008 Reader run completed. INFO 11/2/2017 10:40:59 PM node01_XX WRITER_1_*_1 WRT_8167 Start loading table [EMPLOYEES_TEST] at: Thu Nov 02 22:40:59 2017 ****ERROR 11/2/2017 10:40:59 PM node01_XX WRITER_1__1 WRT_8229 Database errors occurred: ORA-00604: error occurred at recursive SQL level 1 ORA-00942: table or view does not exist Database driver error... Function Name : Prepare DPL SQL Stmt : INSERT INTO EMPLOYEES_TEST(EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) Oracle Fatal Error Database driver error... Function Name : Execute Multiple SQL Stmt : INSERT INTO EMPLOYEES_TEST(EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) Oracle Fatal Error ERROR 11/2/2017 10:40:59 PM node01_XX WRITER_1__1 WRT_8425 ERROR: Writer execution failed. ERROR 11/2/2017 10:40:59 PM node01_XX WRITER_1__1 WRT_8076 Writer run terminated. [Commit Error] INFO 11/2/2017 10:40:59 PM node01_XX WRITER_1__1 WRT_8333 Rolling back all the targets due to fatal session error. INFO 11/2/2017 10:40:59 PM node01_XX WRITER_1__1 WRT_8325 Final rollback executed for the target [EMPLOYEES_TEST] at end of load ERROR 11/2/2017 10:40:59 PM node01_XX WRITER_1__1 WRT_8081 Writer run terminated. [Error in loading data to target table [EMPLOYEES_TEST]] INFO 11/2/2017 10:40:59 PM node01_XX WRITER_1__1 WRT_8168 End loading table [EMPLOYEES_TEST] at: Thu Nov 02 22:40:59 2017 INFO 11/2/2017 10:40:59 PM node01_XX WRITER_1__1 WRT_8035 Load complete time: Thu Nov 02 22:40:59 2017**

Answer 1

Try to access the table using schema name like Schema.tableName or else you create a synonym of the table for your informatica user