Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Modifying and saving a Excel file by the use of Blob-Stream Method?

832380Jan 19 2011 — edited Jan 20 2011
Hi everyone...

I'm trying to change and modify a Excel file which is received as Blob variable from database.

I wrote a PL/SQL Procedure which get the Excel file and convert it to blob; after that send it as the parameter of a Java source called TestPrintExcel
CREATE OR REPLACE DIRECTORY VMI_TEST_DIR AS '/afc/opt/dba/test_utl_dir';

GRANT READ, WRITE ON DIRECTORY SYS.VMI_TEST_DIR TO SYSTEM;

declare  
   bfi  bfile := bfilename('VMI_TEST_DIR','TestFzglist.xls');
   bin  blob;
   cout clob;   
begin
   dbms_lob.open(bfi);
   dbms_lob.createtemporary(bin, true);
   dbms_lob.loadfromfile(bin, bfi, dbms_lob.getlength(bfi));
   dbms_lob.close(bfi);
   dbms_lob.createtemporary(cout, true);
   dbms_java.set_output(100000);

   TestPrintExcel(bin);

   dbms_output.put_line('done!');
end;
With the following Java procedure I can read the content of the Excel file and write it to database perfectly.
But the question is,
how can i save the excel file with the same way, if i would change or insert any cell? I mean with the same way that to save as blob stream.
Because i get permission error when i use another methods like FileOutputStream, FileInputStream etc.
That's way i want to resolve this problem with the blob-stream method.
Can anybody tell me how i can do that or what a method i have to use?
Thanks.
create or replace and compile java source named xltest as
  import org.apache.poi.ss.usermodel.WorkbookFactory;
  import org.apache.poi.ss.usermodel.Workbook;
  import org.apache.poi.ss.usermodel.Sheet;
  import org.apache.poi.ss.usermodel.Row;
  import org.apache.poi.ss.usermodel.Cell;
  import org.apache.poi.ss.util.CellReference;
  import org.apache.poi.ss.usermodel.DataFormatter;
  import org.apache.poi.ss.usermodel.FormulaEvaluator;
  import java.util.*;
  import java.sql.*;
  import oracle.sql.*;
  import oracle.jdbc.driver.*;
  
  public class XLTEST {
    public static void printx( oracle.sql.BLOB xblin ) throws SQLException {                        
      Connection Conn = null;
      try {
        Conn = DriverManager.getConnection( "jdbc:default:connection:" ); 
        Workbook wb = WorkbookFactory.create( xblin.getBinaryStream() ); // "/afc/opt/dba/test_utl_dir/TstFzglist.xls"
        for ( int sheetNum = 0; sheetNum < wb.getNumberOfSheets(); sheetNum++ ) {  //NumberOfSheets
          String TableName = new String();        
          switch( sheetNum )
          {
            case 0:
              TableName = "freeimportxls_s1"; break;            
            case 1:
              TableName = "FreeImportXLS_S2"; break;
            case 2:
              TableName = "FreeImportXLS_S3"; break;
            default: 
              break;                   
          }                   
          Sheet sheet1 = wb.getSheetAt(sheetNum);          
          java.util.Iterator<Row> ri = sheet1.rowIterator();                  
          
          while ( ri.hasNext() ) {
            Row row = ri.next(); 
            java.util.Iterator<Cell> ci = row.cellIterator();                        
            while ( ci.hasNext() ) {
              Cell cell = ci.next();
              CellReference cellRef = new CellReference( row.getRowNum(), cell.getColumnIndex() );                          
              switch( cell.getCellType() ) {
              case Cell.CELL_TYPE_STRING:                             
                Values   += "'" + cell.getRichStringCellValue().getString() + "'";                
                break;
              case Cell.CELL_TYPE_NUMERIC:
                Values   += "'" + cell.getNumericCellValue() + "'";
                break;
              case Cell.CELL_TYPE_BOOLEAN:
                Values   += "'" + cell.getBooleanCellValue() + "'";
                break;
              case Cell.CELL_TYPE_FORMULA:
                Values   += "'" + cell.getCellFormula() + "'";
                break;
              default:
                Values   += "''";                
              }             
              int Counter1 = cell.getColumnIndex() + 1;
              int Counter2 = row.getLastCellNum();
              if ( Counter1==Counter2 ) { 
                Values   += ")";                           
              else { 
                Values   += "," ; 
                }
            } System.out.println ( Values );                
            if ( ( sheetNum >= 0 ) && ( sheetNum <=2 ) ) {
               String sqlUpdate = "INSERT INTO " + TableName + " VALUES " + Values;
               PreparedStatement pstmt = Conn.prepareStatement( sqlUpdate );                                           
               pstmt.executeUpdate();            
               pstmt.close();               
               }
          }
        } Conn.close();
      } catch ( java.sql.SQLException e ) {        
        System.err.println( e.getMessage() );
        e.printStackTrace( System.err );
        Conn.close();}         
    } 
  };
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Feb 17 2011
Added on Jan 19 2011
2 comments
1,469 views