• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4170
  • Last Modified:

How to export data from Oracle with double quotes around text columns

I need my client to export data from their Oracle server (not sure which version) to a flat file. My flat file format requirements are:
1) comma delimited
2) double quote around text columns for every row including both empty and NULL

For example, when the schema has 2 columns, the first column is ID which NUMBER, the secound column is NAME which is string.

When ID is 1 and NAME is Jim, I want the exported line to be
1, "Jim"

When ID is 2 and NAME is empty string, I want the exported line to be
2, ""

When ID is 3 and Name is NULL, I want the exported line to also be
3, ""

I was told that they can not do this when the string is empty or NULL. The best they can do is to to write NULL when the string is NULL, i.e.:3, NULL

This format can be easily done in Microsoft SQL Server. I can't believe that Oracle can not handle this. I do not have an access to any Oracle environment and I can not play with the export utilities myself. Please let me know if you have a way to export data the way I describe on Oracle.

4 Solutions
select id || ',"' || NVL(name,'') || '"' from table;

spool that to a file
In Oracle empty string is NULL - this is different in sql server - see this example:

SQL> create table t (id number, name varchar2(30));

Table created.

SQL> insert into t values (1, 'Jim');

1 row created.

SQL> insert into t values (2, null);

1 row created.

SQL> insert into t values (3, '');

1 row created.

SQL> select id, '"' || name || '"' from t;

        ID '"'||NAME||'"'
---------- --------------------------------
         1 "Jim"
         2 ""
         3 ""

SQL> select id, nvl (name, 'NULL') from t;

        ID NVL(NAME,'NULL')
---------- ------------------------------
         1 Jim
         2 NULL
         3 NULL

As for exporting it to flat file, it depends where do You expect it to create. If on client side - use abuckheit's suggestion - use spool <filename> at the beginning of script and spool off at its end. If on server side - You probably need utl_file to do so. Here is something where probably You'd like to look: http://asktom.oracle.com/tkyte/flat/index.html
Mark GeerlingsDatabase AdministratorCommented:
Remember that SQL Server comes from a software tools company (Microsoft) so they make good, easy-to-use tools for their (not quite so good) database.  Oracle is first of all a database company, so an Oracle database is more stable, scalable and feature-rich than SQL Server, but the Oracle tools for working with Oracle database are not as easy to use as the Microsoft tools are for SQL Server.

Yes, it certainly is possible to get a comma-delimitted ASCII file from Oracle in the format you want, but in Oracle this requires some coding.  No, Oracle's export utility will definitely *NOT* do this for you.  You have to either:
1.  write a *.SQL script that an Oracle client can run in SQL*Plus with the "spool" command to create the ASCII file on a client PC based on a select statement (query) in the *.SQL file.
2. write a PL\SQL stored procedure that uses a cursor loop and calls procedures in the utl_file package to write an ASCII file on the server (not on a client).

Do you have a preference for where you want the file created (on the database server, or on a client PC)?

Here is an example of a *.SQL file that I used in SQL*Plus to create a fixed-length ASCII file (these are easier from Oracle than comma-delimitted).  Note the "set" commands that you will typically need if you want to use this approach:

set head off;
set trimspool on;
set feed off;
set pagesize 0;
set echo off;
set termout off;
set linesize 100;
spool F:\ora_in\itmmasnw.txt;
select itemno, drwrev, drwrel from conx_item_master_upd;
spool off;
-- (end of *.SQL file)

To make that comma-delimitted instead with double quotes around the values for the second and third columns, the query would have to be:
select itemno||',"'||drwrev||"','""||drwrel||'"' "Text" from conx_item_master_upd;

That may be a bit difficult to see and read, since that uses both single quotes and double quotes next to each other in some cases.

If you would rather do this on the server with a stored procedure, I can give you an exmple of that too, just let us know.
Spool the output of the query maybe a simple thing to do if we know exactly which tables should be exported and the data types of the columns in it and we just exported a few tables.

But if it has to be done on entire Database, doing it manually is another story. It will be a different thing. And other things to considered are about converting data types others than number and string (date, clob, etc).

But as Markgeer said it is certainly possible, but it would be not as simple as just spooling the output.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now