Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Oracle 9 - Page Break in spooled file

Posted on 2011-02-11
12
Medium Priority
?
1,185 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 1336 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 664 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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
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 1336 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
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.

610 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