468,103 Members | 1,378 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,103 developers. It's quick & easy.

Error connecting to Oracle using PERL DBI

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

Expand|Select|Wrap|Line Numbers
  1. >>>> ./get_subject_code_for_ids.pl 
  2. 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. 
  3. 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. 
  4.  
Expand|Select|Wrap|Line Numbers
  1. #!/usr/bin/perl -w 
  2. use strict; 
  3.  
  4. use Carp; 
  5. use Data::Dumper; 
  6. use DBI; 
  7. use DBD::Oracle qw(:ora_types); 
  8.  
  9. #DBI->trace(3); 
  10. ################################### 
  11. # ORACLE ENV VARIABLES 
  12. ################################### 
  13. #$ENV{ORACLE_HOME} = q{/usr/lib/oracle/10.2.0.3/client/lib/}; 
  14. #$ENV{ORACLE_SID} = q{cag}; 
  15.  
  16. #print $ENV{ORACLE_HOME}; 
  17. ############################################ 
  18. # ORACLE DATABASE PARAMETERS 
  19. ############################################ 
  20.  
  21. my $user= "username"; 
  22. my $password= "password"; 
  23. my $host= "vc1.cag.chop.edu"; 
  24. my $sid = "cag"; 
  25. my $port = 1521; 
  26.  
  27. ############################### 
  28. # CONNECT TO DATABASE 
  29. ############################## 
  30. 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"; 
  31.  
  32. ################################ 
  33. # SETTING UP QUERIES 
  34. ################################ 
  35. my $subj_code_query = qq{SELECT * FROM sys.LOOKUP}; 
  36. my $sth_subj = $dbh->prepare($subj_code_query); 
  37.  
  38. &execute_subject_query; 
  39.  
  40. # EXECUTE SUbject QUERY and write data to SUBJECT.txt 
  41. ############################### 
  42.  
  43. sub execute_subject_query() { 
  44.     open(SWRT, ">SUBJECT_CODES_FOR_XML.txt"); 
  45.     print "$sth_subj\n"; 
  46.     my $rv = $sth_subj->execute(); 
  47.     if ($rv == 0) { 
  48.         print "No Value Returned\n"; 
  49.     } else { 
  50.         while( my $array_ref = $sth_subj->fetchrow_arrayref) { 
  51.             for(my $i=0; $i<@$array_ref;$i++) { 
  52.                 print "$array_ref->[$i]\n";<STDIN>; 
  53.                 print SWRT "$array_ref->[$i]\n"; 
  54.             } 
  55.         } 
  56.     } 
  57.     $sth_subj->finish(); 
  58.     close SWRT; 
  59. }
  60.  
Aug 21 '07 #1
3 6342
numberwhun
3,503 Expert Mod 2GB
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
Aug 21 '07 #2
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
Expand|Select|Wrap|Line Numbers
  1. Select * from lookup; 
  2.  
and it works fine.

my code says "sys.lookup"....but either ways, i get the same error.
Aug 21 '07 #3
numberwhun
3,503 Expert Mod 2GB
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
Aug 21 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Paul | last post: by
reply views Thread by christian_stengel | last post: by
2 posts views Thread by python_eager | last post: by
4 posts views Thread by alegria4ever | last post: by
1 post views Thread by Solo | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.