Re: ORA-12154 - (DBD: login failed)

[Nelson, Erick]

Did you try TWO_TASK env var ?




Erick Nelson
Cell 858-740-6523
Home 760-930-0461


From: "Mani, Arunkumar (BMS - India GDC)" 
>
Date: Monday, August 8, 2016 at 1:14 AM
To: "mohammed.must...@wipro.com" 
>, 
"dbi-users@perl.org" 
>
Subject: RE: ORA-12154 - (DBD: login failed)

All were done earlier. The problem is perl doesn't recognize my sqlnet.ora or 
tnsnames.ora, though I define TNS_ADMIN correctly in the script.
Note: This was working when we used Oracle 8 client.

Arunkumar Mani
ITO Service Delivery Consultant
Database Engineering, BMS Account

arunkumar.m...@hpe.com
+91 80 338 59305  Office
+91 74062 71026  Mobile

Bangalore, Karnataka/India
hpe.com

[hpesm_pri_grn_pos_email_06]

From: mohammed.must...@wipro.com 
[mailto:mohammed.must...@wipro.com]
Sent: Thursday, August 4, 2016 8:53 PM
To: Mani, Arunkumar (BMS - India GDC) 
>; 
dbi-users@perl.org
Subject: Re: ORA-12154 - (DBD: login failed)




Testing: Manually, try connecting to DB using current .ora file content.





Solution:  update .ora file with right
Ip:
Port:
Sid/service name:




Regards,
Mustafa
+44-7440 56 12 32


From: Mani, Arunkumar (BMS - India GDC) 
>
Sent: Thursday, August 4, 2016 8:41 PM
To: dbi-users@perl.org
Subject: ORA-12154 - (DBD: login failed)


** This mail has been sent from an external source **

Hi,



We have a perl script which is used to connect to all our target databases ( 
close to 1000) and fetch the user related information and inturn compare it 
with our enterprise directory which is used to clear the inactive users 
periodically. This was a very old script and it was using Oracle 8i client. 
Since most of our systems were upgraded to 12c, 8i client is not compatible 
with 12c - so we had to update the perl script to use 10g client instead. After 
we did that, we are not able to connect to any databases and getting below 
errors. The script exclusively sets TNS_ADMIN to point to right sqlnet.ora.



Need your help to fix this.



ORA-12154: TNS:could not resolve the connect identifier specified (DBD: login 
failed)



Perl Version:



bash-2.03$ /usr/local/bin/perl -v



This is perl, version 5.005_03 built for sun4-solaris



Copyright 1987-1999, Larry Wall



Perl may be copied only under the terms of either the Artistic License or the

GNU General Public License, which may be found in the Perl 5.0 source kit.



Complete documentation for Perl, including FAQ lists, should be found on

this system using `man perl' or `perldoc perl'.  If you have access to the

Internet, point your browser at 
http://www.perl.com/,
 the Perl Home Page.



bash-2.03$





Arunkumar Mani
ITO Service Delivery Consultant
Database Engineering, BMS Account

arunkumar.m...@hpe.com
+91 80 338 59305  Office
+91 74062 71026  Mobile

Bangalore, Karnataka/India
hpe.com



[hpesm_pri_grn_pos_email_06]


The information contained in this electronic message and any attachments to 
this message are intended for the exclusive use of the addressee(s) and may 
contain proprietary, confidential or privileged information. If you are not the 
intended recipient, you should not disseminate, distribute or copy this e-mail. 
Please notify the sender immediately and destroy all copies of this message and 
any attachments. WARNING: Computer viruses can be transmitted via email. The 
recipient should check this email and any attachments for the presence of 
viruses. The company accepts no liability for any damage caused by any virus 
transmitted by this email. 
www.wipro.com

RE: Perl DBI Hangs while execute()

[Nelson, Erick [HDS]]

There are many things you can do in sqlplus that you cannot do in sql (in this 
case DBI).
What are you trying to run?

-Original Message-
From: amaresh pothnal [mailto:amaresh.poth...@gmail.com] 
Sent: Monday, November 19, 2012 3:29 AM
To: dbi-users@perl.org
Subject: Perl DBI Hangs while execute()

