Discussion :

[aida2]

bonjour
j'aimerais savoir s'il ya une autre manière de selectionner les differentes valeurs d'une colonne d'une table de type long
d'habitude on utilise select distinct <nom de la colonne> from <nom de la table>
merci

[bouyao]

un peu de lecture sur metalink

Fact(s)
~~~~~~~~
You try to select distinct values from a long column, create a table using as
select with a long column, insert into a table by selecting the long column
from another table and it errors out.
It is a restriction of the long columns that they cannot be used with a DISTINCT
operator and cannot be referenced when creating a table with query
(CREATE TABLE...AS SELECT...) or when inserting into a table(or view) with a
query (INSERT INTO ... SELECT...).
This article gives a workaround to these restrictions using a table function in
8i and above. This workaround is applicable if and only if your data in the long
column is less than 4K.

Symptom(s)
~~~~~~~~~~
ORA-00997: illegal use of LONG datatype

Change(s)
~~~~~~~~~~
No relevant change to the database has been done.

Cause
~~~~~~~
Long columns cannot be used with the DISTINCT operator in SELECT statements.
They cannot be referenced when creating a table with query (CREATE TABLE...AS
SELECT...) or when inserting into a table with a query (INSERT INTO ... SELECT..).
These are restrictions of the long datatype.

Fix
~~~~
This fix is applicable to versions 8i and above.
The fix uses a table function to copy the LONG values into a VARCHAR2 array,
and return that.

Steps to do this:

1. Create a varray or nested table type with varchar2(4000).

create type t_long as varray(1000) of varchar2(4000);
/

Note that you can create a VARRAY of the required size or a nested table.

2. Create the function that fetches the long column from the table into this VARRAY and returns the same.

NOTE: You should replace long_column and long_table with the appropriate column
and table names.

create or replace function f_long return t_long
as
v_var_long t_long := t_long();
i integer;
cursor c_long is select long_column from long_table;
begin
i:=1;
for c in c_long
loop
v_var_long.extend;
v_var_long(i) := c.long_column;
i := i+1;
end loop;
return v_var_long;
end;
/

3. Now select the column_value from the function cast as a table using:

select distinct t.column_value FROM TABLE(CAST(f_long() AS t_long)) t;

OR

create a table selecting from this table with a long column using:

OR

create table L2 as select t.column_value from TABLE(CAST(f_long() AS t_long)) t;

OR

insert into another long column selecting from this long column using:

insert into L3 select t.column_value from TABLE(CAST(f_long() AS t_long)) t;

The following example on 8.1.7 demonstrates the same using a table L1
containing a long column A:

SQL>DESC L1
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
A LONG

SQL>SELECT * FROM L1;

A
--------------------------------------------------------------------------------
hdgfklsdgfsdkgfslkfglksgfslkfgzsldf ghlkgf lasgfslkdfg sdlhkgf sdlfkg lsdkgfs lf
sdifhsdhfoash uiasdfuilf lihg ghsdfl gasdlhkgfadlskf glhgf lasdgf sdghf lhg
sdifhsdhfoash uiasdfuilf lihg ghsdfl gasdlhkgfadlskf glhgf lasdgf 9823479lhg
sdifhsdhfoash 7887b9786lihg ghsdfl gasdlhkgfadlskf glhgf lasdgf 9823479lhg
sdifhsdhfoash 7887b9786lihg ghsdfl gasdlhkgfadlskf glhgf lasdgf 9823479lhg

SQL>SELECT DISTINCT A from L1;
SELECT DISTINCT A from L1
*
ERROR at line 1:
ORA-00997: illegal use of LONG datatype


SQL>create type t_long as varray(1000) of varchar2(4000);
2 /

Type created.

SQL>create or replace function f_long return t_long
2 as
3 v_var_long t_long := t_long();
4 i integer;
5 cursor c_long is select a from L1;
6 begin
7 i:=1;
8 for c in c_long
9 loop
10 v_var_long.extend;
11 v_var_long(i) := c.a;
12 i := i+1;
13 end loop;
14 return v_var_long;
15 end;
16 /

Function created.

SQL>select distinct t.column_value FROM TABLE(CAST(f_long() AS t_long)) t;

COLUMN_VALUE
----------------------------------------------------------------------------------------------------
hdgfklsdgfsdkgfslkfglksgfslkfgzsldf ghlkgf lasgfslkdfg sdlhkgf sdlfkg lsdkgfs lfksdgfl kjgl
sdifhsdhfoash 7887b9786lihg ghsdfl gasdlhkgfadlskf glhgf lasdgf 9823479lhg
sdifhsdhfoash uiasdfuilf lihg ghsdfl gasdlhkgfadlskf glhgf lasdgf 9823479lhg
sdifhsdhfoash uiasdfuilf lihg ghsdfl gasdlhkgfadlskf glhgf lasdgf sdghf lhg

Note that the value in the last row appears only once though there are 2 such
values in the table (as shown in the select * from L1 output), thus showing that
distinct works with that long column.

SQL>create table L2 as select t.column_value from TABLE(CAST(f_long() AS t_long)) t;

Table created.

SQL>select * from l2;

COLUMN_VALUE
----------------------------------------------------------------------------------------------------
hdgfklsdgfsdkgfslkfglksgfslkfgzsldf ghlkgf lasgfslkdfg sdlhkgf sdlfkg lsdkgfs lfksdgfl kjgl
sdifhsdhfoash uiasdfuilf lihg ghsdfl gasdlhkgfadlskf glhgf lasdgf sdghf lhg
sdifhsdhfoash uiasdfuilf lihg ghsdfl gasdlhkgfadlskf glhgf lasdgf 9823479lhg
sdifhsdhfoash 7887b9786lihg ghsdfl gasdlhkgfadlskf glhgf lasdgf 9823479lhg
sdifhsdhfoash 7887b9786lihg ghsdfl gasdlhkgfadlskf glhgf lasdgf 9823479lhg

5 rows selected.

SQL>insert into L3 select t.column_value from TABLE(CAST(f_long() AS t_long)) t;

5 rows created.

SQL>select * from l3;

C
--------------------------------------------------------------------------------
sdifhsdhfoash uiasdfuilf lihg ghsdfl gasdlhkgfadlskf glhgf lasdgf sdghf lhg
sdifhsdhfoash uiasdfuilf lihg ghsdfl gasdlhkgfadlskf glhgf lasdgf 9823479lhg
sdifhsdhfoash 7887b9786lihg ghsdfl gasdlhkgfadlskf glhgf lasdgf 9823479lhg
sdifhsdhfoash 7887b9786lihg ghsdfl gasdlhkgfadlskf glhgf lasdgf 9823479lhg
hdgfklsdgfsdkgfslkfglksgfslkfgzsldf ghlkgf lasgfslkdfg sdlhkgf sdlfkg lsdkgfs lf


In 9i the same can be used like:

SELECT DISTINCT t.column_value FROM TABLE(f_long) t;
CREATE TABLE L2 as select t.column_value from TABLE(f_long) t;
INSERT INTO L3 select t.column_value from TABLE(f_long) t;

Limitation
~~~~~~~~~~
Please note that this workaround is applicable if and only if your data in the
long column < 4K because the upper limit of varchar2 is 4000.