Solved

Oracle 9 - Page Break in spooled file

Posted on 2011-02-11
12
1,166 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +2
12 Comments
 
LVL 74

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 35

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:Chakravarthi Ayyala
ID: 34873990
You already have "set pagesize 55;"
Is that not helping ?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 74

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:Chakravarthi Ayyala
ID: 34874112
oh! ok. Thanks.  Good catch.
0
 

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 77

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 74

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 35

Expert Comment

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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

724 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