Hi All,

 I am using Perl DBI to connect Oracle Database.
 The query runs if type from 'sqlplus' manually but hangs when I use the
sql inside the script.
 Getting following error,
DBD::Oracle::st execute failed: ORA-36871: (XSFTDSC01) Object %s cannot be
used to define a column in a LIMITMAP. (DBD ERROR: OCIStmtExecute) [for
Statement

And If remove that original query and replace 'select * from dual' and
execute the script,
I get 'Segementation Fault'.

Please help me to solve this.
Thanks

-- 
Amaresh P

RE: :ODBC fails, SQL*Plus works

[Nelson, Erick [HDS]]

Well then I'm not sure, except to say why ODBC?
I'm not sure you can equate sqlplus and odbc. Sqlplus uses sqlnet to
connect while odbc uses ... not sure.
Do you not have an oracle client installed on your box?
Can't you put your sqlplus connection string into the oracle
tnsnames.ora file so you could connect like ?
 
  my $dbh = DBI-connect(dbi:Oracle:$tnsname, $user, $pass);

I also highly recommend that you use the 4th argument of the connect and
turn on RaiseError, like this

 my $dbh = DBI-connect(dbi:Oracle:$tnsname, $user, $pass, {RaiseError
= 1});

... so you can 'catch' errors with an eval instead of having to test for
it manually. This will make your code much cleaner.

Another option might be to run on trace levels.
This spits out a whole lotta stuff that may or may not be useful in
deducing your problem.
I'm pretty sure the levels are 1 - 5 where 5 is the most annoying.

  DBI-trace($trace_level)
  DBI-trace($trace_level, $trace_filename)


-Original Message-
From: Scott Stansbury [mailto:sstansb...@aol.com] 
Sent: Wednesday, October 05, 2011 9:22 AM
To: Nelson, Erick [HDS]
Cc: dbi-users@perl.org
Subject: Re: :ODBC fails, SQL*Plus works

I'm sorry - that was just fat fingers on the cut/paste...

In actuality it is $oracle_host = 192.168.15.200:1535


On Oct 5, 2011, at 12:13 PM, Nelson, Erick [HDS] wrote:
 Your $oracle_host Perl var looks like it has more that just the host
ip
 address and port in it.  What is the 10.128 ?
 
 -Original Message-
 From: Scott Stansbury [mailto:sstansb...@aol.com] 
 Sent: Wednesday, October 05, 2011 9:09 AM
 To: dbi-users@perl.org
 Subject: DBD::ODBC fails, SQL*Plus works
 
 Folks, 
 
 I could use a tip or lead with the following issue:
 
 I have a Windows Server 2008 R2 box with Oracle Instant Client 11.2,
 with the SQL*Plus and ODBC modules installed. No other Oracle software
 is installed.
 
 I am connecting to an Oracle 10g instance. The following SQL*Plus
works:
 
 sqlplus

user/passwd@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.
 168.15.200)(PORT=1535)))(CONNECT_DATA=(SID=PROD77))).
 
 The perl script that contains the following does not:
 
 use DBI;
 
 # Oracle / Remedy info
 
 my $oracle_sid = PROD77;
 my $oracle_host = 192.168.15.200:1535;
 
 my $oracle_userid = user;
 my $oracle_passwd = passwd;
 
 
 my $dsn = Driver={Oracle in
 instantclient_11_2};server=$oracle_host;sid=$oracle_sid;
 
 my $dbh = DBI-connect(dbi:ODBC:$dsn, $oracle_userid,
 $oracle_passwd);
 
 It returns (after a few seconds) with an ORA-12154 error: TNS:could
not
 resolve the connect identifier specified ( SQL-08004).
 
 Thanks for any help...
 
 Scott...
 
 
 
 



Scott...

RE: Problem with UTF8 and array binding....

[Nelson, Erick [HDS]]

I had this problem quite awhile ago with an old Oracle 9.2 database.
I don't remember why but the solution involved setting a couple of environment 
vars.

