[resolved] Execute Script sql to Oracle from file

One Star

[resolved] Execute Script sql to Oracle from file

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.

Accepted Solutions
Community Manager

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

Hi
Modify the end code of tJavaFlex to:
globalMap.put("sql","begin"+"\n\r"+sql+"\n\r"+"commit;"+"\n\r"+"end;"
----------------------------------------------------------
Talend | Data Agility for Modern Business

All Replies
Community Manager

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

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
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

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

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
One Star

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

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
One Star

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

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

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

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

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

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
One Star

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

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

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

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

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

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

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

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

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.
One Star

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

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.
One Star

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

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.

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

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
Community Manager

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

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
----------------------------------------------------------
Talend | Data Agility for Modern Business

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

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
Community Manager

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

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.
http://www.talendforge.org/forum/viewtopic.php?id=8370
In your case, if you don't have permission to modify the script file, you can add "begin...end" on tJavaFlex component.
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business

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

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

Thanks.
Community Manager

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

Hi
Modify the end code of tJavaFlex to:
globalMap.put("sql","begin"+"\n\r"+sql+"\n\r"+"commit;"+"\n\r"+"end;"
----------------------------------------------------------
Talend | Data Agility for Modern Business

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

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 Smiley Happy
Seventeen Stars

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

For this purpose I have created a parser which can separate statements and run them separately.
You find this component tSQLScriptParser in Talend Exchange.
Six Stars

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

shong,
i have the similar problem your screenshots are missing to define te tjavaflex can ou please guide me i have a huge sql to execute and then union the result set with normal result inthe job.can you please guide me?
Community Manager

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

Hi sanjana
I did't upload screenshots to this topic before, please refer to the component manual and learn the basic usage of tJavaFlex.
Best regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business