Solved

Oracle 9 - Page Break in spooled file

Posted on 2011-02-11
12
1,120 Views
Last Modified: 2013-12-19
I have a report that is being spooled out to a text file from a sql script.  I need to insert page breaks every 55 Lines.  Please advise how I can add them.  Thank you,
set head off;
set pagesize 55;
set linesize 80;
set tab off;
set feedback off;
set termout off;
set verify off;
set newpage none;
set echo off;
column Logo format A8 truncated
COLUMN curdate NEW_VALUE report_date 
SELECT TO_CHAR(SYSDATE,'dd-Mon-yyyy') curdate
FROM DUAL; 
TTITLE LEFT 'HPECOM/WEB_SKU_STATUS      STERLING JEWELERS, INC.   ' RIGHT report_date "     PAGE" format 999 sql.pno SKIP 1 COL 28 '&1 SITE SKU STATUS'
BTITLE CENTER 'Confidential Property of Sterling Inc.'
spool /var/ecom/skus/web_sku_status.dat
SELECT substr(UPPER(S.DIRECTORY),1,13) as Logo,
       decode(CE.BUYABLE,1,' BUYABLE         ',' NOT BUYABLE     ') as Status,
       to_char(CE.CATENTRY_ID,'fm099999999999') as Sku
FROM ECOMADMIN.CATENTRY CE, 
ECOMADMIN.OFFER O, 
ECOMADMIN.STORECENT SC,
ECOMADMIN.STORE S 
  WHERE CE.CATENTRY_ID = O.CATENTRY_ID 
   and CE.CATENTRY_ID = SC.CATENTRY_ID 
   and SC.CATENTRY_ID = O.CATENTRY_ID 
   and SC.STOREENT_ID = S.STORE_ID
   and SC.STOREENT_ID in (SELECT STORE_ID FROM ECOMADMIN.STORE WHERE DIRECTORY =  initcap('&1'))
   and ( CE.MARKFORDELETE = null or CE.MARKFORDELETE <> 1 ) 
   and O.PUBLISHED = 1
   and O.TRADEPOSCN_ID =  (Case When initcap('&1') = 'Kay' Then '4000000000000000051' Else '1' End)
   and ( CE.CATENTTYPE_ID = 'ProductBean' or CE.CATENTTYPE_ID= 'PackageBean' or CE.CATENTTYPE_ID= 'BundleBean' ) 
   and current_timestamp <= o.enddate 
   and current_timestamp >= o.startdate
    order by CE.BUYABLE,
          ce.catentry_id;
spool off;
exit;

Open in new window

0
Comment
Question by:ddpleasant
  • 3
  • 2
  • 2
  • +2
12 Comments
 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 334 total points
ID: 34873939
remove

set newpage none

that causes "nothing" to be done when the pagesize limit is reached
0
 
LVL 34

Assisted Solution

by:johnsone
johnsone earned 166 total points
ID: 34873974
I believe your problem is this:

set newpage none;

From the documentation, that would prevent the page break from being printed:

If you set NEWPAGE to NONE, SQL*Plus does not print a blank line or formfeed between the report pages.
0
 
LVL 8

Expert Comment

by:ReliableDBA
ID: 34873990
You already have "set pagesize 55;"
Is that not helping ?
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 34874086
ReliableDBA

the pagesize is the right limiting definition

 but the newpage none  effectively disables it
0
 
LVL 8

Expert Comment

by:ReliableDBA
ID: 34874112
oh! ok. Thanks.  Good catch.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:ddpleasant
ID: 34874398
Removing "set newpage none" causes a blank line at the top of the page.  This messes you my report being ported propperly.  Also no page break is inserted, just a blankl line.

as:
                                      Confidential Property of *****  Inc.                    

HPECOM/WEB_SKU_STATUS      *****, INC.      11-Feb-2011     PAGE   2
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34874435
It actually inserts a ctrl-L (ascii 12).  

If you look at the file in hex mode or something that shows control characters you'll see it.
0
 
LVL 73

Accepted Solution

by:
sdstuber earned 334 total points
ID: 34874519
if you don't want the blank line

set newpage 0

0
 

Author Comment

by:ddpleasant
ID: 34874565
As explained above, newpage none  effectively disables pagesize
0
 
LVL 34

Expert Comment

by:johnsone
ID: 34874815
Not NONE, 0.  Those are 2 different things.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now