(OT) DBD error Ora-12154

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";
[download]

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'

Comment on (OT) DBD error Ora-12154 Download Code

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. Read more... (1452 Bytes) Ivan Heffner Sr. Software Engineer, DAS Lead WhitePages.com, Inc.	[reply] [d/l]
Re^2: (OT) DBD error Ora-12154 by Anonymous Monk on Apr 13, 2006 at 18:57 UTC
Thanks for the help everyone... I will try this and see what happens.. I really appreciate all the feed back....	[reply]
Re^2: (OT) DBD error Ora-12154 by Anonymous Monk on Apr 13, 2006 at 19:54 UTC
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??	[reply]
Re^3: (OT) DBD error Ora-12154 by beachbum (Beadle) on Apr 13, 2006 at 21:36 UTC
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.	[reply]
Re^4: (OT) DBD error Ora-12154 by Anonymous Monk on Apr 13, 2006 at 22:55 UTC
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.	[reply]
Re^2: (OT) DBD error Ora-12154 by Anonymous Monk on Apr 10, 2015 at 18:06 UTC
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 !!!	[reply]
Re^3: (OT) DBD error Ora-12154 by Anonymous Monk on Jul 16, 2020 at 17:20 UTC
Any luck? I am going through the same thing now. Everything was working fine and suddenly I can't connect to Oracle from perl.	[reply]
Re^2: (OT) DBD error Ora-12154 by Anonymous Monk on Apr 13, 2006 at 16:55 UTC
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) ) )` [download] This is what the other software are using to connect to oracle and they seem to be working fine. Thanks again...	[reply] [d/l]
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/.	[reply] [d/l] [select]
Re^2: (OT) DBD error Ora-12154 by Anonymous Monk on Apr 13, 2006 at 17:02 UTC
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?	[reply]
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";` [download] 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.	[reply] [d/l]