0

I would like to export in .csv file results of the following query using a SQL Script (not using a Toad tool).

The objective is to use this SQL script by an external tool : eControl-M Workload Automation - BMC This tool just has to execute this sql Query + export script Can you please help me ?

SELECT * FROM TABLE

I found this script. What do you think about this script?

SET COLSEP ";"

COL colonne1 FORMAT A25
COL colonne2 FORMAT A30
COL colonne3 FORMAT A30
COL colonne4 FORMAT A12
COL colonne5 FORMAT A20
COL colonne6 FORMAT A25

SET TERM OFF
SET AUTOTRACE OFF
SET VERIFY OFF
SET ECHO OFF
SET FEEDBACK OFF
SET HEAD ON
SET LINESIZE 1000 
SET PAGESIZE 5000

SET TRIMSPOOL ON

SPOOL &1; --- Mettre la destination du fichier en lieu et place du &1 ---

--- ta requete ---
select....

SPOOL OFF; 
EXIT;

-- Normalement tu vas pouvoir te débrouiller avec ça
diziaq
  • 6,881
  • 16
  • 54
  • 96
Ezequiel_075
  • 77
  • 1
  • 2
  • 8

1 Answers1

2

Tools like TOAD, Oracle SQL Developer, and SQL Server Management Studio all have built in features to export/save query results to .CSV format. These are all part of the tool's functionality and not a function of the SQL language.

I am not familiar with eControl-M Workload Automation, but assuming you can get text output from that tool, you should be able to construct the output using Oracle's concatenate operator || and test it in something like TOAD, just output the results to TEXT format. Note: Concatenating fields can be simple if they are a VARCHAR() or CHAR() data type, but may require explicit conversion if they are some other data type.

Example:

SELECT (FIRST || ',' || LAST || ',' || AGE || ',' || SAL) FROM tbl1;

Example in SQL Fiddle

If you have other data types that need explicit conversion before concatenating to string output, you may want to look into using Oracle's CAST Function.

Hopefully this will help you get started.

Fred
  • 91
  • 5
  • Thanks you, so according to you I can use a script to export in toad. I have not other choice of manage the export in control M. My company contrains me to use Control M. So I suppose I have to ask Control M Manager to manage this export by himself from my query ? – Ezequiel_075 May 07 '15 at 10:20
  • What I suggested isn't only for TOAD. To get CSV out of TOAD or SQL Developer you would use 2 different methods. However, what I suggested would work in both as it's a pure SQL approach to building your own CSV. So, if Control M Manager supports sending Oracle SQL syntax and retrieving the results, then the approach should work. Beyond that, you may need to read up on Control M Manager to find out if there are any special requirements for what you are passing that tool. – Fred May 07 '15 at 14:00
  • Hello, i edit my issue, can you please check below ? – Ezequiel_075 May 07 '15 at 16:04
  • The script you added to your original question looks like a SQL Plus script. SQL Plus is another tool, like TOAD but without the graphical interface. The commands are not pure SQL, which is why I didn't suggest them, since I have no idea if **Control M Manager** supports SQL Plus commands. Does **Control M Manager** support SQL Plus commands? If so, here is a good example of [**Spooling to CSV in SQL Plus**](http://stackoverflow.com/questions/643137/how-do-i-spool-to-a-csv-formatted-file-using-sqlplus) – Fred May 07 '15 at 18:53