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
Expand|Select|Wrap|Line Numbers
- >>>> ./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.
Expand|Select|Wrap|Line Numbers
- #!/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;
- }