sqlplus gui will be deprecated in next release
Monish, March 30, 2003 - 1:27 pm UTC
Hi Tom,
If sqlplus gui is being deprecated in next release, what will be the new user interface to interact with sql.
Thanks.
March 30, 2003 - 2:46 pm UTC
there is and will be character mode sqlplus which IMHO was/is the best interface. I never used that "gui" thing in windows when forced to run windows -- always the dos command line version.
there is "isql" plus as well, the browser thing (which I never use)...
sql+ - no more?
A reader, March 30, 2003 - 6:27 pm UTC
Tom -
1. what is IMHO?
2. Do you mean that on windows, the sql+ gui will not ship with the 10i (if so -- why?? -- what's suddenly wrong??) but that the cmd version will remain?
March 30, 2003 - 8:24 pm UTC
1) imho = in my humble opinion
2) sqlplusw.exe, the single abberation, deviation from cross platform "sameness" may or may not ship -- it is "deprecated". isqlplus -- the browser version -- is supported in addition to sqlplus -- the normal command line version.
Emacs + sqlplus = all I would ever need
A reader, March 31, 2003 - 2:31 am UTC
Running sqlplus in Emacs shell mode or sql-oracle mode is not only efficient, powerful and flexible, but also a joy.
A reader, March 31, 2003 - 4:28 am UTC
yuck! emacs is horrible! who cares if its gnu.
vi (or vim) is the only way, man.
robert, March 31, 2003 - 3:02 pm UTC
>> there is "isql" plus as well, the browser thing (which I >> never use)...
oh, I'd like to give it a go..never knew about it.
Tom, you know how I can try it out ?
either Win client or telnet.
Thanks
More memory with char mode sqlplus
Tim, June 19, 2003 - 5:23 pm UTC
Hi Tom,
You have mentioned couple of times that char mode sqlplus is much better than gui sqlplus and it is your favourite interface. Plus, now that it is going to be deprecated, I wanted to get used to char mode sqlplus.
But I saw that char mode takes a lot of memory. Also, the copy-paste convenience is lost in char mode and unlike gui sqlplus I cannot set a default directory to work with char mode sqlplus.
Is there a way around for the above problems ? I have W2K OS.
TIA !!!
June 20, 2003 - 4:14 pm UTC
what do you mean "it takes alot of memory".
I copied and pasted all of the time in DOS windows. I liked it even BETTER cause I could select a SQUARE region.
Alt-space/e/p (alt-space then edit then paste, once you do alt-space/e/p a time or time, it almost becomes a keystroke you can do it so fast).
then there is an option hidden in some user friendy menu to make it so you can "copy on select" -- it'll let you select text and when you hit "enter" the selected text is placed in the copy buffer.
Tim, June 20, 2003 - 5:54 pm UTC
I mean "it takes alot of memory". If I see in windows task manager, the mem usage shown by sqlplus.exe is much higher than sqlplusw.exe ! Do you know why ???
And by the copy-paste convenience (not feature) being lost I meant that in gui sqlplus, one can simply use ctrl-c/ctrl-v to cut-paste OR just mark the statement and while holding the left mouse click, right click to paste.
TIA !!!
June 20, 2003 - 6:15 pm UTC
what is much higher?
and is it "too much"
and is task manager really at all accurate (i mean, just iconify all of your windows -- wow, you have a ton more memory -- or do you?) I don't trust task manager.
and like I said, you'll find the "select text, hit enter" instead of ctl-c and "alt-space/e/p" to be as handy as ctl-c/ctl-v. Once you do it 10 times, it'll be like you've always been doing it.
Tim, June 20, 2003 - 6:48 pm UTC
Yes, I agree with you. Once I do that copy-paste couple of times, I will get used to that feature even :)
And I am sorry with the memory part -- if you have an window open, then the memory shown is more than the memory shown for other window which is minimized. So when I was looking the memory usage of sqlplus.exe, the sqlplusw.exe windows were minimized. But if I minimize all the windows and see the memory usage, they all show the same -- rather sqlplus.exe consumes less memory :)
Sorry once again for all the trouble Tom !!
A reader, June 20, 2003 - 7:59 pm UTC
Everybody is entitled to their own views. You might find it easier to use the command-line version. But I find the gui version somuch easier. And I am sure there are many out there who feel the same way.
I am no great Microsoft lover. But I just don't understand the rationale of de-supporting a client tool designed to exploit the features of the most popular desktop platform!!
Is Oracle trying to prove something to MS??
June 21, 2003 - 10:11 am UTC
we are making it so that we are the same everywhere.
sqlplusw.exe does not fit that particular niche, it was "extra special for that os"
there is
o sql worksheet in oem
o isqlplus
and a new thing i cannot yet talk about....
that are the same on all platforms and supportable.
DOS based SQL*Plus
Kamal Kishore, June 20, 2003 - 9:31 pm UTC
If you click on the "Command Prompt" system menu (click the icon that appears on the left on the title bar) and click on properties, it will show you "Command Prompt" properties. From the many tabs available, click on the "Options". under "Edit Options", select "QuickEdit Mode" and "Insert Mode", you can also set buffer size.
Once you do this, then for doing cut-n-paste, all you need to do is select the text you want to copy and click right mouse button. The text is copied to the clipboard (this is now same as selecting the text and pressing Ctrl-C in GUI version, but I find this much much better and faster).
When you want to paste, just put your mouse cursor over the "Command Prompt" window and press right mouse button. The text will be pasted at the current cursor position.
This is so much easier than the GUI version. (no need to do Ctrl-C and Ctrl-V anymore).
This has proved so easy for me, I do not even care about the GUI SQL*Plus (as if it does not exists for me at all).
a trick in sqlplusw.exe
W.Hwa Chan, June 21, 2003 - 10:14 am UTC
in sqlplusw.exe, if you wanna copy something, you don't
need anything more than a mouse with 2 buttons. It's simple,
hold left button to select what you want, then click on
the right button (keep holding the left button while you
clicking on the right button!),
the text you'vd just selected
will be pasted right after the next 'SQL>' prompt.
you can also select SQUARE in this way easily. :)
June 21, 2003 - 10:37 am UTC
and if you upgrade your OS to linux, you can do that trick in every application easier.
left click and drag to hilight.
middle click to paste....
a new thing you cannot talk about.. wonder what it is.. fast=true ..:)
A reader, June 21, 2003 - 12:38 pm UTC
<quote>
and a new thing i cannot talk about
</quote>
well cant u just type it on here then ;)
jus kidding..
thanks for all the work you do for the oracle user community.. we are really indebted you...
Thanks
June 21, 2003 - 12:45 pm UTC
You'll find the new thing to be truly
"Marvel"ous
No Horizontal Scrolling in Character SQL*PLUS
A reader, June 26, 2003 - 1:29 pm UTC
In Character based sql plus: there is no horizontal scrolling available.
What if I had to set lines to 2000?
That will be limited by Terminal.
Cursor keys on command line: windows vs Linux
Mark Rynbeek, October 31, 2003 - 5:20 am UTC
hi Tom,
in a SQL*Plus client on Windows2000 (the command shell version) I can use the cursor keys to get previous commands back. Pageup and pagedown works as well. Joy !
But on Linux it never seems to work - if you press cursor up or cursor down the keyboard-input is not interpreted correctly, you just see these weird input values :
markr1@D002>^[OA
SP2-0042: unknown command "A" - rest of line ignored.
markr1@D002>^[OB
Can I get around this? Is it a SQL*PLus setting or a Linux setting ?
October 31, 2003 - 8:05 am UTC
in unix, its a function of the shell, and you are not in the shell anymore.
setting linesize question
james su, February 03, 2004 - 10:22 am UTC
hi Tom,
set linesize 300
select lpad('*',200,'1234567890') from dual;
in sqlplus it works well, but in sqlplusw only the first 100 characters are output. why is that? (sql*plus version 9.2.0.1.0)
February 03, 2004 - 10:29 am UTC
"shake" the window :)
resize it a bit after the set linesize and see what happens........
(and then just use sqlplus.exe -- ugh, i don't like that sqlplusw.exe and it is going away in the next release as well)
doesn't work
James Su, February 03, 2004 - 11:15 am UTC
I shook and shook and shook -- but nothing happened, the output remained the same. I am working in 1024*768.
if you try this:
set linesize 101
select lpad('*',200,'1234567890') from dual;
you will get two lines output and the 101st character '1' is missing.
February 03, 2004 - 1:46 pm UTC
sorry -- don't run any software that even allows me to fire up sqlplusw.exe.
I'll have to refer you to support.
Works for me!
A Reader, February 03, 2004 - 11:23 am UTC
SQL*Plus: Release 9.2.0.1.0 - Production on Tue Feb 3 10:22:26 2004
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.3.0 - Production
SQL> set linesize 101
SQL> select lpad('*',200,'1234567890') from dual;
LPAD('*',200,'1234567890')
-----------------------------------------------------------------------------------------------------
12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901
23456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789*
SQL>
so what's wrong with my sqlplusw ?
James Su, February 03, 2004 - 12:17 pm UTC
hi Tom,
did you try it in sqlplusw ?
below is what I got in sqlplusw (it's fine in sqlplus):
SQL*Plus: Release 9.2.0.1.0 - Production on Tue Feb 3 12:14:29 2004
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
james@DEV2.US.ORACLE.COM> set linesize 101
james@DEV2.US.ORACLE.COM> select lpad('*',200,'1234567890') from dual;
LPAD('*',200,'1234567890')
----------------------------------------------------------------------------------------------------
1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
23456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789*
james@DEV2.US.ORACLE.COM> set linesize 300
james@DEV2.US.ORACLE.COM> select lpad('*',200,'1234567890') from dual;
LPAD('*',200,'1234567890')
----------------------------------------------------------------------------------------------------
1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
james@DEV2.US.ORACLE.COM>
SQL PLUS Command Line interface
Jeff, February 03, 2004 - 1:36 pm UTC
I can't believe no one has mentioned TOAD or SQL Navigator.
I can run circles around a sql_plus user with either of these gui tools.
Why doesn't Oracle come out with nice gui interface like toad? Perhaps OEM is such a tool, but in our environment only DBA's can use this tool.
Where is the command history in sql plus?
Where are the object browsers?
Where are the convenient save results as XLS, CSV, clipboard?
Where is the find/replace?
Every command must be typed in. (slow)
sql plus is a very cludgy pre-1990 (i.e. DOS) interface at best.
I always work in a gui interface tool when possible. It was proven 15 years ago that gui interface is more productive.
SQL Plus has long learning curve. Not good for end users, only veteran Oracle users.
Hope this helps.
February 03, 2004 - 1:57 pm UTC
and I can run circles around your circles with sqlplus ;)
Jdeveloper is the tool you are talking about.
It has the command history.
object browsers
save results as
find replace
full plsql source level debugger
and more...
Where is the "proof" that a gui tool is more efficient. Have you run your gui tool over a slow link recently - the gui only people are totally lost without their gui's.
sqlplus with a long learning curve? you have got to be kidding me - there ain't much to it.
Lets see your gui participate in a nightly unload of data at 2am (while you are asleep). Or be used over dialup, ISDN, wireless aircards....
Hmmm -- different strokes, for different folks I guess.
Tastes Great AND Less Filling
Chuck Jolley, February 03, 2004 - 3:25 pm UTC
Why would anyone confine themselves to either type of tool?
I run a three monitor PC (XP) and frequently have a copy of toad or two and two or three copies of sqlplus (cmd line) all running at once.
Cutting and pasteing between all of them.
Why not? The tools all have their strengths and we all have our different styles (mine is centered around my poor typing skills, if you must know ;) )
As for the OS thing, most DBAs and programmers have absolutely no choice but to use the OS the shop uses.
I happen to like XP, but if they decided to change to *nix tommorow, it's not like I'd quit my job over it.
Who would? And why?
And with VPN and all the fine remote desktop software available now, bandwidth should not be much of a factor in choosing your tool anymore.
chuck
PS You need a new domain for Q&As that get side tracked like this.
I nominate: DiscussWithTom.com
Jdeveloper
Robert, February 04, 2004 - 5:23 am UTC
I like many others I imagine, use a mixture of toad and sqlplus. I have tried jdeveloper and the fact that it is written in java makes it way too slow and frustrating to use.
I think you should use the tools at your disposal - but also that you must know sqlplus because as you said before Tom, it is the only tool that every installation will have.
February 04, 2004 - 7:38 am UTC
I've found jdev quite snappy really - especially in light of the fact that toad et. al. do not even run on the machines I use ;)
Fun experiment
Chuck Jolley, February 04, 2004 - 11:42 am UTC
[quote]
...toad et. al. do not even run on the machines I use ;) ...
[/quote]
Every once and a while I install Linux on a machine at home to see how it's comming along.
I'll see if I can get TOAD to run in an emulator next time and let you know ;)
chuck
February 04, 2004 - 5:19 pm UTC
Linux has arrived -- totally. there isn't a tool you need that isn't already there.
I can even rip and watch my DVD's very nicely...
I actually do run word and ppt -- using crossover office, it could probably run toad if I really needed to.
vmware comes in handy for those pesky websites that demand IE.... running windows in a window -- very nice, ironic in a way -- but very safe.
ok what is the *marvellous* thing
A reader, February 04, 2004 - 3:26 pm UTC
Hi
What is the marvellous new tool you were talking about 8 months ago, now that 10g is released?
February 04, 2004 - 5:48 pm UTC
see
</code>
http://htmldb.oracle.com/ <code>
works with 9iR2 and up -- comes with a nifty sql workshop that I like alot.
erm sqlplusw is still in 10g
A reader, February 04, 2004 - 3:29 pm UTC
I think this sqlplus GUI is in 10g...? I dont think it's deprecated yet am I wrong?
Shell-like navigating is possible in sqlplus under hp-ux !!!
Maik Rabe, February 05, 2004 - 8:03 am UTC
Hi Tom and all other friends,
With HP-UX there IS a possibility to go though the last commands in sqlplus like in the ksh.
Set the following alias (in your .profile):
alias sqlplus="ied sqlplus"
and start sqlplus.
Now you can go back/forward in your history with ESC-k/ESC-j, search in your history with ESC-/ and so on....
Hope, that your are glad now!
Maik
pfft
James Blanding, February 06, 2004 - 3:39 pm UTC
<quote>I always work in a gui interface tool when possible. It was proven 15 years ago that gui interface is more productive.</quote>
Ha. Where is the URL for your so-called proof? Like Tom said, different strokes for different folks. Or to put it another way, *you* use the tool that *you* find works best for *your* task.
Personally, I find that >90% of the time, I can type a command (or series of commands) significantly faster than I can direct a 4-pixel-square pointer (the tip of the arrow, that is) around a maze of icons, menus, and fields. The only real hangup is web browsing, since there are so many damn sites out there nowadays that are flash-based or that have too many links to efficiently tab through.
But I dont go around defending my personal preferences with undocumented proofs :P That's why they're called "personal" preferences.
Anyway, there is a GUI-based SQL-IDE-etc tool out there for linux called TOra. I've read good things about it, but haven't had a chance (or real desire for that matter) to try it. </code>
http://www.globecom.se/tora/ <code>
parms to sqlplus
A reader, March 12, 2004 - 12:00 pm UTC
I am running w2k and oralce 8.1.7.x trying to pass values from dos to a sql script via sqlplus. Is it possible?
from the documentation:
"
SQLPLUS [[option] [logon] [start]]
.
.
.
and where start has the following syntax:
@file_name[.ext] [arg ...]
"
Can i pass a text arg that is available to my script? What is the syntax to recieve the arg in the .sql script?
Thanks in advance.
March 12, 2004 - 4:55 pm UTC
if you have:
prompt this is input #1 &1
prompt this is input #2 &2
exit
in test.sql, then:
C:\Documents and Settings\Administrator>sqlplus tkyte/tkyte @test hello world
SQL*Plus: Release 8.1.7.0.0 - Production on Fri Mar 12 16:56:28 2004
(c) Copyright 2000 Oracle Corporation. All rights reserved.
Connected to:
Oracle8i Enterprise Edition Release 8.1.7.4.1 - Production
With the Partitioning option
JServer Release 8.1.7.4.1 - Production
this is input #1 hello
this is input #2 world
Disconnected from Oracle8i Enterprise Edition Release 8.1.7.4.1 - Production
With the Partitioning option
JServer Release 8.1.7.4.1 - Production
C:\Documents and Settings\Administrator>
works...
Unix env setting
Sean, August 16, 2004 - 4:19 pm UTC
Hi Tom,
After I telnet to Unix from Window, If it is dollar prompt, I can correct the typing mistake by using either backspace or delete key. But In SQLPLUS prompt, I can only use delete key. Using backspace key produces strange sign.
I am using Window 2000, Solaris 9 and Oracle 920.
Thanks so much for your help.
August 16, 2004 - 8:05 pm UTC
SQL> !stty erase <hit backspace here>
different output in sqlplus from sqlplusw
Andrea Stroppolo, August 17, 2004 - 3:00 am UTC
Hello Tom, and thanks for helping us with this site.
Regarding sqlplus vs sqlplusw there is a different behaviour that drives me mad and I was never able to understand/workaround
Both are
SQL*Plus: Release 9.2.0.5.0 - Production on Tue Aug 17 08:15:50 2004
I work with a win client; I set the screen length to 132 characters and the screen buffer length to 1000 in the command line (cmd); with a large setting for 'linesize' and selecting a large line in sqlplusw works 'as expected' while doing the same in sqlplus tents to drift the values of fields with respect to the column headers
here I paste a column slice from both
set lines 1000
select * from all_tables where rownum < 10;
-- output in sqlplusw
...PCT_INCREASE FREELISTS FREELIST_GROUPS LOG
...------------ ---------- --------------- ---
... 50 1 1 YES
... 50 1 1 YES
... 50 1 1 YES
... 0 1 1 YES
... 50 1 1 YES
... 0 1 1 YES
... 0 1 1 YES
... 0 1 1 YES
... 50 1 1 YES
(here the values are aligned with column headings)
-- output in sqlplus
...PCT_INCREASE FREELISTS FREELIST_GROUPS
...------------ ---------- ---------------
... 50 1 1 YES N
... 50 1 1 YES N
... 50 1 1 YES N
... 0 1 1 YES N
... 50 1 1 YES N
... 0 1 1 YES N
... 0 1 1 YES N
... 0 1 1 YES N
... 50 1 1 YES N
(here values are drifted in respect with column headers)
I believe it is a visualization issue, since if you spool it to a file the output is correctly aligned; anyway it breaks the correct layout ant makes hard to read the output in sqlplus while with sqlplusw it is correct.
Is it possible to correct this?
Thanks a lot!!!
Andrea
August 17, 2004 - 8:01 am UTC
I've never had the command line sqlplus do anything remotely similar. You are using a fixed fonted right?
You would have to give me step by steps to reproduce this -- i've *never* seen anything remotely similar and would be inclined to believe it is either a "proportional font" issue or some setting in your login.sql/glogin.sql.
different output in sqlplus from sqlplusw
Andrea Stroppolo, August 17, 2004 - 10:36 am UTC
Tom,
many thanks for the response.
The environment is WinNT 4.0 workstation (Italian version)
Starting from command prompt I choose Terminal8x12 font (but tested with other combinations... Lucida console... Andale Mono...)
Screen buffer is (widthxheight) 1000x2000 and windows size is 132x40 characters wide
I removed the glogin.sql and login.sql (in SQLPATH) is
set termout off
column sqlprompt_col new_value sqlprompt_value
define sqlprompt_value='SQL'
select host_name || ':' || instance_name as sqlprompt_col
from v$instance;
set sqlprompt '&sqlprompt_value>'
undefine sqlprompt_value
set termout on
SQL*Plus version is 9.2.0.5, but the same (even with more evident drifting) applies to releases from
SQL*Plus: Release 3.3.3.0.0 - Production on Tue Aug 17 15:47:26 2004
onwards, on all of the machines I work on (but all of them have the same os version)
SQL*Plus: Release 10.1.0.2.0 - Production on Tue Aug 17 16:18:00 2004
behaves as 9.2.0.5
Since the spool file is 'correctly' visualized in an editor using the same font I use in cmd I tent to believe the issue is in the command prompt visualization... changed too many versions of sqlplus...
This has always been a pain for me... never been able to workaround :-(
Anyone else is able to reproduce the behaviour I observe?
Thanks again for confirming it's an issue in my setup,
Andrea
August 17, 2004 - 10:48 am UTC
I wonder -- if it could have anything to do with TAB?
set tab off
maybe the tabstops are funky?
I'll have to refer you to support, never heard of any drifting and I use sqlplus.exe exclusively on windows, when forced to use windows.
Thanks Thanks Thanks!!!
Andrea Stroppolo, August 17, 2004 - 12:01 pm UTC
Great!!! It works!!!
I'm unsure if being happy or sad... due to the *simple* solution I have not been able to understand in the last years. I am not very proud of myself... in this moment...
... going home to read the documentation
Really many thanks
Andrea
ied sqlplus on HP-UX (Unix shell history )
VKS, October 21, 2004 - 11:40 am UTC
This became the way of working while I was on HP-UX. Is there something like this on solaris ?
I really miss this one !
October 21, 2004 - 2:56 pm UTC
ied? never heard of it.
Command history in SQL*Plus
J. Jenkinson, October 21, 2004 - 3:50 pm UTC
I think the previous poster is asking if there is a command line history capability in SQL*Plus, like there is at the UNIX shell (e.g. <CTRL-P>, if using emacs-style in-line editor, <ESC-K> if using vi-style).
I am unaware of this capability in UNIX (although, curiously, the Windows version of sqlplus.exe does have command line history capability).
To J. Jenkinson
Scot, October 22, 2004 - 1:59 pm UTC
Have you tried LIST or EDIT commands in sqlplus? Not sure if these are what you had in mind...
ied sqlplus - HP-UX
VKS, October 23, 2004 - 3:14 am UTC
ied allows you vi style command history as well as editing.
It is a wrapper program on HP-UX that uses pseudo tty's and redirection to do this. You could also do ied ftp and have the same functionality there.
The manpage for ied is </code>
http://docs.hp.com/hpux/onlinedocs/B2355-60105/00/01/184-con.html <code>
If oracle is to consider history+editing in sqlplus, this would be nice.
October 23, 2004 - 10:07 am UTC
have you yourself filed an enhancement request via support? that is the way to get your ideas into the products themselves.
ied sqlplus - HP-UX
VKS, October 24, 2004 - 1:36 am UTC
Nope Tom, I haven't yet. I will do so now via metalink, I did not know how to give my ideas to features selection / implementation teams.
Thanx,
VKS
Thanks to Maik Rabe for ied tip
Igor, November 09, 2004 - 12:41 pm UTC
Excellent thing
RE: Vim in Windows
Bakunian, April 01, 2005 - 2:43 pm UTC
Tom,
Is there a way of settig Vim as a default _editor. I found article on Oracle site that recomends addit following into glogin.sql
DEFINE _EDITOR='gvim -c "set filetype=sql"'
</code>
http://www.oracle.com/technology/pub/articles/kalosi_vim.html <code>
However this does not work properly it invokes Vim if you type EDIT in SQL Plus but it does not preserves edited going back to same mistyped character.
Thank you for your time.
April 01, 2005 - 3:03 pm UTC
I just
define _editor=vi
there is a vi.bat, mine is:
@echo off
rem -- Run Vim --
set VIM=C:\Program Files\Vim
if exist "%VIM%\vim63\vim.exe" goto havevim
echo "%VIM%\vim63\vim.exe" not found
goto eof
:havevim
rem collect the arguments in VIMARGS for Win95
set VIMARGS=
:loopstart
if .%1==. goto loopend
set VIMARGS=%VIMARGS% %1
shift
goto loopstart
:loopend
if .%OS%==.Windows_NT goto ntaction
"%VIM%\vim63\vim.exe" %VIMARGS%
goto eof
:ntaction
rem for WinNT we can use %*
"%VIM%\vim63\vim.exe" %*
goto eof
:eof
set VIMARGS=
Vim Editor
Bakunian, April 01, 2005 - 4:29 pm UTC
Works great. I have 9i client installed on XP but did not have vi.bat in ORACLE_HOME. Feel sorry for those GUI suckers. Thanks again.
Ravi, July 13, 2005 - 10:55 am UTC
Stumbled on this, aroused my curiousity, thought worthwhile to ask.
In my afiedt.buf file if I code comments as the FIRST line the I get the following error:
"afiedt.buf" 3 lines, 26 characters
1 --
2* Select '1' from dual
z001698@DEVELOP > /
--
*
ERROR at line 1:
ORA-24374: define not done before fetch or execute and fetch
Question:
Makes me remember my PRO*C days, is it true that SQL*PLUS works like PRO*C, say Defines columns and then executes etc?
July 13, 2005 - 12:58 pm UTC
sqlplus is just an oci application, you could write sqlplus yourself if you wanted.
so "yes"
A reader, September 29, 2005 - 8:04 am UTC
Is it fair to say that SQLplus does Dynamic SQL for every statement that it executes and hence the Cursors are closed after each statement?
September 30, 2005 - 7:22 am UTC
it is fair to say the ONLY WAY to execute sql in Oracle is via dyanmic sql at the end of the day - there is no such thing as true "static sql" (ala DB2 on the mainframe).
static sql is a programming environment thing. SQLJ, Pro*C, PLSQL - they all support a programming construct of dynamic sql.
In sqlplus, there is pretty much one open cursor that is used for your sql. When you type in your sql - they parse it using that cursor, they execute it. When you type in your next sql, it happens all over again.
gnu readline and sqlplus
Dana P, November 09, 2005 - 10:43 am UTC
The one thing that has always killed me about sqlplus is that you can't hit the up arrow to see your last query, or use the left or right arrows to go back and correct your query (like you can in a normal shell). Today I stumbled upon a program called rlwrap. It's a wrapper for gnu readline. I don't think it comes installed by default on many Linux systems, but it's available on freshmeat:
</code>
http://freshmeat.net/projects/rlwrap/ <code>
to use with sqlplus: do
$ rlwrap sqlplus scott/tiger@mydb.host.com
enjoy!
November 11, 2005 - 10:03 am UTC
yup, I have it linked to on "links i like" above - I got hooked on it a while ago :)
calling sql procedure from Unix
Rajesh, April 20, 2007 - 8:31 am UTC
Hi Tom,
I am trying to execute a PL/SQL procedure from unix.
It also has a string parameter for it. I am passing the string as command line argument in Unix.
I get that parameter using..
PARAM = $1
and then try to open sqlplus and run the procedure as below
sqlplus -s load/load@cdsdev >>$LOGFILE <<EOF > $CSVFILE
SET SERVEROUT ON SIZE 1000000
BEGIN
mapping_batch('$PARAM');
END;
/
exit
EOFIt is not working.The procedure is called but the parameter is not passed to it.
can you please suggest me the solution ?
Thanks
Rajesh
April 20, 2007 - 10:03 am UTC
works for me
[tkyte@tkyte-pc ~]$ cat test.sh
#!/bin/bash
LOGFILE=x.log
PARAM=hello
CSVFILE=x.csv
sqlplus -s / >>$LOGFILE <<EOF > $CSVFILE
SET SERVEROUT ON SIZE 1000000
BEGIN
dbms_output.put_line('$PARAM');
END;
/
exit
EOF
cat x.csv
[tkyte@tkyte-pc ~]$ sh test.sh
hello
PL/SQL procedure successfully completed.
calling sql procedure from Unix
Rajesh, April 27, 2007 - 4:05 pm UTC
Hi Tom,
Thanks for your reply. I think you didnt get my question.
[tkyte@tkyte-pc ~]$ cat test.sh
#!/bin/bash
LOGFILE=x.log
PARAM=hello
CSVFILE=x.csv
sqlplus -s / >>$LOGFILE <<EOF > $CSVFILE
SET SERVEROUT ON SIZE 1000000
BEGIN
dbms_output.put_line('$PARAM');
END;
/
exit
EOF
cat x.csv
[tkyte@tkyte-pc ~]$ sh test.sh
hello
PL/SQL procedure successfully completed.
In your shell script, you had assigned hello to a PARAM variable. This works for me also.
But I wanted to assign a command line argument ($1) to PARAM as
PARAM = $1
and then pass the PARAM to PL/SQL.
Can we pass a command line argument of Unix as a parameter of the PL/SQL procedure ?
Thanks
Rajesh
April 29, 2007 - 8:38 am UTC
sorry, I thought it would be obvious... ok:
[tkyte@desktop ~]$ cat test.sh
#!/bin/bash
LOGFILE=x.log
PARAM=$1
CSVFILE=x.csv
sqlplus -s / >>$LOGFILE <<EOF > $CSVFILE
SET SERVEROUT ON SIZE 1000000
BEGIN
dbms_output.put_line('$PARAM');
END;
/
exit
EOF
cat x.csv
[tkyte@desktop ~]$ ./test.sh hello
hello
PL/SQL procedure successfully completed.
Oracle to SQL Server
Rajesh, May 10, 2007 - 5:11 pm UTC
Hi Tom,
How can we fetch the records of SQL server from Oracle ?
I hope it could be by using some gateway.
Can you explain this stuff with examples of creating/configuring gateway
and fetching the records from SQL Server ?
Thanks
Rajesh
May 11, 2007 - 11:22 am UTC
Rule Hint
Rajesh, May 14, 2007 - 12:37 pm UTC
Hi Tom,
When will we go for the RULE hint ? Can you explain with examples.
/*+ RULE */
Thanks
Rajesh
May 14, 2007 - 5:00 pm UTC
only when you want to demonstrate how bad of a plan the RBO would give under the same circumstances.
eg: not really ever in real life.
Searching table data
Rajesh, January 17, 2008 - 4:37 pm UTC
Hi Tom,
This is about the search of table data. We can search any text in the package using the dba_source. Similarly, can we search a text among the table data ? i.e. we dont know the table or column name and know only the text to be searched in the tables. We should now find the table where the text is present.
Is it possible ?
Thanks
Rajesh
January 19, 2008 - 10:28 pm UTC
(your analogy doesn't work very well. I would just as easily say "we can search any employee name in ENAME using SCOTT.EMP". You have a specific question answered by a very specific schema)
sure, anything is possible. What you have to ask yourself is:
IS IT PRACTICAL
think about this, most databases are pretty large, do you really want to trawl the entire thing?
You (you, not me, you) could write a routine that queries DBA_TABLES, to find all tables.
You could loop over those.
You could query DBA_TAB_COLUMNS to find the varchar (text) columns therein
You could dynamically construct a query that searches that table, querying those columns for your string.
You could then execute that query, looking for hits. And then print them out.
How to remove unwanted string like "> SQL> SQL> "
Balavnt, April 30, 2008 - 9:41 am UTC
Hi Tom,
When I am dynamically generating the sql script using korn shell, it also generates some string shown in below output.
---------------------output------------------------------
c809861@[gbdsnh20]:/export/home/c809861/add_db_user> cat cr_usr_CO20612_EBDX005B.sql
SQL*Plus: Release 10.2.0.3.0 - Production on Wed Apr 30 09:15:16 2008
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.7.0 - Production
SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 CREATE USER CO20612
IDENTIFIED BY PASSWORD EXPIRE
DEFAULT TABLESPACE TS_USERS
TEMPORARY TABLESPACE TEMP
PROFILE default
/
SQL> SQL> SQL> Disconnected from Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.7.0 - Production
----------------------------------------------------
In above output Sql*Plus generates "SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> 2 3 4", is there any way to suppress this line.
Appreciate for your effort.
Thank you,
Balvant Chhasiya.
April 30, 2008 - 10:56 am UTC
spool instead of redirect
in your script, put
spool filename.whatever
....
spool off
then you can use things like set echo off, set feedback off, set termout off, whatever to control the format of the report and what gets shown on screen as the script runs.
How to remove unwanted string like "> SQL> SQL> "
Balavnt, April 30, 2008 - 11:27 am UTC
Thank you very much for your prompt reply.
Definitely your solution works as usual.
Meanwhile I was searching through your other post for solution on the site, and found the appropriate solution:
"sqlplus -s" does the job, it does not display unwanted strings, Now my output looks like:
------------------Output------------------------------
c809861@[gbdsnh20]:/export/home/c809861/add_db_user> cat cr_usr_CO20612_EBDX005B.sql
CREATE USER CO20612
IDENTIFIED BY WED5007APR$ PASSWORD EXPIRE
DEFAULT TABLESPACE TS_USERS
TEMPORARY TABLESPACE TEMP
PROFILE DEFAULT
/
-------------------------------------------------
Appreciate your valuable effort.
Thanks & Regards,
Balvant Chhasiya.
enabling defualt text editor
anantha krishna, May 12, 2008 - 11:22 pm UTC
hi,
when i am writing some queries,if i typed wrongly it will shows error.but, i am not able to open a default text editor like gedit in linux, plz mail me the process of enabling a default text editor when i type ed
thank you
May 13, 2008 - 10:25 am UTC
did you try reading the documentation for sqlplus?
PLZ - german postal codes, not sure what that has to do with anything, please clarify. PLZ is not a word.
http://docs.oracle.com/docs/cd/B19306_01/server.102/b14357/toc.htm right in the table of contents:
Writing Scripts with a System Editor
http://docs.oracle.com/docs/cd/B19306_01/server.102/b14357/ch5.htm#sthref907 you'll probably want to save your favorite settings in a file: login.sql
and if you want to have sqlplus process that from any directory you are in, set your SQLPATH environment variable (in fact, you can put lots of scripts into the directory pointed to by SQLPATH and then be able to run them as if they were in the current working directory)
Merging PDF files in UNIX
Rajesh, May 21, 2008 - 11:32 am UTC
Hi Tom,
I need to merge two or more PDF files into a single one in UNIX. Is it possible ? If not what can be your suggestions ?
Thanks
Rajesh
May 21, 2008 - 12:07 pm UTC
my suggestion would be to find the askadobe site?
unix tool for merging pdf
Maxim, May 21, 2008 - 3:13 pm UTC
OK
Kumar, January 18, 2012 - 9:25 am UTC
Hi Tom,
while running a table creation sql script in SQL*PLUS,Can we get the feedback as
"Table t created" rather than "Table created"?
Any simple demo you have for this?
Thanks for your time.
January 18, 2012 - 9:58 am UTC
only if you are willing to program it yourself.
ops$tkyte%ORA11GR2> drop table t;
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> set feedback off
ops$tkyte%ORA11GR2> begin
2 execute immediate q'|
3 create table t
4 ( x int primary key,
5 y varchar2(5) default 'x'
6 )
7 |';
8
9 dbms_output.put_line( 'Table T created.' );
10 end;
11 /
Table T created.
ops$tkyte%ORA11GR2> set
you basically just need to put a
begin
execute immediate q'|
in front of your existing DDL statements and:
|';
/* any sort of code you want */
end;
/
after them (remove any semicolons as well)