[resolved] Execute Script sql to Oracle from file

[catusse]

Hi,
I Have done a file sql (with a tMap componant) and I want to load it into Oracle.
Which component should I use?
I 've got 200 request like this:
update OG.COORDONNEES
set OG.COORDONNEES.MEL_PRO1='mail1',
OG.COORDONNEES.NUM_TELPRO1='00000',
OG.COORDONNEES.NUM_POSTPRO1='0000'
WHERE OG.COORDONNEES.COORD_ID in (
select OG.COORDONNEES.COORD_ID
from OG.COORDONNEES, rh.agtnat , OG.IDENT
where rh.agtnat.idf_agent=XXX
AND RH.AGTNAT.IDENT_ID=OG.IDENT.IDENT_ID AND
OG.IDENT.COORD_ID=OG.COORDONNEES.COORD_ID
AND RH.AGTNAT.COD_COLL ='ZZZZ'
)
Is there another way to do this without using an sql script?
Tank's for your help.

[Anonymous]

Even though this method does work, it is not very straightforward.
Talend, it would be great if you had a tExecuteSql step that allowed me to simply either run a sql file or a block of tsql in one workflow step.

[_AnonymousUser]

I was able to resolve the "sql cannot be resolved" issue with:
(String)globalMap.get("sql");
in the tOracleROW

Hi,
Still the error message appears "sql can not be resolved to variable" when used the above mentioned statement.
Can any one of you help me with the correct way/syntax to perform the job

Thanks & Regards
Pavan.

[_AnonymousUser]

Hello!! Thank by the tutorial.
The solutión have an error.
-The correct option in tOracleROW is:

(String)globalMap.get(sql)
See you soon.

Hi,
Still the error message appears "sql can not be resolved to variable" when used the above mentioned statement.
Can any one of you help me with the correct way/syntax to perform the job

Thanks & Regards
Pavan.

[Anonymous]

Hi,
I need to do something similar to this. However when I try out the steps given above I get an error saying :
Exception in component tOracleRow_1
java.sql.SQLSyntaxErrorException : ORA :invalid character
Although it says that there is some issue with the syntax but the same script works without any issue in SQL developer.
Following is a part of the script :
update fact_mfs_agg
set prog_Subprog_area_alt_desc = 'ARV' where trim(upper(program_area_sub_program_area)) in
('15-HTXD ARV DRUGS '|| chr(94) ||' DRUGS',
'15-HTXD ARV DRUGS '|| chr(94) ||' PMTCT',
'15-HTXD ARV DRUGS '|| chr(94) ||' ADULT 1ST LINE ARVS',
'15-HTXD ARV DRUGS '|| chr(94) ||' ADULT 2ND LINE ARVS',
'15-HTXD ARV DRUGS '|| chr(94) ||' PEDIATRIC 1ST LINE '|| chr(38) || ' 2ND LINE ARVS')
AND COUNTRY_CODE = 'ET'
;

update fact_mfs_agg
set prog_Subprog_area_alt_desc = 'Commodity Cost for HSS' where trim(upper(program_area_sub_program_area)) in
('18-OHSS HEALTH SYSTEMS STRENGTHENING ^ WAREHOUSE EQUIPMENT '||CHR(38)||' VEHICLES',
'18-OHSS HEALTH SYSTEMS STRENGTHENING ^ IPLS MATERIALS',
'18-OHSS HEALTH SYSTEMS STRENGTHENING ^ DISTRIBUTION FEE',
'18-OHSS HEALTH SYSTEMS STRENGTHENING ^ MISC. COMMODITY COSTS',
'18-OHSS HEALTH SYSTEMS STRENGTHENING ^ LMIS')
AND COUNTRY_CODE = 'ET'
;
I have done all the settings as per the images given above.
Kindly let me know what can be the case for this error
Thanks,
Shikha

[Anonymous]

Hi
Are there two update statement in your script? If you just copy and paste one update statement into tOracleRow and execute it, does it work?
Shong

[Anonymous]

Hi Shong,
My code has multiple update statements which need to be fetched at runtime from a file placed at the talend server.
When I put the update statements in the query section with "BEGIN UPDATE... END;", they work perfectly.
Regards,
Shikha

[Anonymous]

hi
Yes, if your script file has multiple statements, you need to add "begin....end", please take a look at this topic to know how to execute multiple statements on tOracleRow component.
https://community.talend.com/t5/Design-and-Development/Multiple-update-sql-statement-in-tOracleRow/t...
In your case, if you don't have permission to modify the script file, you can add "begin...end" on tJavaFlex component.
Shong

[Anonymous]

Hi,
I tried the same thing already. Please find below my component setting and the error given by the same :

Thanks.

[Anonymous · Accepted solution]

Hi
Modify the end code of tJavaFlex to:

globalMap.put("sql","begin"+"\n\r"+sql+"\n\r"+"commit;"+"\n\r"+"end;"

[Anonymous]

Hi,
Thank you so much for your prompt replies!
I simply recreated my job in sheer frustration and it worked! There was no change in settings I just created a new one!!
Thanks