Beefy Boxes and Bandwidth Generously Provided by pair Networks
Syntactic Confectionery Delight
 
PerlMonks  

(OT) DBD error Ora-12154

by Anonymous Monk
on Apr 13, 2006 at 16:16 UTC ( [id://543134]=perlquestion: print w/replies, xml ) Need Help??

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

I am having problems connecting to Oracle databases using Perl. I am able to connect to these databases using Benthic Golden32(third party software) and SQLPlus. Im using activestate perl 5.6, DBD-Oracle 1.16, DBI 1.48. I never had this problem before using the same scripts. I just got a new computer at work, dualcore Xeons and now I cant connect after re-installing everything.
Here is the error: failed: ORA-12154: TNS:could not resolve the connect identifier specif +ied (DBD ERROR: OCIServerAttach) on line 70. Heres the code for my script: line 70 is the line that starts with my $dbp = DBI->.... print "Setting Database Connection to CDW-P.....\n"; # setting up database connection to CDW-P my $dbp = DBI->connect( 'dbi:Oracle:CDWP02' , "$user1" , "$passw") || die "Database connection not made: $DBI::errstr"; # Queries $Total_Free = "select SUM(BYTES/(1024*1024*1024)) as FREE_GB\n". "from dba_free_space WHERE TABLESPACE_NAME = 'USERSAUTO'"; $Total_V_Used = "select SUM(bytes/(1024*1024*1024)) as USE_GB\n". "from dba_segments WHERE TABLESPACE_NAME = 'USERSAUTO'\n"; $Used_by_ID = "select owner, SUM(bytes/(1024*1024*1024)) as TOTAL_GB\n +". "from dba_segments WHERE TABLESPACE_NAME = 'USERSAUTO'\n". "group by owner\n". "order by TOTAL_GB desc"; # Run Used Space Query print "Getting Used Space.\n"; ### Prepare a SQL statement for execution $CDWP_space_used = $dbp->prepare( "$Total_V_Used"); ### Execute the statement in the database $CDWP_space_used->execute; while ( $row = $CDWP_space_used->fetchrow_array( ) ) { $GC_space_used = $row; } print "$GC_space_used , Total CDW-P space\n";
Let me know if you need more information or the whole code for the script. Thanks in advance to all u perl gurus....

2006-04-14 Retitled by planetscape, as per Monastery guidelines
Original title: 'DBD error Ora-12154'

Replies are listed 'Best First'.
Re: (OT) DBD error Ora-12154
by Codon (Friar) on Apr 13, 2006 at 17:50 UTC
    Try breaking things down into smaller pieces to find out where it's breaking, so you can work on the solution. Start with a simple script that just dumps $ENV{'ORACLE_HOME'}, checks the existence (and permissions) of "$ENV{'ORACLE_HOME'}/network/admin/tnsnames.ora"; looks for the desired SID in that file, then tries to connect to that data base. If it works from a simple script, then your lager application must have some bug.

    Ivan Heffner
    Sr. Software Engineer, DAS Lead
    WhitePages.com, Inc.
      Thanks for the help everyone... I will try this and see what happens.. I really appreciate all the feed back....
      I tried running this script and I get error stating "Use of uninitialized value in concatenation (.) or string at H:scripts\orcl_test.pl line 8 and line 9". So basically it doesnt know what "$ENV{'ORACLE_HOME'}/network/admin/tnsnames.ora" means??

        Is ORACLE_HOME a variable that is defined? You may need to set up your environment first.

        Also, you could check out tnsping from your new computer, this will help determine if you really can get to the database or not with your current config.

Re: (OT) DBD error Ora-12154
by Fletch (Bishop) on Apr 13, 2006 at 16:22 UTC

    Well, that means that Oracle couldn't connect to the name you gave it . . . doesn't get much simpler than that. You need to consult with your DBA and or sysadmin and make sure that "CDWP02" is the right database name (I think "sid" in Oracle parlance) to be using.

      Man here almost 9 years later I am going through the same head against the wall, bleeding, eyes flaming. Oracle is really the biggest pain to config and I go through this every so many years.

      To all those with idea it's the tnsnames.ora or it cant find it is just flat wrong. Why?

      tnsping hits my alias in tnsnames.ora fine
      toad works fine with the same oracle home
      sqlplus connects with tnsname
      environment vars set
      ORACLE_HOME standard app/client/"username"/client/oracle
      TNS_ADMIN oracle_home/network/admin
      then perl dbi TNS:could not resolve the connect identifier !!! WHAT THE HECK !!!
        Any luck? I am going through the same thing now. Everything was working fine and suddenly I can't connect to Oracle from perl.
      Well CDWP02 worked before with the same type of settings here is what the tnsnames.ora file has for this database:
      CDWP02.HQ.TARGET.COM = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = cdwdb)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = CDWP02) ) )
      This is what the other software are using to connect to oracle and they seem to be working fine. Thanks again...
Re: (OT) DBD error Ora-12154
by Paladin (Vicar) on Apr 13, 2006 at 16:56 UTC
    Since you say you reinstalled everything, make sure your $ORA_HOME/network/admin/tnsnames.ora and $ORA_HOME/network/admin/sqlnet.ora files are set up correctly. You'll need to talk to your DBA for the correct settings for these files.

    You can read more about the error at http://ora-12154.ora-code.com/.

      Well I would think they are ok since the other apps are able to connect just not my Perl scripts, heres, what I remember from my Activestate Perl installation, I was able to check the 'Add Perl to the Path Environment' and 'Create Perl file extension association' but not 'Create IIS script mapping for Perl' or 'Create IIS script mapping for Perl ISAPI'. Would this have anything to do with it?
Re: (OT) DBD error Ora-12154
by kwaping (Priest) on Apr 13, 2006 at 17:00 UTC
    I don't know about your setup, but we need this line in ours:
    #set env variable for database $ENV{'ORACLE_HOME'}="/path/to/oracle/install";
    Try putting something like that near the start of your program.

    ---
    It's all fine and dandy until someone has to look at the code.

Log In?
Username:
Password:

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

How do I use this?Last hourOther CB clients
Other Users?
Others chilling in the Monastery: (5)
As of 2024-03-29 02:36 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found