SQlPlus - Anything better?

Posted on 2001-08-22
Medium Priority
Last Modified: 2008-03-17

I have been using Oracles SQLPlus that came with the Oracle 8i client apps.

I have found it to be a bit lacking for my tastes.

I don't like that I can't edit sql statements short of backspacing them out.

I also don't SQLPlus wrapping long tables.  I know that wrapping can be
turned off and the buffer size increased, but you can only do that to a
certain extent.

Does Oracle make another, similar product that will allow me to edit sql
statements and that will NOT wrap tables?

Is there a third party product that will allow me to connect to Oracle,
do my sql and return me tables in a nice format?

Thanks in advance.

If you can, please email me

Question by:Steve34
  • 5
  • 3
  • 2
  • +8

Expert Comment

ID: 6413725
You might look into a third party product called 'PL/SQL developer'.  You can find more information at:
LVL 27

Expert Comment

ID: 6413735
or toad

there is also the sql-navigator available
LVL 35

Expert Comment

by:Mark Geerlings
ID: 6413783
Please do not ask for a personal e-mail response.  That violates the rules of this site, and it makes the responses (if any) unavailable to other users of the site.

When people post comments and/or proposed answers here, the site will notify you by e-mail (unless you turned off the e-mail notification).

Regarding SQL*Plus - most people who work with Oracle very long get used to its quirks and idiosyncracies.  It does support limit editing features beyond backspacing text out (insert line, delete line, append, change, copy and paste, etc.) but I recommend that you use a good text editor in addition to SQL*Plus.  I like WinEdit from Wilson WindowWare (see: http://www.zdnet.com/pcmag/issues/1421/pcm00149.htm).

Set your working directory for SQL*Plus and your text editor to the same directory, then compose or edit your *.SQL files in your editor, and execute them in SQL*Plus by simply:

For testing PL\SQL, you will want to first:
set serveroutput on;
in order to see the output from the dbms_output.put_line procedure you can use in PL\SQL blocks.
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.


Expert Comment

ID: 6413843

While I might be a traditionalist/dinosaur, SQLPLUS does have options you've mentioned.

You can replace single words anywhere in a line of text, wrapping can be stopped, buffers and linesizes increased.

and simply typing
will bring your statement up into a notepad window.

You can go for something like toad, but you're going to have to learn a very large tool as opposed to learning a very basic one if you actually look a bit deeper into SQL*PLUS.

I have felt the same as you w.r.t. SQL*PLUS in the past.  Then I saw my DBA using it *properly* and realised that there is far more there than is readily apparent. (I was flabbergasted actually at the speed she used it to create scripts, fix typos, change characters, variables, etc.

But if you're determined, you can download TOADFREE from quest.com.  I've been using TOAD for about 2 or three years now.  It's good, but it is discouraged in terms of actual development...You can use TOAD to interrogate tables so they appear as a spreadsheet.  Add filters (WHERE clauses), sorts (Order by clauses), etc.

It is also good for looking at other objects such as views, synonyms, sequences, procedures, constraints, links, java, triggers and indexes.

Also can see grants, table scripts, references...and the list goes on...

But all the same, I currently have SQL*PLUS open on my desktop every day (but TOAD is usually there too).



Expert Comment

ID: 6413860
To follow up from Mark's comments,

another decent editor is UltraEdit...you can get a shareware version I believe...but licensed is of course recommended.

Also, a small typo on Mark's part:

execute using:

(the at sign, not ampersand...)


Accepted Solution

jtrifts earned 200 total points
ID: 6413873
Whenever I start SQL*PLUS I always type the following:
set serverout on (or set serveroutput on)
set buffer 1000
set linesize 1000

This gets around the problem of wrapping tables as long as the output is not greater than 1000 characters.  Set it larger if you are regulrly dealing with larger vals...


Expert Comment

ID: 6413915
I highly recommend TOAD ( www.toadsoft.com ).  I use it daily and find it to be an invaluable tool.  It can do everything SQL*Plus can do and much, much more.

Expert Comment

ID: 6413953
ezsql is pretty good
you can find a review of it on the oracle website

... but as a contractor, everywhere I go has a different favourite tool and some clients don't like you to download and use your own favourite ( which is fair enough ), so it's always good to be fluent in SQL*Plus ( and vi if you use Unix ) just to be on the safe side

but if you're developing at home, then try out all the tools that have been mentioned in answer to your original query

Expert Comment

ID: 6413984
or you can have a look at a whole range of third party tools at:




Expert Comment

ID: 6414177
I use sqlplus, the dos version.
I have set up the dos window so that it has a command buffer and a screen buffer and handles cutting and pasting in a mnner that I like.
The shortcut that invokes sqlplus specifies a start folder where I keep all my scripts.
The first thing I do after starting sqlplus is to run my setup file, @setup, which sets the pagesize, the linesize, formats columns etc.

I also use TextPad (www.textpad.com) and gave up on notepad years ago.
If the script is vaguely involved then I will write the script in TextPad and then just @ the script from sqlplus.

If you have Oracle Enterprise Manager you also receive SQLPlus Worksheet, but I prefer sticking to sqlplus.


Author Comment

ID: 6414505
I didn't know there was more to SQLPlus then the minimum of features you can see from the menus in the windows version.

I found a link with a free online book on how to use it:

I think I will read it as well as checking out  toad, now that I know there is a free version.

I got several answers clueing me into the fact that sqlplus has more to it then meets the eye.  I also got several posts telling me about posts.

I need to decide who to give the points to.

What is a fair criteria given that I will be giving toad a try and sqlplus a second try?

LVL 35

Expert Comment

by:Mark Geerlings
ID: 6414727
Thai is up to you.  One option is to accept a comment as the answer, and if you want to give points to another person as well, you can create another "question" with a title like "Points for [login name]", then accept a response to that question from that person.

Or, you can just pick the response here that helped you the most.  It would be nice if the site allowed us to split the points to multiple people, but that is probably complicated to support.

Expert Comment

ID: 6414791
Just remember,  SQL*Plus is a Licensable Product.
Licensable Product means you must pay real dollars to use this product.
My last quote was $600 US per seat plus support and maintenance.
I'm almost sure all the third party products are cheaper than SQL*Plus.
Just a word of caution, before you distribute it to all your developers.

Expert Comment

ID: 6414859
set serveroutput on
columns xyz format a30
set linesize


commands like this can go in your glogin.sql or login.sql script and will be effective every time.  (glogin gets executed from any directory-- global login, login gets executed from your working directory.)

SQL*Plus comes shipped with Oracle and is Free.  (well you pay for oracle so I betchya its covered).  BTW it would be pretty hard to operate Oracle without SQL*Plus, that's why it is an integral part of the package.

Author Comment

ID: 6414919

I know sqlplus costs money.....but it doesn't cost *me* money:).

Just like myperz wrote, anyplace that I go that has oracle installed also has sqlplus.

I'm going to check into the free toad also.

I already schlep my own text editor with me from job to job ( visual slick edit )

Expert Comment

ID: 6421894
Hi steve,

re: points distribution:
As Mark said you can accept one and give additional points to others just by posting another question "points for..."

Some people (though generally when there are more points at stake) will post a request to the moderators in the community support area noting that they want to split the points.

For example: a question worth 300 to be split among three.
The community support moderator would reduce the 300 point question down to 100 points.  Then one answer comment could be accepted as answer.  Then two additional 100 point questions could be posted as "points for..."



Author Comment

ID: 6429810
I  decided to stick with sql+ and yours was the first answer to help me with this so you get the points.

I apologize to everyone else.

Expert Comment

ID: 6436578
The database painter in PowerBuilder is my favorite tool for exploring/constructing data and crafting SQL.  The PB painter is much, much richer and much faster than Oracle's Query Builder.  When developing Oracle Forms data blocks I craft the SQL in the PB painter then paste it into the Forms data block wizard.  That gives me the best of both environments. But you have to have PB.

Featured Post

[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This article will show a step by step guide on how to mask column values in Oracle 12c using DBMS_REDACT full redaction option. This option is available on licensed Oracle Enterprise edition as part of Oracle's Advanced Security.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses
Course of the Month4 days, 7 hours left to enroll

600 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question