----------------------------------output text of lob-------------------------------
CREATE TYPE content_table_type IS TABLE OF VARCHAR2(100);
CREATE OR REPLACE FUNCTION get_content(no NUMBER)
RETURN content_table_type IS
clob_loc CLOB;
len INT;
amount INT;
offset INT:=1;
temp INT;
content_table content_table_type:=content_table_type('A');
BEGIN
SELECT resume INTO clob_loc FROM clob_table WHERE id=no;
len:=DBMS_LOB.getlength(clob_loc);
temp:=TRUNC((len-1)/100);
IF temp>0 THEN
content_table.extend(temp);
END IF;
FOR i IN 1..(temp+1) LOOP
IF TRUNC((len-offset+1)/100)>0 THEN
amount:=100;
DBMS_LOB.read(clob_loc,amount,offset,content_table(i));
offset:=100*i+1;
ELSE
amount:=len-offset+1;
DBMS_LOB.read(clob_loc,amount,offset,content_table(i));
END IF;
END LOOP;
RETURN content_table;
END;
建表语句如下:
-- Create table
create table CLOB_TABLE
(
ID NUMBER(4) not null,
NAME VARCHAR2(10),
RESUME CLOB
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
-- Create/Recreate primary, unique and foreign key constraints
alter table CLOB_TABLE
add primary key (ID)
using index
tablespace USERS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);