SA-DBA

Jul 10 2009   4:06PM GMT

Piggybacking on VI’s importance…

Richard Evans Richard Evans Profile: Richard Evans

As a guy who uses Solaris for 99% of his administration I’ve gone from fearing vi to loving and respecting it and seriously considering buying it a valentine’s day present every year. That’s probably best reserved for a different blog all together:-)

This is a good write-up on why VI is such a powerful too even today.
Why, oh WHY, do those #?@! nutheads use vi?

I’m by no means an expert. I have learned enough vi to make my daily life so much easier though. As a lazy computer guy, VI is your best friend!!

Some examples of how I use vi on a regular basis.. creating bulk insert statements, generating quick shell scripts to execute similar commands repeatedly, and other countless items…

Yesterday, I was playing with NLS_SORT and I wanted to generate a table with a lot of first & last names. This would take forever if I did it by hand. Thank god there is the Internet!! I downloaded a random .CSV with distinct first name and last name columns, removed the extranious data, and then pasted that data into vi.


[oracle: /oracle] more insert_first_last_names.sql
Ralph Lauren
Piper Laurie
D.H. Lawrence
Martin Lawrence
Steve Lawrence
Thomas Lawrence
Vickie Lawrence
Bill Lee

A quick table creation…

create table rich_test (first_name varchar2(30), last_name varchar2(40));

And now comes the vi fun! Since I know I have only first and last names at this point, I can assume the space between them delineates first from last.

## This guy will take the space and replace it with tick comma tick…
:%s/ /','/g

Ralph','Lauren
Piper','Laurie
D.H.','Lawrence
Martin','Lawrence
Steve','Lawrence
Thomas','Lawrence
Vickie','Lawrence
Bill','Lee

Now let’s get the insert statement taken care of…

## make sure you don’t forget the tick at the beginning of the first name!

:%s/^/insert into rich_test values('/g

insert into rich_test values('Ralph','Lauren
insert into rich_test values('Piper','Laurie
insert into rich_test values('D.H.','Lawrence
insert into rich_test values('Martin','Lawrence
insert into rich_test values('Steve','Lawrence
insert into rich_test values('Thomas','Lawrence
insert into rich_test values('Vickie','Lawrence
insert into rich_test values('Bill','Lee

Last, we can finish it off by closing out the last_name tick, the parenthesis and do NOT forget the semicolon! Otherwise you’ll be left with a bunch of insert statements that are waiting for you to send them!

:%s/$/');/g

insert into rich_test values('Ralph','Lauren');
insert into rich_test values('Piper','Laurie');
insert into rich_test values('D.H.','Lawrence');
insert into rich_test values('Martin','Lawrence');
insert into rich_test values('Steve','Lawrence');
insert into rich_test values('Thomas','Lawrence');
insert into rich_test values('Vickie','Lawrence');
insert into rich_test values('Bill','Lee');

Now it’s as simple as running our SQL statement and then committing the data!

SQL> @"insert_first_last_names.sql"

1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.

I’ve only given you an example with a few entries, but this is beautiful if you wanted to generate a table with a few thousand, tens of thousands, or hundreds of thousands of rows. It’s really just that quick and easy.

Until you’ve memorized the commands, you should keep a cheat sheet close at hand. This one looks pretty good! vi Editor “Cheat Sheet”

And one final note, keep your hands on the home keys. The sooner you learn to use h, j,k, & l to navigate the better off you’ll be.

 Comment on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: