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

Posted on 2007-08-01
Last Modified: 2013-12-18
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.

Question by:proaceteam
    LVL 6

    Accepted Solution

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

    spool that to a file
    LVL 14

    Assisted Solution

    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:
    LVL 34

    Assisted Solution

    by:Mark Geerlings
    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.
    LVL 4

    Assisted Solution

    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.


    Featured Post

    Live: Real-Time Solutions, Start Here

    Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

    Join & Write a Comment

    Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
    Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
    Via a live example, show how to take different types of Oracle backups using RMAN.
    This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now