Re: SQL*Plus problem

From: Eugene Gardner <Gene_at_VaxMan.prestel.co.uk>
Date: Thu, 07 Jan 1999 18:12:55 GMT
Message-ID: <3694d9f0.25231501@news.prestel.co.uk>

SPOOL tmp1.sql
COLumn column_name WORD_wrapped
SET TERMOUT OFF HEADING OFF PAGES0 VERIFY OFF ECHO OFF FEEDBACK OFF ....
Eugene.

---

Ed.Stevens_at_nmm.nissan-usa.com wrote:

> 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

Eugene Gardner.
Oracle Certified dba available for hire on NT or VMS platforms. Received on Thu Jan 07 1999 - 12:12:55 CST