Solved

Oracle 9 - Page Break in spooled file

Posted on 2011-02-11
12
1,155 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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Oracle SQL Select unique values from two columns 4 82
Row_number in SQL 6 45
minium over 4 numeric columns for each row in oracle 2 39
plsql job on oracle 18 79
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
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…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

696 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