Beefy Boxes and Bandwidth Generously Provided by pair Networks
good chemistry is complicated,
and a little bit messy -LW

Perl on Oracle

by Anonymous Monk
on May 07, 2003 at 17:46 UTC ( #256323=perlquestion: print w/replies, xml ) Need Help??

Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

Friends , I need help on a perl script which will select soemthing from an Oracle database and store it in a variable, the script would like do a select statement from the oracle database , can anyone provide me a simple example or a script that would push me in the right direction Thank you very much

Replies are listed 'Best First'.
Re: Perl on Oracle
by shemp (Deacon) on May 07, 2003 at 17:56 UTC
    Go to CPAN and get the module DBI, and DBD::Oracle. read the docs there regarding using them. DBI is an nice interface to various database (Database Independent Layer), and DBD::Oracle, working in conjunction with the DBI contains concerns specific to oracle databases.
    note: i have never used DBD::Oracle, but have used other DBD 's
Re: Perl on Oracle
by Rhose (Priest) on May 07, 2003 at 18:35 UTC
    Here is a sample script I had sitting in my temp directory -- maybe it will help a bit. (Just make sure to enter "real" values for the constants ORAUSER, ORAPASS, and ORATNS.)

    #!/usr/bin/perl -w use strict; #-- Use modules use DBD::Oracle; use DBI; #-- Define local constants use constant TRUE => 1; use constant FALSE => 0; use constant ORAUSER => 'my_ora_user'; use constant ORAPASS => 'secret_password'; use constant ORATNS => 'ora_db_tns_name'; #-- Define local variables my $gDBHandle; my $gSQLCmd; my $gSQLHandle; my @gFields; #-- Initialize local variables $gSQLCmd = 'SELECT column_name, ' . ' nullable, ' . ' data_type, ' . ' data_length ' . ' FROM dba_tab_columns ' . ' WHERE owner = ? ' . ' AND table_name = ? ' . ' ORDER BY column_id '; #-- Connect to the database $gDBHandle = DBI->connect ( 'dbi:Oracle:' . ORATNS, ORAUSER, ORAPASS, { AutoCommit => FALSE, PrintError => FALSE, RaiseError => FALSE, } ) || die 'Could not connect to Oracle ['.$DBI::errstr.' - '.$DBI::er +r.']'; #-- Get the data $gSQLHandle = $gDBHandle->prepare($gSQLCmd) || die 'Error with SQL statement ['.$DBI::errstr.' - '.$DBI::err.']' +; $gSQLHandle->execute('SYS','DBA_TABLES') || die 'Error with SQL statement ['.$DBI::errstr.' - '.$DBI::err.']' +; while (@gFields = $gSQLHandle->fetchrow_array) { print $gFields[0],"\t",$gFields[1],"\t",$gFields[2],"\t",$gFields[3] +,"\n"; } #-- Close the database connection $gDBHandle->disconnect(); #-- Exit exit; #-- End of Example
Re: Perl on Oracle
by Anonymous Monk on May 07, 2003 at 18:12 UTC
    I don't think this is an Oracle question, more of an SQL, Perl DBI question. A simple example script probably goes like this, which is pretty much right out of the DBI synopsis (did you even look at that documentation?):

    use DBI; ... my $dbh = DBI->connect($dsn, $usr, $pwd, \%attr); my $stm = 'SELECT <something> FROM <something><etc>'; my $sth = $dbh->prepare($stm); $sth->execute; while (my $row_of_data = $sth->fetchrow_arrayref) { ...process data, ie, append each row to variable... } ... __END__

    A couple of notes here. The \%attr is an optional arg to connect, but using {AutoCommit => 1, RaiseError =>1} is recommended by Bunce et al in "Programming the Perl DBI" for convenience and safety. You WILL have to consult DBD::Oracle for how to fill in the $dsn arg. The meat of the script is really the contents of the $stm variable, and you will have to learn enough SQL to do what you want to do, no way around it. Finally, fetchrow_arrayref is used here because I don't know how many fields you are returning...fetchrow_array returns an array of results, whereas the former is faster for huge returns because it's merely passing around a reference to that array. This of course means you have to use the usual dereferencing semantics to get at the guts of the results.

Re: Perl on Oracle
by dragonchild (Archbishop) on May 07, 2003 at 20:12 UTC
    Maybe you should ask your teacher for an example to work from?

    We are the carpenters and bricklayers of the Information Age.

    Don't go borrowing trouble. For programmers, this means Worry only about what you need to implement.

    Please remember that I'm crufty and crochety. All opinions are purely mine and all code is untested, unless otherwise specified.

Log In?

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://256323]
Approved by diotalevi
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others chanting in the Monastery: (4)
As of 2023-06-10 04:30 GMT
Find Nodes?
    Voting Booth?
    How often do you go to conferences?

    Results (36 votes). Check out past polls.