topic Re: How to execute Oracle sql script in talend ? in Talend Studio

How to execute Oracle sql script in talend ?

Anonymous — Wed, 04 Jan 2017 11:18:12 GMT

Hi,
I have Oracle SP that return a sql path and I stored it in a context variable.
Now I need to execute this sql script in talend (like we execute it in sql plus --> @ sqlscript.sql )
Is there any similar component in talend to do this task. (I tried it in tOracleRow but it execute only Select & DML queries)
I am also thinking to implement it in tJava/tJavaRow component (by writing java code) ...is this a correct way ?
Kindly assist.
Regards,
Rakesh D

Re: How to execute Oracle sql script in talend ?

Anonymous — Wed, 04 Jan 2017 12:14:57 GMT

Hi
Take a look at this related https://community.talend.com/t5/Design-and-Development/resolved-Execute-Script-sql-to-Oracle-from-file/td-p/110551, or try the custom component tSQLScriptParser shared by jlolling on Talend Exchange.
Regards
Shong

Re: How to execute Oracle sql script in talend ?

Anonymous — Wed, 04 Jan 2017 13:43:50 GMT

Hi Shong,
I have downloaded the TSQLScriptParser (its a zip file) but not able to understand where I need to keep this file/folder.
Can you please help ?
I have extracted this file and it consist of 2 folder tSQLScriptParser and __MACOSX and they have few files (.xml/javalet/jar) but not sure where I have to place these files.
Regards,
Rakesh D

Re: How to execute Oracle sql script in talend ?

Anonymous — Wed, 04 Jan 2017 13:48:12 GMT

Shong,
Do I need to place extracted folder under this path:
C:\Program Files (x86)\Talend-Studio\studio\plugins\org.talend.designer.components.sparkstrprovider_6.2.1.20160704_1411\components
As I can see other components' folders are there.
Please confirm.
Regards,
Rakesh D

Re: How to execute Oracle sql script in talend ?

Anonymous — Wed, 04 Jan 2017 14:44:57 GMT

Hi Shong,
I was able to load the tSQLScriptParser component by installing it properly but its not able to execute the sql script (I am getting "File Not found") even I have given the proper file name in the File *.sql.
Then I read some other post related to this and they said that we can use tFileInputFullRow--> tOracleRow to execute sql script.
I have given the complete file path in the tFileInputFullRow and then I used ((String)globalMap.get("sql")) in the Query section of tOracleRow component but getting error:
SQL statement to execute cannot be empty or null
Am I doing any mistake?

Re: How to execute Oracle sql script in talend ?

Anonymous — Wed, 04 Jan 2017 15:26:04 GMT

Hi ,
Even I use tSystem to execute sqlscript by (I am passing my sql script complete path in the context variable)
"cmd /c echo HELLO & sqlplus dbuser/*****@DBNAME & @" +context.Script_File_path+" "
it is executing without any error but it did not create file (this script create spool file by executing select statement)

Re: How to execute Oracle sql script in talend ?

Anonymous — Mon, 09 Jan 2017 07:43:40 GMT

Hello
tSQLScriptParser is a custom component, to be honest, I have not used it. Please refer to this KB article about how to install a custom component, make sure you have installed into studio correctly.

Then I read some other post related to this and they said that we can use tFileInputFullRow--> tOracleRow to execute sql script

See this https://community.talend.com/t5/Design-and-Development/resolved-Execute-Script-sql-to-Oracle-from-file/td-p/110551, there should be a tJavaFlex component between tFileInputFullRow and tOracleRow.
Regards
Shong