Looking back at those ancient, no longer used, problems I see

$ENV{NLS_LANG} = american_america.we8iso8859p1;
$ENV{ORA_NLS}  = $ENV{ORACLE_HOME}/ocommon/nls/admin/data;



-Original Message-
From: John Scoles [mailto:sco...@pythian.com] 
Sent: Thursday, February 17, 2011 1:10 PM
To: dbi-users@perl.org
Subject: Re: Problem with UTF8 and array binding


  On 17/02/2011 3:05 PM, Bobak, Mark wrote:

Perhaps you have to declare you in type as a NCLOB??

Cheers
John
 Hi all,

 I'm running into a 'PLS-00418: array bind type must match PL/SQL table row 
 type' error, but only when passing UTF8 data.

 The details are as follows.  I have a PL/SQL packaged function that looks 
 like this:
 FUNCTION MSTInsUpdCIT(
PI_CGP_ID   IN  VARCHAR2
 ,PI_CBL_ID   IN  VARCHAR2
 ,PI_VCL_ID   IN  VARCHAR2
 ,PI_CIL_LOCATOR  IN  VARCHAR2
 ,PI_VCL_ID_TAB   IN  vcl_id_tab_type
 ,PI_CIL_LOCATOR_TAB  IN  cil_locator_tab_type
 ,PI_CIT_HITLISTLINE  IN  VARCHAR2
 ,PI_CIT_ALPHA_DATE   IN  VARCHAR2
 ,PI_CIT_START_DATE   IN  VARCHAR2
 ,PI_CIT_END_DATE IN  VARCHAR2
 ,PI_CIT_SORT_DATEIN  VARCHAR2
,PI_CIT_NUMERIC_DATE IN  VARCHAR2
 ,PI_VIBC_ID_TAB  IN  VARCHAR2
 ,PI_CIBD_CODE_TABIN  CLOB
 ,PI_CIT_ABS_WORD_CNT IN  NUMBER
 ,PI_CIT_TXT_WORD_CNT IN  NUMBER
 ,PI_CIT_TYPE IN  VARCHAR2
 ,PI_CIT_UNDATED_FLAG IN  VARCHAR2
 ,PI_CIT_DELETE_FLAG  IN  VARCHAR2
 ,PI_ROUTER_PRIORITY  IN  NUMBER
 ,PI_CIT_COVERIN  VARCHAR2
 ,PO_VCS_IDOUT NUMBER
 ,PO_DELETE_FLAG  OUT  varchar2
 ,PO_HOLD_STATUS  OUT  varchar2
 ,PO_CIT_EXISTS   OUT NUMBER
 ,po_cit_id   OUT VARCHAR2
 ,PO_MESSAGE_GROUP_ID OUT VARCHAR2) RETURN NUMBER;

 The 'cil_locator_tab_type' is defined as:
 TYPE cil_locator_TAB_TYPE is table of cnt_item_locators.cil_locator%type 
 index by binary_integer;

 And cnt_item_locators.cil_locator is VARCHAR2(255 CHAR).

 Database characterset is AL32UTF8.

 In Perl, I'm defining an array reference as:
 my $cil_locator_tab = ['2004322656 Vädskulturmuseet i Göorg',
 '9789187484124 (Vädskulturmuseet i Göorg)',
 '2004322656 Vädskulturmuseet i Göorg'];

 which has UTF8 characters in it.

 Next, I'm setting the multibyte flag via:
 Encode::_utf8_on(@{$cil_locator_tab}[0]);
 Encode::_utf8_on(@{$cil_locator_tab}[1]);
 Encode::_utf8_on(@{$cil_locator_tab}[2]);

 Finally, I define a param_hash as:
 my $paramHash = {cbl_id   =  '23481',
   vcl_id   =  '15',
   cil_locator  =  '2004322656  Vädskulturmuseet i Göorg',
   vcl_id_tab   =  $vcl_id_tab,
   cil_locator_tab  =  $cil_locator_tab,
   hitlistline  =  'Viñ cielo, luna y arena. El espacio 
 Calchaquín el folclore argentino moderno~~~2003~Book Chapter',
   alphadate=  '2003',
   startdate=  '20030101',
   sortdate =  '20030101',
   numdate  =  '20030101',
   vibc_id_tab  =  '1',
   cibd_code_tab=  'MLA',
   cit_abs_word_cnt =  '0',
   cit_txt_word_cnt =  '0',
   cit_undated_flag =  'N',
   cit_delete_flag  =  'N',
   router_priority  =  '5',
   cover_flag   =  'N',
  };
 And the binding of the datatype is here:
  $sth-bind_param(':PI_CIL_LOCATOR_TAB', $paramHash-{cil_locator_tab},
  {ora_type =  ORA_VARCHAR2_TABLE, ora_maxarray_numentries =  
 $maxrows});

 Now, I call my PL/SQL, passing the paramHash, and I'm getting the PLS-00418 
 error described above.

 So, I guess I need to understand how to map my bind variable to match my 
 PL/SQL table type.

 This seems to work for non-UTF8 data, so, I'm not sure if this is a coding 
 error in my code, or some DBD/DBI bug?

 Can anyone offer any insights?

 Thanks,

 -Mark

