select * from tabela t
where contains(t.historico,'eletrico') > 0
SELECT DBMS_LOB.SUBSTR (SEU_CAMPO_CLOB, 41, 1)
FROM SUA_TABELA;
SELECT DBMS_LOB.SUBSTR (historico, 41, 1)
FROM complementos;
You can use the TO_LOB function as part of an INSERT statement.
Given the following two tables:
CREATE TABLE long_table (n NUMBER, long_col LONG);
CREATE TABLE lob_table (n NUMBER, lob_col CLOB);
INSERT INTO lob_table
SELECT n, TO_LOB(long_col) FROM long_table;
CREATE TABLE teste1 (ds_long LONG);
DECLARE
vteste LONG;
vteste_ret LONG;
vteste_sub VARCHAR2(32767);
BEGIN
FOR x IN 1..1000
LOOP
vteste := vteste || '123-';
END LOOP;
INSERT INTO teste1 VALUES (vteste);
COMMIT;
SELECT ds_long
INTO vteste_Ret
FROM teste1
WHERE ROWNUM <= 1;
vteste_sub := SUBSTR(vteste_ret,1,100);
dbms_output.put_line('Teste com substring: '||vteste_sub);
END;
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 ->
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%'));Long still exists for Keep compatibility only. Whenever possible, use Clob fields, blob, etc ...He Use Long Values ??Is Subject to Same
Restrictions:
The 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 (Exceed for NULL AND NOT
NULL CONFRAINTS).
Long columns cannot be indexed.
The STORED FUNCTION CANNOT RETURN A LONG VALUE.
Within 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 Substr or instr)
Expressions or Conditions
Select Lists of Queries Containing Group by Clauses
Select Lists Of Subcuries or Queries Combined By Set Operators
Select Lists of CREATE TABLE ... The Select Statements
Select Lists in Insert Statements
Triggers Can Use The Long Datatype in the Following Manner:
SQL Statement Within A Trigger Can Insert Data Into Long Column.
IF Data from A Long Column Can Be Converted to Constrained Datatype (Such
The Char and Varchar2), the Long Column Can Be Referenced In A SQL Statement
Within to 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 Value from the database.
CREATE OR REPLACE FUNCTION get_search_condition
(
p_owner all_constraints.owner%TYPE,
p_constraint_name all_constraints.constraint_name%TYPE
) RETURN VARCHAR2 IS
v_long LONG;
BEGIN
SELECT search_condition
INTO v_long
FROM all_constraints
WHERE constraint_name = p_constraint_name
AND owner = p_owner
AND constraint_type = 'C';
RETURN substr(v_long, 1, 32760);
END;SELECT constraint_name,
get_search_condition(owner, constraint_name) AS search_condition
FROM all_constraints
WHERE owner = 'MYSCHEMA'
AND table_name = 'MYTABLENAME'
AND constraint_type = 'C'
AND get_search_condition(owner, constraint_name) = 'TEXT_SEARCH';Users browsing this forum: No registered users and 1 guest