473,224 Members | 1,412 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,224 software developers and data experts.

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 6786
numberwhun
3,509 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,509 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

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

Similar topics

1
by: Paul | last post by:
hi, i'm getting the following error when i try to run the code below to connect to an oracle db: java.lang.ClassnotfoundException:oracle.jdbc.driver.OracleDriver java.sql.SQLException No...
0
by: christian_stengel | last post by:
Hi *, I have just started to learn python and I am having a problem with an python client connecting to a perl server using ssl (I tried this with pyOpenSSL and with the build in SSL Module). ...
0
by: Kong Li | last post by:
Follow up to this thread, the latest Oracle 9i release 2 patchset (9.2.0.5) fixed the handle count leak problem. The problem is in Oracle client component. Thanks. Kong ----- From: Kong...
2
by: python_eager | last post by:
Hi i am connecting my database oracle 9i. While connecting i am getting the following error connection = cx_Oracle.connect("myusername", "mypassword", "python") RuntimeError: Unable to acquire...
2
by: Mark Cummings | last post by:
I am migrating an Oracle 9i to Oracle 10g database, and Perl 5.0.4 to Perl 5.8.7 in Sun Solaris environment. Using Perl 5.8.7, the following legacy code segment is returning a '-1' return code on...
4
by: alegria4ever | last post by:
I have an Access 2000 database that links several tables from Oracle 9. For some reason or another, one of our user repeatedly gets the following error when accessing queries within this database:...
4
by: Chronictank | last post by:
Hi, as a bit of background (and seeing as it is my first post :)) i am a complete newbie at perl. I literally picked it up a week ago to do this project as it seemed like the best choice for a...
2
by: devas | last post by:
Hai, Issue: I am using OEM 10g grid control. I want to establish the connection from oracle to sql server=>I canít able to connect it. Please help me I have created the system dsn below steps....
1
by: 2desperate2usedesperate | last post by:
ERROR: You do not have SELECT privileges on sys.V_$PARAMETER view. This process cannot proceed. Hello, I am using ORACLE 10g Standard Edition and I am trying to configure the Oracle Designer for...
1
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.