Clear Screen in SQL*Plus

Question

I'm running the following report but getting an error

/* Simple table formatting */

clear screen;
accept Report_File char prompt 'Enter a file name for summary report ';

/*Set up column headers*/
col StoreCode format A8 heading 'Store Code';
col DESCRIPTION format A8 heading 'Item Description';
col PRICE format $999999.99 heading 'Price';
col QUANTITY format 999 heading 'Quantity';
col (Price*Quantity) format $999999.99 heading 'Value';

/*Format and title pages */

set Pause off;
set Feedback off;
set Space 6;
set newpage 2;
set pagesize 54;
set linesize 200;
set underline =;
title center 'Current Stock Value by Store' skip 2 left -
            'prepared by Jason Kemeys' &Report_Officer right -
            &Todays_Date skip4;
btitle center format 999 SQL.PNO;

/* Set breaks and computes */

break on StoreCode skip 2 on SuppCode skip 1 on Report;
compute sum of (Price*Quantity) on StoreCode;
compute sum of (Price*Quantity) on Report;

/*Select data & send to file*/

spool &Report_File;

select StoreCode, Description, Quantity, Price, (Price*Quantity)
from Stocks
order by StoreCode;

spool off;

/* Clear all settings */

clear breaks;
clear columns;
clear computes;
set Pause on;

Just need to know why its showing the error and how to get it running; first time doing a report in SQL.

This is the error I'm getting

clear screen; * ERROR at line 2: ORA-00900: invalid SQL statement

Answer 1

I suspect this is dependent on the version of Oracle you are using.

This should work in version 11.2 but to quote from the 10g documentation:

CLEAR SCREEN is not available in SQL*Plus.

The same note isn't there in the 11.1 documentation, which would imply that you're using Oracle 10g or earlier. If this supposition is true then there's little that you can do.

It's possible that you can utilise the host command in SQL*Plus to run cls, if you're using Windows, or clear, if you're using Linux, but I'm not certain that it would have exactly the same effect. If it were possible it would simply be:

host cls

host runs an operating system command from SQL*Plus, and so will appear to clear the screen.

Answer 2

cl scr is the command used to clear screen in SQL.