5 Replies Latest reply: Jan 3, 2013 5:31 PM by Kenny Hanberg RSS

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

    bondurs
      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
          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
            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
              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
                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
                  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