(原创)oracle目录及Long类型操作

--创建一个表
create table ascii_docs
(
id number,
document long
);
--创建一个目录

create or replace directory OUT_PATH as 'c:\temp';
--给目录授予权限

grant read, write on directory OUT_PATH to test;

create or replace function dump_doc(docid in number,filename in varchar2) return varchar2 is
  data_chunk varchar2(254);
  chunk_size number :=254;
  chunk_size_returned number;
  --set location to be the directory in which the file should go.
  location varchar2(20) := 'OUT_PATH';
  mycursor number;
  stmt varchar2(1024);
  cur_pos number :=0;
  rows number;
  dummy number;
  file_handle utl_file.file_type;
  status varchar2(50);
  begin
  --open the file for writing.
  file_handle := utl_file.fopen(location,filename,'w');
  --bind the doctoget host variable with the plsql parameter docid
  --whitch is passed into the function
  stmt := 'select document from ascii_docs where id = :doctoget';
  mycursor := dbms_sql.open_cursor;
  dbms_sql.parse(mycursor,stmt,dbms_sql.v7);
  dbms_sql.bind_variable(mycursor,':doctoget',docid);
  --only doing one fetch for the primary key as assuming the whole
  --document is stored in one row
  dbms_sql.define_column_long(mycursor,1);
  dummy := dbms_sql.execute(mycursor);
  rows := dbms_sql.fetch_rows(mycursor);
  loop
    --fetch 'chunks of the long until we have got the lot
    dbms_sql.column_value_long(mycursor,1,chunk_size,cur_pos,data_chunk,chunk_size_returned);
    utl_file.put(file_handle,data_chunk);
    cur_pos := cur_pos + chunk_size;
    exit when chunk_size_returned =0;
  end loop;
  dbms_sql.close_cursor(mycursor);
  utl_file.fclose(file_handle);
  return('success');
  exception
    when others then
      utl_file.fclose(file_handle);
      raise;
      return('Fallre');
  end dump_doc;