Home » RDBMS Server » Server Utilities » sqlldr help (oracle,10g)
sqlldr help [message #535461] Wed, 14 December 2011 04:12 Go to next message
sathik
Messages: 196
Registered: August 2009
Location: INDIA
Senior Member
Hi,

I want to load data from LST file. The data format and control file is given below. It is loading the 1st line only. it is not loading the other lines. pls let me know what needs to be added in the control file to load this data?


Table Scan: |14-DEC-11 09:54 |xest      |   16|             0|SYSTEM            |ws_email|declare v_lst_suc da|14-DEC-11 08:32:39|         716444|XEST_USER
 XEST_USER.X|                |          |     |              |                  |er.exe  |te; v_nxt_sch date; |                  |               |
EST_PING_RCV|                |          |     |              |                  |        |cur_time varchar2(30|                  |               |
D: 28609 out|                |          |     |              |                  |        |); begin    --select|                  |               |
 of 28609 Bl|                |          |     |              |                  |        | last_date, next_dat|                  |               |
ocks done   |                |          |     |              |                  |        |e into v_lst_suc, v_|                  |               |
            |                |          |     |              |                  |        |nxt_sch from dba_job|                  |               |
            |                |          |     |              |                  |        |s where job = 83;   |                  |               |
            |                |          |     |              |                  |        | select timestamp in|                  |               |
            |                |          |     |              |                  |        |to v_lst_suc from (S|                  |               |
            |                |          |     |              |                  |        |ELECT timestamp FROM|                  |               |
            |                |          |     |              |                  |        | xest_statistics whe|                  |               |
            |                |          |     |              |                  |        |re job_name='pr_Upda|                  |               |
            |                |          |     |              |                  |        |teNEO'    ORDER BY t|                  |               |
            |                |          |     |              |                  |        |imestamp DESC) WHERE|                  |               |
            |                |          |     |              |                  |        | ROWNUM <= 1;     se|                  |               |
            |                |          |     |              |                  |        |lect to_char(sysdate|                  |               |
            |                |          |     |              |                  |        |,'DD-MON-YY HH24:MI'|                  |               |
            |                |          |     |              |                  |        |) into cur_time from|                  |               |
            |                |          |     |              |                  |        | dual;              |                  |               |

Table Scan: |14-DEC-11 09:54 |xest      |   16|             0|SYSTEM            |ws_email|declare v_lst_suc da|14-DEC-11 08:32:39|         716444|XEST_USER
 XEST_USER.X|                |          |     |              |                  |er.exe  |te; v_nxt_sch date; |                  |               |
EST_PING_RCV|                |          |     |              |                  |        |cur_time varchar2(30|                  |               |
D: 28609 out|                |          |     |              |                  |        |); begin    --select|                  |               |
 of 28609 Bl|                |          |     |              |                  |        | last_date, next_dat|                  |               |
ocks done   |                |          |     |              |                  |        |e into v_lst_suc, v_|                  |               |
            |                |          |     |              |                  |        |nxt_sch from dba_job|                  |               |
            |                |          |     |              |                  |        |s where job = 83;   |                  |               |
            |                |          |     |              |                  |        | select timestamp in|                  |               |
            |                |          |     |              |                  |        |to v_lst_suc from (S|                  |               |
            |                |          |     |              |                  |        |ELECT timestamp FROM|                  |               |
            |                |          |     |              |                  |        | xest_statistics whe|                  |               |
            |                |          |     |              |                  |        |re job_name='pr_Upda|                  |               |
            |                |          |     |              |                  |        |teNEO'    ORDER BY t|                  |               |
            |                |          |     |              |                  |        |imestamp DESC) WHERE|                  |               |
            |                |          |     |              |                  |        | ROWNUM <= 1;     se|                  |               |
            |                |          |     |              |                  |        |lect to_char(sysdate|                  |               |
            |                |          |     |              |                  |        |,'DD-MON-YY HH24:MI'|                  |               |
            |                |          |     |              |                  |        |) into cur_time from|                  |               |
            |                |          |     |              |                  |        | dual;              |                  |               |
 



load data
infile 'output1.lst' "str '|\n'"
append
into table load_sql
FIELDS TERMINATED BY '|'
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS 
(  
MESSAGE,
REPORT_DATE "to_date(trim(:REPORT_DATE),'DD-MON-YY HH24:MI')",      
DB_NAME,          
SID,              
SQL_HASH_VALUE,   
OSUSER,           
PROGRAM,          
SQL_TEXT,         
QUERY_STARTED "to_date(trim(:QUERY_STARTED),'DD-MON-YY HH24:MI:SS')",    
TIME_REMAINING,
USERNAME
)

[Updated on: Wed, 14 December 2011 04:17]

Report message to a moderator

Re: sqlldr help [message #535462 is a reply to message #535461] Wed, 14 December 2011 04:13 Go to previous messageGo to next message
Littlefoot
Messages: 21561
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What does the log file say?
Re: sqlldr help [message #535465 is a reply to message #535462] Wed, 14 December 2011 04:18 Go to previous messageGo to next message
sathik
Messages: 196
Registered: August 2009
Location: INDIA
Senior Member

Log file says 1 row loaded succesfully.


 (Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array:     64 rows, maximum of 256000 bytes
Continuation:   Last non-white character != 0X7c(character '|')
                Preserving continuation characters as data
Path used:      Conventional

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

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
MESSAGE                             FIRST     *   |  O(") CHARACTER            
REPORT_DATE                          NEXT     *   |  O(") CHARACTER            
    SQL string for column : "to_date(trim(:REPORT_DATE),'DD-MON-YY HH24:MI')"
DB_NAME                              NEXT     *   |  O(") CHARACTER            
SID                                  NEXT     *   |  O(") CHARACTER            
SQL_HASH_VALUE                       NEXT     *   |  O(") CHARACTER            
OSUSER                               NEXT     *   |  O(") CHARACTER            
PROGRAM                              NEXT     *   |  O(") CHARACTER            
SQL_TEXT                             NEXT     *   |  O(") CHARACTER            
QUERY_STARTED                        NEXT     *   |  O(") CHARACTER            
    SQL string for column : "to_date(trim(:QUERY_STARTED),'DD-MON-YY HH24:MI:SS')"
TIME_REMAINING                       NEXT     *   |  O(") CHARACTER            
USERNAME                             NEXT     *   |  O(") CHARACTER            


Table LOAD_SQL:
  1 Row successfully loaded.
  0 Rows not loaded due to data errors.
  0 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.


Space allocated for bind array:                 181632 bytes(64 rows)
Read   buffer bytes: 1048576

Total logical records skipped:          0
Total logical records read:             1
Total logical records rejected:         0
Total logical records discarded:        0

Run began on Wed Dec 14 10:15:27 2011
Run ended on Wed Dec 14 10:15:27 2011

Elapsed time was:     00:00:00.11
CPU time was:         00:00:00.08
Re: sqlldr help [message #535475 is a reply to message #535465] Wed, 14 December 2011 04:34 Go to previous messageGo to next message
Littlefoot
Messages: 21561
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
According to the log file, everything is fine - there were no errors.

This control file line:

infile 'output1.lst' "str '|\n'"

might be important. Could you attach the OUTPUT1.LST file so that we could test it? By the way, which operating system do you use?
Re: sqlldr help [message #535477 is a reply to message #535475] Wed, 14 December 2011 04:37 Go to previous messageGo to next message
sathik
Messages: 196
Registered: August 2009
Location: INDIA
Senior Member

I am doing in windows OS. The output1.lst is attached.
  • Attachment: output1.LST
    (Size: 5.80KB, Downloaded 410 times)
Re: sqlldr help [message #535485 is a reply to message #535477] Wed, 14 December 2011 05:08 Go to previous messageGo to next message
Littlefoot
Messages: 21561
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Oh well, I think I was wrong (about "str '|\n'").

This case is not really an Assembling Logical Records from Physical Records because column values don't follow one after another (through several lines), so I think that you can't even use CONTINUEIF or CONCATENATE.

It is in the middle of the night in California, but I'm quite sure Barbara will notice your problem and, as she's a VERY experienced with SQL*Loader, perhaps she'll have an idea.

Personally, I wouldn't know whether it is even possible to do that with SQL*Loader only. Maybe, if you create a staging table and load the whole file contents into a single VARCHAR2 column, and then - using (PL/)SQL, parse those lines and store values into the real (LOAD_SQL) table. Alternatively, the same could be done if you use the OUTPUT1.LST as an external table (in that case, file must reside on a database server, which is kind of drawback).

No better ideas at the moment, sorry.
Re: sqlldr help [message #535487 is a reply to message #535485] Wed, 14 December 2011 05:16 Go to previous messageGo to next message
sathik
Messages: 196
Registered: August 2009
Location: INDIA
Senior Member
No Problem. Thanks for your response. I will wait for her response.

Meanwhile, can you help me to load this in a staging table and then load into a main table via procudure? I am absolutely no idea about this.

[Updated on: Wed, 14 December 2011 05:17]

Report message to a moderator

Re: sqlldr help [message #535493 is a reply to message #535487] Wed, 14 December 2011 05:57 Go to previous messageGo to next message
Littlefoot
Messages: 21561
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I can't develop the whole solution for you, but here's what I meant: create a staging table:
SQL> create table load_sql_big
  2  (id number,
  3   col varchar2(1000));

Table created.

Write a control file (test1.ctl):
load data
infile 'output1.txt' 
replace
into table load_sql_big
(id  sequence,
 col char(1000)
)

Load data:
SQL> $sqlldr scott/tiger@ora10 control=test1.ctl log=test1.log

SQL*Loader: Release 11.2.0.2.0 - Production on Sri Pro 14 12:49:00 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 41

SQL>

A PL/SQL block that extracts MESSAGE column value:
SQL> declare
  2    l_sub     varchar2(500);
  3    l_message varchar2(500);
  4    l_max_id  number;
  5  begin
  6    select max(id) into l_max_id from load_sql_big;
  7
  8    for cur_r in (select id, col from load_sql_big order by id) loop
  9      l_sub := substr(cur_r.col, 1, instr(cur_r.col, '|', 1, 1) - 1);
 10
 11      l_message := l_message || case when length(l_sub) = 0 then null
 12                                     else l_sub
 13                                end;
 14      if length(cur_r.col) is null or cur_r.id = l_max_id
 15      then
 16         insert into load_sql (message) values (l_message);
 17         l_message := null;
 18      end if;
 19    end loop;
 20  end;
 21  /

PL/SQL procedure successfully completed.

SQL>

Finally, a MESSAGE column values:
SQL> select trim(message) from load_sql;

TRIM(MESSAGE)
----------------------------------------------------------------------------------------------------
Table Scan:  XEST_USER.XEST_PING_RCVD: 28609 out of 28609 Blocks done
Table Scan:  XEST_USER.XEST_PING_RCVD: 28609 out of 28609 Blocks done

SQL>

You'd do the same for the rest of the columns; a lot of string manipulation, obviously. If you are good at regular expressions, it might be simpler.

[Updated on: Wed, 14 December 2011 05:58]

Report message to a moderator

Re: sqlldr help [message #535497 is a reply to message #535493] Wed, 14 December 2011 06:22 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
Registered: November 2002
Location: California, USA
Senior Member
-- test.ctl into staging table (without "str '|\n'"):
load data
infile 'output1.lst'
append
into table staging
FIELDS TERMINATED BY '|'
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(seqnum SEQUENCE,
MESSAGE,
REPORT_DATE "to_date(trim(:REPORT_DATE),'DD-MON-YY HH24:MI')",
DB_NAME,
SID,
SQL_HASH_VALUE,
OSUSER,
PROGRAM,
SQL_TEXT,
QUERY_STARTED "to_date(trim(:QUERY_STARTED),'DD-MON-YY HH24:MI:SS')",
TIME_REMAINING,
USERNAME
)


-- staging table:
SCOTT@orcl_11gR2> create table staging
  2    (seqnum	       number,
  3  	message        varchar2(15),
  4  	report_date    date,
  5  	db_name        varchar2(15),
  6  	sid	       number,
  7  	sql_hash_value varchar2(15),
  8  	osuser	       varchar2(18),
  9  	program        varchar2(15),
 10  	sql_text       varchar2(20),
 11  	query_started  varchar2(15),
 12  	time_remaining varchar2(15),
 13  	username       varchar2(23))
 14  /

Table created.


-- load into staging table:
SCOTT@orcl_11gR2> host sqlldr scott/tiger control=test.ctl log=test.log


-- load_sql table:
SCOTT@orcl_11gR2> create table load_sql
  2    (message        clob,
  3  	report_date    date,
  4  	db_name        varchar2(15),
  5  	sid	       varchar2(15),
  6  	sql_hash_value varchar2(15),
  7  	osuser	       varchar2(18),
  8  	program        varchar2(15),
  9  	sql_text       clob,
 10  	query_started  varchar2(15),
 11  	time_remaining varchar2(15),
 12  	username       varchar2(23))
 13  /

Table created.


-- insert from staging to load_sql:
SCOTT@orcl_11gR2> declare
  2  	v_message	 clob;
  3  	v_report_date	 date;
  4  	v_db_name	 varchar2(15);
  5  	v_sid		 number;
  6  	v_sql_hash_value varchar2(15);
  7  	v_osuser	 varchar2(18);
  8  	v_program	 varchar2(15);
  9  	v_sql_text	 clob;
 10  	v_query_started  varchar2(15);
 11  	v_time_remaining varchar2(15);
 12  	v_username	 varchar2(23);
 13  begin
 14    for r in
 15  	 (select * from staging order by seqnum)
 16    loop
 17  	 if r.report_date is not null then
 18  	   if r.seqnum != 1 then
 19  	     insert into load_sql
 20  	       (message, report_date, db_name, sid, sql_hash_value, osuser,
 21  		program, sql_text, query_started, time_remaining, username)
 22  	     values
 23  	       (v_message, v_report_date, v_db_name, v_sid, v_sql_hash_value, v_osuser,
 24  		v_program, v_sql_text, v_query_started, v_time_remaining, v_username);
 25  	   end if;
 26  	   v_message := r.message;
 27  	   v_report_date := r.report_date;
 28  	   v_db_name := r.db_name;
 29  	   v_sid := r.sid;
 30  	   v_sql_hash_value := r.sql_hash_value;
 31  	   v_osuser := r.osuser;
 32  	   v_program := r.program;
 33  	   v_sql_text := r.sql_text;
 34  	   v_query_started := r.query_started;
 35  	   v_time_remaining := r.time_remaining;
 36  	   v_username := r.username;
 37  	 elsif r.report_date is null then
 38  	   v_message := v_message || r.message;
 39  	   v_sql_text := v_sql_text || r.sql_text;
 40  	 end if;
 41    end loop;
 42    insert into load_sql
 43  	 (message, report_date, db_name, sid, sql_hash_value, osuser,
 44  	  program, sql_text, query_started, time_remaining, username)
 45    values
 46  	 (v_message, v_report_date, v_db_name, v_sid, v_sql_hash_value, v_osuser,
 47  	  v_program, v_sql_text, v_query_started, v_time_remaining, v_username);
 48  end;
 49  /

PL/SQL procedure successfully completed.


-- results:
SCOTT@orcl_11gR2> column message format a30 word_wrapped
SCOTT@orcl_11gR2> column sql_text format a30 word_wrapped
SCOTT@orcl_11gR2> select * from load_sql
  2  /

MESSAGE                        REPORT_DA DB_NAME         SID             SQL_HASH_VALUE  OSUSER             PROGRAM         SQL_TEXT                       QUERY_STARTED   TIME_REMAINING  USERNAME
------------------------------ --------- --------------- --------------- --------------- ------------------ --------------- ------------------------------ --------------- --------------- -----------------------
Table Scan:                    14-DEC-11 xest            16              0               SYSTEM             ws_email        declare v_lst_suc date;        14-DEC-11       716444          XEST_USER
XEST_USER.XEST_PING_RCVD:                                                                                                   v_nxt_sch date; cur_time
28609 outof 28609 Blocks done                                                                                               varchar2(30); begin
                                                                                                                            --selectlast_date, next_date
                                                                                                                            into v_lst_suc, v_nxt_sch from
                                                                                                                            dba_jobs where job = 83;
                                                                                                                            select timestamp into
                                                                                                                            v_lst_suc from (SELECT
                                                                                                                            timestamp FROMxest_statistics
                                                                                                                            where job_name='pr_UpdateNEO'
                                                                                                                            ORDER BY timestamp DESC)
                                                                                                                            WHEREROWNUM <= 1;     select
                                                                                                                            to_char(sysdate,'DD-MON-YY
                                                                                                                            HH24:MI') into cur_time
                                                                                                                            fromdual;

Table Scan:                    14-DEC-11 xest            16              0               SYSTEM             ws_email        declare v_lst_suc date;        14-DEC-11       716444          XEST_USER
XEST_USER.XEST_PING_RCVD:                                                                                                   v_nxt_sch date; cur_time
28609 outof 28609 Blocks done                                                                                               varchar2(30); begin
                                                                                                                            --selectlast_date, next_date
                                                                                                                            into v_lst_suc, v_nxt_sch from
                                                                                                                            dba_jobs where job = 83;
                                                                                                                            select timestamp into
                                                                                                                            v_lst_suc from (SELECT
                                                                                                                            timestamp FROMxest_statistics
                                                                                                                            where job_name='pr_UpdateNEO'
                                                                                                                            ORDER BY timestamp DESC)
                                                                                                                            WHEREROWNUM <= 1;     select
                                                                                                                            to_char(sysdate,'DD-MON-YY
                                                                                                                            HH24:MI') into cur_time
                                                                                                                            fromdual;


2 rows selected.

Re: sqlldr help [message #535499 is a reply to message #535497] Wed, 14 December 2011 06:33 Go to previous messageGo to next message
Littlefoot
Messages: 21561
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I knew Barbara will handle it!
Re: sqlldr help [message #535518 is a reply to message #535499] Wed, 14 December 2011 08:06 Go to previous message
sathik
Messages: 196
Registered: August 2009
Location: INDIA
Senior Member
oh My god. it is working properly. Thanks a lot Barbara. it really helpful for me.
Previous Topic: problem with sqlldr
Next Topic: Data Pump error
Goto Forum:
  


Current Time: Sat May 30 01:46:49 CDT 2020