SQlPlus - Anything better?

Posted on 2001-08-22
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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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:

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.
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


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  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 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 50 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 ( ).  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 ( 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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Via a live example, show how to take different types of Oracle backups using RMAN.

735 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