UTL_FILE CSV export : double quote data with comma(s) in text field

[Advocate]

hi guys,

exporting data into a csv file using 10GR2 utl_file

need to check if data from several fields have comma(s) in them and, if so (iaw client's spec), wrap column data in double quotes (normal standard for csv files)

eg ...

qwerty = qwerty
qwe,rty = "qwe,rty"
qw,er,ty = "qw,er,ty"

planning to use following function unless anybody has other bright ideas ?

========================
FUNCTION CSV_QUOTE (DataIn IN varchar2)
RETURN varchar2
IS
DataOut varchar2(5000);
BEGIN

select decode(instr(instr(DataIn, ','), '0'), 1, DataIn, '"' || DataIn || '"') into DataOut from dual;

RETURN DataOut;
END CSV_QUOTE;
=========================

tanx in advance ...


regards, david - no such thing as problems - only solutions.

 

[SantaMufasa]

Advocate,

I believe that the following code for your function is more efficient since this code does not involve any SQL (which, in your case, requires an extraneous access ["SELECT"] of the database for each execution):

create or replace FUNCTION CSV_QUOTE (DataIn IN varchar2) RETURN varchar2 IS
BEGIN
    if instr(DataIn,',') > 0 then
        return '"'||DataIn||'"';
    else
        return DataIn;
    End if;
END CSV_QUOTE;
/

Function created.

select csv_quote('qwerty') from dual;

CSV_QUOTE('QWERTY')
-------------------
qwerty

select csv_quote('qw,er,ty') from dual;

CSV_QUOTE('QW,ER,TY')
---------------------
"qw,er,ty"

Let us know your thoughts.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[Advocate]

agreed ... good man david, again !

that will do the job nicely ...

thats why i love tek-tips ... lifesaver when we are tired, cant think straight and miss the obvious ...

got an outstanding query on the quest forum if you nave any ideas ... ;-)

happy new year and hope that santa was kind to you ...
(herself bought me a portable gps for xmas but a cunning plan to buy one myself backfired as i under quoted the cost to herslf to make it more palatable ... she went out and bought the one for that price ... mono instead of the colour i wanted ... oh well, that will teach me ...)


regards, david - no such thing as problems - only solutions.

 

[Advocate]

david, as a follow up ...

tried using the function in a package (incl pkg spec declaration & pragma etc) but got a new one for me ...

PLS-00231 (oracle 10.2.0.3)

SELECT --'S' occupancy,
1 || cDelimiter
|| CSV_QUOTE(e.SURNAME) || cDelimiter
|| e.FIRST_NAME || cDelimiter
from ...

nothing worked until i created a standalone function and called that from within the pkg body

something new for me ...


regards, david - no such thing as problems - only solutions.

 

[SantaMufasa]

tried using the function in a package

There should be no reason that a packaged function does not work in your SELECT. If your function call looked like the one you posted, above, it failed because you did not qualify the function name with the package name. For example:

SELECT...<package name>.CSV_QUOTE(e.SURNAME)...

Let us know.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[Advocate]

i created the function in a package body and then tried calling the function from withing another procedure in the same package body ...

also tried declaring function in pkg spec (although it was not required as public for this piece of work) and tried
PRAGMA RESTRICT_REFERENCES (CSV_QUOTE, WNDS, RNDS) as well ...

kept got PLS-00231 until i created standalone function and called that external func from within the package procedure ...

never had this problem before so it is a new one on me ...

been on 9i for last 2 years and 10GR1 before that, so dont know if something has changed or just never came across it ...

regards, david - no such thing as problems - only solutions.

 

[SantaMufasa]

Advocate,

One thing that could explain the (mis)behaviour is that if you call a packaged function from any code unit (e.g., procedure or other function) from within the same packaged procedure, the packaged function definition must appear in the declarative section prior to the packaged object that that calls the packaged function.

Did you code following this requirement?

(Again, neither Oracle 10g, nor any other Oracle version requires independently created functions that any procedure references...your functions may be packaged and if you create the packaged functions as global packaged functions, then any SQL or PL/SQL is able to use/reference your packaged global function(s).

Let us know your findings.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[Advocate]

pseudo code extract ... to avoid you trawling through full code ...

nap is one of several similar procedures in the pkg for export extracts, hence some initial global testing variables ...

summary ...

declared in pkg body in following order

func csv_uuote
proc nap
proc main

main declared in pkg spec

main calls nap
nap calls csv_quote

==========================================

CREATE OR REPLACE package interfaces as

FUNCTION CSV_QUOTE (DataIn IN varchar2) RETURN varchar2;
PRAGMA RESTRICT_REFERENCES (CSV_QUOTE, WNDS, RNDS);--tried with & without pragma

procedure main;

end interfaces;

==============================
CREATE OR REPLACE package body interfaces

vCounter number := 0;
cDelimiter constant varchar2(1) := ',';

FUNCTION CSV_QUOTE (DataIn IN varchar2) RETURN varchar2 IS
BEGIN
if instr(DataIn,',') > 0 then
return '"' || DataIn || '"';
else
return DataIn;
end if;
END CSV_QUOTE;
----------------------------
procedure nap

cursor occupancies is
SELECT --'S' occupancy,
1 || cDelimiter
|| CSV_QUOTE(e.SURNAME) || cDelimiter
|| CSV_QUOTE(e.FIRST_NAME) || cDelimiter
from tables;

begin

for occupancy in occupancies loop
hfile := create_file (dir, file_name);

select 0 || cDelimiter || 'HBOSA_DS_PER_ONLINELEARNING_OUT'
|| cDelimiter || to_char(target_date, 'DDMMYYYY') into out_line from dual;--header
write_file (hfile, out_line);

for occupancy in occupancies loop--details
out_line := occupancy.details;
write_file (hfile, out_line);
vCounter := vCounter+1;
end loop;

select 9 || cDelimiter || vCounter into out_line from dual;--footer
write_file (hfile, out_line);

close_file (hfile);

end loop;--occupancies

end nap;
--------------------------------
procedure main
nap;
end main;

end interfaces;
================================

exact error message =
PLS-00231 function 'CSV_QUOTE' may not be used in SQL

--======================================================

googled ...
pls-00231 siteracle.com
and got a few hits ... but no good ...
wider search also proved fruitless ...


regards, david - no such thing as problems - only solutions.

 

[SantaMufasa]

Advocate,

Please accept my apologies...I do recall now that if you wish to reference a user-defined function in SQL, then it must be an independently defined (non-packaged) function. I hope my mental cog-slip has not caused you enormous delay or frustration.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[Advocate]

not at all david ... nice to get confirmation that i am not screwing it up ... ;-)

funny how i have never come across this one before ...

tanx for your input ... still owe you a few guinness ...


regards, david - no such thing as problems - only solutions.