Re: Quick sqlplus cr/lf question

From: EdStevens <quetico_man_at_yahoo.com>
Date: 4 Aug 2006 04:59:16 -0700
Message-ID: <1154692756.707959.57730@b28g2000cwb.googlegroups.com>

BD wrote:
> > set pagesize 0
> >
> > And if that doesn't do it you need to better explain what you want.
>
> Dan:
>
> Thanks for the pointer to pagesize 0; at least I can see what is having
> the unexpected effect.
>
> Basically, I have a script which:
>
> -Truncates a reporting table
> -Populates a reporting table (via some generated scripts which are then
> called via '@')
> -Assigns some column header formats;
> -Selects data from the reporting table as a report.
>
> What I want to see is:
> -Headers, as coded;
> -No page breaks every 50 or so lines, where the headers are repeated;
> -no cr/lf between my comment (from dual) lines.
>
> What I'm finding is:
>
> No pagesize 0:
> -Headers work - good
> -Pagebreaks appear - bad
> -Comments are double-spaced - bad
>
> Pagesize 0 or any other value, like 200:
> -Headers do not appear - bad
> -No pagebreaks - good
> -Comments are single-spaced - good
>
> I just don't see why a pagesize setting should break column headers
> _and_ affect line spacing. But hey, I'm just a working stiff.
>
> I'll keep mucking with it. My script, for interest's sake, is:
>
> -- *************************************************************
> -- This script will return all database tables where rowcounts differ
> between
> -- the provided users
> -- This script is called with 2 parameters, the USER ids to compare.
>
> -- TRUNCATE REPORTING TABLE
> truncate table appdba.rowcounts;
>
> -- POPULATE REFERENCE COLUMNS IN REPORTING TABLE
> insert into appdba.rowcounts (owner, table_name) select owner,
> table_name from dba_tables where upper (owner) like 'APP%' and upper
> (table_name) like 'TB$_%' escape '$' order by owner, table_name;
>
> -- PREPARE TO GENERATE DATA COLLECTION SCRIPTS
> set line 2000
>
> -- GENERATE SCRIPT 1
> spool test1.sql
> select 'update rowcounts set SLICE1_COUNT = (select count (*) from ' ||
> OWNER || '.' || TABLE_NAME || ' where APP_USER = ''&1'' and APP_SLICE =
> 0) where OWNER = ' || '''' || OWNER || '''' || ' and table_name = ' ||
> '''' || TABLE_NAME || ''';' from rowcounts;
>
> -- GENERATE SCRIPT 2
> spool test2.sql
> select 'update rowcounts set SLICE2_COUNT = (select count (*) from ' ||
> OWNER || '.' || TABLE_NAME || ' where APP_USER = ''&2'' and APP_SLICE =
> 0) where OWNER = ' || '''' || OWNER || '''' || ' and table_name = ' ||
> '''' || TABLE_NAME || ''';' from rowcounts;
> spool off
>
> -- EXECUTE COLLECTION SCRIPTS
> @test1.sql
> @test2.sql
>
>
>
> -- PREPARE FOR REPORTING QUERY
> set line 200
> set echo off
> set verify off
> set feedback off
> set heading off
> set pagesize 0
>
>
> -- REPORT COLLECTED DATA
> spool report.rpt
> select '*********************' from dual;
> select 'TRAINING SLICE COUNTS' from dual;
> select '*********************' from dual;
> select ' ' from dual;
> select ' ' from dual;
> set heading on
> column owner format a10 heading "Owner"
> column table_name format a30 heading "Table"
> column slice1_count format 99999 heading "&1"
> column slice2_count format 99999 heading "&2"
> select owner, table_name, slice1_count, slice2_count from rowcounts
> where slice1_count <> slice2_count;
> spool off
>
> --------------------------------------
> --------------------------------------

I presume this is where you're getting your unwanted double-spacing?

select '*********************' from dual;
select 'TRAINING SLICE COUNTS' from dual;
select '*********************' from dual;

select ' ' from dual;
select ' ' from dual;

If so, try this:

select '*********************' || chr(10) ||,
          'TRAINING SLICE COUNTS'|| chr(10) ||
          '*********************' || chr(10)

   from dual;
select ' ' from dual;
select ' ' from dual; Received on Fri Aug 04 2006 - 06:59:16 CDT