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 Go to next message
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 Go to previous messageGo to next message
Littlefoot
Messages: 21530
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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8919
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.

Re: How to get the proper value in external table [message #553455 is a reply to message #553454] Fri, 04 May 2012 12:26 Go to previous message
Littlefoot
Messages: 21530
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Barbara
You can combine positional and delimited field declarations

Obviously, you can. Thank you, I didn't know that.
Previous Topic: impdp ORA-39029
Next Topic: Skip records on condition SQL Loader
Goto Forum:
  


Current Time: Mon Jan 27 18:08:14 CST 2020