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

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

Anonymous — Wed, 22 May 2019 03:57:37 GMT

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

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

nfz11 — Wed, 22 May 2019 05:41:56 GMT

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.

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

Anonymous — Fri, 24 May 2019 03:59:12 GMT

Hi @nfz11

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

Thanks

Sreeram

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

Anonymous — Fri, 24 May 2019 14:30:18 GMT

Hi,

Could you please verify the flow shown below?

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 🙂

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

Anonymous — Mon, 27 May 2019 06:23:22 GMT

Hi @nthampi

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

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

Anonymous — Mon, 27 May 2019 12:19:05 GMT

HI @nthampi

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

T_DYNAMIC_SELECT_IN_data.jpg
Talend_Job_1.jpg
Talend_Job_2.jpg
Talend_Job_3.jpg
Talend_Job_4.jpg

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

Anonymous — Mon, 27 May 2019 12:20:19 GMT

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

Talend_Job_5.jpg

Execution Error:

Execution_Error.jpg

Thanks

Sreeram

Execution_Error.jpg
Talend_Job_5.jpg

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

Anonymous — Mon, 27 May 2019 16:30:34 GMT

@rchinta

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 🙂

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

Anonymous — Tue, 28 May 2019 04:56:40 GMT

Hi @nthampi

Please find the screenshots output after adding tJava Component

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

Thanks

Sreeram

tFlowToIterate_Error_28May2019.jpg
tJava_28May2019.jpg
tJava_Output_Error_28May2019.jpg

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

Anonymous — Tue, 28 May 2019 16:50:11 GMT

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

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

Anonymous — Wed, 29 May 2019 09:14:04 GMT

Hi @nthampi

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

Multiple_Dynamic_Select_Staements_execution.jpg
tJava_Success_29May2019.jpg