CODEWITHSUNDEEP
sqloracle-databasecsvplsqloracle11g
Jagger
Jagger

Reputation: 10522

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

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

X

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?

Upvotes: 1

Views: 561

Answers (1)

Alex Poole
Alex Poole

Reputation: 191570

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.

Upvotes: 5

Related Questions