LongReadLen, lengthb() and Oracle

Hi Folks

The DBI docs for LongReadLen say to use LENGTHB() for Oracle, but that gives me
an error:

ORA-00932: inconsistent datatypes: expected NUMBER got LONG

when I do:

select max(lengthb(a_session) ) from sessions;

And, yes, sessions is for CGI::Session, and a_session is of type long.

This is Oracle 10, DBI 1.48, DBD::Oracle 1.16.

Any ideas?
--
Ron Savage
ron@savage.net.au
http://savage.net.au/index.html

Re: LongReadLen, lengthb() and Oracle

--6e7ZaeXHKrTJCxdu
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

On 2005-12-14 11:38:56 +1100, Ron Savage wrote:
> The DBI docs for LongReadLen say to use LENGTHB() for Oracle, but that gi=
ves me
> an error:
>=20
> ORA-00932: inconsistent datatypes: expected NUMBER got LONG

The SQL reference manual says:

char can be any of the datatypes CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB,
or NCLOB.=20

Seems that LONG and BLOB are not allowed.

I always used=20

select dbms_lob.getlength(lob_column) as length from table ...

to get the size of a LOB.

hp


--=20
_ | Peter J. Holzer | If I wanted to be "academically correct",
|_|_) | Sysadmin WSR | I'd be programming in Java.
| | | hjp@wsr.ac.at | I don't, and I'm not.
__/ | http://www.hjp.at/ | -- Jesse Erlbaum on dbi-users

--6e7ZaeXHKrTJCxdu
Content-Type: application/pgp-signature
Content-Disposition: inline

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.4 (GNU/Linux)

iQDQAwUBQ6ALylLjemazOuKpAQFoNQXTB/WgyfXp/MFBoO7iqtUDxke7mie4 u8Yv
gF3g8sueCT2PH7V58VuEgRkhzmP2ksqN+ue+5LcXfswrsQ3IqVRf2SkZkRJf hFB3
ZcFHZaQiYmMf+QVnWftprnquXRvhk3vn48jgmgqgKjdQEogY92EkDy1tvCO2 6mOP
vv4kc7Ru0o3J1cJYcaYzKK9V6vSj72QxkmPuRvQ4UMeJNJOb7NrW7hJiS7i6 avY+
bL63Dj0IZDyG22uTathDvfWfVQ==
=hBL8
-----END PGP SIGNATURE-----

--6e7ZaeXHKrTJCxdu--

Re: LongReadLen, lengthb() and Oracle

I don't know where the LENGTHB suggestion came from, but I'll fix it.
Thanks.

Tim.

On Wed, Dec 14, 2005 at 01:10:50PM +0100, Peter J. Holzer wrote:
> On 2005-12-14 11:38:56 +1100, Ron Savage wrote:
> > The DBI docs for LongReadLen say to use LENGTHB() for Oracle, but that gives me
> > an error:
> >
> > ORA-00932: inconsistent datatypes: expected NUMBER got LONG
>
> The SQL reference manual says:
>
> char can be any of the datatypes CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB,
> or NCLOB.
>
> Seems that LONG and BLOB are not allowed.
>
> I always used
>
> select dbms_lob.getlength(lob_column) as length from table ...
>
> to get the size of a LOB.
>
> hp
>
>
> --
> _ | Peter J. Holzer | If I wanted to be "academically correct",
> |_|_) | Sysadmin WSR | I'd be programming in Java.
> | | | hjp@wsr.ac.at | I don't, and I'm not.
> __/ | http://www.hjp.at/ | -- Jesse Erlbaum on dbi-users

Re: LongReadLen, lengthb() and Oracle

On Wed, 14 Dec 2005 13:10:50 +0100, Peter J. Holzer wrote:

Hi Peter

> select dbms_lob.getlength(lob_column) as length from table ...
> to get the size of a LOB.

Thanx, but that didn't work either. Perhaps getlength() has the same
restrictions as lengthb()?

This is in SQL/Plus:

SQL> select dbms_lob.getlength(a_session) as length from sessions;
select dbms_lob.getlength(a_session) as length from sessions
*
ERROR at line 1:
ORA-00997: illegal use of LONG datatype

--
Cheers
Ron Savage, ron@savage.net.au on 15/12/2005
http://savage.net.au/index.html
Let the record show: Microsoft is not an Australian company

RE: LongReadLen, lengthb() and Oracle

Found this Web page on the topic; HTH:

http://www.arikaplan.com/oracle/ari80597b.html

Ted Behling

