Announcement Announcement Module
Collapse
No announcement yet.
How to insert byte array into oracle BFile field? Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • How to insert byte array into oracle BFile field?

    I'm trying to insert a data (photograph) into the Oracle table that contains a BFile field.
    Here are some code snippets:

    A table:
    Code:
    CREATE TABLE BFILESTORE
    (
      IMAGE  BFILE
    )
    Java code:
    Code:
    String pic = "....Base64-encoded picture....";
    LobHandler lHandler = new DefaultLobHandler();
    final int length = pic.length();
    final InputStream inputStream = new ByteArrayInputStream(Base64.decodeBase64(pic.getBytes()));
    template.execute("INSERT INTO BFILESTORE (IMAGE) VALUES(?)", new AbstractLobCreatingPreparedStatementCallback(lHandler) {
        @Override
        protected void setValues(PreparedStatement ps, LobCreator lobCreator)
                throws SQLException, DataAccessException {
            lobCreator.setBlobAsBinaryStream(ps, 1, inputStream, length);
        }
    });
    If I try to insert into BFile this way I get the "ora-00932 inconsistent datatypes expected NUMBER got BINARY", so I've tried to insert through a stored procedure:

    Code:
    CREATE OR REPLACE PROCEDURE INSERTBFILE (
       SOMEIMAGE          IN     BFILE)
    AS
    BEGIN
            INSERT INTO BFILESTORE(IMAGE) VALUES (SOMEIMAGE);
    END INSERTBFILE;
    And code for the SP in java:

    Code:
    class Updater3 extends StoredProcedure {
    private static final String PROC_NAME = "INSERTBFILE";
    private static final String PIC_PARAM_NAME = "SOMEIMAGE";
    
    public Updater3(DataSource dataSource) {
        super(dataSource, PROC_NAME);
        declareParameter(new SqlParameter(PIC_PARAM_NAME, Types.BINARY));
        compile();
    }
            
    public void update(String picInBase64) {
        byte[] b = null;
        if (picInBase64 != null) b = Base64.decodeBase64(picInBase64.getBytes());
        execute(b);
    }
    }
    If I execute this stored procedure I receive PLS-00306 (wrong number or types of arguments in call to 'INSERTBFILE').

    Any help will be highly appreciated!

  • #2
    After some hours of thinking on this problem I decided to write the file from the java code into the oracle directory and "insert" a picture by a bfilename function.

    Comment

    Working...
    X