Easily export data from Oracle to flat file

By Carlos on Mon, 02/15/2010 - 11:49

A simple way to export data from a query, table, etc.. of an oracle database to a flat file is to use the SPOOL command in SQLPlus. This would not need to rely on visual aids, which are not always available or do not always work as we want. Also you can use the Oracle format functions in the same SELECT statement that generated the data already in the format we need.

If, for example, we want to retrieve some data from all records in a table of customers sorted by date of discharge, simply open a SQLPlus session and run this series of commands: 

SQL> SET HEADING OFF
SQL> SET FEEDBACK OFF
SQL> SPOOL C:\datos_de_clientes.txt
SQL> SELECT 'Cliente ' || CLI_NOMBRE || ', ' || CLI_NIF || '. Fecha alta: ' || TO_CHAR(CLI_FECHAALTA,'YYYY-MM-DD')
FROM TABLA_CLIENTES
ORDER BY CLI_FECHAALTA DESC;
SQL> SPOOL OFF;
SQL> SET FEEDBACK ON
SQL> SET HEADING ON

The first lines hide the headers that contain the field name, and do not concern us because we only want the data. Spool directs the output of data to the file 'datos_de_clientes.txt' on the C drive on the local machine.

Oracle 10g: Possible optimization in massive data dump

By il_masacratore on Tue, 02/16/2010 - 13:59
In batch runs to make a massive data dump into the same table using an INSERT or UPDATE for register within a block, the process can be optimized with the use of parameters (if client supports it) or if we use ODBC with bind variables.
Recall the steps taken by Oracle to process a query:
1) Sintactic Validation 
2) Semantic Validation
3) Optimization 
4) Generation of the QEP (Query Execution Plan)
5) Implementation of the QEP (Query Execution Plan)
Sentences can pick up the parameters by value (where salary > 1000) or once the sentence is compiled using Bind Variables (where salary>: b1). The advantage of the second option is that Oracle compile the sentence only one-time and reuses the compiled code for each of the values for the parameters.
But we must be aware because in the latter case because Oracle can't calculate the degree of selectivity of a query and, instead, apply a degree of selectivity by default (associated with each type of operation), which can give in wrong decisions.