How to read data from CLOB columns of any table ?

We can read the clob data from any table by using the DBMS_LOB , oracle built in package.

SELECT dbms_lob.substr(message,1000,1) from message_in;

So it will start from position 1 and provide 1000 characters.

DBMS_LOB.SUBSTR (
   lob_loc     IN    BLOB,
   amount      IN    INTEGER := 32767,
   offset      IN    INTEGER := 1)
  RETURN RAW;

DBMS_LOB.SUBSTR (
   lob_loc     IN    CLOB   CHARACTER SET ANY_CS,
   amount      IN    INTEGER := 32767,
   offset      IN    INTEGER := 1)
  RETURN VARCHAR2 CHARACTER SET lob_loc%CHARSET;

DBMS_LOB.SUBSTR (
   file_loc     IN    BFILE,
   amount      IN    INTEGER := 32767,
   offset      IN    INTEGER := 1)
  RETURN RAW;

SELECT  message from message_in where  dbms_lob.instr(message,'hai',1)>0;

It will search for hai in message column and if found it will return the column.

DBMS_LOB.INSTR (
   lob_loc    IN   BLOB,
   pattern    IN   RAW,
   offset     IN   INTEGER := 1,
   nth        IN   INTEGER := 1)
  RETURN INTEGER;

DBMS_LOB.INSTR (
   lob_loc    IN   CLOB      CHARACTER SET ANY_CS,
   pattern    IN   VARCHAR2  CHARACTER SET lob_loc%CHARSET,
   offset     IN   INTEGER := 1,
   nth        IN   INTEGER := 1)
  RETURN INTEGER;

DBMS_LOB.INSTR (
   file_loc   IN   BFILE,
   pattern    IN   RAW,
   offset     IN   INTEGER := 1,
   nth        IN   INTEGER := 1)
  RETURN INTEGER;

Is it possible to return anything apart from number and varchar2 .Also is it possible to return row of data ?

Is it possible to return anything apart from number and varchar2 from any function.Also is it possible to return row of data ?

Yes it is possible to return row of data from the function.


create or replace function test(emp_name varchar2)
return emp%rowtype
is
x emp%rowtype;

Begin

SELECT * into x from emp wnere ename=emp_name;
return x;
end test;

SELECT * from emp;
Declare
y emp%rowtype;

begin

y:=ha('KING');
DBMS_OUTPUT.put_line('NAME IS '||y.ename||' salary is  '||y.sal||' and job is  '||y.job);

end;

Output:

NAME IS KING salary is 5000 and job is PRESIDENT