1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

How to insert group of pdf files (from a directory) into a blob column of a table

Discussion in 'SQL PL/SQL' started by Penchal, Mar 5, 2018.

  1. Penchal

    Penchal Newly Initiated

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    30
    Location:
    Bangalore
    Hi Folks,
    Need your help to load PDF files into oracle database. I tried the below procedure, but facing issues when I run it.
    Steps Done:
    1. Created Table pdf_table
    BFILE_ID NUMBER
    BFILE_DESC VARCHAR2(30 BYTE)
    BFILE_TYPE VARCHAR2(4 BYTE)
    BFILE_LOC BFILE
    2. Created Table internal_pdf
    ID NUMBER
    BF_DESC VARCHAR2(30 BYTE)
    IMAGE1 BLOB
    BF_TYP VARCHAR2(4 BYTE)
    3. Created Table temp_blob
    TEMP_BLOB BLOB
    4. Created Directory BFILE
    OWNER SYS
    DIRECTORY_NAME BFILE
    DIRECTORY_PATH C:\Temp\PDF_FILES
    ORIGIN_CON_ID 1
    5. Created Procedure load_lob
    Procedure to load a PDF as a BFILE:

    create or replace PROCEDURE load_lob AS
    id NUMBER;
    image1 BLOB;
    locator BFILE;
    bfile_len NUMBER;
    bf_desc VARCHAR2(30);
    bf_name VARCHAR2(30);
    bf_dir VARCHAR2(30);
    bf_typ VARCHAR2(4);
    ctr integer;
    CURSOR get_id IS
    SELECT bfile_id,bfile_desc,bfile_type FROM pdf_table;
    BEGIN
    OPEN get_id;
    LOOP
    FETCH get_id INTO id, bf_desc, bf_typ;
    EXIT WHEN get_id%notfound;
    dbms_output.put_line('ID: '||to_char(id));
    SELECT bfile_loc INTO locator FROM pdf_table WHERE bfile_id=id;
    SYS.dbms_lob.filegetname(locator,bf_dir,bf_name);
    dbms_output.put_line('Dir: '||bf_dir);
    SYS.dbms_lob.fileopen(locator,dbms_lob.file_readonly);
    bfile_len:=dbms_lob.getlength(locator);
    dbms_output.put_line('ID: '||to_char(id)||' length: '||to_char(bfile_len));
    SELECT temp_blob INTO image1 FROM temp_blob;
    bfile_len:=SYS.dbms_lob.getlength(locator);
    dbms_lob.loadfromfile(image1,locator,bfile_len,1,1);
    INSERT INTO internal_pdf VALUES (id,bf_desc,image1,bf_typ);
    dbms_output.put_line(bf_desc||' Length: '||TO_CHAR(bfile_len)||
    ' Name: '||bf_name||' Dir: '||bf_dir||' '||bf_typ);
    dbms_lob.fileclose(locator);
    END LOOP;
    END;

    But when I try to run this procedure, I get the below errors:
    SEVERE 949 1512 oracle.ide.controller.IdeAction Error during handleEvent on action 'Select All' (id=22). None of the attached controllers handled the action. - oracle.ide.controller.IdeAction$ControllerDelegatingController@2755b62c[oracle.dbtools.raptor.controls.grid.RaptorGridTable$1@2755b62c] - oracle.ide.controller.IdeAction$ControllerDelegatingController@ca70434[oracle.dbtools.raptor.controls.grid.RaptorGridTable$1@ca70434] - oracle.ide.controller.IdeAction$ControllerDelegatingController@2d4d8861[oracle.dbtools.raptor.controls.grid.RaptorGridTable$1@2d4d8861] - oracle.ide.controller.IdeAction$ControllerDelegatingController@629f9421[oracle.dbtools.raptor.controls.grid.RaptorGridTable$1@629f9421] - oracle.ide.controller.IdeAction$ControllerDelegatingController@68f1ba88[oracle.dbtools.raptor.controls.grid.RaptorGridTable$1@68f1ba88] - oracle.ide.controller.IdeAction$ControllerDelegatingController@5c439506[oracle.dbtools.raptor.controls.grid.RaptorGridTable$1@5c439506] - oracle.ide.controller.IdeAction$ControllerDelegatingController@1d8c106f[oracle.jdevimpl.compare.CompareFileController@1d8c106f].
    SEVERE 942 8666676 oracle.ide.controller.IdeAction Error during handleEvent on action 'Select All' (id=22). None of the attached controllers handled the action. - oracle.ide.controller.IdeAction$ControllerDelegatingController@2755b62c[oracle.dbtools.raptor.controls.grid.RaptorGridTable$1@2755b62c] - oracle.ide.controller.IdeAction$ControllerDelegatingController@ca70434[oracle.dbtools.raptor.controls.grid.RaptorGridTable$1@ca70434] - oracle.ide.controller.IdeAction$ControllerDelegatingController@2d4d8861[oracle.dbtools.raptor.controls.grid.RaptorGridTable$1@2d4d8861] - oracle.ide.controller.IdeAction$ControllerDelegatingController@629f9421[oracle.dbtools.raptor.controls.grid.RaptorGridTable$1@629f9421] - oracle.ide.controller.IdeAction$ControllerDelegatingController@68f1ba88[oracle.dbtools.raptor.controls.grid.RaptorGridTable$1@68f1ba88] - oracle.ide.controller.IdeAction$ControllerDelegatingController@5c439506[oracle.dbtools.raptor.controls.grid.RaptorGridTable$1@5c439506] - oracle.ide.controller.IdeAction$ControllerDelegatingController@1d8c106f[oracle.jdevimpl.compare.CompareFileController@1d8c106f].
    SEVERE 519 285817 oracle.dbtools.raptor.ui.URLFileChooser Relative path in absolute URI: sqldev.nav://IdeConnections%2523orcl//SYSTEM/PROCEDURE/LOAD_LOB at java.net.URI.checkPath(URI.java:1823)
    SEVERE 420 0 oracle.dbtools.raptor.ui.URLFileChooser Relative path in absolute URI: sqldev.nav://IdeConnections%2523orcl//SYSTEM/PROCEDURE/LOAD_LOB at java.net.URI.checkPath(URI.java:1823)
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    769
    Likes Received:
    148
    Trophy Points:
    830
    Location:
    Russian Federation

    Hi.
    Your error not is rdbms errors.
    I suppose,it is java-error or run-time error of the developer environment.
    Your call procedure on command window sqldev or pls/sql,or sqlplus is successful ?
     
    Last edited: Mar 6, 2018
  3. Penchal

    Penchal Newly Initiated

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    30
    Location:
    Bangalore
    Yes, Thanks Sergei. Got to know that its not rdbms error.
    When I try to run the run in it command window, I am getting "Invalid Data" error.
     
  4. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    769
    Likes Received:
    148
    Trophy Points:
    830
    Location:
    Russian Federation
    Can you provide here runned script?