SQL*Plus problem

From: <Ed.Stevens_at_nmm.nissan-usa.com>
Date: Wed, 06 Jan 1999 17:45:37 GMT
Message-ID: <7707g0$rf2$1@nnrp1.dejanews.com>

Subject: SQL*Plus problem

I am working on a script to run in SQL*Plus that will SELECT from a table, spooling the output to create another query. It looks like this:

SPOOL tmp1.sql
SET TERMOUT OFF HEADING OFF PAGES0 VERIFY OFF ECHO OFF FEEDBACK OFF  SELECT 'SELECT DISTINCT TO_CHAR('||COLUMN_NAME||',''DD-MON-YYYY'')'||
' FROM ' || OWNER ||'.' ||TABLE_NAME||
' ORDER BY TO_CHAR('||COLUMN_NAME||',''DD-MON-YYYY'')'||

          ';'
       FROM DBA_TAB_COLUMNS;

SPOOL OFF;
@tmp1.sql

The problem is that the resulting SELECT DISTINCT statement can resolve to a string that breaks into 2 lines when it, in turn, is processed by SQL*Plus. If this line break occurs in the middle of a word, the statement is seen to have syntax problems. What I would like to do is, as a part of the literals that are becomeing part of the SELECT DISTINCT, insert some sort of escape sequence to force a new line in the spooled output. Is there some way to do this, or another way around the problem?

--
Ed Stevens
Nissan Motor Mfg. Corp., USA

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Wed Jan 06 1999 - 11:45:37 CST