Home » SQL & PL/SQL » Client Tools » Suppressing Blank Lines (Oracle Database 10g)
Suppressing Blank Lines [message #414510] Wed, 22 July 2009 07:05 Go to next message
Mauragon
Messages: 20
Registered: February 2007
Location: Philippines
Junior Member
Hello,

I'm creating a script that spools records from HRMS. I was wondering if there's a way to suppress the blank lines it produce because of the 5 sql statements then at the PL/SQL block i'm using dbms_output.put_line to show control totals so on the output there's preceeding 5 blank lines. The blank lines are not spooled though but I want the control totals to be neatly formatted.

Thanks in advance.

This is the initial part of the code :

SET ECHO OFF
SET FEEDBACK OFF
SET HEADING OFF
SET LINESIZE 1000
SET NEWPAGE NONE
SET PAGESIZE 0
SET SERVEROUTPUT ON
SET SHOWMODE OFF
SET SPACE 0
SET TERMOUT ON
SET TRIMOUT ON
SET TRIMSPOOL ON
SET VERIFY OFF
SET WRAP OFF

--------------------------------------------------------------------------------

COLUMN end_date NOPRINT NEW_VALUE p_end_date

COLUMN directory NOPRINT NEW_VALUE p_directory

COLUMN filename_peo NOPRINT NEW_VALUE p_filename_peo

COLUMN filename_asg NOPRINT NEW_VALUE p_filename_asg

COLUMN filename_svc NOPRINT NEW_VALUE p_filename_svc

SELECT TO_CHAR(end_date,'YYYYMMDD') end_date
FROM per_time_periods
WHERE time_period_id = &1
;

SELECT '&2' directory
FROM DUAL
;

SELECT '&p_directory' || 'PEOPLE_' || '&p_end_date' || '.csv'
filename_peo
FROM DUAL
;

SELECT '&p_directory' || 'ASSIGNMENT_' || '&p_end_date' || '.csv'
filename_asg
FROM DUAL
;

SELECT '&p_directory' || 'SERVICE_' || '&p_end_date' || '.csv'
filename_svc
FROM DUAL
;

<PL/SQL Code Proceeds ... >


Re: Suppressing Blank Lines [message #414519 is a reply to message #414510] Wed, 22 July 2009 07:29 Go to previous messageGo to next message
Michel Cadot
Messages: 59282
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
The blank lines are not spooled though

So what is the problem?
It is SQL*Plus behaviour.

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Use code tags.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Regards
Michel
Re: Suppressing Blank Lines [message #414522 is a reply to message #414510] Wed, 22 July 2009 07:36 Go to previous messageGo to next message
Mauragon
Messages: 20
Registered: February 2007
Location: Philippines
Junior Member
Sorry about my post.

The output is like this :

<blank line>
<blank line>
<blank line>
<blank line>
<blank line>
Control totals :
records read : nnn
records updated : nnn
records rejected : nnn

I want somehow to suppress the blank lines
Anyway if it's Oracle's behavior then there's nothing I can do. Thanks for the reply
Re: Suppressing Blank Lines [message #414528 is a reply to message #414522] Wed, 22 July 2009 07:58 Go to previous message
Michel Cadot
Messages: 59282
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It's not Oracle behaviour it is SQL*Plus one and as you said it is not the spool file.

Regards
Michel
Previous Topic: sql developer 1.5.4
Next Topic: regarding connecting database in 10g on linux
Goto Forum:
  


Current Time: Wed Oct 01 04:09:26 CDT 2014

Total time taken to generate the page: 0.06778 seconds