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
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.