automate command prompt actions

[Paryushan]

Hi,

I would like to automate some of my actions on command prompt using perl. can u plz help me how to start with it.

Like I want to connect to oracle database and run a sql query. How do I do that?

Here are the steps that I need to do --
open cmd.
type -- sqlplus system/manger@px10_machine1
type -- select username from dba_users where username like '%I3598%';
type -- quit

Thanks inadvance,
Paryushan

[Comatose]

Not to distract from your original question (ie: how to run commands from perl) but, if accessing a database is what you want to do, why not simply do so directly from Perl, using say.... The DBI Module?

[Paryushan]

Yeah... I came to know about that later...

I am investigating.... not sure how to use that module to solve my problems :(

[Comatose]

Well, the first step in any perl app when using the dbi interface is to make sure perl knows to use it:

use DBI;

Then, you want to make perl connect to the database using the DBI interface, so, make a variable and assign it to a database connection:

$dbh = DBI->connect("DBI:mysql:users", "Username", "Password");

In this code, we connect using DBI to a mysql database, named users. We also supply the username and password to access the database.
Now, we need to create a query, and execute it:

$query = $dbh->prepare("SELECT username from dba_users where username like '%I3598%';");
$query->execute();

And finally fetch the information we want:

while ($tmpname = $query->fetchrow()) {
     push @dba_usernames, $tmpname;
}

Now, (assuming all the assumptions I made about the type of database, the name of the database, and the name of the tables/columns are accurate) you should have an array called dba_usernames, that contains a list of all the usernames that match your query.
Now, simply display them if you want (though, you could probably do this in the while loop instead for efficiency.. but this lends itself to future growth):

foreach $name (@dba_usernames) {
     print "$name\n";
}

You may have to tweak it a little, to work exactly how you like, but this should at least get you knee deep on the right path.

[Paryushan]

Thanks so much... that what exactly I was looking for :)
Actually I was looking for something to fetch the output after executing the query and you got that --

while ($tmpname = $query->fetchrow()) { push @dba_usernames, $tmpname;}while ($tmpname = $query->fetchrow()) {
push @dba_usernames, $tmpname;
}

Two more questions --
1.How can someone come to know that there is something called "$query->fetchrow"? That is how to study or understand modules to use them?

2. After connecting to oracle db, I want create a db user which can be created using following steps after connecting to db --

SQL> @create_user;
Enter value for user:
Enter value for temporary_tablespace: TEMP
Enter value for userdata_tablespace: USERS

Here after performing @create_user, how do I pass the values for next questions it will ask for?

Thanks in advance.

[KevinADC]

Almost all modules have documentation. And almost all modules are listed on CPAN:

http://search.cpan.org/~timb/DBI-1.607/DBI.pm

There are also many online resources that you can use a search engine to find and many perl books you can purchase or possibly borrow from a library if there is a library/school in your area.