Home > Microsoft, PowerShell, Windows 7, Windows Server > PowerShell: Recreating SQL*Plus ‘ed’ Command

PowerShell: Recreating SQL*Plus ‘ed’ Command

November 9, 2009

Aside from PowerShell, I spend a bit of time day-to-day in Oracle’s SQL*Plus.  I’m slowly replacing my dependency on SQL*Plus with PowerShell scripts, functions, and such, but there are some core things that it’s just easier to bring up a console and hack out.

One thing I really love about SQL*Plus is the ‘ed’ command.  The ‘ed’ command dumps your last statement into a temp file, then opens it in Notepad.  Make your changes in Notepad, save, and rerun (in SQL*Plus, it’s a single slash and then Enter), and the frustration of lengthy command line editing is solved.

So, this is a work in progress—my attempt to simulate the same process in PowerShell.

Example Situation

Let’s say, for example, we have a long command we have been hacking away at, but are getting tired of arrowing back and forth on:

$count = 0; sq mydb “select id from reports” | % { $count++ }; $count

(Yes, yes, it’s not ‘very long’, but that’s why this is called an example!)

So, a simple row counter.

The ‘ed’ Command

To recreate the ‘ed’ experience, I need two things:

  • Access to the command line history via get-history.
  • A temporary file.

touch c:\temp.tmp

get-history | ?{ $_.commandline -ne “ex” } |

? { $_.commandline -ne “ed” } |

select commandline -last 1 |

%{ $_.CommandLine} > c:\temp.tmp

np c:\temp.tmp

Stepping through the code:

  1. Create a temporary file.  I used c:\temp.tmp, but you could get a bit fancier and use the systems $TEMP directory or something.
  2. Get-History.  This returns an ascending list of your command history in PowerShell by ID and CommandLine.  We want to ignore any calls to our ‘ed’ or ‘ex’ functions and grab the “last 1”—then insert it into our temp file.
  3. Finally, open up the temp file in NotePad (np is an alias on my PowerShell profile for NotePad2).

So now that I have my ed command, let’s try it out after I’ve already ran my simple row counter.

Last Command in NotePad2

Cool.  We can now make changes to this and SAVE the temp file.  The next step would be to jump back to PowerShell and execute the updated command.

The ‘ex’ Command

Since the slash has special meaning in a PowerShell window, I simply named this script ‘ex’ instead.  Ex… execute… surely I can’t forget that, right?

get-content c:\temp.tmp |

% {

$_

invoke-expression $_

}

The ‘ex’ command is pretty simple.

  1. Get the content of our temporary file from get-content.
  2. For each line (since get-content returns an array of lines), print out the line as text and then invoke (run) it using invoke-expression.

Invoke-Expression takes a string as a command (from our text file) and returns the results.  For more information on Invoke-Expression, check out MSDN.

Wrapping It Up

Now that we have our two commands—ed and ex—we’re ready for those “oh, this is short and easy” commands that turn into multi-page monsters—without fighting tabs and arrow keys.

As I said, this is a work-in-progress.  I’ll update the post as new/better ideas come up. :)

About these ads
Follow

Get every new post delivered to your Inbox.

%d bloggers like this: