Error while fetching using tOracleRow Talend

Question

I am trying to create a Table using tOracleRow.I am trying to create Table with The Below statement :

DROP TABLE TEMP_ORDERS;
CREATE Table TEMP_ORDERS AS
select a.ITEM_NAME,b.MANUFACTURER_NAME from ITEMS a
LEFT OUTER JOIN MANUFACTURERS b
ON a.MANUFACTURER_ID=b.MANUFACTURER_ID;

Now the Problem is this query is running fine sql Developer.Whereas when i am pasting this query in tOracleRow or tOracleinput it is saying that invalid char.Then I tried removing the ; but it is saying that SQL Command not properly ended.

When I am not giving ";" it is giving the error [statistics] connecting to socket on port 3686 [statistics] connected Exception in component tOracleInput_1 java.sql.SQLSyntaxErrorException: ORA-00911: invalid character The sample data of two Table is — Abhijit
– Abhijit, Commented Apr 16, 2017 at 10:23
please copy and past both error in your post (with and without ;). have you tried just with the first line Drop table ? — Blag
– Blag, Commented Apr 16, 2017 at 10:49

user812786 · Accepted Answer · 2017-07-25 14:31:23Z

1

It is possible to have a tOracleRow execute multiple statements at once.

You need to provide an "additional Parameter" to the connection you are using: allowMultiQueries=true

New Connection Wizard:

New Connection Wizard

You also need to use a BEGIN END block in your tOracleRow component if you are using multiple statements. You need to have an semicolon also after the closing END keyword.

Multiple statements with BEGIN END block:

Multiple statements with BEGIN END block

edited Jul 25, 2017 at 14:31

user812786

4,4306 gold badges42 silver badges50 bronze badges

answered Jul 25, 2017 at 12:17

Carnifex

261 bronze badge

Sign up to request clarification or add additional context in comments.

Comments

Gaby Weiss · Accepted Answer · 2018-03-16 11:10:26Z

1

In your query you are using a DML as well as multiple queries. So you need to do the following to make the job work:

i) Open the oracle connection using tOracleConnection. In additional parameters set allowMultiQueries=true

ii)Use tOracle row to execute the queries. Separate the queries with semicolons.

answered Mar 16, 2018 at 11:10

Gaby Weiss

1364 bronze badges

Comments

Blag · Accepted Answer · 2017-04-16 10:52:01Z

0

Try :

"
DROP TABLE TEMP_ORDERS;
CREATE Table TEMP_ORDERS AS
select a.ITEM_NAME,b.MANUFACTURER_NAME from ITEMS a
LEFT OUTER JOIN MANUFACTURERS b
ON a.MANUFACTURER_ID=b.MANUFACTURER_ID
"

Without the ; at the end of the request

answered Apr 16, 2017 at 10:52

Blag

5,8942 gold badges26 silver badges48 bronze badges

3 Comments

Abhijit Over a year ago

I have tried in your way.Now it is giving like this Exception in component tOracleInput_1 java.sql.SQLSyntaxErrorException: ORA-00911: invalid character at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:91)

Abhijit Over a year ago

I have tried just using only DROP Table.Now when i didn't use ";" it gave me [statistics] connecting to socket on port 3420 [statistics] connected Exception in component tOracleInput_1 java.sql.SQLException: ORA-01003: no statement parsed and when i am using ";" it is giving [statistics] connecting to socket on port 3871 [statistics] connected Exception in component tOracleInput_1 java.sql.SQLSyntaxErrorException: ORA-00911: invalid character

garpitmzn Over a year ago

you should use select statements in tOracleInput component. Can you try to run your multi-line statement in tOracleRow without giving ; at the last statement...does it works..can you try with more simple statements like two drop statements instead of one drop and one create. Also you might want to try to use / as separator of individual sql queries in toracleRow component..

Priyantha Weerakoon · Accepted Answer · 2017-04-17 11:09:49Z

0

Rather using tOracleRow, You can use both tOracleInput and tOracleOutput component and then use only select query "select a.ITEM_NAME,b.MANUFACTURER_NAME from ITEMS a LEFT OUTER JOIN MANUFACTURERS b ON a.MANUFACTURER_ID=b.MANUFACTURER_ID " as tOracleInput query and then select "Drop and create table" under Action on table of tOracleOutput

answered Apr 17, 2017 at 11:09

Priyantha Weerakoon

13 bronze badges

Comments

Anil · Accepted Answer · 2017-04-17 12:08:49Z

0

the purpose of tOracleRow is to execute a single statement for each talend record flow.. so if you give the DROP & CREATE statements in one tOracleRow it is not going to work as per my knowledge..

You can try these below options.

--> separate your two statements into two tOracleRow components Or --> Create a PLSQL Block and use that in tOracleRow like below "BEGIN EXECUTE IMMEDIATE 'DROP TABLE TEMP_ORDERS'; EXECUTE IMMEDIATE 'CREATE Table TEMP_ORDERS AS select a.ITEM_NAME,b.MANUFACTURER_NAME from ITEMS a LEFT OUTER JOIN MANUFACTURERS b ON a.MANUFACTURER_ID=b.MANUFACTURER_ID'; END;"

Let me know if it works out

answered Apr 17, 2017 at 12:08

Anil

2853 silver badges9 bronze badges

Comments

tobi6 · Accepted Answer · 2017-05-04 10:02:58Z

0

You'll need two separate tOracleRow components. It is not possible to use this component like a input shell or tool like SQL Developer where you can execute more than one command.

The first one will only hold the drop statement without the ending semicolon:

"DROP TABLE TEMP_ORDERS"

The second one will only hold the create statement, also without the semicolon:

"CREATE Table TEMP_ORDERS AS 
select a.ITEM_NAME,b.MANUFACTURER_NAME from ITEMS a
LEFT OUTER JOIN MANUFACTURERS b
ON a.MANUFACTURER_ID=b.MANUFACTURER_ID"

answered May 4, 2017 at 10:02

tobi6

8,2666 gold badges31 silver badges43 bronze badges

Comments

Gabriel · Accepted Answer · 2018-02-23 14:21:50Z

0

The solution is based on the previous post. 1) in the oracle connection you have to include the "allowMuliQueries=true". 2) each query needs to use a BEGIN END and also inserted inside an EXECUTE IMMEDIATE ->

BEGIN
EXECUTE IMMEDIATE'DROP TABLE TEMP_ORDERS';
EXECUTE IMMEDIATE'CREATE Table TEMP_ORDERS AS
select a.ITEM_NAME,b.MANUFACTURER_NAME from ITEMS a
LEFT OUTER JOIN MANUFACTURERS b
ON a.MANUFACTURER_ID=b.MANUFACTURER_ID';
END
;

answered Feb 23, 2018 at 14:21

Gabriel

212 bronze badges

Collectives™ on Stack Overflow

Error while fetching using tOracleRow Talend

7 Answers 7

Comments

Comments

3 Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

Comments

Comments

3 Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related