Aggregator | Books | Industry News | Firefox Plugins | Social | Links

Comments

Generating CSV Files - A simple example of using the UTL_FILE package to generate CSV extract files.



Rolf Unger said...

I was searching for some way to check if a file
handle is open or not (something similar to the
%isopen attribute of a cursor, but if I look at
the following piece of your sample code

WHEN UTL_FILE.INVALID_FILEHANDLE THEN
UTL_FILE.FCLOSE(v_file);

it seems like fclose() is a save method, that does
not run wild even if the file handle is invalid.

The invalid filehandle is probably 'v_file', thus
I think fclose(v_file) cannot do anything meaningful.

vetri said...

Its was cool..unlike other examples for opening file. can figureout exact error at first run...Cheers for the creator, good work man.

Terry said...

I can't get CONNECT or REPLACE command to execute properly. I keep getting "ORA-01031: insufficient privileges".

I've tried to signon as sysda and sysoper, but don't have that privilege either.

Can you point me in the right direction?

Tim... said...

Please ask questions on the forum!!

You need to connect as a privileged user, like SYS, to grant the necessary privileges. If you don't know the correct username and password you will have difficulty proceeding.

If you still have issues with this, please post them as a question on the forum. I won't continue this discussion here.

Cheers

Tim...

Prashant said...

Hi,

I have successfully completed the procedure, But when I try to execute the Procedure, it gives me the following error:-

SQL> exec EMP_CSV;
BEGIN EMP_CSV; END;

*
ERROR at line 1:
ORA-20000: File location is invalid.
ORA-06512: at "SYSTEM.EMP_CSV", line 37
ORA-06512: at line 1

But through other stored procedures I am able to create the file in that directory.

Where am I going wrong, Can anyone please help me

Tim... said...

Hi.

Please ask questions in the forum!

If it's worked before, I can only think that you've not granted access on the directory object to the relevant user.

Cheers

Tim...

nisha said...

hi ,
for the above procedure after execution we are also getting the same. in our system once also it has not copied to the file.we are also getting the same error as mentioned above i.e

ORA-20000: File location is invalid.
ORA-06512: at "SYSTEM.EMP_CSV", line 37
ORA-06512: at line 2

so please give us the solution and tell us how to configure the util_file package

Tim... said...

Hi.

Ask questions in the forum! These comments are for article contents, not technical questions.

Bye the way, read the error message. Your directory object is not pointing to a valid location.

Cheers

Tim...

soumya said...

for me everything is working fine . it says directory created. but i'm not able to check where the dir is created. it is not seen in the specified path. do we have to create in some particular path?

Tim... said...

Hi.

An Oracle directory object is just a pointer to a real directory. It doesn't create the physical directory for you. You need to create the physical directory on your server, then use the directory object to point to it and grant permissions on it.

Cheers

Tim...

PS. Questions in the forum, not the comments please. :)

Amy said...

Thanks Tim!!! This was most helpful!!!

ydi said...

i cant find the csv file on extract_dir.. theres nothing wrong on my procedure..

Tim... said...

Hi.

I'm guessing you used the simple exception handler, which is masking the errors. I've changed the simple exception handler to include a RAISE at the end.

Cheers

Tim...

DO NOT ask technical questions here, that's what my forum is for!

These comments should relate to the contents of a specific article. Constructive criticism is good. Advertising and offensive comments are bad and will be deleted!

Add your comments here.
Name
Comment
(max 400 chars - plain text)