Create TXT Records using PL/SQL Oracle 8i

Hello

Need to Create a TXT Records file directly from Oracle 8i Stored Procedure with following specs :

- File Name is dynamic using Date and time in file name , example : file_201505270738.txt
- Field Delimiters :  pipe “|” (Hex 7C)
- Line Terminator : Record “CR LF” (Hex 0d0a)

Can you put an example for a two fields table ?

Thank you
m_jundiAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Wasim Akram ShaikCommented:
you should use utl_file to write to a file, you have to create a oracle directory and specify the file name..

file name can be dynamic and can be passed as a variable..

i cannot see any links of 8i, but they work pretty much the same for 8i

http://www.dataqapps.com/step-by-step-reading-and-writing-a-text-file-using-utl_file-package-in-oracle-10g/

UTL_FILE more info :
http://docstore.mik.ua/orelly/oracle/bipack/ch06_02.htm
0
johnsoneSenior Oracle DBACommented:
I don't recall when directories in Oracle were introduced.  They may not have been there in 8i.  If they are not, the you need to set the UTL_FILE_DIR parameter.  Even if directories were available in 8i, UTL_FILE_DIR was still there for backward compatibility.
0
slightwv (䄆 Netminder) Commented:
Quick doc check shows it uses UTL_FILE_DIR:
http://docs.oracle.com/cd/A87860_01/doc/appdev.817/a76936/utl_fil2.htm#1000987

There are MANY examples out there showing how to use UTL_FILE.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

m_jundiAuthor Commented:
Did the following :

CREATE OR  REPLACE DIRECTORY utl_file_dir  AS ‘C:\TEST';

When execute the Procedure I receive this error :

ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "SYS.UTL_FILE", line 98
ORA-06512: at "SYS.UTL_FILE", line 157
ORA-06512: at "schema.WRITE_FILE", line 19
ORA-06512: at line 2


Procedure
===========================

create or replace procedure write_file is
file1 utl_file.file_type;
cursor empc is
select * from company;
employ empc%rowtype;
stmt varchar2(300);
line  varchar2(300);
begin
file1 := utl_file.fopen('UTL_FILE_DIR','TEST1.TXT','W');
utl_file.new_line(file1);
for employ in empc loop
stmt := rpad(employ.CO_COMPANY_CODE,2,' ') ||
rpad(employ.CO_ENGLISH_NAME,60,' ') ||
rpad(employ.CO_SHORT_NAME,10,' ');
utl_file.PUTF(file1, stmt);
utl_file.new_line(file1);
end loop;
utl_file.fclose(file1);
end;
0
johnsoneSenior Oracle DBACommented:
I don't have a version of 8i that I can test anything on.  The code looks correct to me.  I simplified it to this:
create or replace procedure write_file is
file1 utl_file.file_type;
begin
file1 := utl_file.fopen('UTL_FILE_DIR','TEST1.TXT','W');
utl_file.new_line(file1);
utl_file.PUTF(file1, 'hello world');
utl_file.new_line(file1);
utl_file.fclose(file1);
end;
/

Open in new window

That worked just fine.  Although it was not on 8i.

My first guess as to the problem would be a permission issue on the directory.
0
m_jundiAuthor Commented:
The Windows directory has to be on the Oracle Server , correct ?
How to check the permission if it OK ?

Thank you
0
slightwv (䄆 Netminder) Commented:
>>CREATE OR  REPLACE DIRECTORY utl_file_dir  AS ‘C:\TEST';

UTL_FILE_DIR is a pfile parameter not an Oracle directory.  If it isn't currently set, you must set that in the pfile and restart the database.

>>The Windows directory has to be on the Oracle Server , correct ?

Correct.  UTL_FILE can only write to the database server.

>>How to check the permission if it OK ?

Check with your system administrator.

You right click on the folder, select security.

https://technet.microsoft.com/en-us/library/cc771586.aspx
0
johnsoneSenior Oracle DBACommented:
UTL_FILE_DIR being set is not required.  I checked the documentation and directories were supported in 8i.  So, that must have been the first version.  As I recall, when directories were introduced, you could use either the UTL_FILE_DIR parameter or use a directory.

Code also seems to be failing on the call to NEW_LINE and got past FOPEN.

I really don't like that the example in the link provided in the first post uses UTL_FILE_DIR as the name of the directory.  Bad practice.  I believe author is following that as an example.

