Home » SQL & PL/SQL » SQL & PL/SQL » text file generation
text file generation [message #279768] Sat, 10 November 2007 03:39 Go to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
I have a requirement.
I have to generate text file such that it contains data in a specified format.
I have a table A that i am using in my cursor ie cursor c is select ... from A;
I have to extract data from A and put them in text files.

I can create two tables and put data in them and generate text from that but i want to directly put the data in the text without creating any other tables.
the format for text data is :

10:20:30:10-may-07..etc

Thanks
Re: text file generation [message #279773 is a reply to message #279768] Sat, 10 November 2007 03:44 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Check the UTL_FILE package.
Re: text file generation [message #279785 is a reply to message #279768] Sat, 10 November 2007 04:23 Go to previous messageGo to next message
pablolee
Messages: 2835
Registered: May 2007
Location: Scotland
Senior Member
look up 'spool'
Re: text file generation [message #279810 is a reply to message #279768] Sat, 10 November 2007 13:12 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
create or replace procedure names_to_file
 is
 fileid UTL_FILE.FILE_TYPE;
 V_SYS NUMBER;
 V_AMT NUMBER;
 CNT NUMBER;
 cursor c is (select  hcode,
 dense_rank() over( order by hcode)x,amt,EDATE from a );
 begin
 FILEID:= UTL_FILE.FOPEN('txtfile','UTLFILE.TXT','W');
 execute immediate 'truncate table b';
 execute immediate 'truncate table c';
 v_sys :=1 ;
 v_amt :=0 ;
 cnt:=1;
 for var in c loop
 if cnt=var.x then
 UTL_FILE.PUT_LINE(
  FILEID,
  VAR.X||':'||VAR.HCODE);
  cnt:=cnt+1;
  end if;
 END LOOP;
 UTL_FILE.FCLOSE(FILEID);
 END;
SQL> /

Procedure created.

SQL> exec names_to_file;
BEGIN names_to_file; END;

*
ERROR at line 1:
ORA-29280: invalid directory path
ORA-06512: at "SYS.UTL_FILE", line 18
ORA-06512: at "SYS.UTL_FILE", line 424
ORA-06512: at "SYS.NAMES_TO_FILE", line 10
ORA-06512: at line 1


I have created a directory txtfile.
Re: text file generation [message #279811 is a reply to message #279810] Sat, 10 November 2007 13:16 Go to previous messageGo to next message
pablolee
Messages: 2835
Registered: May 2007
Location: Scotland
Senior Member
Nope, you have created a directory 'TXTFILE'
Change the directory name to uppercase
Re: text file generation [message #279812 is a reply to message #279768] Sat, 10 November 2007 13:20 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
A matter of upper and lower case...
But did not create any utl_file_dir parameter in initfile and it worked without that??
Re: text file generation [message #279816 is a reply to message #279812] Sat, 10 November 2007 13:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It works with Oracle directory object.
utl_file_dir parameter is depreciated.

Regards
Michel
Re: text file generation [message #279869 is a reply to message #279768] Sun, 11 November 2007 04:46 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
Oracle 9.2.0.2 and XP

CREATE OR REPLACE PROCEDURE CHAL_LAN IS
FILEID UTL_FILE.FILE_TYPE;
FILEID1 UTL_FILE.FILE_TYPE;
FILEID2 UTL_FILE.FILE_TYPE;
tcode varchar2(4);
id number;
ch_no varchar2(7);
mhead varchar2(4);
gr_amt varchar2(100);
mjhead varchar2(4):=1111;
entdate date;
dt date;
cnt number;
cursor c is select trea_code,op_code,chal_no,chal_date,depname,address
,h_code,col_code,div_code,chal_amt,src_mjcd,rem,ent_date,
ent_time,reg_no,purpose,rec_per,pr_amt,int_amt,ins_no,slr_no
,pay_type,dense_rank()over(order by substr(h_code,1,4))sysid
from rec_cha;
begin
id:=1;
gr_amt:=0;
cnt:=1;
FILEID :=UTL_FILE.FOPEN('CHAFILE','CHR.TXT','W');
FILEID1:= UTL_FILE.FOPEN('CHAFILE1','CHR1.TXT','W');
FILEID2:= UTL_FILE.FOPEN('CHAFILE2','CHR2.TXT','W');
for var in c
loop
tcode:=(substr(var.trea_code,1,2));
mhead:= substr(var.h_code,1,4);
if cnt=var.sysid then
UTL_FILE.PUT_LINE(FILEID,var.sysid||':'||tcode||':'||mhead||':'||var.ent_date||':'||var.ent_dat
':'||user||':'||var.ent_date||':'||0||':'||' '||':'||' '||':'||'X'||':'||
' '||':'||SYSDATE);
cnt:=cnt+1;
end if;
if (id=var.sysid) then
gr_amt:=gr_amt+var.chal_amt;
else
UTL_FILE.PUT_LINE(FILEID1,id||':'||var.chal_no||':'||'SCTN'||':'||'BOOK'||':'||'CHTYP'||':'||' 
var.chal_date||':'||'N'||':'||'X'||':'||'X'||':'||'N'||':'||
'N'||':'||1||':'||'E'||':'||USER||':'||VAR.ENT_DATE||':'||' '||':'||'E'||':'||' '||':'||0||':'|
'||':'||'N'||':'||'B'||':'||' '||':'||
gr_amt||':'||gr_amt||':'||0||':'||' '||':'||'C'||':'||'G'||':'||' '||':'||'P'||':'||'
'||':'||user||':'||VAR.ent_date||':'||' ');
gr_amt:=var.chal_amt;
id:=var.sysid;
entdate:=var.ent_date;
dt:=var.chal_date;
ch_no:=var.chal_no;
end if;
UTL_FILE.PUT_LINE(FILEID2,var.chal_no||':'||var.sysid||':'||substr(var.h_code,1,9)||'
'||substr(var.h_code,10,2)
||' '||substr(var.h_code,14,2)||':'||' '||':'|| var.chal_amt||':'||'O'||':'||' '||':'||
SYSDATE||':'||' '||':'||' '||':'||' '||':'||' '||':'||
' '||':'||SYSDATE||':'||' ');
end loop;
UTL_FILE.PUT_LINE(FILEID1,id||':'||ch_no||':'||'SCTN'||':'||'BOOK'||':'||'CHTYP'||':'||' '||':'
dt||':'||'N'||':'||'X'||':'||'X'||':'||'N'||':'||
'N'||':'||1||':'||'E'||':'||USER||':'||ENTDATE||':'||' '||':'||'E'||':'||' '||':'||0||':'||'
'||':'||'N'||':'||'B'||':'||' '||':'||
gr_amt||':'||gr_amt||':'||0||':'||' '||':'||'C'||':'||'G'||':'||' '||':'||'P'||':'||'
'||':'||user||':'||dt||':'||' ');
UTL_FILE.FCLOSE(FILEID);
UTL_FILE.FCLOSE(FILEID1);
UTL_FILE.FCLOSE(FILEID2);
COMMIT;
end;
/
Procedure created.

SQL> exec chal_lan;
BEGIN chal_lan; END;

*
ERROR at line 1:
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 449
ORA-29283: invalid file operation
ORA-06512: at "SYS.CHAL_LAN", line 23
ORA-06512: at line 1


I have created the three directories CHAFILE,CHAFILE1,CHAFILE2 and the DBA_DIRECTORIES view shows them created with the appropriate owner and location.
Re: text file generation [message #279872 is a reply to message #279869] Sun, 11 November 2007 05:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't see any line 23.

Regards
Michel
Re: text file generation [message #279873 is a reply to message #279768] Sun, 11 November 2007 05:41 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
1  CREATE OR REPLACE PROCEDURE CHAL_LAN IS
 2  FILEID UTL_FILE.FILE_TYPE;
 3  FILEID1 UTL_FILE.FILE_TYPE;
 4  FILEID2 UTL_FILE.FILE_TYPE;
 5  tcode varchar2(4);
 6     id number;
 7     ch_no varchar2(7);
 8     mhead varchar2(4);
 9     gr_amt varchar2(100);
10     mjhead varchar2(4):=1111;
11     entdate date;
12     dt date;
13     cnt number;
14   cursor c is select trea_code,op_code,chal_no,chal_date,depname,address
15   ,h_code,col_code,div_code,chal_amt,src_mjcd,rem,ent_date,
16   ent_time,reg_no,purpose,rec_per,pr_amt,int_amt,ins_no,slr_no
17   ,pay_type,dense_rank()over(order by substr(h_code,1,4))sysid
18   from rec_cha;
19  begin
20  id:=1;
21  gr_amt:=0;
22  cnt:=1;
23  FILEID :=UTL_FILE.FOPEN('CHAFILE','CHR.TXT','W');
24  FILEID1:= UTL_FILE.FOPEN('CHAFILE1','CHR1.TXT','W');
25  FILEID2:= UTL_FILE.FOPEN('CHAFILE2','CHR2.TXT','W');
26  for var in c
27  loop
28  tcode:=(substr(var.trea_code,1,2));
29   mhead:= substr(var.h_code,1,4);
30  if cnt=var.sysid then
31  UTL_FILE.PUT_LINE(FILEID,var.sysid||':'||tcode||':'||mhead||':'||var.ent_date||':'||var.ent_dat
32  ':'||user||':'||var.ent_date||':'||0||':'||' '||':'||' '||':'||'X'||':'||
33  ' '||':'||SYSDATE);
34  cnt:=cnt+1;
35  end if;
36   if (id=var.sysid) then
37   gr_amt:=gr_amt+var.chal_amt;
38    else
39  UTL_FILE.PUT_LINE(FILEID1,id||':'||var.chal_no||':'||'SCTN'||':'||'BOOK'||':'||'CHTYP'||':'||' 
40  var.chal_date||':'||'N'||':'||'X'||':'||'X'||':'||'N'||':'||
41  'N'||':'||1||':'||'E'||':'||USER||':'||VAR.ENT_DATE||':'||' '||':'||'E'||':'||' '||':'||0||':'|
42  '||':'||'N'||':'||'B'||':'||' '||':'||
43  gr_amt||':'||gr_amt||':'||0||':'||' '||':'||'C'||':'||'G'||':'||' '||':'||'P'||':'||'
44  '||':'||user||':'||VAR.ent_date||':'||' ');
45  gr_amt:=var.chal_amt;
46  id:=var.sysid;
47  entdate:=var.ent_date;
48  dt:=var.chal_date;
49  ch_no:=var.chal_no;
50  end if;
51   UTL_FILE.PUT_LINE(FILEID2,var.chal_no||':'||var.sysid||':'||substr(var.h_code,1,9)||'
52  '||substr(var.h_code,10,2)
53  ||'  '||substr(var.h_code,14,2)||':'||' '||':'|| var.chal_amt||':'||'O'||':'||' '||':'||
54  SYSDATE||':'||' '||':'||' '||':'||' '||':'||' '||':'||
55  ' '||':'||SYSDATE||':'||' ');
56     end loop;
57  UTL_FILE.PUT_LINE(FILEID1,id||':'||ch_no||':'||'SCTN'||':'||'BOOK'||':'||'CHTYP'||':'||' '||':'
58  dt||':'||'N'||':'||'X'||':'||'X'||':'||'N'||':'||
59  'N'||':'||1||':'||'E'||':'||USER||':'||ENTDATE||':'||' '||':'||'E'||':'||' '||':'||0||':'||'
60  '||':'||'N'||':'||'B'||':'||' '||':'||
61  gr_amt||':'||gr_amt||':'||0||':'||' '||':'||'C'||':'||'G'||':'||' '||':'||'P'||':'||'
62  '||':'||user||':'||dt||':'||' ');
63  UTL_FILE.FCLOSE(FILEID);
64  UTL_FILE.FCLOSE(FILEID1);
65  UTL_FILE.FCLOSE(FILEID2);
66  COMMIT;
67* end;
Re: text file generation [message #279874 is a reply to message #279873] Sun, 11 November 2007 06:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ORA-29283: invalid file operation
 *Cause:  An attempt was made to read from a file or directory that does
          not exist, or file or directory access was denied by the
          operating system.
 *Action: Verify file and directory access privileges on the file system,
          and if reading, verify that the file exists.

Regards
Michel
Re: text file generation [message #279876 is a reply to message #279768] Sun, 11 November 2007 06:32 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
I have checked in dba_directories view. I created the directories and procedures as sysdba.
In the post if you look,the first example worked.
I am doing the same way except this time the code generate three text files.
Re: text file generation [message #279880 is a reply to message #279768] Sun, 11 November 2007 07:04 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
Arrght problem solved.
How can i generate a pdf file?Is there any UTL_PDF package or something like that?
Re: text file generation [message #279888 is a reply to message #279880] Sun, 11 November 2007 08:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

Arrght problem solved.

How? You don't want to tell us?

Quote:

How can i generate a pdf file?

Use UTL_FILE.PUT_RAW.

Regards
Michel
Re: text file generation [message #279900 is a reply to message #279768] Sun, 11 November 2007 09:32 Go to previous message
varu123
Messages: 754
Registered: October 2007
Senior Member
Don't know.
I tried once again and it worked. Surprised
Previous Topic: Dependency check
Next Topic: How to Bulk load an XML file content into the DB
Goto Forum:
  


Current Time: Thu Dec 08 20:26:40 CST 2016

Total time taken to generate the page: 0.14396 seconds