One
of the useful features of SQL Plus is that it will allow easy
manipulation of result rows and columns. Here is a simple script that
allows spooling results without header, line breaks, inputs, etc:
SET HEADING OFF
SET PAGESIZE 0
SET LINESIZE 400
SET TERMOUT OFF
SET VERIFY OFF
SET FEEDBACK OFF
SET ECHO OFF
SET SERVEROUTPUT OFF
SET TRIMS OFF
SET COLSEP "|"
SET CONCAT "."
SET NEWPAGE NONE
SET UNDERLINE OFF
COLUMN USER_ID FORMAT 99999999
COLUMN USER_NAME FORMAT A80
COLUMN LAST_LOGON_DATE FORMAT A12
COLUMN CREATION_DATE FORMAT A12
COLUMN CREATED_BY FORMAT 99999999
COLUMN LAST_UPDATE_DATE FORMAT A12
COLUMN LAST_UPDATED_BY FORMAT 999999
COLUMN LAST_UPDATE_LOGIN FORMAT 99999999
COLUMN START_DATE FORMAT A12
COLUMN DISPLAY_NAME FORMAT A80
COLUMN EMPLOYEE_ID FORMAT 99999999
COLUMN EMAIL_ADDRESS FORMAT A80
COLUMN PERSON_PARTY_ID FORMAT 99999999
SPOOL C:\MY_SQL_RESULTS.TXT;
SELECT US.USER_ID, US.USER_NAME, US.LAST_LOGON_DATE,
US.CREATION_DATE, US.CREATED_BY, US.LAST_UPDATE_DATE,
US.LAST_UPDATED_BY, US.LAST_UPDATE_LOGIN, US.START_DATE,
US.DESCRIPTION AS DISPLAY_NAME, US.EMPLOYEE_ID,
US.EMAIL_ADDRESS, US.PERSON_PARTY_ID
FROM APPS.FND_USER US
WHERE US.USER_ID BETWEEN 12345 AND 17654;
SPOOL OFF;
Usage:
1. Set column data type and width appropriately by the usage of DEFINE statements
2. Make sure to have the LINESIZE has more than the total required width for all columns (Eg: SET LINESIZE 600)
3.
Change the query (SELECT ...) as per the requirement (This is a simple
query, while many practical data spool requires multi page SQLs).
4. Make sure to change the output file path correctly (Otherwise it will overwrite the current file - SPOOL C:\MY_SQL_RESULTS.TXT;)
If heading is required in the spool output, please remove the line "SET HEADING OFF".
