Solved

SQlPlus - Anything better?

Posted on 2001-08-22
18
1,202 Views
Last Modified: 2008-03-17
Hi;

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

Steve
stevesusenet@yahoo.com
0
Comment
Question by:Steve34
  • 5
  • 3
  • 2
  • +8
18 Comments
 

Expert Comment

by:cz0763
Comment Utility
You might look into a third party product called 'PL/SQL developer'.  You can find more information at:
http://www.allroundautomations.nl
0
 
LVL 27

Expert Comment

by:kretzschmar
Comment Utility
or toad
from
www.quest.com

there is also the sql-navigator available
0
 
LVL 34

Expert Comment

by:Mark Geerlings
Comment Utility
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:
&[filename]

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.
0
 
LVL 4

Expert Comment

by:jtrifts
Comment Utility
Steve,

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
ed
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).

Regards,

JT
0
 
LVL 4

Expert Comment

by:jtrifts
Comment Utility
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:
@[filename]

(the at sign, not ampersand...)

JT
0
 
LVL 4

Accepted Solution

by:
jtrifts earned 50 total points
Comment Utility
Also...
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...

JT
0
 
LVL 5

Expert Comment

by:ser6398
Comment Utility
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.
0
 
LVL 3

Expert Comment

by:myerpz
Comment Utility
ezsql is pretty good
you can find a review of it on the oracle website
"http://www.orafaq.com/tools/ezsql/ezsql.htm"

... 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
0
 
LVL 4

Expert Comment

by:jtrifts
Comment Utility
or you can have a look at a whole range of third party tools at:

http://www.orafaq.com/tools/index.htm

regards,

JT
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 2

Expert Comment

by:racher
Comment Utility
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.

0
 

Author Comment

by:Steve34
Comment Utility
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:
http://www.oradoc.com/ora816/sqlplus.816/index.htm

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?

Steve
0
 
LVL 34

Expert Comment

by:Mark Geerlings
Comment Utility
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.
0
 
LVL 2

Expert Comment

by:banicki
Comment Utility
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.
Banic
0
 
LVL 1

Expert Comment

by:joelpatt
Comment Utility
set serveroutput on
columns xyz format a30
set linesize

etc.

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.
0
 

Author Comment

by:Steve34
Comment Utility
Banicki;

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 )
0
 
LVL 4

Expert Comment

by:jtrifts
Comment Utility
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..."

Regards,

JT
0
 

Author Comment

by:Steve34
Comment Utility
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.
0
 

Expert Comment

by:rdavisandrulis
Comment Utility
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.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video shows how to recover a database from a user managed backup

743 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now