Skip to Main Content
  • Questions
  • Is the 'set newpage 0' command supported in SQLcl? Not resulting in formfeeds between pages with our installed SQLcl version.

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question.

Asked: January 24, 2022 - 5:42 pm UTC

Last updated: February 03, 2022 - 3:20 am UTC

Version: SQLcl version 21.1

Viewed 1000+ times

You Asked

Database version: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQLcl: Release 21.1 Production
SQL*Plus: Release 12.2.0.1.0 Production
Installed on Red Hat Enterprise Linux Server release 7.9

Script:
set echo off
set feedback on
set termout on
set verify off
set heading on
set trimspool on
set linesize 177
set pagesize 58

set sqlpluscompatibility 12.2.0
set newpage 0


REM hr.employees is valid on the LiveSQL database, but set command not 
select * from hr.employees;



Results:
This script results in output with formfeed (^L or CHR(12)) in spooled output when run in SQL*Plus, but not in SQLcl.





and Chris said...

Check what the SQLFORMAT is set to. If this is ANSICONSOLE it overrides many of the SQL*Plus settings.

If it is set, clear it by running:

set sqlformat


For example:

SQL> sho sqlformat
SQL Format : ansiconsole
SQL> set pages 3
SQL> set newpage 0
SQL> select employee_id, first_name from hr.employees
  2* fetch  first 6 rows only;

   EMPLOYEE_ID    FIRST_NAME
______________ _____________
           100 Steven
           101 Neena
           102 Lex

   EMPLOYEE_ID    FIRST_NAME
______________ _____________
           103 Alexander
           104 Bruce
           105 David


6 rows selected.

SQL> set sqlformat
SQL Format Cleared
SQL> select employee_id, first_name from hr.employees
  2* fetch  first 6 rows only;
EMPLOYEE_ID FIRST_NAME
----------- --------------------
        100 Steven
        101 Neena
EMPLOYEE_ID FIRST_NAME
----------- --------------------
        102 Lex
        103 Alexander
EMPLOYEE_ID FIRST_NAME
----------- --------------------
        104 Bruce
        105 David

6 rows selected.

Rating

  (3 ratings)

Comments

thanks for response

Anna Bachman, February 01, 2022 - 11:03 pm UTC

Thanks for the response.

I tried your suggestions:
> show sqlformat
SQL Format : Default

> set sqlformat
SQL Format Cleared

> select ....


Same results, i.e. the column names are printed every 58 rows, but no page break character
Chris Saxon
February 02, 2022 - 8:53 am UTC

What exactly are you expecting to appear that doesn't? How does this differ to SQL*Plus?

expect a formfeed character in SQLCl (as I do in SQL*Plus)

Anna Bachman, February 02, 2022 - 3:06 pm UTC

Hi again,

When I use SQL*Plus, I see a formfeed character at the bottom of each page. Not so with running the same query in SQLCl.

As mentioned in my initial Results, I am wondering why there is a difference between the output of a query in SQL*Plus vs SQLCl. Perhaps a configuration or set command?

from initial post...
Results: This script results in output with formfeed (^L or CHR(12)) in spooled output when run in SQL*Plus, but not in SQLcl.
Connor McDonald
February 03, 2022 - 3:20 am UTC

We'll check with the SQLcl team and report back

set classic on

Ramon Caballero, February 02, 2022 - 9:07 pm UTC

Have you tried what is mentioned here?: https://community.oracle.com/tech/developers/discussion/4090029/sqlcl-spooled-file-output-issue

Normally when SQLcl vs SQL*Plus behavior differs, we recommend...
set classic on

It is from 2017 but they might still accept such setting
Connor McDonald
February 03, 2022 - 3:20 am UTC

Setting is accepted but does not change things. We'll ask the SQLcl team internally