CODEWITHSUNDEEP
plsqlclobutl-file
Akhil Sharma
Akhil Sharma

Reputation: 11

How to import data from large CSV file(file is bigger then 32767) to database using UTL_FILE

I have a csv file with 90000 records, when I am trying to import the csv in database I am getting below error-

Error report - ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at "AUTOLOCK.TEST_C1", line 136 ORA-06512: at line 1 06502. 00000 - "PL/SQL: numeric or value error%s" *Cause: An arithmetic, numeric, string, conversion, or constraint error occurred. For example, this error occurs if an attempt is made to assign the value NULL to a variable declared NOT NULL, or if an attempt is made to assign an integer larger than 99 to a variable declared NUMBER(2). *Action: Change the data, how it is manipulated, or how it is declared so that values do not violate constraints.

I know that the size of buffer is 32767 and my file is larger then this. But please tell me how to handle such case using UTL_FILE package.

below is the code-

create or replace PROCEDURE test_c1(errbuff varchar2,errcode number)
AS
v_line                      VARCHAR2(32767);
v_file                      SYS.UTL_FILE.FILE_TYPE;
--v_dir                     VARCHAR2(250);
v_filename                  VARCHAR2(250);
p_ignore_headerlines  NUMBER;

BEGIN
v_filename := 'file.csv';       
v_file := SYS.UTL_FILE.FOPEN('CSV_DIR',v_filename,'R',32767);

p_ignore_headerlines:=1;
   IF p_ignore_headerlines > 0
   THEN
      BEGIN
         FOR i IN 1 .. p_ignore_headerlines
         LOOP
            UTL_FILE.get_line (v_file, V_LINE);
         END LOOP;
            END;
   END IF;

LOOP
BEGIN
SYS.UTL_FILE.GET_LINE(v_file,v_line);
EXCEPTION
WHEN no_data_found THEN
exit;
END;
INSERT INTO load_csv
VALUES (--my columns--);

END LOOP;
UTL_FILE.FCLOSE(v_file);
END;


/

I need to load data using UTL_File Package.

Upvotes: 1

Views: 893

Answers (1)

alvalongo
alvalongo

Reputation: 571

Error "ORA-06502" is not about the file size but about line size.

The variable "v_line" is meant for 32767 bytes, but the line has more bytes.

On which operating system are your Oracle DataBase?

If it is Windows then the end-of-line (or end-of-record) MUST be 2 (two) characters: CARRIAGE_RETURN (ASCII 13 decimal or hex value 0D) and next LINE_FEED (ASCII 10 decimal or hex value 0A).

If it is Linux/Unix then end-of-line is ONLY 1 (one) character: LINE_FEED (ascii 10).

I suggest use a Hex Editor, like XVI32, so you can "see" what is the end-of-line.

On the image a text file for Windows system, with CARRIAGE_RETURN (CR, ascii 13 or hex value 0D) and next LINE_FEED (LF, ascii 10, or hex valur 0A).

enter image description here

Note: as an alternative if reading the CVS file is to be more than once, (for example every week or month) you can define a table of type "External Table". Let me know to help you. With this you can use "select" on the CVS file and it is faster than UTL_FILE.

Upvotes: 1

Related Questions