DBD::Oracle::db prepare failed: ORA-00932: inconsistent datatypes

I cannot do a bind_param () before I prepare, but prepare () already
fails :(

$ cat test.pl
use strict;
use warnings;

use DBI;

my $dbh = DBI->connect ("dbi:Oracle:", $ENV{DBI_USER}, $ENV{DBI_PASS}, {
    PrintError       => 1,
    RaiseError       => 1,
    PrintWarn        => 1,
    FetchHashKeyName => "NAME_lc",
    AutoCommit       => 0,
    }) or die "Connect: $DBI::errstr\n";

$dbh->do (qq;
    create global temporary table t_foo (
	h_key	blob,
	h_value	blob
	);
    );

my $sth = $dbh->prepare ("select h_value from t_foo where h_key = ?");
$ perl test.pl
DBD::Oracle::db prepare failed: ORA-00932: inconsistent datatypes: expected - got BLOB (DBD ERROR: error possibly near <*> indicator at char 32 in 'select h_value from t_foo where <*>h_key = :p1') [for Statement "select h_value from t_foo where h_key = ?"] at test.pl line 23.
DBD::Oracle::db prepare failed: ORA-00932: inconsistent datatypes: expected - got BLOB (DBD ERROR: error possibly near <*> indicator at char 32 in 'select h_value from t_foo where <*>h_key = :p1') [for Statement "select h_value from t_foo where h_key = ?"] at test.pl line 23.
Issuing rollback() due to DESTROY without explicit disconnect() of DBD::Oracle::db handle  at test.pl line 23.


$ perl -MV=DBI,DBD::Oracle
DBI
        /pro/lib/perl5/site_perl/5.10.1/x86_64-linux/DBI.pm: 1.613
DBD::Oracle
        /pro/lib/perl5/site_perl/5.10.1/x86_64-linux/DBD/Oracle.pm: 1.24


Oracle-client: Release 11.2.0.0.2

Oracle-server: Oracle Database 11g Enterprise Edition
               Release 11.1.0.6.0 - 64bit Production

-- 
H.Merijn Brand  http://tux.nl      Perl Monger  http://amsterdam.pm.org/
using 5.00307 through 5.12 and porting perl5.13.x on HP-UX 10.20, 11.00,
11.11, 11.23, and 11.31, OpenSuSE 10.3, 11.0, and 11.1, AIX 5.2 and 5.3.
http://mirrors.develooper.com/hpux/           http://www.test-smoke.org/
http://qa.perl.org      http://www.goldmark.org/jeff/stupid-disclaimers/
0
8/5/2010 11:49:57 AM
perl.dbi.dev 1960 articles. 0 followers. Follow

3 Replies
1799 Views

Similar Articles

[PageSpeed] 12
Get it on Google Play
Get it on Apple App Store

On Thu, 5 Aug 2010 13:49:57 +0200, "H.Merijn Brand"
<h.m.brand@xs4all.nl> wrote:

> my $sth = $dbh->prepare ("select h_value from t_foo where h_key = ?");
> $ perl test.pl
> DBD::Oracle::db prepare failed: ORA-00932: inconsistent datatypes: expected - got BLOB (DBD ERROR: error possibly near <*> indicator at char 32 in 'select h_value from t_foo where <*>h_key = :p1') [for Statement "select h_value from t_foo where h_key = ?"] at test.pl line 23.
> DBD::Oracle::db prepare failed: ORA-00932: inconsistent datatypes: expected - got BLOB (DBD ERROR: error possibly near <*> indicator at char 32 in 'select h_value from t_foo where <*>h_key = :p1') [for Statement "select h_value from t_foo where h_key = ?"] at test.pl line 23.
> Issuing rollback() due to DESTROY without explicit disconnect() of DBD::Oracle::db handle  at test.pl line 23.

With 1.25rc2

$ perl /tmp/test.pl
DBD::Oracle::db prepare failed: ORA-00932: inconsistent datatypes: expected - got BLOB (DBD ERROR: error possibly near <*> indicator at char 32 in 'select h_value from t_foo where <*>h_key = :p1') [for Statement "select h_value from t_foo where h_key = ?"] at /tmp/test.pl line 23.
DBD::Oracle::db prepare failed: ORA-00932: inconsistent datatypes: expected - got BLOB (DBD ERROR: error possibly near <*> indicator at char 32 in 'select h_value from t_foo where <*>h_key = :p1') [for Statement "select h_value from t_foo where h_key = ?"] at /tmp/test.pl line 23.
Issuing rollback() due to DESTROY without explicit disconnect() of DBD::Oracle::db handle  at /tmp/test.pl line 23.

-- 
H.Merijn Brand  http://tux.nl      Perl Monger  http://amsterdam.pm.org/
using 5.00307 through 5.12 and porting perl5.13.x on HP-UX 10.20, 11.00,
11.11, 11.23, and 11.31, OpenSuSE 10.3, 11.0, and 11.1, AIX 5.2 and 5.3.
http://mirrors.develooper.com/hpux/           http://www.test-smoke.org/
http://qa.perl.org      http://www.goldmark.org/jeff/stupid-disclaimers/
0
h
8/5/2010 11:58:39 AM
On 05/08/10 12:49, H.Merijn Brand wrote:

Snip

> $dbh->do (qq;
>     create global temporary table t_foo (
> 	h_key	blob,
> 	h_value	blob
> 	);
>     );
> 
> my $sth = $dbh->prepare ("select h_value from t_foo where h_key = ?");
> $ perl test.pl
> DBD::Oracle::db prepare failed: ORA-00932: inconsistent datatypes: expected - got BLOB (DBD ERROR: error possibly near <*> indicator at char 32 in 'select h_value from t_foo where <*>h_key = :p1') [for Statement "select h_value from t_foo where h_key = ?"] at test.pl line 23.

This is nothing to do with perl. You get this message from Oracle if you
attempt to compare a blob with anything, including itself. Here is a
simple demonstration using SQLPlus.

 Connected to:
 Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
 SQL> create table t_foo (h_key   blob);
 Table created.
 SQL> select h_key from t_foo where h_key = h_key;
 select h_key from t_foo where h_key = h_key
                               *
 ERROR at line 1:
 ORA-00932: inconsistent datatypes: expected - got BLOB

It is a bad error message, but the rule is clear. This is
from the section on Comparison Conditions in the Oracle 10.2
SQL Reference Manual

> Large objects (LOBs) are not supported in comparison conditions.
> However, you can use PL/SQL programs for comparisons on CLOB data.

-- 
Charles Jardine - Computing Service, University of Cambridge
cj10@cam.ac.uk    Tel: +44 1223 334506, Fax: +44 1223 334679
0
cj10
8/6/2010 8:56:36 AM
On Fri, 06 Aug 2010 09:56:36 +0100, Charles Jardine <cj10@cam.ac.uk>
wrote:

> On 05/08/10 12:49, H.Merijn Brand wrote:
> 
> Snip
> 
> > $dbh->do (qq;
> >     create global temporary table t_foo (
> >         h_key	blob,
> >         h_value	blob
> >         );
> >     );
> > 
> > my $sth = $dbh->prepare ("select h_value from t_foo where h_key = ?");
> > $ perl test.pl
> > DBD::Oracle::db prepare failed: ORA-00932: inconsistent datatypes: expected - got BLOB (DBD ERROR: error possibly near <*> indicator at char 32 in 'select h_value from t_foo where <*>h_key = :p1') [for Statement "select h_value from t_foo where h_key = ?"] at test.pl line 23.
> 
> This is nothing to do with perl. You get this message from Oracle if you
> attempt to compare a blob with anything, including itself.

How ultimately useless. Yet another reason to hate Oracle.
Thanks for the heads-up though.

> Here is a simple demonstration using SQLPlus.
> 
>  Connected to:
>  Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
>  SQL> create table t_foo (h_key   blob);
>  Table created.
>  SQL> select h_key from t_foo where h_key = h_key;
>  select h_key from t_foo where h_key = h_key
>                                *
>  ERROR at line 1:
>  ORA-00932: inconsistent datatypes: expected - got BLOB
> 
> It is a bad error message, but the rule is clear. This is
> from the section on Comparison Conditions in the Oracle 10.2
> SQL Reference Manual
> 
> > Large objects (LOBs) are not supported in comparison conditions.
> > However, you can use PL/SQL programs for comparisons on CLOB data.

-- 
H.Merijn Brand  http://tux.nl      Perl Monger  http://amsterdam.pm.org/
using 5.00307 through 5.12 and porting perl5.13.x on HP-UX 10.20, 11.00,
11.11, 11.23, and 11.31, OpenSuSE 10.3, 11.0, and 11.1, AIX 5.2 and 5.3.
http://mirrors.develooper.com/hpux/           http://www.test-smoke.org/
http://qa.perl.org      http://www.goldmark.org/jeff/stupid-disclaimers/
0
h
8/6/2010 9:46:53 AM
Reply:

Similar Artilces:

ORA-12638 DBI->connect(nirvana) failed: ORA-12638: Credential r etrieval failed (DBD ERROR: OCIServerAttach)
ORA-12638 DBI->connect(nirvana) failed: ORA-12638: Credential retrieval failed (DBD ERROR: OCIServerAttach) Can anyone help me with the above error message? (Environment describe below.) I am trying to run this Perl script. MAOtestORA.pl At this point I'm just trying to get the connect to work, not trying to do anything with the data retrieved. #!/usr/local/bin/perl use DBI; use CGI::Form; use CGI::Carp qw(carpout); carpout(STDOUT); $dbh = DBI->connect("dbi:Oracle:nirvana", "system", "umntest") or die "Cannot Connect"...

test DBD::Oracle DBI connect('','user/password@database',...) failed: ORA-12154
--0-621501335-1235620987=:55352 Content-Type: text/plain; charset=us-ascii I can connect to the database using sqlplus as user oracle, but not as root. I can compile DBD::Oracle but not complete the test. I tried to set variables properly: ORACLE_HOME=/u/oracle/10.2.0 ORACLE_HOST=hostname ORACLE_PORT=1522 ORACLE_SID=databasename ORACLE_USERID=user/password@databasename PATH=/u/oracle/10.2.0/bin:/usr/kerberos/sbin:/usr/kerberos/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/usr/X11R6/bin:/root/bin PWD=/usr/src/pub/dbd-oracle/DBD-Oracle-1.22 TNS_ADMIN=/u/or...

ORA-00932 inconsistent datatypes
Hi all, I have created a query in Oracle 8.0.5, which worked with PowerBuilder 8.0.3. When I use the same query with Oracle 9i and PowerBuilder 8.0.3, I get following error: "ORA-00932 inconsistent datatypes: expected NUMBER got INTERVAL Do you want to correct errors?" The query runs fine in Oracle9i SQL+ prompt. I have used Oracle Native Driver O90 Oracle 9.0.1. Please help if you have come across this error. Thanks in advance, Vinay Joshi. Could you post the SQL statement this is generating the error? On Fri, 4 Apr 2003 22:52:21 +0530, &...

DBI.pm DBD::Oracle::db
------_=_NextPart_000_01C399AB.DBBF6CDE Content-Type: text/plain; charset="iso-8859-1" Users, I wrote a perl script and compiled it by perl2exe. I placed the exe file to different machines, the results were different: Procedures: 1. ran from my pc, it worked fine (my PC is Win2K) - My pc has DBI module and perl 5.6 installed 2. placed the executable on server A (Win NT)and ran on it, it worked fine. - This server happened to have perl 5.0.1 and oracle client installed. 3. placed on Server B (W2k) and ran on it, it failed on db-connect (please see the attached tra...

DBD::Oracle::db ora_lob_write failed
All, I am getting an error when inserting into a CLOB. The error message is: DBD::Oracle::db ora_lob_write failed: ORA-01031: insufficient privileges Here is my code (object names have been changed to protect the = innocent): my $sth =3D $dbh->prepare(qq{ INSERT INTO owner.tab (report_header, ctime, file_data) VALUES ( SYS.XMLType.CREATEXML(:report_header), TO_DATE(:ctime, 'DD/MM/YYYY HH24:MI:SS'), EMPTY_CLOB() ) RETURNING ad_id INTO :ad_id }) || die $DBI::errstr; # bind all parameters ... $sth...

DBI and DBD::Oracle throw error Had to create DBD::Oracle::dr::imp_data_size unexpectedly
Hi Running some redhat servers and created some scripts that use DBI and = DBD::Oracle to access our 10.2.0.3 databases. The script was built and tested and has been used successfully on dual = core 64bit Xeon CPU servers and with as best as we can tell an identical = installation on some new quad core servers the script fails with the = above error. We have tried a later version of DBI we were using the 1.40 version = installed as a RPM and have installed 1.59 using perl and make. DBD::Oracle is 1.19 I have some strace output from where it fails running a simple piece of = co...

DBD-Oracle-1.03 and DBD-Oracle-1.06 FAIL make test
Hey all, I had this problem once before and someone gave me the right answer. I have built, tested and installed perl5.005_03. I have built, tested and installed DBI-1.14. I have built dbd-oracle-1.03 successfully, but it fails the make test. If I try to make install, it lets me, but my perl dbi calls don't work. What do I need to do to get dbd-oracle to pass the test. Thanks, Jim --------------DA432E5717C81378EB12773F Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit James, I just installed DBD::Oracle today on Solaris 7 with perl 5.6....

DBD-Oracle-1.03 and DBD-Oracle-1.06 FAIL make test #3
I also forgot to mention that I'm attempting this install on HP/UX 11.0. Thanks Jim ...

DBD-Oracle-1.03 and DBD-Oracle-1.06 FAIL make test #2
Sorry all, here's the output of my make test. PERL_DL_NONLAZY=1 /usr/bin/perl -Iblib/arch -Iblib/lib -I/opt/perl55/lib/5.00503/PA-RISC2.0-thread -I/opt/perl55/lib/5.00503 -e 'use Test::Harness qw(&runtests $verbose); $verbose=0; runtests @ARGV;' t/*.t t/base............../usr/lib/dld.sl: Can't shl_load() a library containing Thread Local Storage: /opt/oracle/product/8.1.6/JRE/lib/PA_RISC/native_threads/libjava.sl /usr/lib/dld.sl: Exec format error install_driver(Oracle) failed: Can't load 'blib/arch/auto/DBD/Oracle/Oracle.sl' for module DBD::O...

DBD::CSV::db prepare failed when using IN with where clause
Hi, I am getting an error when I am trying to use an IN with DBD-CSV. It used to work before, so I don't know what happened. Every since DBD-CSV was installed on a new machine, the statements no longer work. I currently have version 0.1021 of SQL::Statement installed on Solaris. Below is the output of my test program when I set the trace to 2. Should I reword this select statement using ORs instead of the IN clause? Is this a version problem? Should I use SQL::Statement 1.005 or is the XS version okay with DBD::CSV? TIA Gino PS: I hope this is the right addres...

ORA-00932: inconsistent datatypes: expected
Hello, I have trouble with a page that I’ve been attempting to make via VS 2005. This includes a Gridview and FormView controls that are linked to my sqlDataSource.  The connections to the database are working fine, I can send it values and get what I want back but when I edit/update and delete from the Formview, I got the error below.  Do I do thing worng or  missing something?   Many thanks for your help. the returned error: Exception Details: System.Data.OracleClient.OracleException: ORA-00932: inconsistent datatypes: expected - got BLOB  Here&nb...

DBI / DBD::Oracle: defunct process after $db->disconnect
Hello, we're using Perl-5.6.1 with DBI-1.15 and DBD-Oracle-1.06 on Solaris 7 t= o connect to several databases. We've found, that calling $dbh->disconnec= t is ending the oracle-client, but the return code of the child is not colle= cted with a "wait"-call. So we get a defunct-process for each disconnected database: A small test-program shows the behaviour: >>>>>>>>>>>>>>> test.pl <<<<<<<<<<<<<<< #!/opt/perl/bin/perl use DBI; print "connecting to database ....

DBI/DBD::Oracle and Oracle 10g
------=_NextPart_000_004B_01C68019.4E9B0DC0 Content-Type: text/plain; charset="US-ASCII" Content-Transfer-Encoding: 7bit Should it be necessary to recompile DBI and/or DBD::Oracle when upgrading from Oracle 9.2.0.4.0 to 10.2.0.2.0? Our DBA's recently did this upgrade, and since then, I've been unable to get DBI working 100%. The lingering problem is that I cannot select character fields. If I do "select varchar2_col from random_table" using DBI, I receive back "ORA-03106: fatal two-task communication protocol error". Doing "select sy...

Looking for help with odd DBD::Oracle::db prepare behavior
I've been using DBD::Oracle::db from my Perl scripts for some time and have had no problems. Today, I've started to get some odd behavior. I create a delete string and attempt to prepare it. I get a ORA-00936 error meesage. I don't know why. I create a delete string with multiple items in the "where ... in" clause: new_delete = delete from damping.search_criteria where rdb_search_criteria_id in ( 56554 , 56558 ) I then call the prepare statement with the new_delete string and get the following: DBD::Oracle::db prepare failed: ORA-00936: missing expre...

Web resources about - DBD::Oracle::db prepare failed: ORA-00932: inconsistent datatypes - perl.dbi.dev

Resources last updated: 1/22/2016 2:45:29 AM