Pipe delimitating function

[orajit]

I have created one file
c;/temp/test.txt

000|23102007
001|21102007|231020 07|GB11223344
001|20102007|221020 07|GB11223345
001|19102007|221020 07|GB11223346
999|3

The highlighted text are started date and end date and account number .

Suppose I wanted to insert the date into am_test the from the test.txt by using following code

I dont wanted to use hardcoded values in my code means
IF(LENGTH(vNewL ine) > 13) THEN ... like this

I wanted to insert '|' seperated values in my table .


cud u please tell me how to to it .

Thnaks

DECLARE
 start_date VARCHAR2(20);
 end_date VARCHAR2(20);
 filename    VARCHAR2(1000);
 input_file   utl_file.file_type;
 input_buffer INTEGER;
 vNewLine  VARCHAR2(1000);
 BEGIN
       input_file := utl_file.fopen ('/tmp','ADDER_GENEVA_REVENUE_REQUEST_23102007121005.    dat', 'R');
       utl_file.get_line(input_file,vNewLine);
         LOOP
         EXIT WHEN LENGTH(vNewLine) <= 0);
         dbms_output.put_line(vNewLine);
         IF(LENGTH(vNewLine) > 13) THEN          
         start_date := SUBSTR(vNewLine,5,8);
         end_date:= SUBSTR(vNewLine,14,8);
          INSERT INTO  am_test VALUES(start_date,end_date);
          COMMIT;
         END IF;
         vNewLine:= NULL;
        start_date:= NULL;
        end_date:= NULL;
         END LOOP;
      EXCEPTION
         WHEN OTHERS THEN
                 utl_file.fclose(input_file);
      END;
 

[amitpatel66]

Try this:

DECLARE
 start_date VARCHAR2(20);
 end_date VARCHAR2(20);
 filename    VARCHAR2(1000);
 input_file   utl_file.file_type;
 input_buffer INTEGER;
 vNewLine  VARCHAR2(1000);
 BEGIN
       input_file := utl_file.fopen ('/tmp','ADDER_GENEVA_REVENUE_REQUEST_23102007121005.    dat', 'R');
       utl_file.get_line(input_file,vNewLine);
         LOOP
         EXIT WHEN LENGTH(vNewLine) <= 0);
         dbms_output.put_line(vNewLine);
         start_date := SUBSTR(vNewLine,INSTR(vNewLine,'|',1,1) + 1, 8);
         end_date:= SUBSTR(vNewLine,INSTR(vNewLine,'|',1,2) + 1,8);
          IF(start_date IS NOT NULL AND end_Date IS NOT NULL) THEN
          INSERT INTO  am_test VALUES(start_date,end_date);
          COMMIT;
         END IF;
         vNewLine:= NULL;
        start_date:= NULL;
        end_date:= NULL;
         END LOOP;
      EXCEPTION
         WHEN OTHERS THEN
                 utl_file.fclose(input_file);
      END;
 

[orajit]

Thanks for ur solution .. its working ...:)

[amitpatel66]

Thanks for ur solution .. its working ...:)

You are welcome!! :)

I would like you to go through the POSTING GUIDELINES which would help you understand how to make use of CODE TAGS and many other forum guidelines which needs to be followed while posting in this forum!!

MODERATOR

[orajit]

Re: utl_file
--------------------------------------------------------------------------------

if i have my txt file as shown below ...


000|23102007
001|R|21102007| 23102007|GD1121 12111|B|LIss
001|R|20102007| 22102007|GD112| B|WOss
001|R|19102007| 22102007|GD1133 34444223346||WO ss
001|R|19102007| 22102007|GD111| |WOss
001|R|19102007| 22102007|GD1133 44||WOss
999|5


Now i wanted to read and insert account number using above code ... The account number is shown as bold (GD133331223344 ,GD112 etc)

Could u please advice me how to do it.

[amitpatel66]

 
DECLARE
 start_date VARCHAR2(20);
 end_date VARCHAR2(20);
 acct_num VARCHAR2(20);
 filename    VARCHAR2(1000);
 input_file   utl_file.file_type;
 input_buffer INTEGER;
 vNewLine  VARCHAR2(1000);
 BEGIN
       input_file := utl_file.fopen ('/tmp','ADDER_GENEVA_REVENUE_REQUEST_23102007121005.      dat', 'R');
       utl_file.get_line(input_file,vNewLine);
         LOOP
         EXIT WHEN LENGTH(vNewLine) <= 0);
         dbms_output.put_line(vNewLine);
         start_date := SUBSTR(vNewLine,INSTR(vNewLine,'|',1,1) + 1, 8);
         end_date:= SUBSTR(vNewLine,INSTR(vNewLine,'|',1,2) + 1,8);
         acct_num:= SUBSTR(vNewLine,INSTR(vNewLine,'|',1,3) + 1,INSTR(vNewLine,'|',1,4)-1);
          IF(start_date IS NOT NULL AND end_Date IS NOT NULL AND acct_num IS NOT NULL) THEN
          INSERT INTO  am_test VALUES(start_date,end_date,acct_num);
          COMMIT;
         END IF;
         vNewLine:= NULL;
        start_date:= NULL;
        end_date:= NULL;
        acct_num:= NULL;
         END LOOP;
      EXCEPTION
         WHEN OTHERS THEN
                 utl_file.fclose(input_file);
      END;
 
 

[orajit]

If use ur code for account number
SUBSTR(vl_c_vNe wLine,INSTR(vl_ c_vNewLine,'|', 1,3) + 1,INSTR(vl_c_vN ewLine,'|',1,4)-1)

Then it will give me output as 23102007|GP1122 3344|B|L

I wanted only GP11223344..cou ld u plz tell me how to find it

..
declare 
      vl_c_vNewLine varchar2  (1000):='001|R|21152007|23102007|GP11223344|B|LION';
      v_in varchar2(1000);
begin 
      select SUBSTR(vl_c_vNewLine,INSTR(vl_c_vNewLine,'|',1,3) + 1,INSTR(vl_c_vNewLine,'|',1,4)-1)
      into v_in from dual ;
      dbms_output.put_line(v_in);
end ;
 

[amitpatel66]

Try this:

 
declare 
      vl_c_vNewLine varchar2  (1000):='001|R|21152007|23102007|GP11223344|B|LION';
      v_in varchar2(1000);
begin 
      select SUBSTR(vl_c_vNewLine,INSTR(vl_c_vNewLine,'|',1,4) + 1,(INSTR(vl_c_vNewLine,'|',1,5) - INSTR(vl_c_vNewLine,'|',1,4))-1)
      into v_in from dual ;
      dbms_output.put_line(v_in);
end ;
 

[orajit]

First of all Thanks for your valuable solution and time for my question related to UTL_FILE.

Sir, I am facing one problem . I wanted to build a procedure that will give me Pipe delimitating values and insert it in the table .

I am giving you the same example .suppose I have text file called pile.txt and that contains following info

000|23102007 ----header
001|R|21102007| 23102007|GP1122 3344|B|LION
001|R|20102007| 22102007|GP1122 3345|B|WOMBAT
001|R|19102007| 22102007|GP1122 3346||WOMBAT
999|3 ---footer

Now I wanted to store each pipe separated value including null in a variable and insert into the table (except header and footer).

Could you Please suggest and function that will give me '|' (pipe) separated values .
Please advice .. Looking for your valuable advice once again ,,,

Thanks

[debasisdas]

Why don't you use SQL * Loader.