8 Replies Latest reply: Apr 25, 2012 7:03 AM by Hesh RSS

    Reading Excel in Blob

    Hesh
      I am searching for code that can read a spreadsheet sotred in a BLOB column, I got this from following thread but not able to get the last part , can any one help me understanding this?

      Re: Storing XLS into BLOB column and retrieving it back

      SQL> create table t
      (
         a    int primary key,
         bl   blob
      )
      / 
      Table created.
       
      SQL> declare
         bl            blob;
         bf            bfile;
         dest_offset   integer := 1;
         src_offset    integer := 1;
      begin
         dbms_lob.createtemporary (bl, true);
         bf := bfilename ('TEMP', 'test.xls');
         dbms_lob.open (bf, dbms_lob.lob_readonly);
         dbms_lob.loadblobfromfile (bl, bf, dbms_lob.getlength (bf), dest_offset, src_offset);
         dbms_lob.close (bf);
       
         insert into t values (1, bl);
       
         dbms_lob.freetemporary (bl);
      end;
      / 
      PL/SQL procedure successfully completed.
       
      SQL> create index t_idx on t (bl) indextype is ctxsys.context
         parameters ( 'filter ctxsys.auto_filter' )
      / 
      Index created.
       
      SQL> declare
         mklob   clob;
      begin
         ctx_doc.filter ('t_idx', '1', mklob, true);
         dbms_output.put_line (substr (mklob, 1, 500));
         dbms_lob.freetemporary (mklob);
      end;
      / 
       
      Tabelle1 
       
       
       
          A 
       
       
       
       1  This is from my excel file 
      PL/SQL procedure successfully completed.
        • 1. Re: Reading Excel in Blob
          BluShadow
          Well what is it you are actually trying to achieve?

          When you say you want to "read" an excel file that's stored in a blob, in what way do you want to "read" it?

          If you are expecting structured data, then what you are using appears to be wrong as it's using oracle text functionality that can read and index the textual content of data, which is returned from the Oracle Text as a CLOB as that's the 'readable' stuff from the binary data.

          You have to remember that an Excel file is a binary file structured in a Microsoft proprietary format and not something that Oracle knows how to read in a structured manner.

          If the Excel file was on the file server as a file and it was a windows server, you could set up an Excel ODBC connection to the workbook and use Oracle's heterogeneous services (HS) to see that workbook as an external database (see {message:id=9360007} in the FAQ)
          • 2. Re: Reading Excel in Blob
            Saubhik
            I am not sure what exactly, You have not understood. reading the Oracle Text Application Developer's Guide will help a lot to understand.
            http://docs.oracle.com/cd/B19306_01/text.102/b14217/toc.htm
            Also, there is another demo for pdf files here : Re: Searching a string in a pdf blob
            • 3. Re: Reading Excel in Blob
              Hesh
              may be I am completely wrong ! comparing apples and oranges !.

              My requirement is to give end user a possible way to upload data(during any of the weekday ) . Problem is end user(who actually uploads it ) getting this from another source in xls format.

              My client wants me to develop an APEX application which accepts this spread sheet and uploads into the database.

              I am trying various possible ways to do this, I have following options with my knowledge.

              1) Ask the end user to save the file into csv format. As the file contains multiple sheets there would be multiple csv files with many steps to proceed further.

              2) Simple file browser in APEX that will load the xls file into a table containing BLOB column.I have two options for further processing of reading this spread sheet and insert data into target tables.

              2a) Read xls file stored in BLOB column using PL/SQL and insert into the target tables
              2b) configure Oracle HS (Heterogeneous services ) and user a DB link to xls book


              Is there any way to achieve this using option 2a ?

              Thanks,
              Hesh.

              Edited by: Hesh on Apr 25, 2012 3:24 AM
              • 4. Re: Reading Excel in Blob
                odie_63
                2a) Read xls file stored in BLOB column using PL/SQL and insert into the target tables
                Is it old Excel binary proprietary format or new Office Open XML (xlsx)?
                • 5. Re: Reading Excel in Blob
                  BluShadow
                  Hesh wrote:
                  may be I am completely wrong ! comparing apples and oranges !.

                  My requirement is to give end user a possible way to upload data(during any of the weekday ) . Problem is end user(who actually uploads it ) getting this from another source in xls format.

                  My client wants me to develop an APEX application which accepts this spread sheet and uploads into the database.

                  I am trying various possible ways to do this, I have following options with my knowledge.

                  1) Ask the end user to save the file into csv format. As the file contains multiple sheets there would be multiple csv files with many steps to proceed further.

                  2) Simple file browser in APEX that will load the xls file into a table containing BLOB column.I have two options for further processing of reading this spread sheet and insert data into target tables.

                  2a) Read xls file stored in BLOB column using PL/SQL and insert into the target tables
                  2b) configure Oracle HS (Heterogeneous services ) and user a DB link to xls book


                  Is there any way to achieve this using option 2a ?
                  If the "user" is obtaining the XLS file, and they are using APEX, then APEX can be used to upload the file to the server (into BLOB) from where it can be saved to the server filesystem as a file with a known name, for which you can then have a HS set up to query the data from the sheets within that.

                  In terms of your option 2a), what you have when the file is in the BLOB is just a large object containing some binary data. Oracle doesn't know it's an Excel file, and Oracle doesn't know how to read such a binary format of file.
                  • 6. Re: Reading Excel in Blob
                    Hesh
                    Is it old Excel binary proprietary format or new Office Open XML (xlsx)?
                    It is xls format , is there any way to read xlsx? ( now I am thinking of whether I can ask the user who is uploading to save the file in xlsx format if possible)
                    If the "user" is obtaining the XLS file, and they are using APEX, then APEX can be used to upload the file to the server >(into BLOB) from where it can be saved to the server filesystem as a file with a known name, for which you can then >have a HS set up to query the data from the sheets within that.
                    I tried this but run into two issues..

                    1) I am able to load the xls file into BLOB and from this BLOB generated the xls file in the server filesystem. When I tried to open this output xls file (generated from BLOB) I got following message
                     'Excel found unreadable content in 'tst.xls'.
                    Do you want to recover the contents of this workbook, If you trust the source of this workbook, click Yes '
                    I am able to open the workbook after clicking Yes but I don't know whether this can stop the processing when HS configured?

                    2) I am able to configure HS, tested listener by ' lsnrctl status' and TNS status by 'tnsping'. But during the execution of list of tables using DB link created using 'SELECT table_name FROM all_tables@custard' getting following error
                    ORA-28545: error diagnosed by Net8 when connecting to an agent
                    Unable to retrieve text of NETWORK/NCR message 65535
                    ORA-02063: preceding 2 lines from CUSTARD
                    Thanks,
                    Hesh.
                    • 7. Re: Reading Excel in Blob
                      BluShadow
                      Hesh wrote:
                      Is it old Excel binary proprietary format or new Office Open XML (xlsx)?
                      It is xls format , is there any way to read xlsx? ( now I am thinking of whether I can ask the user who is uploading to save the file in xlsx format if possible)
                      If the "user" is obtaining the XLS file, and they are using APEX, then APEX can be used to upload the file to the server >(into BLOB) from where it can be saved to the server filesystem as a file with a known name, for which you can then >have a HS set up to query the data from the sheets within that.
                      I tried this but run into two issues..

                      1) I am able to load the xls file into BLOB and from this BLOB generated the xls file in the server filesystem. When I tried to open this output xls file (generated from BLOB) I got following message
                      'Excel found unreadable content in 'tst.xls'.
                      Do you want to recover the contents of this workbook, If you trust the source of this workbook, click Yes '
                      I am able to open the workbook after clicking Yes but I don't know whether this can stop the processing when HS configured?
                      Then the file has not be transferred and saved correctly in binary format and has got corrupted somewhere. Without seeing your code that does this we can't help.
                      2) I am able to configure HS, tested listener by ' lsnrctl status' and TNS status by 'tnsping'. But during the execution of list of tables using DB link created using 'SELECT table_name FROM all_tables@custard' getting following error
                      ORA-28545: error diagnosed by Net8 when connecting to an agent
                      Unable to retrieve text of NETWORK/NCR message 65535
                      ORA-02063: preceding 2 lines from CUSTARD
                      Not an oracle error, but an error from CUSTARD (whatever that is). Looks like you are reading the file over a network or something and there's an issue with that?

                      Following the basic steps for setting up HS should work ok...
                      Example...

                      1- Go to Control Panel>Administrative Tools>Data Sources (ODBC)>System DSN and create a data source with appropriate driver. Name it EXCL.

                      2- In %ORACLE_HOME%\Network\Admin\Tnsnames.ora fie add entry:
                      EXCL =
                        (DESCRIPTION =
                          (ADDRESS_LIST =
                            (ADDRESS = (PROTOCOL = TCP)(HOST = 10.12.0.24)(PORT = 1521))
                          )
                          (CONNECT_DATA =
                            (SID = EXCL)
                          )
                          (HS = OK)
                        )
                      Here SID is the name of data source that you have just created.

                      3- In %ORACLE_HOME%\Network\Admin\Listener.ora file add:
                      (SID_DESC = 
                        (PROGRAM = hsodbc) 
                        (SID_NAME = <hs_sid>) 
                        (ORACLE_HOME = <oracle home>) 
                      )
                      under SID_LIST_LISTENER like:
                      SID_LIST_LISTENER =
                        (SID_LIST =
                          (SID_DESC =
                            (SID_NAME = PLSExtProc)
                            (ORACLE_HOME = d:\ORA9DB)
                            (PROGRAM = extproc)
                          )
                          (SID_DESC =
                            (GLOBAL_DBNAME = ORA9DB)
                            (ORACLE_HOME = d:\ORA9DB)
                            (SID_NAME = ORA9DB)
                          )
                          (SID_DESC = 
                            (PROGRAM = hsodbc) 
                            (SID_NAME = EXCL) 
                            (ORACLE_HOME = D:\ora9db) 
                          )
                        ) 
                      Dont forget to reload the listener
                      C:\> lsnrctl reload
                      4- In %ORACLE_HOME%\hs\admin create init<HS_SID>.ora. For our sid EXCL we create file initexcl.ora.

                      In this file set following two parameters:
                      HS_FDS_CONNECT_INFO = excl
                      HS_FDS_TRACE_LEVEL = 0
                      5- Now connect to Oracle database and create database link with following command:
                      SQL> CREATE DATABASE LINK excl
                      2 USING 'excl'
                      3 /
                      
                      Database link created.
                      Now you can perform query against this database like you would for any remote database.
                      SQL> SELECT table_name FROM all_tables@excl;
                      
                      TABLE_NAME
                      ------------------------------
                      DEPT
                      EMP
                      Note:
                      1) Each worksheet in the Excel workbook is taken to be a "table" with the worksheet name being the table name (must not exceed the 30 character limit of Oracle, as Excel allows 32 chrs).
                      2) The first row on the worksheet provides the column names for the table (again, limited to 30 characters)
                      3) The second and subsequent rows provide the actual rows of data on the table.


                      Or refer to this Article...
                      http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4406709207206
                      • 8. Re: Reading Excel in Blob
                        Hesh
                        here is my code for generating BLOB-->xls
                        declare
                          b_obj      BLOB;
                          amount      BINARY_INTEGER;
                          file_handle UTL_FILE.FILE_TYPE;
                          l_pos        INTEGER            := 1;
                          l_blob_len   INTEGER;
                          l_buffer     RAW (32767);
                          l_amount     BINARY_INTEGER     := 32767;
                          l_file_name VARCHAR2(200);
                                  --v_run_dt    DATE:=TO_DATE(p_business_date,'mm/dd/yyyy');
                        BEGIN
                        -----------------------------------------------------------------------------
                        --                                   VLITE
                        -----------------------------------------------------------------------------
                          SELECT NVL((  SELECT column2  FROM TEST_LOAD_XL 
                                                WHERE rownum=1),NULL)  
                         INTO b_obj FROM DUAL;
                         ----------------------
                         --Check whether there is data in the file or not if not no file will be generated
                         IF b_obj IS NOT NULL AND DBMS_LOB.getlength (b_obj)>0 THEN
                         -----------------------
                            l_file_name :='tstxl.xls';
                          file_handle := UTL_FILE.FOPEN('FOO_DIR2',
                                                        l_file_name,
                                                        'wb' ----Note this line
                                                        );
                          l_blob_len := DBMS_LOB.getlength (b_obj);
                          -- Read chunks of the BLOB and write them to the file
                        -- until complete.
                           WHILE l_pos < l_blob_len
                           LOOP
                              DBMS_LOB.READ (b_obj, l_amount, l_pos, l_buffer);
                              UTL_FILE.put_raw (file_handle, l_buffer, FALSE);
                              l_pos := l_pos + l_amount;
                           END LOOP;
                           UTL_FILE.FCLOSE(file_handle);
                        -----------------------   
                           END IF;
                        -----------------------
                        END;
                        Not an oracle error, but an error from CUSTARD (whatever that is). Looks like you are reading the file over >a network or something and there's an issue with that?
                        I am trying with the same thread from 'Ask Tom'.

                        I am not testing this in network . It is my local machine installed oralce 11g on windows xp.

                        Thanks,
                        Hesh.