RE: How to catch the returned error message.

[Nelson, Erick [HDS]]

When you create your database connection use the 4th arg to change
connection behaviour. Specifically, tell it to throw exceptions when
they occur so that you can trap them with an eval;

eval {
my $dbh = DBI-connect($connstr, $user, $pass, {RaiseError = 1});
my $stmt = $dbh-prepare($select_sql);
$stmt-execute();
while (my @row = $stmt-fetchrow_array()) {
}
};
if ($@) {
# error message, if one occurs, will now be in $@
# for any error thrown by DBI
}

-Original Message-
From: Parag Kalra [mailto:paragka...@gmail.com] 
Sent: Monday, November 09, 2009 2:37 AM
To: dbi-users@perl.org
Subject: How to catch the returned error message.

Hello All,

This is my first post here.

I am executing a Perl script which makes use of 'DBI' module.

I am performing a select operation on a table through the Perl script
and I
am getting following error on the console:

DBD::ODBC::st execute failed: [Oracle][ODBC][Ora]ORA-00942: table or
view
does not exist
 (SQL-42S02) at Test.pl line 67

The error message is well justified as the table doesn't exist.

However I just wanted to know if there is a way to catch this error
message
through some variable so that I can print it in a log file as well.

Cheers,
Parag

RE: new question: accurately detecting ? placeholders

[Nelson, Erick [HDS]]

Maybe trap for oracle error number 1008 ?

my @sql = (
q{select * from sfile where id = ?},
q{select * from sfile where id = '?'},
);
for my $sql (@sql) {
my $stmt = $dbh-prepare($sql);
eval {
$stmt-execute();
};
if ($@) {
if ($stmt-err() == 1008) {
print statement: $sql is missing a bind var\n;
}
}
}


 perl etest
statement: select * from sfile where id = ? is missing a bind var

-Original Message-
From: E R [mailto:pc88m...@gmail.com] 
Sent: Monday, April 20, 2009 2:56 PM
To: dbi-users@perl.org
Subject: new question: accurately detecting ? placeholders

I've got tons of sql that uses ? placeholders.

Is there a way to accurately determine which question marks represent
placeholders and which ones don't?

For instance:

  SELECT LENGTH('?') FROM DUAL;

v.s.

  SELECT LENGTH(?) FROM DUAL;

Thanks,
ER

RE: dbi, can kill an oracle job?

[Nelson, Erick [HDS]]

Look up 'ALTER SYSTEM KILL SESSION sid,serial'

You can get sid and serial from sys.v_$session

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: Friday, May 16, 2008 10:23 AM
To: dbi-users@perl.org
Subject: dbi, can kill an oracle job?

oracle 101 guy

env: oracle 9i, linux, perl dbi, apache, webpages with  javascript

Executing a sql statement from a browser with  perl and dbi.

Can we kill the job?  There are two main scenarios where the option to
kill might be nice.  The user leaves the browser and, two,  box
resources get to a point where it is no longer useful to try and
finish the job (excessive load on the box).