-----Original Message-----
From: Ron Savage [mailto:ron@savage.net.au]=20
Sent: Tuesday, December 13, 2005 7:39 PM
To: List - DBI users
Subject: LongReadLen, lengthb() and Oracle

Hi Folks

The DBI docs for LongReadLen say to use LENGTHB() for Oracle, but that
gives me an error:

ORA-00932: inconsistent datatypes: expected NUMBER got LONG

when I do:

select max(lengthb(a_session) ) from sessions;

And, yes, sessions is for CGI::Session, and a_session is of type long.

This is Oracle 10, DBI 1.48, DBD::Oracle 1.16.

Any ideas?
--
Ron Savage
ron@savage.net.au
http://savage.net.au/index.html

RE: LongReadLen, lengthb() and Oracle

Ron,

What data type is 'a_session'? I have never worked with this lengthb()
method, but if this is truly a LONG type, there is nothing you can do
with it natively in SQL (i.e. get the length). You will need tto fetch
it and then get the length. Also, the dbms_lob package does not work
against LOBs (you might have guessed this already).

-
Ron Reidy
Lead DBA
Array BioPharma, Inc.

-----Original Message-----
From: Ron Savage [mailto:ron@savage.net.au]=20
Sent: Wednesday, December 14, 2005 2:44 PM
To: List - DBI users
Subject: Re: LongReadLen, lengthb() and Oracle


On Wed, 14 Dec 2005 13:10:50 +0100, Peter J. Holzer wrote:

Hi Peter

> select dbms_lob.getlength(lob_column) as length from table ... to get=20
> the size of a LOB.

Thanx, but that didn't work either. Perhaps getlength() has the same=20
restrictions as lengthb()?

This is in SQL/Plus:

SQL> select dbms_lob.getlength(a_session) as length from sessions; =20
select dbms_lob.getlength(a_session) as length from sessions
*
ERROR at line 1:
ORA-00997: illegal use of LONG datatype

--=20
Cheers
Ron Savage, ron@savage.net.au on 15/12/2005
http://savage.net.au/index.html Let the record show: Microsoft is not an
Australian company



This electronic message transmission is a PRIVATE communication which =
contains
information which may be confidential or privileged. The information is =
intended=20
to be for the use of the individual or entity named above. If you are =
not the=20
intended recipient, please be aware that any disclosure, copying, =
distribution=20
or use of the contents of this information is prohibited. Please notify =
the
sender of the delivery error by replying to this message, or notify us =
by
telephone (877-633-2436, ext. 0), and then delete it from your system.

RE: LongReadLen, lengthb() and Oracle

On Wed, 14 Dec 2005 15:03:42 -0700, Reidy, Ron wrote:

Hi Ron

> What data type is 'a_session'? I have never worked with this

It's a long, as per Oracle's error msg below.

> ERROR at line 1: ORA-00997: illegal use of LONG datatype
--
Cheers
Ron Savage, ron@savage.net.au on 15/12/2005
http://savage.net.au/index.html
Let the record show: Microsoft is not an Australian company

RE: LongReadLen, lengthb() and Oracle

On Wed, 14 Dec 2005 17:00:43 -0500, Ted Behling wrote:

Hi Ted

> http://www.arikaplan.com/oracle/ari80597b.html

Thanx.

The problem arose when I got an error that LongReadLen was not set and
LongTruncOk was 0 (quoting vaguely from memory).

So I was trying to find out the length of a column's data, since I'd set
LongReadLen =3D 99999 in $dbh, and was highly confused.

But it was just arbitrary code/memory corruption. Re-running the query=
worked -
the alleged error had vanished.

--
Cheers
Ron Savage, ron@savage.net.au on 15/12/2005
http://savage.net.au/index.html
Let the record show: Microsoft is not an Australian company

RE: LongReadLen, lengthb() and Oracle

On Thu, 15 Dec 2005 19:55:28 +1100, Ron Savage wrote:

This reminds me:

In DBI V 2 I'd like a new option: $$dbh{'MakeLikeSimple'} =3D 1;

It would of course be on by default...
--
Cheers
Ron Savage, ron@savage.net.au on 15/12/2005
http://savage.net.au/index.html
Let the record show: Microsoft is not an Australian company

RE: LongReadLen, lengthb() and Oracle

On Thu, 15 Dec 2005 20:03:53 +1100, Ron Savage wrote:

Hi Idiot

> In DBI V 2 I'd like a new option: $$dbh{'MakeLikeSimple'} =3D 1;

That joke was meant to be MakeLifeSimple...

--
Cheers
Ron Savage, ron@savage.net.au on 16/12/2005
http://savage.net.au/index.html
Let the record show: Microsoft is not an Australian company