--创建一个表
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;
