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



[CODE=oracle]
DECLARE
start_date VARCHAR2(20);
end_date VARCHAR2(20);
filename VARCHAR2(1000);
input_file utl_file.file_t ype;
input_buffer INTEGER;
vNewLine VARCHAR2(1000);
BEGIN
input_file := utl_file.fopen ('/tmp','ADDER_GEN EVA_REVENUE_REQ UEST_2310200712 1005. dat', 'R');
utl_file.get_li ne(input_file,v NewLine);
LOOP
EXIT WHEN LENGTH(vNewLine ) <= 0);
dbms_output.put _line(vNewLine) ;
IF(LENGTH(vNewL ine) > 13) THEN
start_date := SUBSTR(vNewLine ,5,8);
end_date:= SUBSTR(vNewLine ,14,8);
INSERT INTO am_test VALUES(start_da te,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;
[/CODE]

[amitpatel66]

Try this:

[code=oracle]
DECLARE
start_date VARCHAR2(20);
end_date VARCHAR2(20);
filename VARCHAR2(1000);
input_file utl_file.file_t ype;
input_buffer INTEGER;
vNewLine VARCHAR2(1000);
BEGIN
input_file := utl_file.fopen ('/tmp','ADDER_GEN EVA_REVENUE_REQ UEST_2310200712 1005. dat', 'R');
utl_file.get_li ne(input_file,v NewLine);
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_da te,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;
[/code]

[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]

[code=oracle]

DECLARE
start_date VARCHAR2(20);
end_date VARCHAR2(20);
acct_num VARCHAR2(20);
filename VARCHAR2(1000);
input_file utl_file.file_t ype;
input_buffer INTEGER;
vNewLine VARCHAR2(1000);
BEGIN
input_file := utl_file.fopen ('/tmp','ADDER_GEN EVA_REVENUE_REQ UEST_2310200712 1005. dat', 'R');
utl_file.get_li ne(input_file,v NewLine);
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(vNewLin e,'|',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_da te,end_date,acc t_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;

[/code]

[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:

[CODE=oracle]

declare
vl_c_vNewLine varchar2 (1000):='001|R| 21152007|231020 07|GP11223344|B |LION';
v_in varchar2(1000);
begin
select SUBSTR(vl_c_vNe wLine,INSTR(vl_ c_vNewLine,'|', 1,4) + 1,(INSTR(vl_c_v NewLine,'|',1,5 ) - INSTR(vl_c_vNew Line,'|',1,4))-1)
into v_in from dual ;
dbms_output.put _line(v_in);
end ;
[/CODE]

[orajit]

Pipe delimitating function

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.

[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]

[code=oracle]

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_t ype;
input_buffer INTEGER;
vNewLine VARCHAR2(1000);
BEGIN
input_file := utl_file.fopen ('/tmp','ADDER_GEN EVA_REVENUE_REQ UEST_2310200712 1005. dat', 'R');
utl_file.get_li ne(input_file,v NewLine);
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_vNe wLine,INSTR(vl_ c_vNewLine,'|', 1,4) + 1,(INSTR(vl_c_v NewLine,'|',1,5 ) - INSTR(vl_c_vNew Line,'|',1,4))-1);
flag:= SUBSTR(vl_c_vNe wLine,INSTR(vl_ c_vNewLine,'|', 1,5) + 1,(INSTR(vl_c_v NewLine,'|',1,6 ) - INSTR(vl_c_vNew Line,'|',1,5))-1);
prod:= SUBSTR(vl_c_vNe wLine,INSTR(vl_ c_vNewLine,'|', 1,6) + 1,(INSTR(vl_c_v NewLine,'|',1,7 ) - INSTR(vl_c_vNew Line,'|',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_da te,end_date,acc t_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;
[/code]