How To Export Data with a Field Delimiter

Q

How To Export Data with a Field Delimiter? - Oracle DBA FAQ - Loading and Exporting Data

✍: FYIcenter.com

A

The previous exercise allows you to export data with fixed field lengths. If you want export data with variable field lengths and field delimiters, you can concatenate your fields with an expression in the SELECT clause as shown in the tutorial exercise bellow:

SQL> SET HEADING OFF;
SQL> SET FEEDBACK OFF;
SQL> SET LINESIZE 1000;
SQL> SPOOL \oraclexe\test\fyi_links.txt;
SQL> SELECT id ||','|| url ||','|| notes ||','|| counts
  ||','|| created FROM fyi_links;
......
SQL> SPOOL OFF;

You should see all records in fyi_links.txt with ',' delimited fields as shown here:

101,fyicenter.com,Session 1,,17-MAY-06
110,centerfyi.com,Session 1,,17-MAY-06

2007-04-30, 4559👍, 0💬

What Is SQL*Loader
What Is SQL*Loader? - Oracle DBA FAQ - Loading and Exporting Data SQL*Loader is a database tool that allows to load data from external files into database tables. SQL*Loader is available as part of the free Oracle 10g Expression Edition. It has some interesting features as: Can load data from multip... 2007-04-30, 5282👍, 0💬

What Is an External Table
What Is an External Table? - Oracle DBA FAQ - Loading and Exporting Data An external table is a table defined in the database with data stored outside the database. Data of an external table is stored in files on the operating systems. Accessing data of external tables are done through data access d... 2007-04-30, 5158👍, 0💬

How To Load Data with SQL*Loader
How To Load Data with SQL*Loader? - Oracle DBA FAQ - Loading and Exporting Data Let's say you have a table defined as: CREATE TABLE student (id NUMBER(5) PRIMARY KEY, first_name VARCHAR(80) NOT NULL, last_name VARCHAR(80) NOT NULL, birth_date DATE NOT NULL, social_number VARCHAR(80) UNIQUE NOT NULL)... 2007-04-30, 5132👍, 0💬

How To Get Execution Path Reports on Query Statements
How To Get Execution Path Reports on Query Statements? - Oracle DBA FAQ - Introduction to Command-Line SQL*Plus Client Tool If your user account has autotrace configured by the DBA, you can use the "SET AUTOTRACE ON EXPLAIN" command to turn on execution path reports on query statements. The tutorial... 2007-04-30, 5023👍, 0💬

What Is a SQL*Loader Control File
What Is a SQL*Loader Control File? - Oracle DBA FAQ - Loading and Exporting Data A SQL*Loader control file a text that defines how data files should be loaded into the database. It allows you to specify: Where is the input data file. The format of the input date file. The target table where the data... 2007-04-30, 4845👍, 0💬

How To Get Execution Statistics Reports on Query Statements
How To Get Execution Statistics Reports on Query Statements? - Oracle DBA FAQ - Introduction to Command-Line SQL*Plus Client Tool If your user account has autotrace configured by the DBA, you can use the "SET AUTOTRACE ON STATISTICS" command to turn on execution statistics reports on query statement... 2007-04-30, 4820👍, 0💬

What Is the Quickest Way to Export a Table to a Flat File
What Is the Quickest Way to Export a Table to a Flat File? - Oracle DBA FAQ - Loading and Exporting Data The quickest way to export a table to a flat file is probably to use the SQL*Plus SPOOL command. It allows you to record SELECT query result to a text file on the operating system. The following ... 2007-04-30, 4724👍, 0💬

How To Export Data with a Field Delimiter
How To Export Data with a Field Delimiter? - Oracle DBA FAQ - Loading and Exporting Data The previous exercise allows you to export data with fixed field lengths. If you want export data with variable field lengths and field delimiters, you can concatenate your fields with an expression in the SELEC... 2007-04-30, 4560👍, 0💬

How To Load Data through External Tables
How To Load Data through External Tables? - Oracle DBA FAQ - Loading and Exporting Data If you have data stored in external files, you can load it to database through an external table by the steps below: Create an external table with columns matching data fields in the external file. Create a regul... 2007-04-30, 4554👍, 0💬

All rights in the contents of this web site are reserved by the individual author. fyicenter.com does not guarantee the truthfulness, accuracy, or reliability of any contents.