Utl_File

PL/SQL Sample�B�@UTL_FILE

*** INIT�E��E�B�E�A�E襪�k"utl_file_dir"�A�z�T��g蠅��m��w��A�k�A�P

CREATE OR REPLACE PROCEDURE SP_UTL_FILE
    ( nRET           OUT   NUMBER
    )
/******************************************************************************
** �E��E蹈�L�E�N�E稾�Z�B�B�B�B�B�@SP_UTL_FILE
** �l�k�\�B�B�B�B�B�B�B�B�B�B�B�B�@01
** �I�B�k�X�E�I�w��B�B�B�B�B�B�B�B�B�@UTL_FILE�A�kEXPORT�A�zDAT�E��E�B�E�A�E襪鄒��A�P�A�
**                    �A�E�A�zSP�A�z�I�B�k�X�A�}�c�Z�A�zSP�A�q�c��A�L�A�j�z�m�A�A
**                    OUT   nRET       �X萢覯�t
** �E�[�E蹇�V�E�~�E襦�B�B�B�B�B�B�@none:
******************************************************************************/
AS
    /* ﾜｰｸ�n��\�z�g蟲�` */
    FILE_HANDLE   UTL_FILE.FILE_TYPE;   --ﾌｧｲﾙﾊﾝﾄﾞﾙ�A�z�g蟲�`
    DATA_LINE     VARCHAR2(800);        --ﾌｧｲﾙ�X�~�z�}ｴﾘｱ
    WK_DATA_LINE VARCHAR2(800);        --ﾌｧｲﾙ�X�~�z�}�n��X�Nｴﾘｱ

    v_FILE_PATH   VARCHAR2(40) := 'D:\DBMS_OUT';                   -- Out Path
    vCtrl_FILE    VARCHAR2(40) := 'EXPORT.bat';                    -- Out File
    cDATE         VARCHAR2(8);
    oRET          NUMBER;

BEGIN

-- �j��A�n�k�V
SELECT to_char(SYSDATE ,'YYYYMMDD') INTO cDATE FROM DUAL;

-- �^�D�N罐�~�E�e�E�`�E��E�B�E�A�E觝鄒��
FILE_HANDLE := UTL_FILE.FOPEN(v_FILE_PATH ,vCtrl_FILE,'W'); -- �E��E�B�E�A�E襪鄒��

-- �E��E薀瓠�V�E�\�T��g蠅�J�A�k1�P��A�k�X�b�A�E�A�l
UTL_FILE.PUT_LINE(FILE_HANDLE , 'exp80 USER/PASSWD@SID parfile=' || v_FILE_PATH || '\pfile' || cDATE || '.dat');

   oRET := SF_YE_UTL_FILE(cDATE);                                  -- EXPORT�w�� pfile�A鄒��A�L�A�J�A�P

   IF oRET <> 0 THEN
      nRET := 9;
   END IF;

   UTL_FILE.FCLOSE(FILE_HANDLE);                                   -- �X��P�K�X�F�z�T

nRET := 0; -- �^�I�Z鐔�F�z�T

EXCEPTION
/*** �`�G�A�j�A�z�E�B�E蕁�V�A�z�Z豺� ***/
WHEN OTHERS THEN

/* �t瓩蠱�w�A�z�^�K�g� */
nRET := SQLCODE; -- �[��Z�
END SP_UTL_FILE;
/
SHOW ERRORS