Remove leading spaces and the SQL> tags when spooling

Question

I m doing an export using SQL*Plus using the following code:

Set echo off
Set verify off
Set feedback off
Set heading off
Set termout on
Set pages 50000 
Set linesize 1000
Set newpage none
Set colsep ","
spool Z:\ff\ss.csv
Select Client_ID, Cust_ID from cust; 
spool off;
exit;

I get the correct output, but it doesn't have the best formatting.

SQL> Select Client_ID, Cust_ID from cust; 
       100,200
       200,300
       300,400
       400,500
SQL>spool off;

1. How can I get rid of the spaces before the values?

   100,200

2. How can I get rid of the SQL> statements in the output file?

Answer 1

echo off only works to remove the SQL> prompts when run from a file. Put your commands in a file called export_client_data.sql and run them using the @ command:

SQL>@export_client_data.sql

SQL*Plus, being a reporting tool, will format output to the column width as defined in the table. Using TRIM() doesn't always work as expected because of this. You will see a VARCHAR column padded on the right to the width for example.

Typically to create an extract using SQL*Plus you would format the query like this and no need to TRIM anything:

Select Client_ID || ',' || Cust_ID from cust;