A bit of oracle clue?

RT Developers
1

Heya all,

    So I've run into a minor issue with oracle. The following

chunk of SQL is valid on mysql and postgres, but not on oracle.
The oracle docs I have seem to claim that it’s kosher. Anyone have a helpful
suggestion for how to make oracle happy with this:

SELECT DISTINCT * from Tablename;

Thanks,
Jesse

jesse reed vincent – root@eruditorum.org – jesse@fsck.com
70EBAC90: 2A07 FC22 7DB4 42C1 9D71 0108 41A3 3FB3 70EB AC90

Any e-mail sent to the SLA will immediately become the intellectual property
of the SLA and the author of said message will enter into a period of
indentured servitude which will last for a period of time no less than seven
years.

2

Jesse wrote:
-> So I’ve run into a minor issue with oracle. The following
-> chunk of SQL is valid on mysql and postgres, but not on oracle.
-> The oracle docs I have seem to claim that it’s kosher. Anyone have a helpfu
-> suggestion for how to make oracle happy with this:
->
-> SELECT DISTINCT * from Tablename;

Works fine in Oracle 8i’s sqlplus, and with Oraperl. Maybe the error
isn’t in your syntax, but elsewhere?

                   -Robert Tarrall.-
                   Unix System/Network Admin
                   E.Central/Neighborhood Link
3

Jesse wrote:
→ So I’ve run into a minor issue with oracle. The following
→ chunk of SQL is valid on mysql and postgres, but not on oracle.
→ The oracle docs I have seem to claim that it’s kosher. Anyone have a helpfu
→ suggestion for how to make oracle happy with this:
→
→ SELECT DISTINCT * from Tablename;

Works fine in Oracle 8i’s sqlplus, and with Oraperl. Maybe the error
isn’t in your syntax, but elsewhere?

Hrm. I’ve got an 8i (8.1.7) database running.

with a default RT installation, using sqlplus, ‘select * from Users;’
gets me a nice set of returned values.

‘select distinct * from Users;’ gets me:

select distinct * from Users
ERROR at line 1:
ORA-00932: inconsistent datatypes

Does that shed any light on it?

                   -Robert Tarrall.-
                   Unix System/Network Admin
                   E.Central/Neighborhood Link

jesse reed vincent – root@eruditorum.org – jesse@fsck.com
70EBAC90: 2A07 FC22 7DB4 42C1 9D71 0108 41A3 3FB3 70EB AC90

After all, it’s not every day you meet up with an evil power
-M. Bulgakov

4

Works just fine for me. (Oracle 8.0.5 and 8.1.5)

-alexOn Wed, 21 Mar 2001, Jesse wrote:

Heya all,

    So I've run into a minor issue with oracle. The following

chunk of SQL is valid on mysql and postgres, but not on oracle.
The oracle docs I have seem to claim that it’s kosher. Anyone have a helpful
suggestion for how to make oracle happy with this:

SELECT DISTINCT * from Tablename;

Thanks,
Jesse

5

The problem appears to be that oracle can’t SELECT DISTINCT
if there are CLOBS in the row to be returned. Ugh. this is going to suck.On Wed, Mar 21, 2001 at 07:07:13PM -0500, Alex Pilosov wrote:

Heya all,

    So I've run into a minor issue with oracle. The following

chunk of SQL is valid on mysql and postgres, but not on oracle.
The oracle docs I have seem to claim that it’s kosher. Anyone have a helpful
suggestion for how to make oracle happy with this:

SELECT DISTINCT * from Tablename;

Thanks,
Jesse

jesse reed vincent – root@eruditorum.org – jesse@fsck.com
70EBAC90: 2A07 FC22 7DB4 42C1 9D71 0108 41A3 3FB3 70EB AC90

They’ll take my private key when they pry it from my cold dead fingers!

6

Anyone have a helpful
suggestion for how to make oracle happy with this:

SELECT DISTINCT * from Tablename;

Thanks,
Jesse

I’ve never tried Oracle, but I’d suggest trying this:

SELECT UNIQUE(*) from Tablename;

7

Jesse,

Can you explain why you need this statement?

Apparently you have doubles in the database, and want to get rid of it?
What is the use of having doubles in the database anyway?

Or do you need a distinct for a join over multiple tables?

Can’t you work around it by sorting the select and removing doubles
in the Perl code?

In Oracle you can not work with the contents of CLOBS, BLOBS, and for that
matter the LONG datatype, in queries directly. I’m not sure how the
Perl module solves this, but in Java or PL/SQL you get a reference to
the CLOB or BLOB from the select, and do some magic handwaving to
get the contents of the CLOB/BLOB based on this reference.

Anyone have a helpful
suggestion for how to make oracle happy with this:

SELECT DISTINCT * from Tablename;

Thanks,
Jesse

Kind regards,
– Marco Nijdam, marco@west.nl
– West Consulting B.V., Delftechpark 5, 2628 XJ Delft, The Netherlands
– P.O. Box 3318, 2601 DH Delft
– Tel: +3115 219 1600, Fax: +3115 214 7889

8

Jesse,

Or do you need a distinct for a join over multiple tables?

Yep. you got it.

