Sqlplus

[Mark Battle]

I occasionally have to do ad-hoc Oracle queries on VMS using Sqlplus. Everytime I am annoyed because I cannot use the up-arrow to recall previous input.
Does anybody have a solution.

[Hein van den Heuvel]

Yeah, that is annoying isn't it.
Makes me want to call it SQLminus

You did not specify an OS/SQLplus/DB version, but I don't think it works for any combination.

Even SQLplus on windows works better in that respect, so that's what I use at times. Just connect remotely.

But when connecting remotely you might as well do it properly with the (free download) Oracle SQLdeveloper or such tool, or "SQL commands" shipped with the XE download.

For strict VMS I 'grow' my queries in an editor window and cut & paste all the time, occasionally save seemingly useful intermediate states to disk.

There is of course the edit command build into SQLplus, and you can augment that.

check out VimTip 41: in - http://users.cis.fiu.edu/~prabakar/resource/Linux/vimtips.txt

sharing the frustration,

fwiw,
Hein

[Hoff]

Your organization likely pays for Oracle support. (Most organizations don't use Oracle without also having Oracle support.) Contact Oracle support and ask for assistance with SQL*Plus, or (more likely) ask for an enhancement.

Switch to another database (maybe Ingres or MySQL?) that better meets your needs and expectations.

Fix the Oracle source code to implement this via SMG or another mechanism? (Well, "use the source" is probably not an option for you here.)

Don't use the SQL*Plus command line? Migrate to a GUI-based package or a remote package with the necessary features, and use that for ad-hoc queries into the database, either directly or via ODBC or JDBC?

Looks to be the / executes the last command or you enter a number of the command, and per a very cursory read of the Oracle SQL*Plus documentation, there is no command-like recall beyond the last command. The Oracle documentation steers readers into the ED command.

[John Gillings]

Mark,

A pity Oracle doesn't seem to have done anything with SQLplus for the last 20 years!
I'm constantly amazed by the primitive interface that users (apparently) put up with, especially considering the cost.

You can PIPE your input into SQL, which means the text of your query is then in a DCL command, which works with command line editing. Of course, the query is in the first PIPE stage, so it's messy to edit, and is often hit by line length limits and wrapping. You can get around that by packaging it up into a procedure.

Mine looks like this:

$ @ORACLE

(with stuff to select username, password etc.. hidden in the procedure)

Inside, it comes down to:

$ PIPE | (DEFINE/USER SYS$INPUT SYS$PIPE ; SQLPLUS -s 'usr'/'pwd')

Note that this also solves the issue of substituting DCL symbols into queries.

[Mark Battle]

@Hoff. No-one is going to any great effort to save me from occasional annoyance.

@Hein. My simple solution is to have a 2nd decterm in wich I do a $create x.tmp. Then I can paste anything useful into it.

@John. Your solutions seemt to involve repeated restarts of SqlPlus which is not exactly quick. Also how do you maintain the environment, e.g line and page length.

Mark

[Mark Battle]

I guess I have to live with it.

[Hoff]

You have three general choices. Live with it, try different tools, or picking a different approach with the same tools. Or four choices, if you count finding a different job with folks that use different tools.

I don't (personally) usually use the command-line tools for ad-hoc queries; that's too much like work. Even an eon ago (and as long-retired as it is now) the Rdb InstantSQL tool was way faster for that. Its many relatives and modern descendants are far more advanced.

[John Gillings]

Mark,

>Your solutions seemt to involve repeated >restarts of SqlPlus

Possibly. It depends on how you want to code it. The trick is to use one chunk of DCL to generate your query (including any extra stuff like page lengths) and pipe it into SQLplus. This gets around several limitations of the SQL interface. My generator module recognises a number short macros to include standard SET commands.

As written, yes it will fire up Oracle for each invocation. No big deal, as the cost of the query usually far outweighs the activation cost, and there's little, if any context to be saved.

If you're really worried about context, you can easily extend the concept a little. Create a pair of mailboxes, and start an oracle subprocess attached to them. Feed your commands into one, and catch the output from the other. Use some kind of marker to indicate end of input. Process creation could be hidden inside the procedure.


re: $create x.tmp

Another option

$ COPY SYS$INPUT NL:

(no junk file left over)