Learn how to a build a cloud-first strategyRegister Now


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

Posted on 2007-08-01
Medium Priority
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

Accepted Solution

abuckheit earned 500 total points
ID: 19614260
select id || ',"' || NVL(name,'') || '"' from table;

spool that to a file
LVL 14

Assisted Solution

GGuzdziol earned 500 total points
ID: 19614838
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
LVL 35

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 500 total points
ID: 19616311
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.

Assisted Solution

prast1007 earned 500 total points
ID: 19736320
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Suggested Courses
Course of the Month20 days, 15 hours left to enroll

864 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