23-1:
	CREATE TABLE clob_table(
	  id NUMBER(4) PRIMARY KEY,name VARCHAR2(10),resume CLOB
	) LOB(resume) STORE AS SEGNAME (DISABLE STORAGE IN ROW);
23-2:
	INSERT INTO clob_table (id,name) VALUES(1,'');
	INSERT INTO clob_table VALUES(2,'','1978');
	INSERT INTO clob_table VALUES(3,'',empty_clob());
	UPDATE clob_table SET resume=empty_clob() WHERE id=1;
	COMMIT;
23-3:
	CREATE OR REPLACE PROCEDURE add_content(no NUMBER,text VARCHAR2) IS
	  clob_loc CLOB;
	  amount INT;
	  offset INT;
	BEGIN
	  SELECT resume INTO clob_loc FROM clob_table WHERE id=no FOR UPDATE;
	  offset:=DBMS_LOB.GETLENGTH(clob_loc)+1;
	  amount:=LENGTH(text);
	  DBMS_LOB.WRITE(clob_loc,amount,offset,text);
	  COMMIT;
	END;
	/
	exec add_content(1,'1978꣬ҵڹҵѧ')
	SELECT resume FROM clob_table WHERE id=1;
23-4:
	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;
	/
	DECLARE
	  content_table content_table_type:=content_table_type('A');
	BEGIN
	  content_table.extend(&length-1);
	  content_table:=get_content(&id);
	  FOR i IN 1..content_table.COUNT LOOP
	    dbms_output.put_line(content_table(i));
	  END LOOP;
	END;
	/
23-5:
	conn system/admin
	CREATE DIRECTORY user_dir AS 'E:\BFILE_DEMO';
	GRANT READ,WRITE ON DIRECTORY user_dir TO scott;
23-6:
	CREATE TABLE bfile_table(
	  id NUMBER(6) PRIMARY KEY,name VARCHAR2(10),resume BFILE
	);
23-7:
	INSERT INTO bfile_table VALUES(1,'',bfilename('USER_DIR','.TXT'));
	INSERT INTO bfile_table VALUES(2,'',bfilename('USER_DIR','.TXT'));
	COMMIT;
23-8:
	DECLARE
	  clob_loc CLOB;
	  bfile_loc BFILE;
	  amount INT;
	  src_offset INT:=1;
	  dest_offset INT:=1;
	  csid INT:=0;
	  lc INT:=0;
	  warning INT;
	  no INT:=&id;
	BEGIN
	  SELECT resume INTO bfile_loc FROM bfile_table WHERE id=no;
	  DBMS_LOB.FILEOPEN(bfile_loc,0);
	  amount:=DBMS_LOB.GETLENGTH(bfile_loc);
	  SELECT resume INTO clob_loc FROM clob_table WHERE id=no FOR UPDATE;
	  DBMS_LOB.LOADCLOBFROMFILE(clob_loc,bfile_loc,amount,
	    dest_offset,src_offset,csid,lc,warning);
	  DBMS_LOB.FILECLOSE(bfile_loc);
	  COMMIT;
	END;
	/
	SELECT resume FROM clob_table WHERE id=2;
23-9:
	CREATE TABLE blob_table (
	  id NUMBER(6) PRIMARY KEY, name VARCHAR2(10),photo BLOB
	);
23-10:
	INSERT INTO blob_table VALUES(1,'',EMPTY_BLOB());
	INSERT INTO blob_table VALUES(2,'',EMPTY_BLOB());
	COMMIT;
23-11:
	CREATE OR REPLACE PROCEDURE load_photo(no NUMBER,filename VARCHAR2) IS
	  blob_loc BLOB;
	  bfile_loc BFILE;
	  amount INT;
	  src_offset INT:=1;
	  dest_offset INT:=1;
	BEGIN
	  SELECT photo INTO blob_loc FROM blob_table WHERE id=no FOR UPDATE;
	  bfile_loc:=bfilename('USER_DIR',filename);
	  DBMS_LOB.FILEOPEN(bfile_loc,0);
	  amount:=DBMS_LOB.GETLENGTH(bfile_loc);
	  DBMS_LOB.LOADBLOBFROMFILE(blob_loc,bfile_loc,amount,dest_offset,src_offset);
	  DBMS_LOB.FILECLOSE(bfile_loc);
	  COMMIT;
	END;
	/
	exec load_photo(1,'demo.jpg')
23-12:
	CREATE OR REPLACE PROCEDURE download_file(no NUMBER)
	IS
	  blob_loc BLOB;
	  handle UTL_FILE.FILE_TYPE;
	  photo_length INT;
	  amount INT;
	  offset INT:=1;
	  buffer RAW(1000);
	  temp INT;
	  v_name VARCHAR2(10);
	BEGIN
	  SELECT name,photo INTO v_name,blob_loc FROM blob_table WHERE id=no;
	  photo_length:=dbms_lob.getlength(blob_loc);
	  handle:=utl_file.fopen('USER_DIR',v_name||'.jpg','wb');
	  temp:=TRUNC((photo_length-1)/1000)+1;
	  FOR i IN 1..temp LOOP
	    IF i<>temp THEN
	       amount:=1000;
	    ELSE
	       amount:=photo_length-(temp-1)*1000;
	    END IF;
	    dbms_lob.read(blob_loc,amount,offset,buffer);
	    offset:=offset+1000;
	    utl_file.put_raw(handle,buffer);
	  END LOOP;
	  utl_file.fclose(handle);
	END;
	/
	exec download_file(1)
	