One other thing that I would recommend.  On the call to FOPEN, for a mode you are specifying W, I would change that to w (ie lowercase instead of uppercase).  As I recall, early versions of UTL_FILE were picky and would not take the uppercase version.
0
m_jundiAuthor Commented:
1)   Now I got below because I have used create statement, should I do ?

SELECT * FROM ALL_DIRECTORIES;
OWNER                          DIRECTORY_NAME                 DIRECTORY_PATH
----------                         ------------------------------           ------------------------
SYS                                 UTL_FILE_DIR                           C:\TEST

2)  is the following parameter is correct in init.ora
utl_file_dir=c:\test

3) Also have added "everyone" with full permission to this folder.

Thank you
0
johnsoneSenior Oracle DBACommented:
Are you still getting the same error?

Did you try changing the mode to lowercase?

If you are still getting errors, then you are going to have to put in some error handling.  Check the link that slightwv provided for the documentation on FOPEN.  At a minimum, catch those three and print a message.
0
m_jundiAuthor Commented:
No, no I did not implement it yet because it is a production DB,, but please confirm by 3 points
0
m_jundiAuthor Commented:
I mean my 3 point **
0
johnsoneSenior Oracle DBACommented:
Those should be correct.  You need to be aware that you need to restart the database before the parameter setting of UTL_FILE_DIR will take effect.  I don't think it is necessary though.
0
johnsoneSenior Oracle DBACommented:
Also, testing in production is a bad thing.
0
Mark GeerlingsDatabase AdministratorCommented:
Changes to the "utl_file_dir" value *ONLY* get applied when you restart the database!  That is a big disadvantage of these, compared to "logical" directories, because those can be added dynamically.

Either physical or logical directories for utl_file are only on the DB server.
0
m_jundiAuthor Commented:
I have added  the following line to init.ora file and restarted the DB but got the same error for the procedure

utl_file_dir = C:\Test

Can somebody summarize steps needed if I missed something ?

Procedure executed :

procedure write_file1 is
file1 utl_file.file_type;
begin
file1 := utl_file.fopen('UTL_FILE_DIR','TEST.TXT','w');
utl_file.new_line(file1);
utl_file.PUTF(file1, 'hello world');
utl_file.new_line(file1);
utl_file.fclose(file1);
end;
 
Errors:
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "SYS.UTL_FILE", line 98
ORA-06512: at "SYS.UTL_FILE", line 157
ORA-06512: at "TRANSCEND.WRITE_FILE1", line 4
ORA-06512: at line 2
0
slightwv (䄆 Netminder) Commented:
>>file1 := utl_file.fopen('UTL_FILE_DIR','TEST.TXT','w');

In this usage: UTL_FILE_DIR is an Oracle Directory Object.
This must be created with the CREATE DIRECTORY SQL statement.

>>utl_file_dir = C:\Test
This init.ora parameter doesn't use Oracle directory objects.  To use this method you specify the folder.

Going from memory:
file1 := utl_file.fopen('C:\Test','TEST.TXT','w');

Use the DIRECTORY OBJECTS if your version of Oracle will let you.

The online docs for your specific version will have examples of all of this as well as tell you if FOPEN will use directory objects.
0
Mark GeerlingsDatabase AdministratorCommented:
This line in your procedure is wrong:
file1 := utl_file.fopen('UTL_FILE_DIR','TEST.TXT','w');

That must be changed to:
file1 := utl_file.fopen('C:\Test','TEST.TXT','w');

Explanation:
'UTL_FILE_DIR' is just the name of your initialization parameter that contains your physical directory path (or paths, since this can support multiple values)

When you call utl_file.fopen, you must supply one of the valid values that is in your 'UTL_FILE_DIR' parameter.
0
johnsoneSenior Oracle DBACommented:
As I recall when using UTL_FILE_DIR parameter, then you need to change the open command.  The parameter lists the valid directories, and there can be more than one.  You need to specify which one in the open.

I believe your open command should be:

file1 := utl_file.fopen('C:\TEST','TEST.TXT','w');

If that doesn't work, we need to know what the error is that is being generated.  You need to put an error handler into the code and handle the list of errors the comes from UTL_FILE.  There are many examples out there of how to do that.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
m_jundiAuthor Commented:
I LOVE U MAN
0
Mark GeerlingsDatabase AdministratorCommented:
I wonder why you accepted johnsone's comment when both slightwv and I had posted the same answer just before he did.  I didn't see slightwv's comment before I posted mine.  And, I'm guessing that johnsone didn't see either slightwv's or mine, before he posted his.
0
johnsoneSenior Oracle DBACommented:
I'm pretty sure we were all typing in that same answer at the same time.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.