Um cara chamado DAVID HUNT dá uma dica quanto a isso:
http://www.dba-oracle.com/t_select_long ... mn_sql.htm
Selecionar tudo
REM **********************************************************
REM Author: David Hunt
REM Date Created: July 18, 2007
REM Purpose: Creates function that returns contents of
REM PART_COMNT:COMNT_TXT. (a LONG field)
REM Warning: if the contents of COMNT_TXT exceed 32767 chrs, then
REM none of the data from that row's LONG RETURN;
REM an error message results.
REM **********************************************************
create or replace function Get_Comnt_txt
(RowID_of_Long in rowid)
return varchar2
is
Long_to_Varchar varchar(32767);
begin
select comnt_txt into Long_to_Varchar
from part_comnt
where rowid = rowid_of_long;
return long_to_varchar;
exception
when others then
return 'Error occurred on rowid: '||rowid_of_long;
end; /
select replace(get_Comnt_txt(rowid),chr(10),null)comnt_txt
from part_comnt;
COMNT_TXT
----------------------------------------------------------------
This is short text in a LONG column.
This is row 2; a <carriage return> is here ->This is row 2; a <carriage return> is here ->This is row 2; a <carriage return> is here ->
Selecionar tudo
create table rv_test (a number, b long)
/
insert into rv_test values (1, 'asdfjasraghavkjasdfdsafqafl;kasdfnbdsamnbfdasraghavasdfl')
/
insert into rv_test values (2, 'asdfjasdf;lkjadsflkjdasf;lkjasdflkjdsafl;kasdfnbdsamnbfdasraghavasdflkjasdf')
/
insert into rv_test values (3, & #39;asdfjasdf;lasdfa192387sfdsflkjdasf;lkjasdfdsafq3lkjdsafl;kasdfnbdsamnbfdasra
ghavasdflraghav')
/
commit
-- ************************
create or replace type rv_test_type as object(a number, b clob);
/
create or replace type rv_test_table as table of rv_test_type;
/
create or replace function rv_test_search(x varchar2)
return rv_test_table pipelined is
r rv_test_type;
begin
for f in (select * from rv_test) loop
if (f.b like x) then
r:= rv_test_type(f.a,f.b);
pipe row(r);
end if;
end loop;
end;
/
select a from table(rv_test_search('%raghav%'));
Algumas coisas importantes sobre LONG. (restrições).
he use of LONG values is subject to some
restrictions:
A table cannot contain more than one LONG column.
You cannot create an object type with a LONG attribute.
LONG columns cannot appear in integrity constraints (except for NULL and NOT
NULL constraints).
LONG columns cannot be indexed.
A stored function cannot return a LONG value.
Within a single SQL statement, all LONG columns, updated tables, and locked
tables must be located on the same database.
LONG columns cannot appear in certain parts of SQL statements:
WHERE clauses, GROUP BY clauses, ORDER BY clauses, or CONNECT BY clauses or
with the DISTINCT operator in SELECT statements
The UNIQUE operator of a SELECT statement
The column list of a CREATE CLUSTER statement
The CLUSTER clause of a CREATE MATERIALIZED VIEW statement
SQL functions (such as SUBSTR or INSTR)
Expressions or conditions
SELECT lists of queries containing GROUP BY clauses
SELECT lists of subqueries or queries combined by set operators
SELECT lists of CREATE TABLE ... AS SELECT statements
SELECT lists in subqueries in INSERT statements
Triggers can use the LONG datatype in the following manner:
A SQL statement within a trigger can insert data into a LONG column.
If data from a LONG column can be converted to a constrained datatype (such
as CHAR and VARCHAR2), a LONG column can be referenced in a SQL statement
within a trigger.
Variables in triggers cannot be declared using the LONG datatype.
:NEW and :OLD cannot be used with LONG columns.
However you can use the Oracle Call Interface functions to retrieve a
portion of a LONG value from the database.
O LONG ainda existe para manter compatibilidade apenas. Sempre que possível, use campos CLOB, BLOB, etc...