This discussion is archived
5 Replies Latest reply: Jan 3, 2013 3:31 PM by Kenny Hanberg RSS

Issues loading data from CSV file in Data Utilities ....

bondurs Newbie
Currently Being Moderated
Greetings:

We are using APEX 4.0.2.00.06, Database is 11.2.0.3.0

I am attempting to upload an Excel spreadsheet, which I have exported as a pipe-delimited file (there are commas in the data) with an extension of *.csv. The size of the entire file is 1.44 MG. Using the APEX Data Import Utilities, whether I have APEX create a table for me, or try to import into a table that is already defined (all columns in the table have varchar2(4000) character size), I get the following error:

ORA-20001: Unable to create collection: ORA-06502: PL/SQL: numeric or value error: character string buffer too small
Error creating collection using loaded data.

Now, if I take a small chunk of records from the file (about 50 rows of data) and attempt to upload this small file, it works fine.

This issue occurs whether I choose to load Text Data or Spreadsheet Data.

Does anyone know what might be causing this?

Thanks,
Stan
  • 1. Re: Issues loading data from CSV file in Data Utilities ....
    TexasApexDeveloper Guru
    Currently Being Moderated
    Maybe you could instead create the file as an external table and then select from it into your new table?

    Thank you,

    Tony Miller
    Ruckersville, VA
  • 2. Re: Issues loading data from CSV file in Data Utilities ....
    bondurs Newbie
    Currently Being Moderated
    Tony, I'll have to look in to importing from an external table, since I haven't done that before. If it requires connecting to the database, then I can't do it. All of my development has to be done within the APEX SQL Workshop developer tool., and I dont' get access to connect strings and such. Ugh... I miss Toad! :-)

    I guess what I'm looking for is if there is some parameter that sets a buffer size that my DBA can reset to allow me to upload this file. Or some type of work-around I can do.

    Stan
  • 3. Re: Issues loading data from CSV file in Data Utilities ....
    TexasApexDeveloper Guru
    Currently Being Moderated
    Youch.. There are some older methods for loading a csv file into oracle tables via APEX that I know of.. They take the file into the APEX meta data table and then parse it up, which might have a better result..

    Do you have the ability to build a one or two column table and attempt uploading the csv file into this table just to see if it is a timeout issue on your database connection?

    http://avdeo.com/2008/05/21/uploading-excel-sheet-using-oracle-application-express-apex/
    Importing Excel spreadsheet into Oracle via Apex

    The above links should help you see alternate means to upload csv files into Oracle tables...

    Thank you,

    Tony Miller
    Ruckersville, VA

    PS: No Toad, NO SQL Developer... That is a STRANGE place to be in....
  • 4. Re: Issues loading data from CSV file in Data Utilities ....
    ascheffer Expert
    Currently Being Moderated
    Or you can wait for some newer methods. I'm working on an Excel data loader.
    At this moment it loads only into Apex collections.
    I've never tried it with large csv-files, but large (more than 1 Mb, several sheets with 5000 lines) xls and xlsx works

    http://apex.oracle.com/pls/apex/f?p=23131:2:9558734812176::NO:::
  • 5. Re: Issues loading data from CSV file in Data Utilities ....
    Kenny Hanberg Explorer
    Currently Being Moderated
    Stan,

    Try this...

    An alternative method You might consider is uploading the file in apex - as a clob and use plsql to import the rows to the appropriate table.

    Create a temp. page in apex with a simple report region 'Uploaded files'
    SELECT *
      FROM apex_application_files
     WHERE flow_id in (v('APP_ID'),0)
    Create a page item P1_FILENAME, display as "File Browse..."
    Create a button to submit the page (and automatically upload your file to the database), and remember to create a simple branch



    Create a support package for pipelining rows from the clob as a table...
    CREATE OR REPLACE PACKAGE csv_from_clob_pkg2
    AS
       TYPE csv_1col_type IS RECORD(chars VARCHAR2(4000));
    
       TYPE csv_2col_type IS RECORD
       (
          col1   VARCHAR2(4000)
         ,col2   VARCHAR2(4000)
       );
    
       TYPE CSV_1COL_TYPE_SET IS TABLE OF csv_1col_type;
    
       TYPE CSV_2COL_TYPE_SET IS TABLE OF csv_2col_type;
    
       FUNCTION get_lines_as_1_col_table(p_id IN APEX_APPLICATION_FILES.ID%TYPE)
          RETURN csv_1col_type_Set
          PIPELINED;
    
       FUNCTION get_lines_as_2_col_table( p_id IN APEX_APPLICATION_FILES.ID%TYPE, p_delimiter IN VARCHAR2 DEFAULT NULL)
          RETURN csv_2col_type_Set
          PIPELINED;
    END csv_from_clob_pkg2;
    /
    CREATE OR REPLACE PACKAGE BODY csv_from_clob_pkg2
    AS
       PROCEDURE free_clob(theClob IN OUT NOCOPY CLOB)
       IS
       BEGIN
          IF DBMS_LOB.ISTEMPORARY(theClob) = 1 THEN
             DBMS_LOB.freetemporary(theClob);
          END IF;
    
          IF DBMS_LOB.ISOPEN(theClob) = 1 THEN
             DBMS_LOB.close(theClob);
          END IF;
       EXCEPTION
          WHEN OTHERS THEN
             NULL;
       END free_clob;
    
       PROCEDURE free_blob(theBlob IN OUT NOCOPY BLOB)
       IS
       BEGIN
          IF DBMS_LOB.ISTEMPORARY(theBlob) = 1 THEN
             DBMS_LOB.freetemporary(theBlob);
          END IF;
    
          IF DBMS_LOB.ISOPEN(theBlob) = 1 THEN
             DBMS_LOB.close(theBlob);
          END IF;
       EXCEPTION
          WHEN OTHERS THEN
             NULL;
       END free_blob;
    
    
       FUNCTION get_clob(p_id IN APEX_APPLICATION_FILES.ID%TYPE)
          RETURN CLOB
       IS
          newClob          CLOB;
          theBlob          BLOB;
          l_warning        INTEGER;
          l_lang_context   INTEGER;
          l_dest_offset    INTEGER;
          l_src_offset     INTEGER;
          l_amount         INTEGER;
       BEGIN
          DBMS_LOB.CREATETEMPORARY( newClob, TRUE);
    
          BEGIN
             l_dest_offset    := 1;
             l_src_offset     := 1;
             l_lang_context   := DBMS_LOB.DEFAULT_LANG_CTX;
             l_amount         := DBMS_LOB.LOBMAXSIZE;
    
             SELECT BLOB_CONTENT
               INTO theBlob
               FROM APEX_APPLICATION_FILES
              WHERE id = p_id;
    
             DBMS_LOB.CONVERTTOCLOB(dest_lob       => newClob
                                   ,src_blob       => theBlob
                                   ,amount         => l_amount
                                   ,dest_offset    => l_dest_offset
                                   ,src_offset     => l_src_offset
                                   ,blob_csid      => DBMS_LOB.default_csid
                                   ,lang_context   => l_lang_context
                                   ,warning        => l_warning);
             free_blob(theBlob);
          EXCEPTION
             WHEN NO_DATA_FOUND THEN
                free_blob(theBlob);
                free_clob(newClob);
                RAISE;
             WHEN OTHERS THEN
                free_blob(theBlob);
                free_clob(newClob);
                RAISE;
          END;
    
          RETURN newClob;
       END get_clob;
    
       FUNCTION get_line_from_clob( p_clob IN CLOB, p_offset IN OUT NOCOPY INTEGER)
          RETURN VARCHAR2
       IS
          l_found_CR_At   INTEGER;
          l_found_LF_At   INTEGER;
          l_amount        INTEGER;
          vBuffer         VARCHAR2(32767);
       /* CR:13:0d
          LF:10:0a
          HT:09:09 (tab) */
       BEGIN
          l_found_CR_At   := DBMS_LOB.INSTR( p_clob, CHR(13), p_offset);
          l_found_LF_At   := DBMS_LOB.INSTR( p_clob, CHR(10), p_offset);
          l_amount        :=   GREATEST( l_found_CR_At, l_found_LF_At)
                             - p_offset
                             + 1;
    
          IF (l_amount < 0) THEN
             l_amount   := LEAST( DBMS_LOB.GETLENGTH(p_clob) + 1 - p_offset, 32767);
          END IF;
    
          IF (l_amount > 0) THEN
             DBMS_LOB.READ(lob_loc   => p_clob
                          ,amount    => l_amount                        /*in/out*/
                          ,offset    => p_offset
                          ,buffer    => vBuffer                            /*out*/
                                               );
             p_offset   := p_offset + l_amount;
          ELSE
             RAISE NO_DATA_FOUND;
          END IF;
    
          RETURN REPLACE( REPLACE( vBuffer, CHR(13), ''), CHR(10), '');
       END get_line_from_clob;
    
       FUNCTION get_lines_as_1_col_table(p_id IN APEX_APPLICATION_FILES.ID%TYPE)
          RETURN csv_1col_type_Set
          PIPELINED
       IS
          vBuffer        VARCHAR2(32767);
          newClob        CLOB := EMPTY_CLOB;
          l_src_offset   INTEGER;
          l_old_offset   INTEGER;
          out_rec        csv_1col_type;
       BEGIN
          newClob         := GET_CLOB(p_id);
          l_src_offset    := 1;
          out_rec.chars   := '';
    
          IF (DBMS_LOB.INSTR( newClob, CHR(0), 1) > 0
           OR DBMS_LOB.INSTR( newClob, CHR(255), 1) > 0) THEN
             free_clob(newClob);
             RAISE NO_DATA_FOUND;
          END IF;
    
          LOOP
             BEGIN
                l_old_offset    := l_src_offset;
                out_rec.chars   := SUBSTR( get_line_from_clob( newClob, l_src_offset), 1, 4000);
                EXIT WHEN l_old_offset = l_src_offset;
                PIPE ROW (out_rec);
             EXCEPTION
                WHEN NO_DATA_FOUND THEN
                   EXIT WHEN TRUE;
                WHEN OTHERS THEN
                   RAISE;
             END;
          END LOOP;
    
          free_clob(newClob);
          RETURN;
       END get_lines_as_1_col_table;
    
       FUNCTION get_lines_as_2_col_table( p_id IN APEX_APPLICATION_FILES.ID%TYPE, p_delimiter IN VARCHAR2 DEFAULT NULL)
          RETURN csv_2col_type_Set
          PIPELINED
       IS
          vBuffer        VARCHAR2(32767);
          newClob        CLOB := EMPTY_CLOB;
          l_src_offset   INTEGER;
          l_old_offset   INTEGER;
          l_delimiter    VARCHAR2(1);
          out_rec        csv_2col_type;
          l_vc_arr2      HTMLDB_APPLICATION_GLOBAL.VC_ARR2;
       BEGIN
          newClob        := GET_CLOB(p_id);
          l_src_offset   := 1;
          out_rec.col1   := NULL;
          out_rec.col2   := NULL;
          l_delimiter    := p_delimiter;
    
          IF (DBMS_LOB.INSTR( newClob, CHR(0), 1) > 0
           OR DBMS_LOB.INSTR( newClob, CHR(255), 1) > 0) THEN
             free_clob(newClob);
             RAISE NO_DATA_FOUND;
          END IF;
    
          LOOP
             BEGIN
                l_old_offset   := l_src_offset;
                vBuffer        := get_line_from_clob( newClob, l_src_offset);
                l_vc_arr2      := HTMLDB_UTIL.STRING_TO_TABLE( p_string => vBuffer, p_separator => l_delimiter);
                out_rec.col1   := SUBSTR( l_vc_arr2(1), 1, 4000);
                out_rec.col2   := SUBSTR( l_vc_arr2(2), 1, 4000);
                EXIT WHEN l_old_offset = l_src_offset;
                PIPE ROW (out_rec);
             EXCEPTION
                WHEN NO_DATA_FOUND THEN
                   EXIT WHEN TRUE;
                WHEN OTHERS THEN
                   RAISE;
             END;
          END LOOP;
    
          free_clob(newClob);
          RETURN;
       END get_lines_as_2_col_table;
    END csv_from_clob_pkg2;
    /
    Create a 2nd Page with a sql-report
    SELECT rownum, chars,length ( chars) - nvl(length(replace(chars,';','')),0)+1 no_of_cols
      FROM table(
                 csv_from_clob_pkg2.get_lines_as_1_col_table (1329507462824961,'|')
                )
     WHERE ROWNUM <= 10
    Replace the hard-coded id (1329507462824961) with the id of Your uploaded file..
    Run the page and see if it works :)

    Create a 2nd a sql-report on the 2nd Page
    SELECT rownum, col1,col2
      FROM table(csv_from_clob_pkg2.get_lines_as_2_col_table (1329507462824961,'|')
                 )
     WHERE ROWNUM <= 10
    Hope it still runs after removing aloto llines from the package:)

    /Kenny

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points