Equivalent in Perl

[pankaj_wolfhunter]

Greetings,

Whats the equivalent for the following code in perl:

sqlplus -s orauser/orapass@oadb << eof > output_file.txt
some oracle queries
exit;
eof;

I did this using "system" command.
We've been told to do this using DBI or some other method?

I tried with DBI but the problem is I dont know how to redirect the
oracle output to
to a file (outout_file.txt) instead of console.

Help would be appreciated.

TIA

 

[xhoster]

Greetings,

Whats the equivalent for the following code in perl:

sqlplus -s orauser/orapass@oadb << eof > output_file.txt
some oracle queries
exit;
eof;

I did this using "system" command.

That *is* the equivalent in Perl.

We've been told to do this using DBI or some other method?

Was that a statement or a question? Why were you told this?
If you were told this for a reason, that reason matters.

I tried with DBI but the problem is I dont know how to redirect the
oracle output to
to a file (outout_file.txt) instead of console.

You use DBI when you want the data to come into Perl (from where you can
wrangle and format and print to your heart's content--just like you can
with any other data in Perl). If you just want it sent to a file exactly
the way sqlplus does it, then just use system and sqlplus.

Xho

 

[pankaj_wolfhunter]

That *is* the equivalent in Perl.


Was that a statement or a question? Why were you told this?
If you were told this for a reason, that reason matters.


You use DBI when you want the data to come into Perl (from where you can
wrangle and format and print to your heart's content--just like you can
with any other data in Perl). If you just want it sent to a file exactly
the way sqlplus does it, then just use system and sqlplus.

Xho

Thanks Xho.

Was that a statement or a question

That was a statement.

Still if we need to convert the above code using DBI, how'll that be.
Actually I tried myself but couldnt get through.

 

[Ted Zlatanov]

pwci> Whats the equivalent for the following code in perl:

pwci> sqlplus -s orauser/orapass@oadb << eof > output_file.txt
pwci> some oracle queries
pwci> exit;
pwci> eof;

pwci> I did this using "system" command.
pwci> We've been told to do this using DBI or some other method?

pwci> I tried with DBI but the problem is I dont know how to redirect
pwci> the oracle output to to a file (outout_file.txt) instead of
pwci> console.

You are confusing DBI with something else. DBI lets you make SQL
queries inside Perl, `sqlplus' is never involved. You need to rewrite
your queries to do the right thing inside Perl, essentially. Either
learn DBI programming from one of the many online tutorials, or hire
someone who understands the topic.

Ted

 

[xhoster]

Still if we need to convert the above code using DBI, how'll that be.
Actually I tried myself but couldnt get through.

I would start by reading the DBI docs, or maybe a book about using DBI.
Surely you don't expect us to read the DBI docs to you, or just retype the
docs into usenet, right?

What did you try and what problems did you encounter?

Did you establish a connection? If so, did you prepare and execute a
query? If so, did you get the results? If so, do you know how to use join
and print? If so, then what is left to be done?

Xho

 

[pankaj_wolfhunter]

I would start by reading the DBI docs, or maybe a book about using DBI.
Surely you don't expect us to read the DBI docs to you, or just retype the
docs into usenet, right?

What did you try and what problems did you encounter?

Did you establish a connection? If so, did you prepare and execute a
query? If so, did you get the results? If so, do you know how to use join
and print? If so, then what is left to be done?

Xho

Thanks for replying.
As I told before also i did try it with DBI and all I couldnt do is
how
to redirect the output instead of console to a file.
Neverming here's my code

#!/usr/bin/perl -w

use DBI;

$dbh = DBI->connect("DBI:Oracleradb","username","password") || die
"Database Connection not Made : $DBI::errstr\n";

### Prepare a SQL statement for execution
$sth = $dbh->prepare( "SELECT SYSDATE FROM DUAL1" );

### Execute the statement in the database
$sth->execute();
$sth->finish();
$dbh->disconnect();

Now whatever is the output (error or actual output) goes to console.
How to redirect it to file instead of console.

I tried using STDERR but couldnt get it to work.

What code to put exactly where in order to achieve this?

TIA

 

[pankaj_wolfhunter]

I would start by reading the DBI docs, or maybe a book about using DBI.
Surely you don't expect us to read the DBI docs to you, or just retype the
docs into usenet, right?

What did you try and what problems did you encounter?

Did you establish a connection? If so, did you prepare and execute a
query? If so, did you get the results? If so, do you know how to use join
and print? If so, then what is left to be done?

Xho

Thanks for replying.
As I told before I tried this with DBI but just didnt know
how to redirect the output to a file.

here's the code I used:

#!/usr/bin/perl -w

use DBI;

$dbh = DBI->connect("DBI:Oracle:ndmid","ssatyasa","ssatyasa") || die
"Database Connection not Made : $DBI::errstr\n";

### Prepare a SQL statement for execution
$sth = $dbh->prepare( "SELECT SYSDATE FROM DUAL1" );

### Execute the statement in the database
$sth->execute( );
$sth->finish();
$dbh->disconnect();

I tried redirecting using STDERR but just couldnt make it work.
What to put and exactly where?

Any help?

TIA

 

[pankaj_wolfhunter]

Since you are apparently unable to use Google, I did it for you. I typed
in "perl DBI example" and looked at the first item returned. I recommend
you get into the habit of using Google.

http://www.saturn5.com/~jwb/dbi-examples.htmlhas this example

------------------------------------8<--------------------------------
use strict;
use DBI;

my $dbh = DBI->connect( 'dbi:Oraclercl',
'jeffrey',
'jeffspassword',
{
RaiseError => 1,
AutoCommit => 0
}
) || die "Database connection not made:
$DBI::errstr";

my $sql = qq{ SELECT id, name, title, phone FROM employees };
my $sth = $dbh->prepare( $sql );
$sth->execute();

my( $id, $name, $title, $phone );
$sth->bind_columns( undef, \$id, \$name, \$title, \$phone );

while( $sth->fetch() ) {
print "$name, $title, $phone\n";

}

$sth->finish();
$dbh->disconnect();
------------------------------------8<--------------------------------

I'm assuming you know how to use Perl to open a file for writing and how
to compose a perl "print" statement to write to a file. If not try using
the perl documentattion by typing these commands:
perldoc -f open
perldoc -f print

With that knowledge you can adapt the above example to write the SQL
data to a file.- Hide quoted text -

- Show quoted text -

Thanks Ian and thanks all.
Didnt know that I am asking something which is really bugging a lot of
ppl here.
Next time I gotta be a bit careful
Thanks again.

 

[Tad McClellan]

Didnt know that I am asking something which is really bugging a lot of
ppl here.

You didn't know that you were treating a lot of people (not ppl)
with disrespect when you asked your question without trying
even a little bit to answer it yourself first?

I don't see how that outcome could be surprising...

Next time I gotta be a bit careful

Next time you simply have to respect the time of others.