Re: Quick sqlplus cr/lf question


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;

.

Relevant Pages

  • Re: Quick sqlplus cr/lf question
    ... Thanks for the pointer to pagesize 0; at least I can see what is having ... -Populates a reporting table (via some generated scripts which are then ... My script, for interest's sake, is: ... insert into appdba.rowcounts (owner, table_name) select owner, ...
    (comp.databases.oracle.server)
  • Re: Microsoft - Windows XP SP1 DX9.00 is not reporting KB839643 via WMI
    ... > It's not even really a 'custom' script in how it pulls the information. ... and this hotfix (unlike all other DX hotfixes) is ... > reporting their hotfixes via WMI, and now this one doesn't work as the ... >> like to submit a request for support using your phone, ...
    (microsoft.public.windowsupdate)
  • Re: DTS automate Reporting Services
    ... >We need to automate from DTS the execution of REPORTING SERVICES to run ... Probably the easiest way would be to use the RS script utility ... global community for SQL Server professionals ...
    (microsoft.public.sqlserver.dts)
  • Re: Unidentified Index Problem on some servers
    ... if you want the bugs now or the bugs later. ... script, ... >Also error reporting can be too high, ... >shouldn't die when encountering a notice, ...
    (comp.lang.php)