Home » SQL & PL/SQL » SQL & PL/SQL » UTL_FILE problem (merged)
UTL_FILE problem (merged) [message #233482] Thu, 26 April 2007 05:46 Go to next message
rajareddy_24
Messages: 23
Registered: April 2007
Location: bangalore
Junior Member

I want to write data to text file using PL/SQL procedure.I executed commands as below.
SQL> connect system@cld1
Enter password: ******
Connected.
SQL> create directory utl_file_dir as 'c:\temp\utl_file';

Directory created.

SQL> grant write,read on directory utl_file_dir to lriuser;

Grant succeeded.

SQL> grant write,read on directory utl_file_dir to anauser;

Grant succeeded.

SQL> connect lriuser/lriuser@cld1
Connected.
SQL> set serveroutput on
SQL> ed
Wrote file afiedt.buf

1 CREATE OR REPLACE PROCEDURE Empflatfile
2 IS
3 file_id UTL_FILE.FILE_TYPE;
4 BEGIN
5 file_id := utl_file.FOPEN( 'UTL_FILE_DIR', 'test.txt', 'w' );
6 FOR TLTA_NOTICES IN (SELECT NOTICE_TYPE FROM TLTA_NOTICES)
7 LOOP
8 utl_file.PUT_LINE( file_id, TLTA_NOTICES.NOTICE_TYPE );
9 END LOOP;
10 utl_file.fCLOSE(file_id);
11 EXCEPTION
12 WHEN utl_file.invalid_path THEN
13 RAISE_APPLICATION_ERROR(-20001, 'utl_file.invalid_path');
14 WHEN utl_file.invalid_mode THEN
15 RAISE_APPLICATION_ERROR(-20001, 'utl_file.invalid_mode');
16 WHEN utl_file.invalid_filehandle THEN
17 RAISE_APPLICATION_ERROR(-20001,'utl_file.invalid_filehandle');
18 WHEN utl_file.invalid_operation THEN
19 RAISE_APPLICATION_ERROR(-20001,'utl_file.invalid_operation');
20 WHEN utl_file.read_error THEN
21 RAISE_APPLICATION_ERROR(-20001, 'utl_file.read_error');
22 WHEN utl_file.write_error THEN
23 RAISE_APPLICATION_ERROR(-20001, 'utl_file.write_error');
24 WHEN utl_file.internal_error THEN
25 RAISE_APPLICATION_ERROR(-20001, 'utl_file.internal_error');
26 WHEN OTHERS THEN
27 RAISE_APPLICATION_ERROR(-20001, 'utl_file.other_error');
28* END;
29 /

Procedure created.

SQL> execute Empflatfile;
BEGIN Empflatfile; END;

*
ERROR at line 1:
ORA-20001: utl_file.invalid_operation
ORA-06512: at "LRIUSER.EMPFLATFILE", line 19
ORA-06512: at line 1

Why I am getting above error?
Re: UTL_FILE error [message #233485 is a reply to message #233482] Thu, 26 April 2007 06:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The error is to put an exception block that prevent us from knowing if the error comes from the fopen, put_line or fclose function.

Regards
Michel
Re: UTL_FILE error [message #233487 is a reply to message #233485] Thu, 26 April 2007 06:15 Go to previous messageGo to next message
rajareddy_24
Messages: 23
Registered: April 2007
Location: bangalore
Junior Member
I didnt understand what you said.can you please tell me clearly


Re: UTL_FILE error [message #233489 is a reply to message #233487] Thu, 26 April 2007 06:33 Go to previous messageGo to next message
rajareddy_24
Messages: 23
Registered: April 2007
Location: bangalore
Junior Member
I removed exception block.When I am executing below procedures,I am getting following errors

SQL> ed
Wrote file afiedt.buf

1 CREATE OR REPLACE PROCEDURE Empflatfile
2 IS
3 file_id UTL_FILE.FILE_TYPE;
4 BEGIN
5 file_id := utl_file.FOPEN( 'UTL_FILE_DIR', 'test.txt', 'w' );
6 FOR TLTA_NOTICES IN (SELECT NOTICE_TYPE FROM TLTA_NOTICES)
7 LOOP
8 utl_file.PUT_LINE( file_id, TLTA_NOTICES.NOTICE_TYPE );
9 END LOOP;
10 utl_file.fCLOSE(file_id);
11* END;
SQL> /

Procedure created.

SQL> execute Empflatfile;
BEGIN Empflatfile; 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 "LRIUSER.EMPFLATFILE", line 5
ORA-06512: at line 1
Re: UTL_FILE error [message #233490 is a reply to message #233489] Thu, 26 April 2007 06:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Does your oracle instance owner (local system I think, check you Windows service) has write access to "c:\temp\utl_file" directory?

Regards
Michel
Re: UTL_FILE error [message #233500 is a reply to message #233490] Thu, 26 April 2007 07:10 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
c:\temp\utl_file is a directory on the database SERVER, right? Not (only) on your client?
Re: UTL_FILE error [message #233501 is a reply to message #233500] Thu, 26 April 2007 07:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes Frank, forgot to mention that.
Always start with the basis. Smile

Regard
Michel
Re: UTL_FILE error [message #233505 is a reply to message #233501] Thu, 26 April 2007 07:35 Go to previous messageGo to next message
rajareddy_24
Messages: 23
Registered: April 2007
Location: bangalore
Junior Member
c:\temp\utl_file is on oracle client windows machines.Oracle database server is running on UNIX server. what can I do?
Re: UTL_FILE error [message #233507 is a reply to message #233505] Thu, 26 April 2007 07:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Your procedure can only see files on SERVER.
Create your file on server and transfer it on client afterwards.

Regards
Michel
Re: UTL_FILE error [message #233520 is a reply to message #233507] Thu, 26 April 2007 08:06 Go to previous messageGo to next message
rajareddy_24
Messages: 23
Registered: April 2007
Location: bangalore
Junior Member
how can i tranfer that file from server to client.
Re: UTL_FILE error [message #233525 is a reply to message #233520] Thu, 26 April 2007 08:08 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
ftp, webDAV, whatever.
Not Oracle related.
Re: UTL_FILE error [message #233526 is a reply to message #233520] Thu, 26 April 2007 08:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ftp.

Regards
Michel
UTL_FILE problem [message #233634 is a reply to message #233482] Fri, 27 April 2007 01:00 Go to previous messageGo to next message
rajareddy_24
Messages: 23
Registered: April 2007
Location: bangalore
Junior Member
have used following commands in sequence.

CREATE DIRECTORY utl_file_tmp AS '/tmp';
CREATE DIRECTORY utl_file_dir AS '/oracle/oracle9/admin/CLD1/utl_file';

SQL>GRANT READ ON DIRECTORY utl_file_tmp to lriuser;
SQL>GRANT WRITE ON DIRECTORY utl_file_tmp to lriuser;
SQL>GRANT READ ON DIRECTORY utl_file_dir to lriuser;
SQL>GRANT WRITE ON DIRECTORY utl_file_dir to lriuser;
Oracle Database is running on UNIX machine ,there I have given following command
cmdb> mkdir utl_file
cmdb> ls -l
drwxr-xr-x 2 cmdb dba 96 Sep 6 13:11 utl_file


SQL> ed
Wrote file afiedt.buf

1 CREATE OR REPLACE PROCEDURE EmployeeFlatFile
2 IS
3 file_id UTL_FILE.FILE_TYPE;
4 BEGIN
5 file_id := utl_file.FOPEN( '/temp', 'test.txt', 'w' );
6 FOR TLTA_GAZETTES IN (SELECT GAZETTE_NO FROM TLTA_GAZETTES)
7 LOOP
8 utl_file.PUT_LINE( file_id, TLTA_GAZETTES.GAZETTE_NO );
9 END LOOP;
10 utl_file.fCLOSE(file_id);
11 EXCEPTION
12 WHEN utl_file.invalid_path THEN
13 RAISE_APPLICATION_ERROR(-20001, 'utl_file.invalid_path');
14 WHEN utl_file.invalid_mode THEN
15 RAISE_APPLICATION_ERROR(-20001, 'utl_file.invalid_mode');
16 WHEN utl_file.invalid_filehandle THEN
17 RAISE_APPLICATION_ERROR(-20001, 'utl_file.invalid_filehandle');
18 WHEN utl_file.invalid_operation THEN
19 RAISE_APPLICATION_ERROR(-20001, 'utl_file.invalid_operation');
20 WHEN utl_file.read_error THEN
21 RAISE_APPLICATION_ERROR(-20001, 'utl_file.read_error');
22 WHEN utl_file.write_error THEN
23 RAISE_APPLICATION_ERROR(-20001, 'utl_file.write_error');
24 WHEN utl_file.internal_error THEN
25 RAISE_APPLICATION_ERROR(-20001, 'utl_file.internal_error');
26 WHEN OTHERS THEN
27 RAISE_APPLICATION_ERROR(-20001, 'utl_file.other_error');
28* END;
SQL> /

Procedure created.

SQL> execute EmployeeFlatFile;
BEGIN EmployeeFlatFile; END;

*
ERROR at line 1:
ORA-20001: utl_file.invalid_path
ORA-06512: at "LRIUSER.EMPLOYEEFLATFILE", line 13
ORA-06512: at line 1

Why I am getting Still above error?
Re: UTL_FILE problem [message #233639 is a reply to message #233634] Fri, 27 April 2007 01:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The answers are in your previous post (http://www.orafaq.com/forum/m/233482/102589/#msg_233482):
1/ Remove that bloody and useless exception block.
2/ Does your oracle instance owner has write access to the target directory?

Regards
Michel

How can I write table(all columns) to text file using UTL_FILE [message #233767 is a reply to message #233482] Fri, 27 April 2007 07:22 Go to previous messageGo to next message
rajareddy_24
Messages: 23
Registered: April 2007
Location: bangalore
Junior Member
This is my PL/SQL program.


CREATE OR REPLACE PROCEDURE EMPLOYEEFLATFILE
IS
FILE_ID UTL_FILE.FILE_TYPE;
CURRENT_DATE1 VARCHAR2(30);
CURRENT_DATE2 VARCHAR2(30);
CURRENT_DATE3 VARCHAR2(30);
HEADING VARCHAR2(100);
BEGIN
HEADING := 'Intereq- Outstanding LR Requests (LRs were not sent to agencies in next working day)';

SELECT TO_CHAR(SYSDATE,'yyyymmdd')
INTO CURRENT_DATE1
FROM DUAL;

SELECT TO_CHAR(SYSDATE,'dd/mm/yyyy')
INTO CURRENT_DATE2
FROM DUAL;

SELECT TO_CHAR(SYSDATE,'dy/mon/yyyy hh24:ss')
INTO CURRENT_DATE3
FROM DUAL;

FILE_ID := UTL_FILE.FOPEN('UTL_FILE_TMP','OutstandingLR-REQUEST-'
||CURRENT_DATE1
||'.txt','w');

UTL_FILE.PUT_LINE(FILE_ID,HEADING);

UTL_FILE.PUT_LINE(FILE_ID,CURRENT_DATE2);

FOR TLTA_GAZETTES IN (SELECT GAZETTE_NO
FROM TLTA_GAZETTES)
LOOP
UTL_FILE.PUT_LINE(FILE_ID,TLTA_GAZETTES.GAZETTE_NO);
END LOOP;

UTL_FILE.PUT_LINE(FILE_ID,CURRENT_DATE3);

UTL_FILE.FCLOSE(FILE_ID);
END;


I am using UTL_FILE to generate text file.
here I am using TLTA_GAZETTES table.
I want to write the output(of all columns of table) that comes when I execute" select * from TLTA_GAZETTES" .
I want to write output as it is in the output.

How can I do that.

Re: How can I write table(all columns) to text file using UTL_FILE [message #233769 is a reply to message #233767] Fri, 27 April 2007 07:24 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Pick your method from here
http://asktom.oracle.com/tkyte/flat/
Works real good.
Re: How can I write table(all columns) to text file using UTL_FILE [message #233895 is a reply to message #233769] Sat, 28 April 2007 00:23 Go to previous messageGo to next message
psix666
Messages: 51
Registered: April 2007
Location: Azerbaijan
Member

This method only can be used on the server using Command promt and SQL Loader.

There is another way to make it using pl/sql procedure.

You can generate select statement dynamically using names of columns as the following

begin
v_str := 'select ';

loop
  /* open cursor with names of columns of that table*/
  /*if you need flat file then you need to write some additional code to fill free spaces*/
  v_str := v_str ||column_name||'||';
end loop;
  /*add table name and execute query in ref cursor*/
  /*now you have flat strings and should just add them to file*/

end;


I appologize this will help you.

Thanks.

[Updated on: Sat, 28 April 2007 00:25]

Report message to a moderator

Re: UTL_FILE problem [message #233905 is a reply to message #233634] Sat, 28 April 2007 01:30 Go to previous messageGo to next message
psix666
Messages: 51
Registered: April 2007
Location: Azerbaijan
Member

Hi

ERROR at line 1:
ORA-20001: utl_file.invalid_path
ORA-06512: at "LRIUSER.EMPLOYEEFLATFILE", line 13
ORA-06512: at line 1


I find that the letters in the name of directory are case sensitive.

For example:
create or replace directory C_FILES as 'C:\C_FILES\';

and
create or replace directory C_FILES as 'C:\C_files\';

are not equals.
If you create directory on server names 'c_Files' both below declarations trow exception of invalid path.

Regards.
i need table format sepecified in attached file using UTL_FILE [message #233934 is a reply to message #233482] Sat, 28 April 2007 06:09 Go to previous messageGo to next message
rajareddy_24
Messages: 23
Registered: April 2007
Location: bangalore
Junior Member
Hi ,
I am able to write table data to MS Word file.I want table format like in the world file which I am attaching.

can anybody suggest what I need to do changes to PL/SQL Procedures code.

this is my PL/SQL code

CREATE OR REPLACE PROCEDURE EmployeeFlatFile
IS
File_Id utl_File.File_Type;
Current_Date1 VARCHAR2(30);
Current_Date2 VARCHAR2(30);
Current_Date3 VARCHAR2(30);
Heading VARCHAR2(100);
BEGIN
Heading := 'Intereq- Outstanding LR Requests (LRs were not sent to agencies in next working day)';

SELECT To_char(SYSDATE,'yyyymmdd')
INTO Current_Date1
FROM Dual;

SELECT To_char(SYSDATE,'dd/mm/yyyy')
INTO Current_Date2
FROM Dual;

SELECT To_char(SYSDATE,'dy mon yyyy hh24:ss am')
INTO Current_Date3
FROM Dual;

File_Id := utl_File.Fopen('UTL_FILE_TMP','OutstandingLR-REQUEST-'
||Current_Date1
||'.doc','w');

utl_File.Put_Line(File_Id,Heading);

utl_File.Put_Line(File_Id,Current_Date2);

utl_File.Put_Line(File_Id,' ');

utl_File.Put_Line(File_Id,'empno '
||'ename '
||'job '
||'mgr '
||'sal '
||'deptno ');

utl_File.Put_Line(File_Id,' ');

FOR emp IN (SELECT empNo,
eName,
Job,
mgr,
sal,
deptNo
FROM emp)
LOOP
utl_File.Put_Line(File_Id,emp.empNo
||' '
||emp.eName
||' '
||emp.Job
||' '
||emp.mgr
||' '
||emp.sal
||' '
||emp.deptNo);
END LOOP;

utl_File.Put_Line(File_Id,' ');

utl_File.Put_Line(File_Id,Current_Date3);

utl_File.fClose(File_Id);
EXCEPTION
WHEN utl_File.Invalid_Path THEN
Raise_Application_Error(- 20001,'utl_file.invalid_path');
WHEN utl_File.Invalid_Mode THEN
Raise_Application_Error(- 20001,'utl_file.invalid_mode');
WHEN utl_File.Invalid_FileHandle THEN
Raise_Application_Error(- 20001,'utl_file.invalid_filehandle');
WHEN utl_File.Invalid_Operation THEN
Raise_Application_Error(- 20001,'utl_file.invalid_operation');
WHEN utl_File.Read_Error THEN
Raise_Application_Error(- 20001,'utl_file.read_error');
WHEN utl_File.Write_Error THEN
Raise_Application_Error(- 20001,'utl_file.write_error');
WHEN utl_File.Internal_Error THEN
Raise_Application_Error(- 20001,'utl_file.internal_error');
WHEN OTHERS THEN
Raise_Application_Error(- 20001,'utl_file.other_error');
END;
/
  • Attachment: TABLE.doc
    (Size: 37.00KB, Downloaded 236 times)
Re: i need table format sepecified in attached file using UTL_FILE [message #233955 is a reply to message #233934] Sat, 28 April 2007 07:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ Read How to format your posts and edit your post

2/ For the third time: REMOVE THIS USELESS EXCEPTION BLOCK

Regards
Michel
Re: UTL_FILE problem (merged) [message #233961 is a reply to message #233482] Sat, 28 April 2007 08:42 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>both below declarations trow exception of invalid path.
Nitpick.
There was Nothing below this statement.
The reason both throw exception is because BOTH are in valid.
Please NOTE that "C:" is NOT part of a valid directory pathname; as far as Oracle is concerned.
Above is part of the penalty one pays to do Oracle on Windoze.

HTH & YMMV
Re: UTL_FILE problem (merged) [message #233983 is a reply to message #233961] Sat, 28 April 2007 13:07 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
@OP: you have opened 3 (three) topics about the UTL_FILE problem you have. Please, stop doing that - continue discussion in the original topic.
Re: UTL_FILE problem (merged) [message #234136 is a reply to message #233983] Mon, 30 April 2007 02:29 Go to previous messageGo to next message
rajareddy_24
Messages: 23
Registered: April 2007
Location: bangalore
Junior Member
This is my PL/SQL code.

CREATE OR REPLACE PROCEDURE EmployeeFlatFile
IS
File_Id utl_File.File_Type;
Current_Date1 VARCHAR2(30);
Current_Date2 VARCHAR2(30);
Current_Date3 VARCHAR2(30);
Heading VARCHAR2(100);
BEGIN
Heading := 'Intereq- Outstanding LR Requests (LRs were not sent to agencies in next working day)';

SELECT To_char(SYSDATE,'yyyymmdd')
INTO Current_Date1
FROM Dual;

SELECT To_char(SYSDATE,'dd/mm/yyyy')
INTO Current_Date2
FROM Dual;

SELECT To_char(SYSDATE,'dy mon yyyy hh24:ss')
INTO Current_Date3
FROM Dual;

File_Id := utl_File.Fopen('UTL_FILE_TMP','OutstandingLR-REQUEST-'
||Current_Date1
||'.txt','w');

utl_File.Put_Line(File_Id,Heading);

utl_File.Put_Line(File_Id,Current_Date2);

utl_File.Put_Line(File_Id,' ');

utl_File.Put_Line(File_Id,'|'
||'empno| '
||'|ename| '
||'|job| '
||'|mgr| '
||'|sal| '
||'|deptno| ');

utl_File.Put_Line(File_Id,' ');

FOR emp IN (SELECT empNo,
eName,
Job,
mgr,
sal,
deptNo
FROM emp)
LOOP
utl_File.Put_Line(File_Id,'---------------------------------------------------------------------------------------------------------- ---------');

utl_File.Put_Line(File_Id,'|'
||emp.empNo
||'| '
||'|'
||emp.eName
||'| '
||'|'
||emp.Job
||'| '
||'|'
||emp.mgr
||'| '
||'|'
||emp.sal
||'| '
||'|'
||emp.deptNo);
END LOOP;

utl_File.Put_Line(File_Id,' ');

utl_File.Put_Line(File_Id,Current_Date3);

utl_File.fClose(File_Id);
END;
/

I attached Table.doc file.In that you can see the output how I want.

Now I am getting output like following

Re: How can we generate table data in table format in text file using UTL_FILE
Posted: Apr 29, 2007 9:48 PM in response to: Satyaki_De Reply


I am getting output as follows

|empno| |ename| |job| |mgr| |sal| |deptno|

-------------------------------------------------------------------------------------------------------------------
|7369| |SMITH| |CLERK| |7902| |800| |20
-------------------------------------------------------------------------------------------------------------------
|7499| |ALLEN| |SALESMAN| |7698| |1600| |30
-------------------------------------------------------------------------------------------------------------------
|7521| |WARD| |SALESMAN| |7698| |1250| |30
-------------------------------------------------------------------------------------------------------------------
|7566| |JONES| |MANAGER| |7839| |2975| |20
-------------------------------------------------------------------------------------------------------------------
|7654| |MARTIN| |SALESMAN| |7698| |1250| |30
-------------------------------------------------------------------------------------------------------------------
|7698| |BLAKE| |MANAGER| |7839| |2850| |30
-------------------------------------------------------------------------------------------------------------------
|7782| |CLARK| |MANAGER| |7839| |2450| |10
-------------------------------------------------------------------------------------------------------------------
|7788| |SCOTT| |ANALYST| |7566| |3000| |20
-------------------------------------------------------------------------------------------------------------------
|7839| |KING| |PRESIDENT| || |5000| |10
-------------------------------------------------------------------------------------------------------------------
|7844| |TURNER| |SALESMAN| |7698| |1500| |30
-------------------------------------------------------------------------------------------------------------------
|7876| |ADAMS| |CLERK| |7788| |1100| |20
------------------ -------------------------------------------------------------------------------------------------
|7900| |JAMES| |CLERK| |7698| |950| |30
-------------------------------------------------------------------------------------------------------------------
|7902| |FORD| |ANALYST| |7566| |3000| |60
-------------------------------------------------------------------------------------------------------------------
|7934| |MILLER| |CLERK| |7782| |1300| |10

But I need output in table format.see sample output in below link
http://www.eskimo.com/~ericj/personal/postgresql/sql1b.htm

please help.Its very urgent



  • Attachment: TABLE.doc
    (Size: 37.00KB, Downloaded 154 times)
Re: UTL_FILE problem (merged) [message #234138 is a reply to message #234136] Mon, 30 April 2007 02:34 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
An interesting thread for you

If you are providing code snippets such as SQL*Plus copy/paste, please be sure to format your post. This means putting your code between the tags [code] and [/code]. Alternatively, you can just copy and paste your code in the topic, select the code and hit this button: http://www.orafaq.com/forum/theme/orafaq/images/b_code.gif. This will generate the tags for you.
It makes a huge difference to the forum readers. Unreadable code makes a question harder to understand and will delay answers. A lot of people don't spend that much time on the forum and unclear posts will be skipped more often because they take a lot more time to decypher.

The difference between the code blocks below is clear:

First a block without proper tags, you'll notice that it is not that readable
Declare
Cursor yourcursor
Is
Select yourcolumn
, another_column
From yourtable
Where some_column Between A And B
And some_other > SYSDATE;
Begin
For a_record In yourcursor
Loop
do_something_here;
If a_record.another_column = 1
Then
do_extra_stuff;
Else
do_other_stuff;
Else;
End Loop;
End;

And this block is the same, only now it is included in code tags. It distincts clearly the code from the other text and preserves text intending.
Declare
  Cursor yourcursor
      Is
  Select yourcolumn
       , another_column
    From yourtable
   Where some_column Between A And B
     And some_other  > SYSDATE;
Begin
  For a_record In yourcursor
  Loop
    do_something_here;
    If a_record.another_column  = 1
    Then
      do_extra_stuff;
    Else
      do_other_stuff;
    Else;
  End Loop;
End;


Scott Mackey (scottwmackey) has created a nice little document explaining in detail and with screencaps how to format. The document can be downloaded/viewed here.

A second point of attention is that you should provide your own table creation and insert script with sample data so others can easily recreate the table structure and provide you with an answer that is to the point.

Thanks to Scott for the document.

The moderator staff


READ IT.
READ IT AGAIN.
READ IT ONE MORE TIME.
APPLY THE TIPS.

MHE
Re: UTL_FILE problem (merged) [message #234149 is a reply to message #234136] Mon, 30 April 2007 03:34 Go to previous messageGo to next message
rajareddy_24
Messages: 23
Registered: April 2007
Location: bangalore
Junior Member
anybody help please.Its very urgent
Re: UTL_FILE problem (merged) [message #234159 is a reply to message #234149] Mon, 30 April 2007 04:19 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Ok, I gave you a chance. You have been asked to format your code properly. But you sir, are
a) ignoring other members
b) stubborn as hell
c) changing your requirement all the time.

Now, unless you format your code properly, as described in my previous reply, you will be stuck with your urgent requirement. If you have the same attitude to your colleagues I pity them.

MHE
Re: UTL_FILE problem (merged) [message #234161 is a reply to message #234159] Mon, 30 April 2007 04:30 Go to previous messageGo to next message
rajareddy_24
Messages: 23
Registered: April 2007
Location: bangalore
Junior Member
This is my formatted code.please have a look at this.

CREATE OR REPLACE PROCEDURE EmployeeFlatFile
IS
File_Id utl_File.File_Type;
Current_Date1 VARCHAR2(30);
Current_Date2 VARCHAR2(30);
Current_Date3 VARCHAR2(30);
Heading VARCHAR2(100);

cursor c1
is
select '|'||
lpad(nvl(to_char(empno),' '), max(length(nvl(to_char(empno),' '))) over(),' ')||'||'||
lpad(nvl(ename,' '), max(length(nvl(ename,' '))) over(),' ')||'||'||
lpad(nvl(job,' '), max(length(nvl(job,' '))) over(),' ')||'||'||
lpad(nvl(to_char(mgr),' '), max(length(nvl(to_char(mgr),' '))) over(),' ')||'||'||
lpad(nvl(to_char(sal),' '), max(length(nvl(to_char(sal),' '))) over(),' ')||'||'||
lpad(nvl(to_char(deptno),' '), max(length(nvl(to_char(deptno),' '))) over(),' ') ||'|' as good_str
from emp;
r1 c1%rowtype;
BEGIN
Heading := 'Intereq- Outstanding LR Requests (LRs were not sent to agencies in next working day)';
SELECT To_char(SYSDATE,'yyyymmdd')
INTO Current_Date1
FROM Dual;
SELECT To_char(SYSDATE,'dd/mm/yyyy')
INTO Current_Date2
FROM Dual;
SELECT To_char(SYSDATE,'dy mon yyyy hh24:ss')
INTO Current_Date3
FROM Dual;
File_Id := utl_File.Fopen('UTL_FILE_TMP','OutstandingLR-REQUEST-'
||Current_Date1
||'.txt','w');
utl_File.Put_Line(File_Id,Heading);
utl_File.Put_Line(File_Id,Current_Date2);
utl_File.Put_Line(File_Id,' ');
utl_File.Put_Line(File_Id,'|'||'empno| '||'|ename| '||' |job| '||' |mgr| '||'|sal| '||' |deptno| ');
utl_File.Put_Line(File_Id,' ');
FOR r1 in c1
LOOP
utl_File.Put_Line(File_Id,'---------------------------------------------------------------------------------------------------------- ---------');
utl_File.Put_Line(File_Id,r1.good_str);
END LOOP;
utl_File.Put_Line(File_Id,' ');
utl_File.Put_Line(File_Id,Current_Date3);
utl_File.fClose(File_Id);
END;
Re: UTL_FILE problem (merged) [message #234166 is a reply to message #234161] Mon, 30 April 2007 04:48 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
I see you still didn't get it, but hey, you put at least an effort in it. Next time:
1. SELECT your code.
2. Hit this button: http://www.orafaq.com/forum/theme/orafaq/images/b_code.gif

And you'll be fine. Here's the formatted procedure:

CREATE OR REPLACE PROCEDURE employeeflatfile
IS
   file_id         UTL_FILE.file_type;
   current_date1   VARCHAR2 (30);
   current_date2   VARCHAR2 (30);
   current_date3   VARCHAR2 (30);
   heading         VARCHAR2 (100);

   CURSOR c1
   IS
      SELECT    '|'
             || LPAD (NVL (TO_CHAR (empno), '   ')
                    , MAX (LENGTH (NVL (TO_CHAR (empno), '   '))) OVER ()
                    , '   '
                     )
             || '||'
             || LPAD (NVL (ename, '    ')
                    , MAX (LENGTH (NVL (ename, '   '))) OVER ()
                    , '   '
                     )
             || '||'
             || LPAD (NVL (job, '     ')
                    , MAX (LENGTH (NVL (job, '    '))) OVER ()
                    , '     '
                     )
             || '||'
             || LPAD (NVL (TO_CHAR (mgr), '    ')
                    , MAX (LENGTH (NVL (TO_CHAR (mgr), '    '))) OVER ()
                    , '    '
                     )
             || '||'
             || LPAD (NVL (TO_CHAR (sal), '      ')
                    , MAX (LENGTH (NVL (TO_CHAR (sal), '     '))) OVER ()
                    , '    '
                     )
             || '||'
             || LPAD (NVL (TO_CHAR (deptno), '     ')
                    , MAX (LENGTH (NVL (TO_CHAR (deptno), '    '))) OVER ()
                    , '    '
                     )
             || '|' AS good_str
      FROM   emp;

   r1              c1%ROWTYPE;
BEGIN
   heading    :=
      'Intereq- Outstanding LR Requests (LRs were not sent to agencies in next working day)';

   SELECT TO_CHAR (SYSDATE, 'yyyymmdd')
   INTO   current_date1
   FROM   DUAL;

   SELECT TO_CHAR (SYSDATE, 'dd/mm/yyyy')
   INTO   current_date2
   FROM   DUAL;

   SELECT TO_CHAR (SYSDATE, 'dy mon yyyy hh24:ss')
   INTO   current_date3
   FROM   DUAL;

   file_id    :=
      UTL_FILE.fopen ('UTL_FILE_TMP'
                    , 'OutstandingLR-REQUEST-' || current_date1 || '.txt'
                    , 'w'
                     );
   UTL_FILE.put_line (file_id, heading);
   UTL_FILE.put_line (file_id, current_date2);
   UTL_FILE.put_line (file_id, ' ');
   UTL_FILE.put_line (file_id
                    ,    '|'
                      || 'empno| '
                      || '|ename| '
                      || ' |job| '
                      || '  |mgr| '
                      || '|sal| '
                      || ' |deptno| '
                     );
   UTL_FILE.put_line (file_id, ' ');

   FOR r1 IN c1
   LOOP
      UTL_FILE.put_line
         (file_id
        , '----------'
         );
      UTL_FILE.put_line (file_id, r1.good_str);
   END LOOP;

   UTL_FILE.put_line (file_id, ' ');
   UTL_FILE.put_line (file_id, current_date3);
   UTL_FILE.fclose (file_id);
END;


Note, that for display purposes, I've shortened the put_line with the hyphens.

MHE
Re: UTL_FILE problem (merged) [message #234169 is a reply to message #234166] Mon, 30 April 2007 04:59 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Are you betting on two horses?

I was looking for an OWA_UTIL solution but immediately recognised the code.

MHE
Re: UTL_FILE problem (merged) [message #234172 is a reply to message #234169] Mon, 30 April 2007 05:12 Go to previous messageGo to next message
rajareddy_24
Messages: 23
Registered: April 2007
Location: bangalore
Junior Member
Hi Maaher,

I posted same question in oracle forums also.still I didnt get exact solution.

Can you please help me regarding this?
Re: How can I write table(all columns) to text file using UTL_FILE [message #234184 is a reply to message #233769] Mon, 30 April 2007 05:45 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Mahesh Rajendran wrote on Fri, 27 April 2007 14:24
Pick your method from here
http://asktom.oracle.com/tkyte/flat/
Works real good.


I found this a very good tip. Have a look at OWA_SYLK. It allows you to generate a Formatted file you can open in MS Excel.

I've just tested it: installed it with the source from the asktom website, created a directory and created some files.

MHE
Re: How can I write table(all columns) to text file using UTL_FILE [message #234200 is a reply to message #234184] Mon, 30 April 2007 07:51 Go to previous messageGo to next message
rajareddy_24
Messages: 23
Registered: April 2007
Location: bangalore
Junior Member
hi maher,
OWA_SYLK is for only excel.I need procedure to generate text file.i did this thing.
The problem I am facing is I need to genetate output format like in below link
http://www.eskimo.com/~ericj/personal/postgresql/sql1b.htm

Re: How can I write table(all columns) to text file using UTL_FILE [message #234204 is a reply to message #234200] Mon, 30 April 2007 08:03 Go to previous message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Did you atleast try the given options?
Option "PLSQL routine that uses utl_file" will just create a text file with your choice of delimiter.

>> I need to genetate output format like in below link
THat seems to be HTML formatted. Is that what you are looking for?
Just use HTML options in sql*plus.
REfer docs/ search this forum.
Previous Topic: is there any function to remove white space in between words
Next Topic: how to give input such as 100.00 or 23.78 or 80.34
Goto Forum:
  


Current Time: Sat Dec 10 10:59:49 CST 2016

Total time taken to generate the page: 0.10965 seconds