topic Re: Write Multiple Queries in toracleRow in Talend Studio

Write Multiple Queries in toracleRow

abhi90 — Mon, 04 Jun 2018 17:30:27 GMT

Hi All,

Hope everyone is doing well . I am facing an issue.I am trying to integrate multiple sql queries of Oracle in 1 toracleRow. I have already setup my toracleconnection using allowMultiQueries=true. But when I am using Multiple Queries with ";" separated by them giving ora error of sql command not properly ended. Is there any way of writing multiple queries in one Row components

@rhall, @TRF, @vboppudi

Re: Write Multiple Queries in toracleRow

Anonymous — Mon, 04 Jun 2018 18:34:29 GMT

You need a Begin and and End in your SQL. Follow this....

http://djugal.blogspot.com/2013/03/talend-writing-multiple-sql-queries-in.html

Re: Write Multiple Queries in toracleRow

abhi90 — Tue, 05 Jun 2018 14:58:22 GMT

Hi @rhall,

I was trying with your solution.But still getting sql command not properly ended. I have tested my Oracle Connection with allowMultiQueries=true. Is there any different WayOut richard?

Re: Write Multiple Queries in toracleRow

Anonymous — Tue, 05 Jun 2018 15:00:52 GMT

It might be easier to resolve if you paste your query 🙂

Re: Write Multiple Queries in toracleRow

abhi90 — Tue, 05 Jun 2018 16:30:17 GMT

Hi @rhall,

Just this One.

Create Table B as select * from A;

truncate Table tableA;

That's it

Re: Write Multiple Queries in toracleRow

Anonymous — Tue, 05 Jun 2018 16:38:37 GMT

Try this.....

"BEGIN
Create Table B as select * from A;
truncate Table tableA;
END"

Re: Write Multiple Queries in toracleRow

abhi90 — Thu, 07 Jun 2018 10:23:57 GMT

Hi @Richard Hall,

I resolved the issue context to this Article

https://community.talend.com/s/article/Executing-multiple-SQL-statements-with-one-tOracleRow-component-G7XJq

Actually for DDL have to use execute immediate.

😉

Re: Write Multiple Queries in toracleRow

Anonymous — Thu, 14 May 2020 18:30:26 GMT

Hi all,
I am using toraclerow component and writing multiple update statements.I want to get updated records counts.When I use NB_LINE_UPDATED after variable it is returning -1 but I need exact count of updated records.

Writing queries on below format:

BEGIN
UPDATE EMPLOYEE SET DATE=SYSDATE;
UPDATE SAL SET AMOUNT=10000;
END;

Someone help me on this.

Thanks in advance.

Re: Write Multiple Queries in toracleRow

kotvir — Mon, 18 Oct 2021 04:52:13 GMT

Page is not working.

Also, I am trying to re-create sequence by Drop and Craete sequence statements, but its not working with BEGIN and END.

"BEGIN

DROP SEQUENCE SEQ1;

CREATE SEQUENCE SEQ1 MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE;

END"

Any thoughts ?

Re: Write Multiple Queries in toracleRow

Anonymous — Mon, 18 Oct 2021 11:38:35 GMT

Hi @Viral Kothari,

Thanks for raising the broken link issue. We had to dynamically modify quite a bit of HTML when we migrated all of the posts from our old Community application to this new application. Some of the links got missed. I have corrected this one, so hopefully it will be of more use to you now.

Regards

Richard

Re: Write Multiple Queries in toracleRow

kotvir — Mon, 18 Oct 2021 21:51:49 GMT

The correct way to write multiple SQL commnds in ORACLE (which involves Drop and Create statements) is below.

"BEGIN

execute immediate 'DROP SEQUENCE SEQ1' ;

execute immediate 'CREATE SEQUENCE SEQ1 MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE';

END

;