Hello,

I having troubles trying to build a job based on Oracle ELT component.
The tELTOracleOutput seams to generate a INSERT INTO table (SELECT XXX, YYYY FROM table1 inner join table2 ON table1.xxx = table2.xxxx).

When I launch my job I get an Oracle error complaning about the VALUES statment that is missing  (ORA-00926: missing VALUES keyword).

Here is the query that is generated by talend :

INSERT INTO context.db_oracle_echange_Schema.DEMANDE_SNGI
  (
    SELECT 1, indiv.ID_T_INDIVIDU_CPR , sysdate, null, indiv.N_NIR , indiv.N_INDICE_PENSION , indiv.N_IMMAT_SNCF , indiv.C_SEXE , indiv.L_TYPE , indiv.L_NOM_FAMILLE , indiv.L_NOM_MARITAL , indiv.L_PRENOM_1 , indiv.L_PRENOM_2 , indiv.L_PRENOM_3 , indiv.D_NAISSANCE , indiv.C_PAYS_NAISSANCE , indiv.L_PAYS_NAISSANCE , indiv.C_DEPT_NAISSANCE , indiv.L_DEPT_NAISSANCE , indiv.C_COM_NAISSANCE , indiv.L_COM_NAISSANCE , indiv.L_LOC_NAISSANCE , pere.L_NOM_FAMILLE , pere.L_PRENOM_1 , pere.L_PRENOM_2 , pere.L_PRENOM_3 , mere.L_NOM_FAMILLE , mere.L_PRENOM_1 , mere.L_PRENOM_2 , mere.L_PRENOM_3 
  FROM 
    context.db_oracle_nir_Schema.T_INDIVIDU_CPR indiv
    LEFT OUTER JOIN  context.db_oracle_nir_Schema.T_ASCENDANT_CPR pere ON(  pere.ID_T_INDIVIDU_CPR = indiv.ID_T_INDIVIDU_CPR  AND  pere.C_TYPE = 1 ) 
    LEFT OUTER JOIN  context.db_oracle_nir_Schema.T_ASCENDANT_CPR mere ON(  mere.ID_T_INDIVIDU_CPR = indiv.ID_T_INDIVIDU_CPR  AND  mere.C_TYPE = 2 ) 
    INNER JOIN  context.db_oracle_nir_Schema.TA_HISTORIQUE hist ON(  hist.ID_T_INDIVIDU_CPR = indiv.ID_T_INDIVIDU_CPR  ) 
    INNER JOIN  context.db_oracle_nir_Schema.TR_ETAT etat ON(  etat.ID_TR_ETAT = hist.ID_TR_ETAT  )
  WHERE   
    etat.L_ETAT = 'A IDENTIFIER'
  )

If I replace all "context.db_oracle_echange_Schema." by the schema names and paste it into SQLDeveloper and execute this query the query works fine.

Here is the query from SQLDeveloper :

INSERT INTO SAS_EXHANGES.DEMANDE_SNGI
  (
    SELECT 1, indiv.ID_T_INDIVIDU_CPR , sysdate, null, indiv.N_NIR , indiv.N_INDICE_PENSION , indiv.N_IMMAT_SNCF , indiv.C_SEXE , indiv.L_TYPE , indiv.L_NOM_FAMILLE , indiv.L_NOM_MARITAL , indiv.L_PRENOM_1 , indiv.L_PRENOM_2 , indiv.L_PRENOM_3 , indiv.D_NAISSANCE , indiv.C_PAYS_NAISSANCE , indiv.L_PAYS_NAISSANCE , indiv.C_DEPT_NAISSANCE , indiv.L_DEPT_NAISSANCE , indiv.C_COM_NAISSANCE , indiv.L_COM_NAISSANCE , indiv.L_LOC_NAISSANCE , pere.L_NOM_FAMILLE , pere.L_PRENOM_1 , pere.L_PRENOM_2 , pere.L_PRENOM_3 , mere.L_NOM_FAMILLE , mere.L_PRENOM_1 , mere.L_PRENOM_2 , mere.L_PRENOM_3 
  FROM 
    NIR.T_INDIVIDU_CPR indiv
    LEFT OUTER JOIN  NIR.T_ASCENDANT_CPR pere ON(  pere.ID_T_INDIVIDU_CPR = indiv.ID_T_INDIVIDU_CPR  AND  pere.C_TYPE = 1 ) 
    LEFT OUTER JOIN  NIR.T_ASCENDANT_CPR mere ON(  mere.ID_T_INDIVIDU_CPR = indiv.ID_T_INDIVIDU_CPR  AND  mere.C_TYPE = 2 ) 
    INNER JOIN  NIR.TA_HISTORIQUE hist ON(  hist.ID_T_INDIVIDU_CPR = indiv.ID_T_INDIVIDU_CPR  ) 
    INNER JOIN  NIR.TR_ETAT etat ON(  etat.ID_TR_ETAT = hist.ID_TR_ETAT  )
  WHERE   
    etat.L_ETAT = 'A IDENTIFIER'
  )

I have questions on this problem :
It seams that the generated query from the console is not the same than the one send to Oracle. Oracle parse the query before executing it and send an ORA error before to execute it, I can't find the real talend query into the oracle v$sqlarea view.
Is there a way to get the real query send to Oracle ?
I am doing something wrong in my job ?
How can I fixe this problem ?

I am using TOS 3.2 using Java language.

Thanks for reading.
Regards,

Francois Chiausa

Last edited by tchoua (2010-05-19 09:10:38)