Mostly Making oracle easy, mostly:
  • Join me on the EMEA Oracle Community 2023 Tour!
  • cURL vs Python: loading data from a file via Oracle REST API
  • ORDS 101: Enabling Oracle Schemas for HTTPS/REST
  • Developers, please share your experience via our SURVEY
  • Getting plans for your queries in Oracle SQL Developer Web
  • Oracle Database Tools at Cloud World 2023
  • What is going on with Social Media, for this ‘Oracle guy’
  • Disabling the Landing page in ORDS 23.2 and higher
Search for:
  • Twitter
  • YouTube
  • LinkedIn
ThatJeffSmith ThatJeffSmith
  • SQL Developer
  • SQL Developer Web
  • REST APIs
  • Data Modeling
  • Ask A Question
  • About
In SQL Developer

How to Format Your Script Output in SQL Developer…By Default

May 19, 2015 2 Mins Read

Support for SQL*Plus functionality in SQL Developer has gotten better from release to release. In version 4.1 we took a giant leap forward, adding support for many SQL*Plus commands, and coming much closer to emulating script execution behavior.

One example, we added support for BREAK and COMPUTE commands.

Access to these commands and formatting is available when executing one or more statements via F5.
Access to these commands and formatting is available when executing one or more statements via F5.

Another change was adopting several of the SQL*Plus defaults, including pagesize.

The number of lines to print on a 'page' in SQL*Plus
The number of lines to print on a ‘page’ in SQL*Plus

It’s not just the number of rows on a query, you also count blank lines, the column headers, etc.

So querying for 15 rows on a pagesize of 14 gives me more than one page.

This is how SQL Developer version 4.1 will behave now as well.

Don't like the defaults? Change them!
Don’t like the defaults? Change them!

Changing the Defaults

It’s very easy. You can use the same mechanism that SQL*Plus users have been using for decades – a ‘logon’ SQL script.

This script will be executed whenever SQL Developer creates an Oracle Database connection.
This script will be executed whenever SQL Developer creates an Oracle Database connection.

Apply preferences. Disconnect. Connect. Open Worksheet, run again.

Voila! My entire resultset fits on an entire 'page' now.
Voila! My entire resultset fits on an entire ‘page’ now.

Another ‘Trick’

Some of you don’t like having to toggle on DBMS Output support. So I’ve shown you how to get this output by default. It’s using the same mechanism as above.

SET SERVEROUTPUT ON

Just show me all the settings, so I know what’s what

Ok, use ‘SHOW ALL’

pagesize5

You can change them for your session at any time using the SET command in a worksheet, or you can change them at logon using the script and preference method.

formatloginpagesizesqldevsqlplus
thatjeffsmith
Author thatjeffsmith

I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

  • Website
Prev Post

SQL Developer Data Modeler, Pending Changes & Versioning

May 14, 2015 2 Mins Read
Next Post

Quick Tip: PLSQL Variable Suggestions

May 28, 2015 1 Min Read

Related Posts

ORDS 101: Enabling Oracle Schemas for HTTPS/REST

September 8, 2023

Getting plans for your queries in Oracle SQL Developer Web

August 31, 2023

Oracle Database Tools at Cloud World 2023

August 28, 2023

Diagramming JSON in SQL Developer Web Data Modeler

August 14, 2023

How to maximize your command line experience with Oracle

August 1, 2023

Accessing SQL Developer Web in Oracle Autonomous Database

July 26, 2023

72 Comments

  1. Avatar
    Philip 1 year ago Reply

    Hi Jeff, we’ve finally started using SQLDev over PLSQL Developer. There are a few irritations can you help with one or two please?
    How do we preserve white space?
    Can we have a “dark mode”?
    Please can we have a “duplicate” function on a SQL window that is open so we do not need to create one and choose a connection?

    • thatjeffsmith
      thatjeffsmith Post Author 1 year ago

      Dark mode, maybe, kinda, not really, no.

      Maybe – if you want to do something radical.
      Kinda – on the code editors, yes.
      No – JDeveloper, who provides our underlying framework doesn’t support true dark mode, so we can’t either.

      However, we’re building a brand new SQL Developer on a new platform, and it WILL support Dark Mode.

      Preserving white space…do you mean when using the formatter?

      Duplicate SQL Window, you have that now, hit the Worksheet button in the Worksheet toolbar. You’ll get a new connection on the same database in a new SQL Worksheet.

Previous

Write A Comment Cancel Reply

  • Search this site
  • Looking for help?

    I'm a product manager for Oracle Database,
    publishing tips and tricks for SQL Developer, SQLcl, Data Modeler, and REST Data Services (ORDS)

    Twitter YouTube LinkedIn

  • Your Favorite Posts
    • How to Import from Excel to Oracle with SQL Developer
    • Keyboard Shortcuts in Oracle SQL Developer
    • Enabling DBMS_OUTPUT by default in SQL Developer
    • How To: Generate an ERD for Selected Tables in SQL Developer
    • Top 10 Preferences to Tweak when using SQL Developer
    • Formatting Query Results to CSV in Oracle SQL Developer
    • Resetting Your Oracle User Password with SQL Developer
    • Die! Or How to Cancel Queries in Oracle SQL Developer
  • Don’t miss a single post! Enter e-mail to subscribe.
  • Mostly Making Oracle Easy, Mostly

    Privacy Policy 

  • Twitter
  • YouTube
  • LinkedIn
  • Download SQL Developer
  • Developer Tools Message Boards

The views expressed on this blog are my own and do not necessarily reflect the views of Oracle. © Jeff Smith and ThatJeffSmith, 2023

Top
ThatJeffSmith