?
Solved

Oracle 9 - Page Break in spooled file

Posted on 2011-02-11
12
Medium Priority
?
1,176 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

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.
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

762 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