topic Re: tELTOracleOutput and generated SQL in Talend Studio

tELTOracleOutput and generated SQL

Anonymous — Sat, 16 Nov 2024 13:25:37 GMT

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

Re: tELTOracleOutput and generated SQL

Anonymous — Wed, 19 May 2010 08:56:42 GMT

tChoua
I posted an answer on your first topic. Plz keep asking question about your ELT issue on the intitial topic it will be easier for us to follow it...

Re: tELTOracleOutput and generated SQL

Anonymous — Wed, 19 May 2010 14:06:36 GMT

Yes thanks very mutch,
But I create this new post cause I consider that my problem is not the same.
the post https://community.talend.com/t5/Archive/taggregate/td-p/183348 is about the SELECT statment that is genarated by the tELTOracleMap and the LEFT OUTER JOIN statment.
This post is about the INSERT generated statment, that seams to be wrong (or the way I use it that is wrong).
As you asked me I keep going to discuss on the other post : https://community.talend.com/t5/Archive/taggregate/td-p/183348
regards,
Francois

Re: tELTOracleOutput and generated SQL

Anonymous — Wed, 12 Jan 2011 14:12:40 GMT

Hi,
I am also facing the similar issue with tELTOracleoutput as mentioned below :
Inserting with :
INSERT INTO EMPLOYEE1(ID,NAME) (SELECT emp.ID , emp.NAME FROM tp2.EMPLOYEE (Table)tp2.tp2.EMPLOYEE emp)
Exception in component tELTOracleOutput_1 java.sql.SQLException: ORA-00907: missing right parenthesis
at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:74)
Can you pls suggest me asap.
Thanks for your help.
Pratik

Re: tELTOracleOutput and generated SQL

Anonymous — Fri, 22 Jul 2011 14:47:25 GMT

Hi, I am facing a similar issue...Pls help...
i am trying to insert in a table in a different schema than my connection...The component is tOracleOutput
The connection has all privilages in oracle to insert....but i get this message ...I think it is not able to parse the sql string passed to the oracle
connecting to socket on port 3351
connected
ORA-00926: missing VALUES keyword
disconnected
can anyone share how they overcone this issue...
Thanks
Mohan