topic Re: Problem with tOracleOutputBulkExec, SQL*Loader starts, job freezes in Talend Studio

Problem with tOracleOutputBulkExec, SQL*Loader starts, job freezes

Anonymous — Sat, 16 Nov 2024 13:31:38 GMT

Hello,
i use a tOracleOutputBulkExec component in my job.
When the execution comes to this component the .ctl file and the .csv file are created, the SQL*Loader starts and the the job freezes.
(The .log file from the SQL*Loader is also created but it is still empty.)
That is nothing happens any more.
The last message in the console window is

SQL*Loader: Release 10.2.0.3.0 - Production on Di Mrz 2 15:45:05 2010
Copyright (c) 1982, 2005, Oracle.  All rights reserved.

I know that this is a problem with the SQL*Loader and not with TOS but perhaps anybody in this forum has had the same problem before and can say me what to do?
Here is the content of the .ctl file:

OPTIONS (PARALLEL=false)
LOAD DATA
CHARACTERSET 'WE8ISO8859P15'
INFILE 'c:\Work\.....\jobGP_Import2Bulk\rCuAd_Out.csv'
BADFILE 'c:\Work\.....\jobGP_Import2Bulk\rCuAd_Out.csv.bad'
DISCARDFILE 'c:\Work\.....\jobGP_Import2Bulk\rCuAd_Out.csv.dsc'
INTO TABLE CURSOR_CRM_10_2.RCUAD#0
INSERT
FIELDS TERMINATED BY ";"
TRAILING NULLCOLS
("PK", 
"MASTERPK", 
"SLAVEPK", 
"DEFAULTCUAD", 
"LETTERCUAD", 
"ACTIVE", 
"CREATEDATE" DATE 'dd-MM-yyyy', 
"CREATEUSER", 
"UPDATEDATE" DATE 'dd-MM-yyyy', 
"UPDATEUSER", 
"STATUS", 
"WFINSTANCEID", 
"RIGHTPK", 
"CLIENTNO", 
"MASSDATA", 
"OFFLINEDATA")

Here are some lines of the data out of the .csv file:

;5BC731X;39D731X;1;1;;02-03-2010;GP_IMPORT;02-03-2010;GP_IMPORT;;;;;;
;7BC731X;59D731X;1;1;;02-03-2010;GP_IMPORT;02-03-2010;GP_IMPORT;;;;;;
;9BC731X;79D731X;1;1;;02-03-2010;GP_IMPORT;02-03-2010;GP_IMPORT;;;;;;
;BBC731X;99D731X;1;1;;02-03-2010;GP_IMPORT;02-03-2010;GP_IMPORT;;;;;;
;DBC731X;B9D731X;1;1;;02-03-2010;GP_IMPORT;02-03-2010;GP_IMPORT;;;;;;

I want to do the insert into the view RCUAD#0 and not into a table.
On this view there is an instead of trigger.
But this should be no problem isn't it?
Many thanks in advance for any help!
Regards Hartmut

Re: Problem with tOracleOutputBulkExec, SQL*Loader starts, job freezes

Anonymous — Tue, 02 Mar 2010 16:45:19 GMT

Hello,
maybe it is a TOS problem after all.
If i use the generated .ctl file and .csv file with my own call of SQL*Loader

sqlldr userid=cursor_crm_10_2/secret@hrudb control=rCuAd_Out.csv.ctl log=sqlldr.log

then it works!
Here is the logfile sqlldr.log:

