external table issue

From: <epipko_at_gmail.com>
Date: 13 Apr 2006 11:03:24 -0700
Message-ID: <1144951404.153875.261520@u72g2000cwu.googlegroups.com>

Hi all,
I am running Oracle 9.2.0.5 on Win2003 and having a problem deleting badfile created by load from external table. I get ORA-29291 and if I try to delete if manually, then I am getting an OS error: "cannot delete... being used by another program...". What am I doing wrong???

Here is the code used:

---

CREATE TABLE UB_CASE_EXT
(

  CASE_NUMBER    VARCHAR2(18 BYTE),
  UPC_ID         VARCHAR2(12 BYTE),
  STYLE_ID       VARCHAR2(14 BYTE),
  COLOR_ID       VARCHAR2(6 BYTE),
  IDENTIFIER_ID  VARCHAR2(10 BYTE),
  SIZE_ID        VARCHAR2(5 BYTE),
  DIMENSION_ID   VARCHAR2(5 BYTE),
  PROPERTY_MARK  VARCHAR2(15 BYTE),
  ETA_DATE       DATE,
  CASE_QUANTITY  NUMBER(9),
  CASE_VOLUME    NUMBER(9),
  CASE_WEIGHT    NUMBER(7,2),
  SC_FILE_ID     NUMBER(10)

)
ORGANIZATION EXTERNAL
  ( TYPE ORACLE_LOADER

     DEFAULT DIRECTORY ORALOAD
     ACCESS PARAMETERS
       ( records delimited BY newline
         NOLOGFILE
         badfile ORALOAD:UB_CASE
         FIELDS TERMINATED BY '|' LRTRIM
       	 MISSING FIELD VALUES ARE NULL
         (case_number,
          upc_id,
          style_id,
          color_id,
          identifier_id,
          size_id,
          dimension_id,
          property_mark,
          eta_date,
          case_quantity,
          case_volume,
          case_weight,
          sc_file_id)
               )
     LOCATION (ORALOAD:'case_file.txt')

  )
REJECT LIMIT UNLIMITED
PARALLEL ( DEGREE 4 INSTANCES 1 )
NOMONITORING; PROCEDURE LOG_BAD_CASES_P IS
vExists BOOLEAN;
vFile_l NUMBER; -- file lenght
vFile_bs NUMBER; -- file block size

BEGIN
 UTL_FILE.FGETATTR(LOCATION => 'e:\utl_file_dir\ORALOAD',

                                 FILENAME => 'ub_case.bad',
		 FEXISTS  => vExists,
		 FILE_LENGTH => vFile_l,
		 BLOCK_SIZE => vFile_bs);

 IF (vExists) THEN

    UTL_FILE.FREMOVE(LOCATION => 'e:\utl_file_dir\ORALOAD',

                                    FILENAME => 'ub_case.bad');

 END IF;
 COMMIT;   END LOG_BAD_CASES_P; Thanks,
Eugene Received on Thu Apr 13 2006 - 13:03:24 CDT