Suggestions?  I've never killed an Oracle job before.  I assume dbi
has a way to get a process/job number and the facility to kill it ...
right?

Thank you.

RE: Recursive SQL query?

[Nelson, Erick [HDS]]

The closest thing that comes to this would be Oracle's Connect By
Prior syntax.  I don't know if other database vendors support this.

-Original Message-
From: Colin Wetherbee [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, April 15, 2008 11:36 AM
To: Scott Webster Wood
Cc: dbi-users@perl.org
Subject: Re: Recursive SQL query?

Scott Webster Wood wrote:
 let'sjust say I left Property1 blank on the link forWindows and also
 in it's parent Desktop but had it filled in on theDesktop parent
 Computers (as shown above).  If I wanted to find the Property1 for
 the sub category but found it blank, I'd like to grab thevalue from
 the next parent(s) above it that did have a value.
 
 Is there any way to writethat kind of recursive search right in the
 SQL itself rather than doingtons of lookups in an 'until' type
 structure in my scripting languageuntil I find a value that's not
 null?

This sounds rather like a poor database design.

Each row of a table should contain one fact, which can then be merged 
via JOIN to incorporate supplementary information pertaining to that 
single fact.  You shouldn't really need to go recursing through a data 
tree to come up with all the parts of a single fact.

For your example, which I've omitted in preference of brevity, I would 
suggest adding a property table with two columns: primary key for 
property id and property description.  Then, instead of storing property

description text separately in your categories and categoryLinks tables,

have a foreign key reference the id column in the property table.

In addition, I'd get rid of your categoryLinks table altogether.  In the

categories table, for each row that needs a parent, include a foreign 
key that references that same table's primary key.  Then, OUTER JOIN the

table to itself later.

If your tree is going to be very deep, keep in mind that some databases 
impose (reasonable) limits on the number of JOINs that can be in a 
single SQL statement.  In the end, you may want to do some processing in

a stored procedure, too, rather than relying on a single SQL query to 
provide your entire hierarchy.

Just my $0.02.

Also, since the DBI list isn't really the right place to ask database 
design questions, you'll probably want to find a more SQL-centric forum 
for future questions.  PostgreSQL maintains a pgsql-sql list for 
SQL-specific questions, for example.

PostgreSQL would probably be a very good choice for your implementation,

by the way, since it has extensive text searching abilities.

Colin

RE: compiling DBD module on system with multiple versions of oracle

[Nelson, Erick [HDS]]

For the last question, you could always try out DBD::Proxy

-Original Message-
From: Richard Labutis [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, September 12, 2007 12:17 PM
To: dbi-users@perl.org
Subject: compiling DBD module on system with multiple versions of oracle

if you have a system with multiple versions of oracle and using Perl DBI
and
DBD modules.

how to use/setup the DBD module to work on any version of oracle. In
otherwords which oracle environment am i going to use?
the make file will hard code the full path of the libraries into the
binaries.

what happens when  you need to create the dbi/dbd module for another
system
that does not have the version of oracle on the system that has the IBM
C
compiler?

RE: perl date

[Nelson, Erick [HDS]]

I've created dbf files using DBD::XBase a few times.
To get dates to insert correctly I had to have the date in MMDD
format.
DBD::Xbase writes dbase 4 files.
If Clipper is old enough that it is using dbase 3 files the answer might
be different.

-Original Message-
From: Samuel_Zheng [mailto:[EMAIL PROTECTED] 
Sent: Monday, August 27, 2007 8:45 AM
To: dbi-users@perl.org
Subject: perl date

Hi,

I am using ODBC to convert an old clipper application to a web based
application and I came across something seems simple but it's not.

how can I save a date from perl to a date field in dbf file? can a perl
expert helps out someone who is novice in perl like me? your help would
be very much appreciated! Thanks.

Samuel

RE: Special characters

[Nelson, Erick [HDS]]

I had that problem too.
I ended up having to set to env vars to make the ? marks go away.

   $ENV{NLS_LANG} = american_america.we8iso8859p1;
   $ENV{ORA_NLS}  = $ENV{ORACLE_HOME}/ocommon/nls/admin/data;

These must be set before you get a connection

-Original Message-
From: Dan King [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, August 21, 2007 10:33 AM
To: dbi-users@perl.org
Subject: Special characters

I am having issues running a web application, called OTRS, that uses DBI and 
DBD::Oracle. When I insert special characters, such as é or â they show up as 
question marks in the database when looking at them from sqlplus or through the 
web application. I am running the web app on Apache v1 with an Oracle 10g 
backend.

 

I have been able to successfully insert and read the special characters using 
sqlplus from the command line of the server running the web app, apache and 
Oracle as well as creating a small perl test script that looks as follows:

 

#!/usr/local/bin/perl

use warnings;

use strict;

use Data::Dumper;

use DBI;

use DBD::Oracle;

 

my $dbh = DBI-connect(dbi:Oracle:host=localhost;sid=QAT, rm_ticket, 
rm_ticket);

my $sql = qq{ UPDATE signature SET text='çè' WHERE id=1 };

my $sth = $dbh-prepare($sql);

$sth-execute();

$sth-finish();

$dbh-disconnect;

 

The reading script looks as follows:

 

#!/usr/local/bin/perl

use warnings;

use strict;

use Data::Dumper;

use DBI;

use DBD::Oracle;

 

my $dbh = DBI-connect(dbi:Oracle:host=localhost;sid=QAT, rm_ticket, 
rm_ticket);

my $sql = qq{ SELECT text from signature };

my $sth = $dbh-prepare($sql);

$sth-execute();

my($text);

$sth-bind_columns(undef, \$text);

print Text of signature:\n\n;

while( $sth-fetch() ) {

print Object: $text\n;

}

$sth-finish();

$dbh-disconnect;

 

Does anyone know why I can successfully write and read from the database when I 
am not using the web application but when I use the web application the special 
characters fail to work properly?

Dan King
Software Developer
Canadian Resident Matching Service
613.237.0075  ext. 241
(Toll free) 877.CARMS.42
171 Nepean Street, Suite 300
Ottawa, ON, CANK2P 0B4
www.carms.ca http://www.carms.ca 



This e-mail message, including any attachments, is for the sole use of the 
intended recipients and may contain confidential and or privileged information. 
 If you are not the intended recipient or this information has been forwarded 
in error, please contact the sender by reply e-mail and destroy copies of the 
original message.  Ce message (incluant toute pièce jointe) s'adresse 
uniquement au(x) destinataire(s) prévu(s) ou à une personne autorisée à le 
recevoir en son (leur) nom. Il pourrait contenir des renseignements 
confidentiels ou protégés.  Si vous l'avez reçu par erreur, nous vous prions 
d'en informer l'auteur dans les meilleurs délais, de ne pas divulguer son 
contenu et de le supprimer de votre système. Merci.

 

RE: Connecting to Progress Database

[Nelson, Erick [HDS]]

But you can make an ODBC connection to Progress so you should be able to
use DBD::ODBC, right?

-Original Message-
From: Jeff Zucker [mailto:[EMAIL PROTECTED] 
Sent: Thursday, August 16, 2007 9:54 AM
To: Alexander Foken
Cc: Ram; dbi-users@perl.org
Subject: Re: Connecting to Progress Database

There seems to be no *progress* on this list since the last time someone

asked about a DBD for *progress* (not postgres), someone also mentioned 
DBD::Pg.  Progress is a commercial RDBMS and if there's a DBD for it, I 
haven't seen one.

-- 
Jeff

Alexander Foken wrote:
 There is DBD::Pg, available from CPAN. You also need to install 
 PostgreSQL (client only) to compile DBD::Pg.

 Alexander

 Ram wrote:
 Hi,
 I need to connect to the progress database through perl. I was 
 wondering if there is any way that I can use the DBI module to do 
 this. I searched the CPAN but could not find a module to use for 
 progress DB. Though I would ask in here.

 Thanks in advance.
 Ram.