Getting query result as CSV in PL/SQL using exactly the same command as in SQL with hint

Question

I am able to get the result as CSV in Oracle by using this simple query with a hint.

SELECT /*csv*/ * FROM dual;

This returns

"DUMMY"
"X"

Now I would like to use exactly the same hint in PL/SQL in order not to reinvent the wheel.

SET SERVEROUTPUT ON;
declare
  cur sys_refcursor;
  csv_line varchar2(4000);
begin
  open cur for select /*csv*/ * from dual;
  loop
    fetch cur into csv_line;
    exit when cur%NOTFOUND;
    dbms_output.put_line(csv_line);
  end loop;
  close cur;
end;

Unfortunately this prints only

which seems to ignore the hint.

Any way to do it that simple or do I have to write a special piece of code for exporting the data as CSV?

Alex Poole · Accepted Answer · 2016-06-07 08:30:01Z

5

The /*csv*/ hint is specific to SQL Developer and it's sibling SQLCl; and is somewhat superseded by the set sqlformat csv option.

It is not a hint recognised by the optimiser; those are denoted by a plus sign, e.g. /*+ full(...) */ or less commonly --+ full(...).

If you're creating the output in PL/SQL you will need to construct the string yourself, adding the double quotes and delimiters. You can either have the cursor query do that so you can select into a single string even when you have multiple columns; or have a 'normal' query that selects into a record and have PL/SQL add the extra characters around each field as it's output.

It would be more normal to use utl_file than dbms_output as the client may not have that enabled anyway, but of course that writes to a directory on the server. If you're writing to a file on the client then PL/SQL may not be appropriate or necessary.

If you need to do some manipulation of the data in PL/SQL then one other option is to use a collection type and have an output/bind ref cursor, and then have SQL Developer print that as CSV. But you don't normally want to be too tied to a single client.

edited Jun 7, 2016 at 8:30

answered Jun 7, 2016 at 8:25

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jagger Over a year ago

Pity. Thanks for the answer though! I think I will go with UTL_FILE.

Collectives™ on Stack Overflow

Getting query result as CSV in PL/SQL using exactly the same command as in SQL with hint

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related