utl_file

[Adebayor]

1.utl_file如何判断文件是否存在呀？ 如果存在抛出异常
2.将1写成package并用shell调用 该怎么写？
请高手帮忙

[Adebayor]

[Quote=引用楼主 adebayor 的回复:]
1.utl_file如何判断文件是否存在呀？ 如果存在抛出异常
2.将1写成package并用shell调用 该怎么写？
请高手帮忙
[/Quote]
补充下：
1.如果文件不存在将dept表数据写入文件中
2.package下的procedure具有两个参数，文件路径和文件名

[shiyiwan]

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

[guser25]

学习&

[inthirties]

不错，收藏一个

[oraclemch]

[Quote=引用 2 楼 shiyiwan 的回复:]
SQL codecreateorreplace package packisprocedureproc(v_pathvarchar2,v_filenamevarchar2);end;/createorreplace package body packisprocedureproc(v_pathvarchar2,v_filenamevarchar2)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 exceptionif UTL_FILE.IS_OPEN(v_file)then
UTL_FILE.FCLOSE(v_file);
RAISE_APPLICATION_ERROR(-20001,'File already exists');endif;

exceptionwhen INVALID_OPERATIONthen-- 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 recin (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.');endproc;end pack;/

Perl code#!/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 size100000
whenever sqlerrorexit failure
beginpack.proc(${path},${filename});
end;/
listexit successEOF
[/Quote]

这个还是真的不会啊，谢谢楼主的提问，谢谢这位的回复，学习了，先！

[Adebayor]

[Quote=引用 2 楼 shiyiwan 的回复:]

Perl code#!/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 size100000
whenever sqlerrorexit failure
beginpack.proc(${path},${filename});
end;/
listexit successEOF
[/Quote]
这个是什么意思呀？ 不知道怎么才能运行 帮忙解释下呗

[Adebayor]

如果用shell该怎么调用plsql呀？

[shiyiwan]

第二段就是shell代码呀，你改下里面的值就可以用了
DB_LOGIN=user/password@servicename
sqlLogFile=proclog.log #sql打印信息输出到proclog.log文件
filename=test.csv #你的文件名
path=/home/dev/ #你的文件路径

[Adebayor]

[Quote=引用 8 楼 shiyiwan 的回复:]
第二段就是shell代码呀，你改下里面的值就可以用了
DB_LOGIN=user/password@servicename
sqlLogFile=proclog.log #sql打印信息输出到proclog.log文件
filename=test.csv #你的文件名
path=/home/dev/  #你的文件路径
[/Quote]
thank you and ありがとう
呵呵

[小灰狼W]

[Quote=引用 2 楼 shiyiwan 的回复:]
SQL codecreateorreplace package packisprocedureproc(v_pathvarchar2,v_filenamevarchar2);end;/createorreplace package body packisprocedureproc(v_pathvarchar2,v_filenamevarchar2)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 exceptionif UTL_FILE.IS_OPEN(v_file)then
UTL_FILE.FCLOSE(v_file);
RAISE_APPLICATION_ERROR(-20001,'File already exists');endif;

exceptionwhen INVALID_OPERATIONthen-- 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 recin (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.');endproc;end pack;/

Perl code#!/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 size100000
whenever sqlerrorexit failure
beginpack.proc(${path},${filename});
end;/
listexit successEOF
[/Quote]
学习