SQL*Loader: Release 10.2.0.3.0 - Production on Di Mrz 2 17:37:35 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Kontrolldatei: rCuAd_Out.csv.ctl
Für gesamte Eingabe angegebener Zeichensatz WE8ISO8859P15.
Datendatei: c:\Work\Projekte\_z_TOS mit SOA\Export\jobGP_Import2Bulk_0.1\jobGP_Import2Bulk\rCuAd_Out.csv
Fehlerdatei: c:\Work\Projekte\_z_TOS mit SOA\Export\jobGP_Import2Bulk_0.1\jobGP_Import2Bulk\rCuAd_Out.csv.bad
Datei für zurückgewiesene Sätze: c:\Work\Projekte\_z_TOS mit SOA\Export\jobGP_Import2Bulk_0.1\jobGP_Import2Bulk\rCuAd_Out.csv.dsc
(alle Discards zulassen)
Zu ladende Anzahl: ALL
Zu überspringende Anzahl: 0
Zulässige Fehler: 50
Bind-Array: 64 Zeilen, maximal 256000 Bytes
Fortsetzung: nichts spezifiziert
Benutzer Pfad: Konventionell
Tabelle CURSOR_CRM_10_2.RCUAD#0, geladen von jedem logischen Satz.
Insert-Option in Kraft für diese Tabelle: INSERT
Option TRAILING NULLCOLS ist wirksam
Spaltenname Position Läng Term Eing Datentyp
------------------------------ ---------- ----- ---- ---- ---------------------
"PK" FIRST * ; CHARACTER
"MASTERPK" NEXT * ; CHARACTER
"SLAVEPK" NEXT * ; CHARACTER
"DEFAULTCUAD" NEXT * ; CHARACTER
"LETTERCUAD" NEXT * ; CHARACTER
"ACTIVE" NEXT * ; CHARACTER
"CREATEDATE" NEXT * ; DATE dd-MM-yyyy
"CREATEUSER" NEXT * ; CHARACTER
"UPDATEDATE" NEXT * ; DATE dd-MM-yyyy
"UPDATEUSER" NEXT * ; CHARACTER
"STATUS" NEXT * ; CHARACTER
"WFINSTANCEID" NEXT * ; CHARACTER
"RIGHTPK" NEXT * ; CHARACTER
"CLIENTNO" NEXT * ; CHARACTER
"MASSDATA" NEXT * ; CHARACTER
"OFFLINEDATA" NEXT * ; CHARACTER
Wert, der für ROWS-Parameter benutzt wird, wurde von 64 in 62 geändert
Tabelle CURSOR_CRM_10_2.RCUAD#0:
111 Zeilen erfolgreich geladen.
0 Zeilen aufgrund von Datenfehlern nicht geladen.
0 Zeilen nicht geladen, da alle WHEN-Klauseln fehlerhaft waren.
0 Zeilen nicht geladen, da alle Felder NULL waren.

Zugewiesener Bereich für Bind-Array: 255936 Bytes (62 Zeilen)
Byte in Lese-Puffer: 1048576
Gesamtzahl der übersprungenen logischen Datensätze: 0
Gesamtzahl der gelesenen logischen Datensätze: 111
Gesamtzahl der abgelehnten logischen Datensätze: 0
Gesamtzahl der zurückgewiesenen logischen Datensätze: 0
Lauf begonnen am Di Mrz 02 17:37:35 2010
Lauf beendet am Di Mrz 02 17:37:37 2010
Abgelaufene Zeit: 00:00:01.91
CPU-Zeit: 00:00:00.13

So perhaps the call of SQL*Loader from inside TOS is wrong?
Many thanks in advance for any help!
Regards Hartmut

Re: Problem with tOracleOutputBulkExec, SQL*Loader starts, job freezes

Anonymous — Tue, 02 Mar 2010 16:55:38 GMT

Hello,
is it possible to print the call of SQL*Loader from inside TOS on the console?
I want so see how TOS calls the SQL*Loader.
Is there a possibility to trace this call in debug mode?
Many thanks for any help!
Regards Hartmut

Re: Problem with tOracleOutputBulkExec, SQL*Loader starts, job freezes

Anonymous — Wed, 03 Mar 2010 11:05:35 GMT

Hello,
if there is an error in my configuration (e.g. if i do not check the option "Trailing null columns" although there are no values for every column in my data flow)
then i get errors written in the SQL*Loader .log file and the TOS job comes to an end.
But if there are no errors in the configuration (that is when calling the SQL*Loader manually with the generated .ctl file and .csv file works fine)
then the TOS job hangs after the SQL*Loader is started.
Any ideas?
Many thanks in advance for any help.
Regards Hartmut

Re: Problem with tOracleOutputBulkExec, SQL*Loader starts, job freezes

Anonymous — Fri, 05 Mar 2010 09:22:32 GMT

Hello,
does really nobody has an idea what i can do to get the tOracleOutputBulkExec component (i. e. the call of oracle SQL*Loader) run properly?
Many thanks in advance for any help.
Regards Hartmut