Can’t you work around it by sorting the select and removing doubles
in the Perl code?

Yes. but that’s really really ugly. I’d prefer not have to do this,
since it’s well, fairly standard SQL and the database is going to be
faster if it’s doable, etc. and it just makes everything more complex :confused:

In Oracle you can not work with the contents of CLOBS, BLOBS, and for that
matter the LONG datatype, in queries directly. I’m not sure how the
Perl module solves this, but in Java or PL/SQL you get a reference to
the CLOB or BLOB from the select, and do some magic handwaving to
get the contents of the CLOB/BLOB based on this reference.

Wait. so I can’t even search against the contents of a CLOB with a LIKE?

jesse reed vincent – root@eruditorum.org – jesse@fsck.com
70EBAC90: 2A07 FC22 7DB4 42C1 9D71 0108 41A3 3FB3 70EB AC90

I have images of Marc in well worn combat fatigues, covered in mud,
sweat and blood, knife in one hand and PSION int he other, being
restrained by several other people, screaming “Let me at it!
Just let me at it!” Eichin standing calmly by with something
automated, milspec, and likely recoilless.
-xiphmont on opensource peer review

9

From where I sit, that’s not valid SQL syntax. (mysql rejects it).
How’s life in petersburg?

    -jOn Thu, Mar 22, 2001 at 11:25:24AM +0100, Tobias Brox wrote:

Anyone have a helpful
suggestion for how to make oracle happy with this:

SELECT DISTINCT * from Tablename;

Thanks,
Jesse

I’ve never tried Oracle, but I’d suggest trying this:

SELECT UNIQUE(*) from Tablename;

jesse reed vincent – root@eruditorum.org – jesse@fsck.com
70EBAC90: 2A07 FC22 7DB4 42C1 9D71 0108 41A3 3FB3 70EB AC90

autoconf is your friend until it mysteriously stops working, at which
point it is a snarling wolverine attached to your genitals by its teeth
(that said, it’s better than most of the alternatives) – Nathan Mehl

10

As quoted from Jesse:

In Oracle you can not work with the contents of CLOBS, BLOBS, and for that
matter the LONG datatype, in queries directly. I’m not sure how the
Perl module solves this, but in Java or PL/SQL you get a reference to
the CLOB or BLOB from the select, and do some magic handwaving to
get the contents of the CLOB/BLOB based on this reference.

Wait. so I can’t even search against the contents of a CLOB with a LIKE?

Correct. But if you would have Oracle interMedia enabled, it could be used
to create an index on the CLOB, allowing you to search for e.g. words
in the CLOB using the added keyword “CONTAINS”.

Tables store a so called LOB locator, refering to the actual LOB contents.
So a SELECT will return only the locator. CLOB and BLOBS use copy
semantics for both locator and contents, so two LOB locators in a table
will always be different (so even if DISTINCT would work, every row
would be different because it would probably only compare the locators).

The deprecated LONG and LONG RAW datatypes work a bit different, more
like normal text columns, but you can’t use these columns in a WHERE
clause either (except through interMedia), and you are restricted to
just one LONG column per table.

It might be possible to write a PL/SQL stored procedure (or stored function)
working on a LOB that could be used in an SQL query (in the WHERE
clause), though I never tried. E.g. a function that is passed a LOB locator
and a string, and inspects the contents of a LOB for the given string and
returns TRUE/FALSE. Of cause this is Oracle specific.

An (also not very clean) alternative would be to store large texts in
multiple rows. Oracle 8i allows up to 4K per varchar (Oracle 7 up to 2K).
So split up a text in 4K chunks and store those in a separate table.
Although it is clumsy and cumbersome, and less efficient in storage and
searching (compared to using Oracle interMedia), it would allow a
cross-database solution.

Less space efficient, but maybe easier would be to store a summary
in a normal varchar column, which can be used for searching etc.
and store the full text in a CLOB.

Kind regards,
– Marco Nijdam, marco@west.nl
– West Consulting B.V., Delftechpark 5, 2628 XJ Delft, The Netherlands
– P.O. Box 3318, 2601 DH Delft
– Tel: +3115 219 1600, Fax: +3115 214 7889

11

As quoted from Jesse:

Jesse,

Or do you need a distinct for a join over multiple tables?

Yep. you got it.

Can’t you work around it by sorting the select and removing doubles
in the Perl code?

Yes. but that’s really really ugly. I’d prefer not have to do this,
since it’s well, fairly standard SQL and the database is going to be
faster if it’s doable, etc. and it just makes everything more complex :confused:

BTW I don’t think it is (database wise) efficient to do a DISTINCT on
the whole row, if there are large texts in the row. Just checking
the primary keys should be enough. I.e. a two step approach to first
select the primary keys you need and then getting the long texts
that belong to them (maybe use a nested query).

Though I must admit that the query optimizer may
be very smart (especially Oracle’s) so it might already automatically
check only primary keys for a DISTINCT.

Kind regards,
– Marco Nijdam, marco@west.nl
– West Consulting B.V., Delftechpark 5, 2628 XJ Delft, The Netherlands
– P.O. Box 3318, 2601 DH Delft
– Tel: +3115 219 1600, Fax: +3115 214 7889