topic Re: toracleoutputbulk in Talend Studio

toracleoutputbulk

_AnonymousUser — Thu, 05 Jun 2014 08:31:01 GMT

Hi all,
Could u pls tell me how to use tOracleOutputBulkExec component for bulk load into a table.
I used the following job:
toracleinput -> tmap -> toracleoutputbulk -> toraclebulkexec -> toracleoutputbulkexec
I am getting an error:
SQL*Loader-350: Syntax error at line 10.
Expecting "(", found end of file.
Exception in component tOracleBulkExec_1
java.lang.Exception
How to resolve this error. Pls help...
Regards,
Hermione

Re: toracleoutputbulk

Anonymous — Thu, 05 Jun 2014 09:05:46 GMT

Hi,
Why did you use toracleoutputbulk, toraclebulkexec and toracleoutputbulkexec in your job design?
The tOracleOutputBulk and tOracleBulkExec components are used together in a two step process. In the first step, an output file is generated. In the second step, this file is used in the INSERT operation used to feed a database. These two steps are fused together in the tOracleOutputBulkExec component, detailed in a separate section. The advantage of using two separate steps is that the data can be transformed before it is loaded in the database.
You workflow should be:toracleinput -> tmap ->toracleoutputbulkexec
or toracleinput -> tmap ->toracleoutputbulk
|
|
onSubjobOk
|
toraclebulkexec
Best regards
Sabrina

Re: toracleoutputbulk

_AnonymousUser — Thu, 05 Jun 2014 09:18:07 GMT

hi sabrina,
I used the second method

toracleinput -> tmap ->toracleoutputbulk
|
|
onSubjobOk
|
toraclebulkexec

But still i get the same error..
SQL*Loader-350: Syntax error at line 10.
Expecting "(", found end of file.
Exception in component tOracleBulkExec_1
java.lang.Exception
How to resolve this? Kindly help me
Regards,
hermione

Re: toracleoutputbulk

Anonymous — Thu, 05 Jun 2014 09:24:59 GMT

Can you show the screenshot of component where you encountered error, metadata and sample data from 10th row
Vaibhav

Re: toracleoutputbulk

Anonymous — Thu, 05 Jun 2014 09:27:09 GMT

Hi,
Is everything OK for your when you use toracleinput -> tmap ->toracleoutputbulkexec or toracleinput -> tmap ->tlogrow?
Best regards
Sabrina

Re: toracleoutputbulk

_AnonymousUser — Thu, 05 Jun 2014 09:36:03 GMT

Hi vaibhav,
Error gets thrown in toraclebulkexec. Am not sure, i cant show the records as they are client oriented data. Sorry..

Re: toracleoutputbulk

_AnonymousUser — Thu, 05 Jun 2014 09:38:03 GMT

Hi sabrina,
Yes. when i use toracleinput -> tmap ->toracleoutputbulkexec, everything is ok. Job doesnt show any error. But the records are not getting inserted into the table in database mentioned in that component. And further, do the schemas should be same. i mean, the schemas in toracleinput and toraclebulkexec?
Regards,
hermione

Re: toracleoutputbulk

_AnonymousUser — Thu, 08 Sep 2016 09:15:05 GMT

Hi,
Is everything OK for your when you use toracleinput -> tmap ->toracleoutputbulkexec data will load to database

generate
1.data file
2.control file
3.discard file

if u can see the Table in database it will generate data.

SQL*Loader: Release 10.2.0.1.0 - Production on Thu Sep 8 13:23:15 2016

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Control File: C:/Ismail_talend/New folder_output/Bulkload1.txt1.ctl
Character Set UTF8 specified for all input.

Data File: C:/Ismail_talend/New folder_output/Bulkload1.txt
Bad File: C:/Ismail_talend/New folder_output/Bulkload1.txt1.bad
Discard File: C:/Ismail_talend/New folder_output/Bulkload1.txt1.dsc
(Allow all discards)
control file generate like:
===================
OPTIONS ()
LOAD DATA
CHARACTERSET 'UTF8'
INFILE 'C:/Ismail_talend/New folder_output/Bulkload1.txt'
BADFILE 'C:/Ismail_talend/New folder_output/Bulkload1.txt1.bad'
DISCARDFILE 'C:/Ismail_talend/New folder_output/Bulkload1.txt1.dsc'
INTO TABLE BULK1
INSERT
FIELDS TERMINATED BY ";"
("ID" "TO_NUMBER(ltrim(rtrim(:id)), '999G999G999G999D999999999999', 'NLS_NUMERIC_CHARACTERS=''.,''')",
"NAME")

but if u sue like
toracleinput -> tmap ->toracleoutputbulk
|
|
onSubjobOk
|
toraclebulkexec

toralceinput doesn't trigger with toraclebulkexec.

Re: toracleoutputbulk

Anonymous — Sat, 16 Jun 2018 02:01:05 GMT

This error can appear when you have not yet defined any columns in your schema. Click the 'Edit schema' button on your component and add a column or two. Then the error messages should start making more sense.

SQL*Loader-350: Syntax error at line 10.
Expecting "(", found end of file.
Exception in component tOracleBulkExec_1
java.lang.Exception

The error message is referring to the control file (.ctl) that Talend will auto-generate for you (unless you override it on the 'Advanced settings' tab > "Use existing control file"). Turn up your logging on the Job tab > Advanced settings.

If you look at a valid control file, it will look like this:

OPTIONS ()
LOAD DATA
CHARACTERSET 'UTF8'
INFILE 'C:/temp2/ora_data.txt'
BADFILE 'C:/temp2/ora_data.txt1.bad'
DISCARDFILE 'C:/temp2/ora_data.txt1.dsc'
INTO TABLE system.table1
INSERT
FIELDS TERMINATED BY ","
("COLUMN1", 
"COLUMN2")

If you look at the control file that was generated by Studio, and you get the error at top, you will notice the line line or two are missing -- the "('COLUMN1','COLUMN2')" line -- presumably because Studio did not know how to generate that line, because you did not specify anything for a schema.