create or replace package pack is procedure proc(v_path varchar2,v_filename varchar2); end; / create or replace package body pack is procedure proc(v_path varchar2,v_filename varchar2) is v_file UTL_FILE.FILE_TYPE; begin v_file := UTL_FILE.FOPEN(v_path,v_filename, 'a',32767); -- check if file is open , if yes, raise an exception if UTL_FILE.IS_OPEN(v_file) then UTL_FILE.FCLOSE(v_file); RAISE_APPLICATION_ERROR(-20001,'File already exists'); end if; exception when INVALID_OPERATION then -- when file does not exist, will raise INVALID_OPERATION exception -- will catch and insert data into file v_file := UTL_FILE.FOPEN(v_path,v_filename, 'w',32767); for rec in (select * from dept) loop UTL_FILE.PUT_LINE(v_file, rec); end loop; UTL_FILE.FCLOSE(v_file); dbms_output.put_line('data inserting opreation done.'); end proc; end pack; / #!/bin/ksh export DB_LOGIN=yourSchemaName/yourOraclePsw@serviceName sqlLogFile="your log name" typeset filename="yourfilename" typeset path="yourpath" sqlplus -s ${DB_LOGIN} >> ${sqlLogFile} <<-EOF set serveroutput on size 100000 whenever sqlerror exit failure begin pack.proc(${path},${filename}); end; / list exit success EOF
1.如果文件不存在将dept表数据写入文件中
2.package下的procedure具有两个参数,文件路径和文件名
is
procedure proc(v_path varchar2,v_filename varchar2);
end;
/
create or replace package body pack
is
procedure proc(v_path varchar2,v_filename varchar2)
is
v_file UTL_FILE.FILE_TYPE;
begin v_file := UTL_FILE.FOPEN(v_path,v_filename, 'a',32767);
-- check if file is open , if yes, raise an exception
if UTL_FILE.IS_OPEN(v_file) then
UTL_FILE.FCLOSE(v_file);
RAISE_APPLICATION_ERROR(-20001,'File already exists');
end if; exception
when INVALID_OPERATION then
-- when file does not exist, will raise INVALID_OPERATION exception
-- will catch and insert data into file
v_file := UTL_FILE.FOPEN(v_path,v_filename, 'w',32767);
for rec in (select * from dept)
loop
UTL_FILE.PUT_LINE(v_file, rec);
end loop;
UTL_FILE.FCLOSE(v_file);
dbms_output.put_line('data inserting opreation done.');
end proc;
end pack;
/
#!/bin/ksh
export DB_LOGIN=yourSchemaName/yourOraclePsw@serviceName
sqlLogFile="your log name"
typeset filename="yourfilename"
typeset path="yourpath"
sqlplus -s ${DB_LOGIN} >> ${sqlLogFile} <<-EOF
set serveroutput on size 100000
whenever sqlerror exit failure
begin
pack.proc(${path},${filename});
end;
/
list
exit success
EOF
DB_LOGIN=user/password@servicename
sqlLogFile=proclog.log #sql打印信息输出到proclog.log文件
filename=test.csv #你的文件名
path=/home/dev/ #你的文件路径
呵呵