Error connecting to Oracle using PERL DBI

[hakiran]

Hello all,

I have been using Perl DBI the last 6months or so. I use it extensively with MySQL. But recently i tried to access Oracle DB with it and was having trouble. Any help would be appreciated.

Here is the code and the error i get. I know the table/view do exist.

Thanks all
Kiran

----------

ERROR

>>>> ./get_subject_code_for_ids.pl 
DBD::Oracle::db prepare failed: ORA-00942: table or view does not exist (DBD ERROR: error possibly near <*> indicator at char 18 in 'SELECT * FROM sys.<*>LOOKUP') [for Statement "SELECT * FROM sys.LOOKUP"] at ./get_subject_code_for_ids.pl line 37. 
DBD::Oracle::db prepare failed: ORA-00942: table or view does not exist (DBD ERROR: error possibly near <*> indicator at char 18 in 'SELECT * FROM sys.<*>LOOKUP') [for Statement "SELECT * FROM sys.LOOKUP"] at ./get_subject_code_for_ids.pl line 37. 
 

#!/usr/bin/perl -w 
use strict; 
 
use Carp; 
use Data::Dumper; 
use DBI; 
use DBD::Oracle qw(:ora_types); 
 
#DBI->trace(3); 
################################### 
# ORACLE ENV VARIABLES 
################################### 
#$ENV{ORACLE_HOME} = q{/usr/lib/oracle/10.2.0.3/client/lib/}; 
#$ENV{ORACLE_SID} = q{cag}; 
 
#print $ENV{ORACLE_HOME}; 
############################################ 
# ORACLE DATABASE PARAMETERS 
############################################ 
 
my $user= "username"; 
my $password= "password"; 
my $host= "vc1.cag.chop.edu"; 
my $sid = "cag"; 
my $port = 1521; 
 
############################### 
# CONNECT TO DATABASE 
############################## 
my $dbh = DBI->connect("DBI:Oracle:host=$host;port=$port;sid=$sid",$user, $password, {PrintError=>1,RaiseError=>1}) or die "Can't connect to ORACLE database:$DBI::errstr\n"; 
 
################################ 
# SETTING UP QUERIES 
################################ 
my $subj_code_query = qq{SELECT * FROM sys.LOOKUP}; 
my $sth_subj = $dbh->prepare($subj_code_query); 
 
&execute_subject_query; 
 
# EXECUTE SUbject QUERY and write data to SUBJECT.txt 
############################### 
 
sub execute_subject_query() { 
    open(SWRT, ">SUBJECT_CODES_FOR_XML.txt"); 
    print "$sth_subj\n"; 
    my $rv = $sth_subj->execute(); 
    if ($rv == 0) { 
        print "No Value Returned\n"; 
    } else { 
        while( my $array_ref = $sth_subj->fetchrow_arrayref) { 
            for(my $i=0; $i<@$array_ref;$i++) { 
                print "$array_ref->[$i]\n";<STDIN>; 
                print SWRT "$array_ref->[$i]\n"; 
            } 
        } 
    } 
    $sth_subj->finish(); 
    close SWRT; 
}
 

[numberwhun]

And when you take the "SELECT * FROM sys.LOOKUP" and you use it directly in the Oracle interface ( hopefully similar to the mysql prompt), does it return what you expect or throw an error?

Also, be sure to use code tags around your code when posting.

Regards,

Jeff

[hakiran]

i do not have direct access to the DB from command line. I use either TOAD or Oracle SQL Developer interface to query the Oracle DB.

You think that is preventing me from connecting via scripts?

But via the interface i use

Select * from lookup; 
 

and it works fine.

my code says "sys.lookup"....but either ways, i get the same error.

[numberwhun]

Ok, in looking again (with new bits of info), I notice that this script is geared for Unix (thus the shebang line on line 1). If that is the case, then you are dealing with case sensitivity. In your code you have "sys.LOOKUP", and in your example, you list it as "lookup". That is one difference. The other being that the table in your example that worked is called "lookup", but as you stated, your code has sys.LOOKUP. If you used lookup in your example and it worked, why not use it in your code?

Regards,

Jeff