Home » RDBMS Server » Server Utilities » Sql Loader log file result in table? (merged)
Sql Loader log file result in table? (merged) [message #270469] Thu, 27 September 2007 01:58 Go to next message
rk1982
Messages: 24
Registered: September 2007
Junior Member
guide me how to extract the log file(created by sql * loader) information in table so that i can track it in my query.
I need how may record loaded,is operation was sucessful,any discard etc..

[mod-edit] illiterate IM speak removed.

[Updated on: Thu, 27 September 2007 08:37] by Moderator

Report message to a moderator

Re: Sql Loader log file result in table? [message #270472 is a reply to message #270469] Thu, 27 September 2007 02:03 Go to previous messageGo to next message
Michel Cadot
Messages: 68658
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use an external table.

Regards
Michel
Re: Sql Loader log file result in table? [message #270489 is a reply to message #270472] Thu, 27 September 2007 03:31 Go to previous messageGo to next message
rk1982
Messages: 24
Registered: September 2007
Junior Member
Could you please give me a example how to do this ..means the log information in table after sqlldr completes the loading in a table.

[Updated on: Thu, 27 September 2007 03:31]

Report message to a moderator

Re: Sql Loader log file result in table? [message #270492 is a reply to message #270469] Thu, 27 September 2007 03:36 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Is not it better to see all the table data rather than log information?
Re: Sql Loader log file result in table? [message #270497 is a reply to message #270492] Thu, 27 September 2007 03:47 Go to previous messageGo to next message
rk1982
Messages: 24
Registered: September 2007
Junior Member
My target is to capture weather all records are sucessfuly inserted in table by sql loader .That sucess or failure information i need to capture but not in log file it should be any custom table.
Kindly give me a example of it.
Re: Sql Loader log file result in table? [message #270502 is a reply to message #270497] Thu, 27 September 2007 03:55 Go to previous messageGo to next message
Michel Cadot
Messages: 68658
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post a (small) example of log file and I'll show you.

Regards
Michel
Re: Sql Loader log file result in table? [message #270512 is a reply to message #270469] Thu, 27 September 2007 04:06 Go to previous messageGo to next message
rk1982
Messages: 24
Registered: September 2007
Junior Member
Here is an example of log file..


SQL*Loader: Release 9.2.0.1.0 - Production on Wed Sep 26 14:26:02 2007

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Control File: abc.ctl
Data File: abc.dat
Bad File: abc.bad
Discard File: none specified

(Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Continuation: none specified
Path used: External Table

Table OUTCOLLECT, loaded from every logical record.
Insert option in effect for this table: APPEND

Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
CALLNO FIRST * , CHARACTER
CALLINGNO NEXT * , CHARACTER



CREATE DIRECTORY statements needed for files
------------------------------------------------------------------------
CREATE DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 AS '/oracle/oraclient/rohit'


CREATE TABLE statement for external table:
------------------------------------------------------------------------
CREATE TABLE "SYS_SQLLDR_X_EXT_OUTCOLLECT"
(
CALLNO VARCHAR2(20),
CALLINGNO VARCHAR2(20)
)
ORGANIZATION external
(
TYPE oracle_loader
DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
BADFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'abc.bad'
LOGFILE 'abc.log_xt'
READSIZE 1048576
FIELDS LDRTRIM
REJECT ROWS WITH ALL NULL FIELDS
(
CALLNO CHAR(255)
TERMINATED BY ",",
CALLINGNO CHAR(255)
TERMINATED BY ","
)
)
location
(
'abc.dat'
)
)REJECT LIMIT UNLIMITED


INSERT statements used to load internal tables:
------------------------------------------------------------------------
INSERT /*+ append */ INTO OUTCOLLECT
(
CALLNO,
CALLINGNO
)
SELECT
CALLNO,
CALLINGNO
FROM "SYS_SQLLDR_X_EXT_OUTCOLLECT"


statements to cleanup objects created by previous statements:
------------------------------------------------------------------------
DROP TABLE "SYS_SQLLDR_X_EXT_OUTCOLLECT"
DROP DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000



Run began on Wed Sep 26 14:26:02 2007
Run ended on Wed Sep 26 14:26:02 2007

Elapsed time was: 00:00:00.10
CPU time was: 00:00:00.02
Re: Sql Loader log file result in table? [message #270519 is a reply to message #270512] Thu, 27 September 2007 04:37 Go to previous messageGo to next message
Michel Cadot
Messages: 68658
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I provide help only to those who follow the guide.
Please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.

In addition, the data you asked are not in the file you posted, so the answer is easy: you can't extract them from the file.

Regards
Michel

[Updated on: Thu, 27 September 2007 04:48]

Report message to a moderator

Please help me on slql * loader [message #270754 is a reply to message #270469] Thu, 27 September 2007 23:33 Go to previous messageGo to next message
rk1982
Messages: 24
Registered: September 2007
Junior Member
Once loading with sqlloader is done with table it create a log file.Please give me a example(explain how exactly) how to store the log file informaiton in a any custom table.
Re: Please help me on slql * loader [message #270762 is a reply to message #270754] Fri, 28 September 2007 00:00 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
If you are capable & willing to learn, then you should be able to Read The Fine UTILITIES Manual & answer all your questions.
http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/toc.htm
Otherwise You're On Your Own (YOYO)!
Re: Please help me on slql * loader [message #270768 is a reply to message #270762] Fri, 28 September 2007 00:06 Go to previous messageGo to next message
rk1982
Messages: 24
Registered: September 2007
Junior Member
Buddy i already have link which u have send.But the problem is that am not able to collect from this doc.Kindly give a small example to do it.
Re: Please help me on slql * loader [message #270778 is a reply to message #270754] Fri, 28 September 2007 00:30 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Are you saying that you do not know how to loading a table with a single VARCHAR2 variable?
Please post what you have already tried & explain in detail why the results are not as expected.
Re: Please help me on slql * loader [message #270786 is a reply to message #270778] Fri, 28 September 2007 00:47 Go to previous messageGo to next message
rk1982
Messages: 24
Registered: September 2007
Junior Member
see i just loaded some data from flat file which looks like
ABCCORP,100
BAQCORP,200
in table name CORP(name varchar(20),id int).Which SQLLDR loaded successfuly and created log file.
Now my problem is how to store the information in another custom table which contains how many records loaded in corp table by sqlldr , was the operation successful,any discard,what was the flat file name where load operation done.
Please explain me in deatil how i can achieve this.
Re: Please help me on slql * loader [message #270790 is a reply to message #270754] Fri, 28 September 2007 00:56 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>see i just loaded some data from flat file which looks like
ABCCORP,100
BAQCORP,200
>Please explain me in deatil how i can achieve this.
Do it just like you did before.
CONGRATULATIONS!
Re: Please help me on slql * loader [message #270794 is a reply to message #270790] Fri, 28 September 2007 01:07 Go to previous messageGo to next message
rk1982
Messages: 24
Registered: September 2007
Junior Member
Buddy its not done before . yesterday i did't get proper reply so thats why am asking. plz help me .. i need to develop some requrement.
Re: Please help me on slql * loader [message #270800 is a reply to message #270754] Fri, 28 September 2007 01:15 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Buddy its not done before
I am confused.
>>see i just loaded some data from flat file which looks like
you said you loaded data.
Therefore just do it again but to different table.
Re: Please help me on slql * loader [message #270806 is a reply to message #270794] Fri, 28 September 2007 01:24 Go to previous messageGo to next message
rk1982
Messages: 24
Registered: September 2007
Junior Member
Loading is not problem i need to store some other informaiton in other table which contain how many records loaded and was the loading successful. Table structure as follows
Log_Info(Record_loaded int,Is_Success int)
Re: Please help me on slql * loader [message #270810 is a reply to message #270754] Fri, 28 September 2007 01:29 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Loading is not problem i need to store some other informaiton in other table which contain how many records loaded and was the loading successful.
If you can load data, what prevent you from COUNT records loaded & LOADING that where you desire?
Re: Please help me on slql * loader [message #270812 is a reply to message #270806] Fri, 28 September 2007 01:31 Go to previous messageGo to next message
Michel Cadot
Messages: 68658
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So this is the same question as your previous one.
I merged the two.

Regards
Michel
Re: Please help me on slql * loader [message #270822 is a reply to message #270806] Fri, 28 September 2007 01:46 Go to previous messageGo to next message
rk1982
Messages: 24
Registered: September 2007
Junior Member
see how can i store information weather the sql loader loading was successful or not.and I mean the log file contains information i need to put it in custom table.Is any way ?? please guide me how to acheive this?
Re: Sql Loader log file result in table? (merged) [message #270825 is a reply to message #270469] Fri, 28 September 2007 01:54 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>data is data.
If you can load DATA, then you can load details (data) about the data you just loaded.
So your problem is solved or you need to hire somebody who can produce results instead of questions.
Re: Please help me on slql * loader [message #270828 is a reply to message #270822] Fri, 28 September 2007 02:00 Go to previous messageGo to next message
rk1982
Messages: 24
Registered: September 2007
Junior Member
If you ever seen the log file you would not replied like that.Well you don't want to tell me then fine.
Re: Sql Loader log file result in table? (merged) [message #270830 is a reply to message #270469] Fri, 28 September 2007 02:05 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>If you ever seen the log file you would not replied like that.Well you don't want to tell me then fine.
I probably seen more log files, than you can imagine.
A logfile contains text stings.
If you can not load a single,simple text string into Oracle, then you may want to consider a different occupation.
Re: Please help me on slql * loader [message #270845 is a reply to message #270828] Fri, 28 September 2007 02:48 Go to previous messageGo to next message
Michel Cadot
Messages: 68658
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As I already said, the file you posted does not contain the information you ask, so you can't extract them from it.

Regards
Michel
Re: Please help me on slql * loader [message #270865 is a reply to message #270845] Fri, 28 September 2007 03:37 Go to previous messageGo to next message
rk1982
Messages: 24
Registered: September 2007
Junior Member
please check out the log file attached and please explain me how to retrive.
  • Attachment: abc.log
    (Size: 1.41KB, Downloaded 1132 times)
Re: Please help me on slql * loader [message #270875 is a reply to message #270865] Fri, 28 September 2007 04:08 Go to previous messageGo to next message
Michel Cadot
Messages: 68658
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> create table load_log (line varchar2(2000))
  2  organization external (
  3     type oracle_loader
  4     default directory log_file_dir
  5     access parameters (
  6        records delimited by newline
  7        nobadfile
  8        nologfile
  9        nodiscardfile
 10        fields terminated by '<michel>'
 11        missing field values are null
 12        (line)
 13        )
 14     location ('abc.log') 
 15     )
 16  reject limit unlimited
 17  /

Table created.

SQL> select * from load_log where line like 'Total logical records%';
LINE
----------------------------------------------------------------
Total logical records skipped:          0
Total logical records read:             2
Total logical records rejected:         0
Total logical records discarded:        0

4 rows selected.

Regards
Michel
Re: Please help me on slql * loader [message #270884 is a reply to message #270865] Fri, 28 September 2007 04:42 Go to previous message
rk1982
Messages: 24
Registered: September 2007
Junior Member
Thanx buddy.
Previous Topic: External table question
Next Topic: How to properly load CHAR from CSV file to DATE column? (split and renamed by LF and merge by MC)
Goto Forum:
  


Current Time: Sat Jun 01 09:13:44 CDT 2024