- Integrated Systems
- About Us
- Integrated Systems
- About Us
11-12-2010 06:10 AM
Does anybody have a solution.
11-12-2010 06:40 AM
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,
11-12-2010 06:53 AM
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.
11-14-2010 01:28 PM
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:
(with stuff to select username, password etc.. hidden in the procedure)
Inside, it comes down to:
Note that this also solves the issue of substituting DCL symbols into queries.
11-15-2010 01:01 AM
@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.
11-15-2010 06:45 AM
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.
11-15-2010 01:02 PM
>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
$ COPY SYS$INPUT NL:
(no junk file left over)