8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Misc » Here

Comments for Generating CSV 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...

Moose T said...

Great code :-)

I extended it to include a flag to to add quotes to string columns (plus you can supply the quote char)
In generate_all, I compare the col_type
IF (g_add_quotes='Y') AND (l_desc_tab(i).col_type = dbms_types.TYPECODE_VARCHAR OR ... ) THEN
put(l_file, g_quote_char);
put(l_file, l_buffer);
put(l_file, g_quote_char);
ELSE
put(l_file, l_buffer);
END IF;

Tim... said...

Hi.

Added it to the CSV package, with a few tweaks. Gave "Moose T" a shout out. :)

Cheers

Tim...

Tom M. said...

First, thank you for sharing. I like how everything was put together. Definitely like the header at the top.

I am going to use this with a project in AWS RDS to push CSV files to the AWS S3 buckets. To utilize the S3 query, I needed to change the open and puts. I added _nchar to each call so the output was UTF-8. This may be something to consider moving forward.

Tom

Anssi Kanninen said...

Hi!
Thank you for a great tool!
Adding quotes is nice but then you also need to escape the quote character like this:

IF g_add_quotes AND l_is_str THEN
put(l_file, g_quote_char);
put(l_file, replace(l_buffer, g_quote_char, '\'||g_quote_char));
put(l_file, g_quote_char);
ELSE
put(l_file, l_buffer);
END IF;

Best regards,
Anssi Kanninen
Q5 Software

Tim... said...

Hi.

I added a variation on what you suggested, making the escaping of the quote character optional. I gave you a shout-out in the code comment in the package body. :)

Cheers

Tim...

Anssi Kanninen said...

Thanks Tim!

Further improving the script:
If we escape the quote char, we also have to escape the possible escape characters already in the source text:

IF g_escape THEN
put(l_file, replace(l_buffer, '\', '\\')); -- Added this row
put(l_file, replace(l_buffer, g_quote_char, '\'||g_quote_char));
ELSE
put(l_file, l_buffer);
END IF;

Anssi Kanninen said...

OOPS, rewrite:

IF g_escape THEN
l_buffer := replace(l_buffer, '\', '\\');
l_buffer := replace(l_buffer, g_quote_char, '\'||g_quote_char);
END IF;
put(l_file, l_buffer);

Tim... said...

Hi.

Added that now. Thanks for the suggestion.

Cheers

Tim...

John Wehle said...

Nice script ... suggestion explicitly format dates:
...
IF l_desc_tab(i).col_type = DBMS_TYPES.typecode_date THEN
BEGIN
DBMS_SQL.COLUMN_VALUE(l_cursor, i, l_date);
l_buffer := to_char(l_date, 'YYYY-MM-DD HH24:MI:SS');
l_is_str := TRUE;
END;
ELSE
DBMS_SQL.COLUMN_VALUE(l_cursor, i, l_buffer);
END IF;

Tim... said...

Hi.

As per example, I use NLS_DATE_FORMAT to decide on the date format.

If I were to add it to the code, I would not hard-code like your example, but parameterise it. Maybe I'll do that.

Cheers

Tim...

DO NOT ask technical questions here! They will be deleted!

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!

If you post personal information (name, email address etc.) you are agreeing to them being stored and displayed. Feel free to remain anonymous.