Mostly Making oracle easy, mostly:
  • ORDS & SQL Developer Web 22.4.2 – Self Service Schemas
  • Generating DDL for your Oracle objects in SQL Developer Web
  • Hours wasted watching Grey’s Anatomy? Finding out with SQL!
  • Learning SQL using Spotify II: Functions, Group By, & Dates
  • Learning SQL with your own Spotify Streaming History data
  • Troubleshooting ORDS startup errors, the pool is invalid.
  • Create a ORDS REST API in a single PL/SQL call
  • Physical Properties in Oracle TABLE Liquibase changeSets
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 & SQL Developer Web 22.4.2 – Self Service Schemas

January 31, 2023

Generating DDL for your Oracle objects in SQL Developer Web

January 26, 2023

Physical Properties in Oracle TABLE Liquibase changeSets

January 4, 2023

Run Liquibase updates for a specific schema with SQLcl

December 19, 2022

How to use JSON, XML, YAML Liquibase changeSets in SQLcl

December 13, 2022

Cloud World 2022: Database Tools Tips & Tricks Video

November 29, 2022

72 Comments

  1. Avatar
    Philip 6 months 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 6 months 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 1,000+ posts
  • What it is I do here

    I'm a product manager at Oracle, and
    I publish weekly - tips and tricks for Oracle SQL Developer, SQLcl, SQL Developer Data Modeler, and Oracle REST Data Services.

    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
    • Top 10 Preferences to Tweak when using SQL Developer
    • Formatting Query Results to CSV in Oracle SQL Developer
    • How To: Generate an ERD for Selected Tables in SQL Developer
    • Resetting Your Oracle User Password with SQL Developer
    • Die! Or How to Cancel Queries in Oracle SQL Developer
  • Enter your email address
  • Mostly Making Oracle Easy, Mostly

    Privacy Policy 

  • Mastodon
  • 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, 2021

Top
ThatJeffSmith