Oracle Multiple Select Statements execution using Table/File into a Standard Output format

Four Stars

Oracle Multiple Select Statements execution using Table/File into a Standard Output format

Hi All,

 

Requirement:

 I have Oracle configured Table with "Select Statements" as data.

First "select statement" to be executed and produce a result into an Oracle Table/File and then 

Second "select statement" to be executed and produce a result and then third ..... until end of all "Select Statements" execution is completed.

 

Note: 

1. All Select Statements having same output format to be loaded into a Oracle Table/File

2. Database schema is same for Oracle Configured Tables and Execution of Select Statements

 

Please provide detailed explanation along with examples

 

Thanks & Regards

Sreeramkumar

 


Accepted Solutions
Employee

Re: Oracle Multiple Select Statements execution using Table/File into a Standard Output format

Hi,

 

   Could you please verify the flow shown below?

image.png

 

image.png

image.png

 

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved :-)

View solution in original post

Employee

Re: Oracle Multiple Select Statements execution using Table/File into a Standard Output format

Hi Sreeram,

 

    Could you please print and see the results using below command in tJava and see whether results are coming?

System.out.println(<your tFlowtoIterate variable here>); 

 

Warm Regards,

 

Nikhil Thampi

View solution in original post


All Replies
Nine Stars

Re: Oracle Multiple Select Statements execution using Table/File into a Standard Output format

You need to connect two tDBInputs and then one tDBOutput.  The first tDBInput reads the SQL statements from your table.  The second tDBInput executes each statement and then the tDBOutput stores the results from the second tDBinput.

--
Please give Kudos and mark topics as solved where appropriate.
Four Stars

Re: Oracle Multiple Select Statements execution using Table/File into a Standard Output format

Hi @nfz11 

 How Second DBInput reads the SQL ? Can you send me Talend Job/Screenshot of each component with configuration? 

 

Thanks

Sreeram

Employee

Re: Oracle Multiple Select Statements execution using Table/File into a Standard Output format

Hi,

 

   Could you please verify the flow shown below?

image.png

 

image.png

image.png

 

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved :-)

View solution in original post

Four Stars

Re: Oracle Multiple Select Statements execution using Table/File into a Standard Output format

Hi @nikhilthampi 

First of all thank you for detailed explanation 
Please send screenshot of TFlowtoIterate because it's throwing following error while executing the job at "tDBInput2" with error message 'ORA-00904: "RULE_QUERY": invalid identifier'

 

Thanks 

Sreeram

Four Stars

Re: Oracle Multiple Select Statements execution using Table/File into a Standard Output format

HI @nikhilthampi 

 

Tried above approach with sample data still getting errors 

 

Please find  complete flow in below two posts screenshots are attached here : 

 

Input table: 

Attachment: T_DYNAMIC_SELECT_IN_data.jpg

 

Talend Code: 

Attachment : 

Talend_Job_1.jpg

Talend_Job_2.jpg

Talend_Job_3.jpg

Talend_Job_4.jpg

 

Conitunation....... below post

Four Stars

Re: Oracle Multiple Select Statements execution using Table/File into a Standard Output format

Talend Code: 
Conitunation....... above post

Talend_Job_5.jpg


Execution Error: 

Execution_Error.jpg

 

Thanks 

Sreeram

Employee

Re: Oracle Multiple Select Statements execution using Table/File into a Standard Output format

@sreeram_mandem 

 

It is throwing an Oracle error saying invalid identifier. So could you please run only the first part of the job and deactivate the part from tDBInput3? Please try to print the results using a tJava to see whether the data is coming properly out of tFlowtoIterate.

 

Please also verify whether you are connecting to the right database. If there are multiple databases, often we add wrong DB credentials (it has happened to me couple of times in the past :-) )

 

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved :-)

 

 

Four Stars

Re: Oracle Multiple Select Statements execution using Table/File into a Standard Output format

Hi @nikhilthampi 

 

Please find the screenshots output after adding tJava Component

If possible please send any working solution for this scenario with sample data.

 

Thanks 

Sreeram

Employee

Re: Oracle Multiple Select Statements execution using Table/File into a Standard Output format

Hi Sreeram,

 

    Could you please print and see the results using below command in tJava and see whether results are coming?

System.out.println(<your tFlowtoIterate variable here>); 

 

Warm Regards,

 

Nikhil Thampi

View solution in original post

Four Stars

Re: Oracle Multiple Select Statements execution using Table/File into a Standard Output format

Hi @nikhilthampi 

 

I'm able to see output using "tJava" with 'System.out.println((String)globalMap.get("RULE_QR"));' 

 

Also Now I'm able to see Output in FlatFile Output.

 

Ref: screenshots attached 

 

Thanks & Regards

Sreeram

2019 GARTNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog