topic Re: [resolved] Execute Script sql to Oracle from file in Talend Studio

[resolved] Execute Script sql to Oracle from file

catusse — Sat, 16 Nov 2024 13:40:48 GMT

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.

Re: [resolved] Execute Script sql to Oracle from file

Anonymous — Wed, 18 Nov 2009 06:34:01 GMT

Hello
You can use a tFileInputFullRow to read the sql file, and then execute the sql statement on tOracleRow component. see my screenshots.
Best regards

shong

Re: [resolved] Execute Script sql to Oracle from file

_AnonymousUser — Thu, 24 Jun 2010 12:12:07 GMT

Hello
You can use a tFileInputFullRow to read the sql file, and then execute the sql statement on tOracleRow component. see my screenshots.
Best regards

shong

Hi shong,
i've followed your steps, but when i execute job i have following error:
sql cannot be resolved
Why?
Giancarlo

Re: [resolved] Execute Script sql to Oracle from file

_AnonymousUser — Thu, 24 Jun 2010 12:18:02 GMT

Hello
You can use a tFileInputFullRow to read the sql file, and then execute the sql statement on tOracleRow component. see my screenshots.
Best regards

shong

Hi shong,
i've followed your steps, but when i execute job i have following error on tOracleRow:
sql cannot be resolved
Why?
Giancarlo

Re: [resolved] Execute Script sql to Oracle from file

Anonymous — Tue, 10 May 2011 17:48:51 GMT

hello, all
i followed the same steps , finaly when i execute , i got this error "sql cannot be resolved"
thanks for help

Re: [resolved] Execute Script sql to Oracle from file

Anonymous — Wed, 11 May 2011 20:23:33 GMT

Make sure you declare the variable "sql" in the Begin section of the JavaFlex like in Shongs image.

Re: [resolved] Execute Script sql to Oracle from file

_AnonymousUser — Tue, 26 Jul 2011 17:16:33 GMT

hello
i've got the same error "sql cannot be resolved"

i thing it's caused by tOracleRow because he doesn't see the variable "sql"
in code "sql" is declared in public void tFileInputFullRow_1Process(
but we try to use it in public void tOracleRow_1Process(
with the instruction : query_tOracleRow_1 = (String) globalMap.get("context.sql", sql);

how can i resolve this ?

thanks for your help

Re: [resolved] Execute Script sql to Oracle from file

Anonymous — Tue, 18 Oct 2011 09:30:36 GMT

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

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

Re: [resolved] Execute Script sql to Oracle from file

Anonymous — Tue, 29 Nov 2011 18:22:16 GMT

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

Re: [resolved] Execute Script sql to Oracle from file

Anonymous — Mon, 12 Dec 2011 21:50:07 GMT

Indeed
(String)globalMap.get("sql");
is the solution. I was able to run a query against a postgreSql database using this method (tPostgresqlRow).

Re: [resolved] Execute Script sql to Oracle from file

Anonymous — Mon, 12 Dec 2011 21:53:58 GMT

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.

Re: [resolved] Execute Script sql to Oracle from file

_AnonymousUser — Fri, 09 Mar 2012 13:25:42 GMT

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.

Re: [resolved] Execute Script sql to Oracle from file

_AnonymousUser — Mon, 12 Mar 2012 15:06:25 GMT

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

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

Re: [resolved] Execute Script sql to Oracle from file

Anonymous — Wed, 26 Dec 2012 09:57:12 GMT

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

Re: [resolved] Execute Script sql to Oracle from file

Anonymous — Wed, 26 Dec 2012 10:40:01 GMT

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

Re: [resolved] Execute Script sql to Oracle from file

Anonymous — Wed, 26 Dec 2012 10:48:22 GMT

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

Re: [resolved] Execute Script sql to Oracle from file

Anonymous — Wed, 26 Dec 2012 11:07:18 GMT

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/td-p/108270
In your case, if you don't have permission to modify the script file, you can add "begin...end" on tJavaFlex component.
Shong

Re: [resolved] Execute Script sql to Oracle from file

Anonymous — Wed, 26 Dec 2012 11:31:58 GMT

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

Thanks.

Re: [resolved] Execute Script sql to Oracle from file

Anonymous — Thu, 27 Dec 2012 07:14:51 GMT

Hi
Modify the end code of tJavaFlex to:

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

Re: [resolved] Execute Script sql to Oracle from file

Anonymous — Thu, 27 Dec 2012 07:46:54 GMT

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