Pipe delimitating function

[orajit]

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

000|23102007
001|21102007|23102007|GB11223344
001|20102007|22102007|GB11223345
001|19102007|22102007|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(vNewLine) > 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|GD112112111|B|LIss
001|R|20102007|22102007|GD112|B|WOss
001|R|19102007|22102007|GD113334444223346||WOss
001|R|19102007|22102007|GD111||WOss
001|R|19102007|22102007|GD113344||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_vNewLine,INSTR(vl_c_vNewLine,'|',1,3) + 1,INSTR(vl_c_vNewLine,'|',1,4)-1)

Then it will give me output as 23102007|GP11223344|B|L

I wanted only GP11223344..could 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|GP11223344|B|LION
001|R|20102007|22102007|GP11223345|B|WOMBAT
001|R|19102007|22102007|GP11223346||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.

[orajit]

No.. i dont want to use sql loader I have tried with external table also but i am facing problem with header and footer . I dont want header and footer containt in my table .
I want only highligted text to be inserted into my table .

My table has following columns
st date
end date
acc_no
flag
prod

000|23102007---header
001|R||21102007 |23102007 |GP11223344 |B |LION
999|1--footer

could u please tell me how to do this .. Is there any method by using external table ..

Thanks

[amitpatel66]

No.. i dont want to use sql loader I have tried with external table also but i am facing problem with header and footer . I dont want header and footer containt in my table .
I want only highligted text to be inserted into my table .

My table has following columns
st date
end date
acc_no
flag
prod

000|23102007---header
001|R||21102007 |23102007 |GP11223344 |B |LION
999|1--footer

could u please tell me how to do this .. Is there any method by using external table ..

Thanks

I think I had given you the solution in your previous thread for this requirement. You just need to modify that slightly to store the value of last two columns flag and prod.

[amitpatel66]

Threads merged for better management of forum

MODERATOR

[orajit]

can U please send it for flag and product ....

[amitpatel66]

 
DECLARE
 start_date VARCHAR2(20):= NULL;
 end_date VARCHAR2(20):= NULL;
 acct_num VARCHAR2(20):= NULL;
 flag VARCHAR2(2) := NULL;
 prod VARCHAR2(20) := NULL;
 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(vl_c_vNewLine,INSTR(vl_c_vNewLine,'|',1,4) + 1,(INSTR(vl_c_vNewLine,'|',1,5) - INSTR(vl_c_vNewLine,'|',1,4))-1);
         flag:= SUBSTR(vl_c_vNewLine,INSTR(vl_c_vNewLine,'|',1,5) + 1,(INSTR(vl_c_vNewLine,'|',1,6) - INSTR(vl_c_vNewLine,'|',1,5))-1);
         prod:= SUBSTR(vl_c_vNewLine,INSTR(vl_c_vNewLine,'|',1,6) + 1,(INSTR(vl_c_vNewLine,'|',1,7) - INSTR(vl_c_vNewLine,'|',1,6))-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,flag,prod);
          COMMIT;
         --END IF;
         vNewLine:= NULL;
        start_date:= NULL;
        end_date:= NULL;
        acct_num:= NULL;
        flag:= NULL;
        prod:= NULL;
         END LOOP;
      EXCEPTION
         WHEN OTHERS THEN
                 utl_file.fclose(input_file);
      END;
 

[orajit]

Thank You very much ..I am ending the discussion for UTL_FILE :)

Thanks for your help for all ..

[amitpatel66]

Thank You very much ..I am ending the discussion for UTL_FILE :)

Thanks for your help for all ..

You are welcome any time :)

MODERATOR