Hi All,
I have an issue with the external table I build for reading a CSV file. Below are the details.
The database is Oracle Cloud Infrastructure Database. And my requirement was to upload a Zip file with 4 different CSVs inside this Zip and then I had built an external table for each CSV.
The issue is that all the external tables are reading the exact count of records which are in CSVs EXCEPT one external table (say line.csv ).
I have close to 50,000 records in 03 CSV and out this 2 CSV are yielding exact same count in the external table except for line.csv.
Somehow the external table for line.csv gives total count always as 9,999 that's all when it should have been 50k.
I have not placed any limitation on num of records anywhere in the external table to create a script or the preprocessor script, not sure where it is going wrong.
Below are the scripts. I have checked the ZIP uploaded on the server to see if CSV has 50k lines or not, and the file has all correct record num.
________________________________________________________________________________
External Table Script :
CREATE TABLE "AH_FSCM_EXT"."XXGPO_PO_LINES_EXTERNAL_T"
( "INTERFACE_LINE_KEY" VARCHAR2(500 BYTE),
"INTERFACE_HEADER_KEY" VARCHAR2(500 BYTE),
"ACTION" VARCHAR2(500 BYTE),
"LINE_NUM" NUMBER(8,2),
"LINE_TYPE" VARCHAR2(500 BYTE),
"ITEM" VARCHAR2(500 BYTE),
"RMV_ITEM_DESCRIPTION" VARCHAR2(2000 BYTE),
"RMV_ITEM_REVISION" VARCHAR2(500 BYTE),
"RMV_CATEGORY_NAME" VARCHAR2(500 BYTE),
"RMV_AGREEMENT_AMOUNT" NUMBER(8,2),
"UOM_CODE" VARCHAR2(500 BYTE),
"UNIT_PRICE" NUMBER(8,2),
"ALLOW_PRICE_OVERRIDE_FLAG" VARCHAR2(500 BYTE),
"NOT_TO_EXCEED_PRICE" NUMBER(8,2),
"VENDOR_PRODUCT_NUM" VARCHAR2(500 BYTE),
"NEGOTIATED_BY_PREPARER_FLAG" VARCHAR2(500 BYTE),
"RMV_NOTE_TO_SUPPLIER" VARCHAR2(500 BYTE),
"RMV_NOTE_TO_RECEIVER" VARCHAR2(500 BYTE),
"RMV_MINIMUM_RELEASE_AMOUNT" NUMBER(8,2),
"EXPIRATION_DATE" DATE,
"RMV_SUPPLIER_PART_AUX_ID" VARCHAR2(500 BYTE),
"RMV_SUPPLIER_REF_NUM" VARCHAR2(500 BYTE),
"ATTRIBUTE_CATEGORY" VARCHAR2(500 BYTE),
"ATTRIBUTE1" VARCHAR2(500 BYTE),
"ATTRIBUTE2" VARCHAR2(500 BYTE),
"ATTRIBUTE3" VARCHAR2(500 BYTE),
"ATTRIBUTE4" VARCHAR2(500 BYTE),
"ATTRIBUTE5" VARCHAR2(500 BYTE),
"ATTRIBUTE6" VARCHAR2(500 BYTE)
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY "EXAMPLE_LOB_DIRDEV_GPO"
ACCESS PARAMETERS
( RECORDS DELIMITED BY NEWLINE
NOBADFILE NODISCARDFILE NOLOGFILE
DISABLE_DIRECTORY_LINK_CHECK
PREPROCESSOR EXAMPLE_PREPROC_GPO:'preprocesslines.bat'
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' MISSING FIELD VALUES ARE NULL
(
interface_line_key
, interface_header_key
, action
, line_num
, line_type
, item
, rmv_item_description
, rmv_item_revision
, rmv_category_name
, rmv_agreement_amount
, uom_code
, unit_price
, allow_price_override_flag
, not_to_exceed_price
, vendor_product_num
, negotiated_by_preparer_flag
, rmv_note_to_supplier
, rmv_note_to_receiver
, rmv_minimum_release_amount
, expiration_date CHAR date_format DATE mask "yyyy/mm/dd"
, rmv_supplier_part_aux_id
, rmv_supplier_ref_num
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
)
)
LOCATION
( "EXAMPLE_LOB_DIRDEV_GPO":'PoImportBlanketAgreements.zip'
)
)
REJECT LIMIT UNLIMITED ;
Preprocessor Script: preprocesslines.bat
#!/bin/bash
x="$(/usr/bin/unzip -Z1 $1 | /bin/grep -i 'PoLinesInterfaceBlanket.csv')"
# /usr/bin/unzip -c $x
/usr/bin/unzip -p $1 $x
________________________________________________________________________________
Message was edited by: Veena VDS
removed Urgent line