Home » RDBMS Server » Server Utilities » How to get the proper value in external table (Sql/plsql)
How to get the proper value in external table [message #553364] |
Thu, 03 May 2012 17:39 |
punu77
Messages: 20 Registered: July 2005
|
Junior Member |
|
|
Hi,
Can some one help me in getting proper value from the file in external table.
How can I get the whole status in STATUS column like completed , Inprogress, incompleted.
Right now, if I gave position like (38:9) full status doesn't show. if I give (38:11) then '|1' is adding in status from the flat file.
Please help me.
BATCH_NO FILE_DATE EMP_ID COMPANY_ID TRANSACTIN_ID FILE_NAME STATUS DOC_NO
100001 04252012 100001*** 4252012 **1 :35:57***D 100001***04252012***10:35:57***Diver ified
201441 _7076551 201441424 7076551 OLC 1234567899 201441424_7076551_AAA_1234567899.txt Completed 10085300090061
201441 _7076551 201441424 7076551 OLC 1234567899 201441424_7076551_AAA_1234567899.txt Completed 10085300090091
201441 _7076551 201441424 7076551 OLC 1234567899 201441424_7076551_AAA_1234567899.txt Completed 10085300090021
201441 _7076551 201441424 7076551 OLC 1234567899 201441424_7076551_AAA_1234567899.txt Completed 10085300090051
700331 _7078197 700331352 7078197 RIE 5678901234 700331352_7078197_BBB_5678901234.txt Inprogres |
201441 _7075703 201441424 7075703 OLC 3456789134 201441424_7075703_AAA_3456789134.txt Incomplet d|
700223 _7077646 700223567 7077646 ECS 2345678912 700223567_7077646_CCC_2345678912.txt Completed 10085200080281
700223 _7077646 700223567 7077646 ECS 2345678912 700223567_7077646_CCC_2345678912.txt Completed 10085200080251
700223 _7077646 700223567 7077646 ECS 2345678912 700223567_7077646_CCC_2345678912.txt Completed 10085200080111
201442 _7075775 201442669 7075775 RIE 5432167891 201442669_7075775_BBB_5432167891.txt completed 10085200080111
Here is the orginal flat file
100001***04252012***10:35:57***NONUS
201441424_7076551_AAA_1234567899.txt|Completed|10085300090061|
201441424_7076551_AAA_1234567899.txt|Completed|10085300090091|
201441424_7076551_AAA_1234567899.txt|Completed|10085300090021|
201441424_7076551_AAA_1234567899.txt|Completed|10085300090051|
700331352_7078197_BBB_5678901234.txt|Inprogress|
201441424_7075703_AAA_3456789134.txt|Incompleted|
700223567_7077646_CCC_2345678912.txt|Completed|10085200080281|
700223567_7077646_CCC_2345678912.txt|Completed|10085200080251|
700223567_7077646_CCC_2345678912.txt|Completed|10085200080111|
201442669_7075775_BBB_5432167891.txt|completed|10085200080111|
CREATE TABLE master_xt
(
BATCH_NO VARCHAR2(6 BYTE),
FILE_DATE VARCHAR2(8 BYTE),
emp_ID VARCHAR2(12 BYTE),
company_ID VARCHAR2(12 BYTE),
SOURCE_ID VARCHAR2(3 BYTE),
transction_ID VARCHAR2(12 BYTE),
FILE_NAME VARCHAR2(50 BYTE),
STATUS VARCHAR2(15 BYTE),
DOC_NO VARCHAR2(20 BYTE)
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY EXT_DIR
ACCESS PARAMETERS
( RECORDS DELIMITED BY NEWLINE
BADFILE ext_tab_dir:'master_xt.DAT'
LOGFILE ext_tab_dir:'master_xt.LOG'
FIELDS NOTRIM
(
BATCH_NO (1:6) CHAR(6),
FILE_DATE (10:8) CHAR(8),
emp_ID (1:9) CHAR(9),
company_ID (11:7) CHAR(7),
SOURCE_ID (19:3) CHAR(3),
transction_ID (23:33) CHAR(10),
FILE_NAME (1:36) CHAR(36),
STATUS (38:9) CHAR(9),
DOC_NO (48:62) CHAR(14)
)
)
LOCATION (EXT_DIR:'Master.txt')
)
REJECT LIMIT UNLIMITED;
[mod-edit: smileys disabled by bb to avoid converting char(8) to a smiley and code tags added by bb]
[Updated on: Fri, 04 May 2012 11:23] by Moderator Report message to a moderator
|
|
|
Re: How to get the proper value in external table [message #553375 is a reply to message #553364] |
Fri, 04 May 2012 00:54 |
|
Littlefoot
Messages: 21818 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
I don't think that you can do that as you planned to, because STATUS' length varies and you can't fetch its value positionally.
Here's another option - create an external table that separates fields by a pipe (|) sign - 3 fields in all. Then write a SELECT statement which will extract desired values.
SQL> create table master_xt_1
2 (
3 field_1 varchar2(40),
4 field_2 varchar2(20),
5 field_3 varchar2(20)
6 )
7 organization external
8 (type oracle_loader
9 default directory ext_dir
10 access parameters
11 (records delimited by newline
12 fields terminated by '|'
13 missing field values are null
14 )
15 location ('master.txt')
16 )
17 reject limit unlimited;
Table created.
SQL> select * from master_xt_1;
FIELD_1 FIELD_2 FIELD_3
---------------------------------------- -------------------- --------------------
100001***04252012***10:35:57***NONUS
201441424_7076551_AAA_1234567899.txt Completed 10085300090061
201441424_7076551_AAA_1234567899.txt Completed 10085300090091
201441424_7076551_AAA_1234567899.txt Completed 10085300090021
201441424_7076551_AAA_1234567899.txt Completed 10085300090051
700331352_7078197_BBB_5678901234.txt Inprogress
201441424_7075703_AAA_3456789134.txt Incompleted
700223567_7077646_CCC_2345678912.txt Completed 10085200080281
700223567_7077646_CCC_2345678912.txt Completed 10085200080251
700223567_7077646_CCC_2345678912.txt Completed 10085200080111
201442669_7075775_BBB_5432167891.txt completed 10085200080111
11 rows selected.
SQL> select
2 substr(field_1, 1, 6) batch_no,
3 substr(field_1, 10, 8) file_date,
4 substr(field_1, 1, 9) emp_id,
5 substr(field_1, 11, 7) company_id,
6 substr(field_1, 19, 3) source_id,
7 substr(field_1, 23, 33) transction_id,
8 substr(field_1, 1, 36) file_name,
9 field_2 status,
10 field_3 doc_no
11 from master_xt_1;
BATCH_ FILE_DAT EMP_ID COMPANY SOU TRANSCTION_ID FILE_NAME STATUS DOC_NO
------ -------- --------- ------- --- ------------------ ------------------------------------ ----------- ---------------
100001 04252012 100001*** 4252012 **1 :35:57***NONUS 100001***04252012***10:35:57***NONUS
201441 _7076551 201441424 7076551 AAA 1234567899.txt 201441424_7076551_AAA_1234567899.txt Completed 10085300090061
201441 _7076551 201441424 7076551 AAA 1234567899.txt 201441424_7076551_AAA_1234567899.txt Completed 10085300090091
201441 _7076551 201441424 7076551 AAA 1234567899.txt 201441424_7076551_AAA_1234567899.txt Completed 10085300090021
201441 _7076551 201441424 7076551 AAA 1234567899.txt 201441424_7076551_AAA_1234567899.txt Completed 10085300090051
700331 _7078197 700331352 7078197 BBB 5678901234.txt 700331352_7078197_BBB_5678901234.txt Inprogress
201441 _7075703 201441424 7075703 AAA 3456789134.txt 201441424_7075703_AAA_3456789134.txt Incompleted
700223 _7077646 700223567 7077646 CCC 2345678912.txt 700223567_7077646_CCC_2345678912.txt Completed 10085200080281
700223 _7077646 700223567 7077646 CCC 2345678912.txt 700223567_7077646_CCC_2345678912.txt Completed 10085200080251
700223 _7077646 700223567 7077646 CCC 2345678912.txt 700223567_7077646_CCC_2345678912.txt Completed 10085200080111
201442 _7075775 201442669 7075775 BBB 5432167891.txt 201442669_7075775_BBB_5432167891.txt completed 10085200080111
11 rows selected.
|
|
|
Re: How to get the proper value in external table [message #553454 is a reply to message #553375] |
Fri, 04 May 2012 11:45 |
|
Barbara Boehmer
Messages: 9097 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
You can combine positional and delimited field declarations, as shown below.
SCOTT@orcl_11gR2> CREATE OR REPLACE DIRECTORY ext_dir AS 'c:\my_oracle_files'
2 /
Directory created.
SCOTT@orcl_11gR2> CREATE OR REPLACE DIRECTORY ext_tab_dir AS 'c:\my_oracle_files'
2 /
Directory created.
SCOTT@orcl_11gR2> CREATE TABLE master_xt
2 (BATCH_NO VARCHAR2( 6 BYTE),
3 FILE_DATE VARCHAR2( 8 BYTE),
4 emp_ID VARCHAR2(12 BYTE),
5 company_ID VARCHAR2(12 BYTE),
6 SOURCE_ID VARCHAR2( 3 BYTE),
7 transction_ID VARCHAR2(12 BYTE),
8 FILE_NAME VARCHAR2(50 BYTE),
9 STATUS VARCHAR2(15 BYTE),
10 DOC_NO VARCHAR2(20 BYTE))
11 ORGANIZATION EXTERNAL
12 (TYPE ORACLE_LOADER
13 DEFAULT DIRECTORY EXT_DIR
14 ACCESS PARAMETERS
15 (RECORDS DELIMITED BY NEWLINE
16 BADFILE ext_tab_dir:'master_xt.DAT'
17 LOGFILE ext_tab_dir:'master_xt.LOG'
18 FIELDS NOTRIM
19 MISSING FIELD VALUES ARE NULL
20 (BATCH_NO ( 1: 6) CHAR( 6),
21 FILE_DATE (10:16) CHAR( 8),
22 emp_ID ( 1: 9) CHAR( 9),
23 company_ID (11:17) CHAR( 7),
24 SOURCE_ID (19:21) CHAR( 3),
25 transction_ID (23:33) CHAR(10),
26 FILE_NAME ( 1:36) CHAR(36),
27 STATUS (38) CHAR(15) TERMINATED BY '|',
28 DOC_NO (*) CHAR(14) TERMINATED BY '|'))
29 LOCATION (EXT_DIR:'Master.txt'))
30 REJECT LIMIT UNLIMITED
31 /
Table created.
SCOTT@orcl_11gR2> SELECT * FROM master_xt
2 /
BATCH_ FILE_DAT EMP_ID COMPANY_ID SOU TRANSCTION_I FILE_NAME STATUS DOC_NO
------ -------- ------------ ------------ --- ------------ -------------------------------------------------- --------------- --------------------
100001 04252012 100001*** 4252012 **1 :35:57***N 100001***04252012***10:35:57***NONUS
201441 _7076551 201441424 7076551 AAA 1234567899 201441424_7076551_AAA_1234567899.txt Completed 10085300090061
201441 _7076551 201441424 7076551 AAA 1234567899 201441424_7076551_AAA_1234567899.txt Completed 10085300090091
201441 _7076551 201441424 7076551 AAA 1234567899 201441424_7076551_AAA_1234567899.txt Completed 10085300090021
201441 _7076551 201441424 7076551 AAA 1234567899 201441424_7076551_AAA_1234567899.txt Completed 10085300090051
700331 _7078197 700331352 7078197 BBB 5678901234 700331352_7078197_BBB_5678901234.txt Inprogress
201441 _7075703 201441424 7075703 AAA 3456789134 201441424_7075703_AAA_3456789134.txt Incompleted
700223 _7077646 700223567 7077646 CCC 2345678912 700223567_7077646_CCC_2345678912.txt Completed 10085200080281
700223 _7077646 700223567 7077646 CCC 2345678912 700223567_7077646_CCC_2345678912.txt Completed 10085200080251
700223 _7077646 700223567 7077646 CCC 2345678912 700223567_7077646_CCC_2345678912.txt Completed 10085200080111
201442 _7075775 201442669 7075775 BBB 5432167891 201442669_7075775_BBB_5432167891.txt completed 10085200080111
11 rows selected.
|
|
|
|
Goto Forum:
Current Time: Sat Sep 28 20:02:31 CDT 2024
|