Home » RDBMS Server » Server Utilities » sqlldr help (oracle,10g)
| sqlldr help [message #535461] |
Wed, 14 December 2011 04:12  |
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 #535465 is a reply to message #535462] |
Wed, 14 December 2011 04:18  |
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 #535485 is a reply to message #535477] |
Wed, 14 December 2011 05:08 |
 |
Littlefoot
Messages: 21823
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 |
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 |
|
Littlefoot
Messages: 21823
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 |
 |
Barbara Boehmer
Messages: 9101
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.
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Mon Dec 23 06:31:17 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
