Insert file content into oracle table

create or replace
PACKAGE BODY     ORI_POST_FILE AS
    PROCEDURE LOAD_FILE_CONTENT (I_REPORT_TYPE_ID NUMBER , I_FILE_NAME VARCHAR2)
    AS
      src_loc bfile:= bfilename(‘FLAT_MOD_ORI_INCOMING_LOC’,LOAD_FILE_CONTENT.I_FILE_NAME);
      dest_loc BLOB;
      var_seq_id NUMBER;
      var_STATUS_ID NUMBER :=200;
      var_USER_ID NUMBER :=1;
      var_REPORT_SEQUENCE_ID number;
      var_file_size NUMBER :=0;
      begin
        –select REPORT_SEQUENCE_SEQID_SEQ.nextval into var_REPORT_SEQUENCE_ID from dual;
        INSERT INTO REPORT_SEQUENCE(
            , REPORT_TYPE_ID
            , CREATED_TS
            , CREATED_USER_ID
            , STATUS_ID)
        VALUES (I_REPORT_TYPE_ID
        , CURRENT_TIMESTAMP
        , var_USER_ID
        , var_STATUS_ID) returning REPORT_SEQUENCE_ID into var_REPORT_SEQUENCE_ID;

 
       UPDATE ORI.REPORT_SEQUENCE
   SET REPORT_URL = XMLTYPE(‘<Links>
           <Link>/site/generatereport?reportpostid=’|| var_REPORT_SEQUENCE_ID ||'</Link>
         </Links>’)
    WHERE REPORT_SEQUENCE_ID = var_REPORT_SEQUENCE_ID;
  
      dbms_lob.open(src_loc,DBMS_LOB.LOB_READONLY);
      var_file_size := DBMS_LOB.getLength(src_loc);

      –insert into blob_test values(10,empty_blob()) returning docs into dest_loc;
      insert into ORD.report_post (report_sequence_id, file_name, file_size,file_content) values (var_REPORT_SEQUENCE_ID,LOAD_FILE_CONTENT.I_FILE_NAME,var_file_size,empty_blob() )  returning file_content into dest_loc;

      DBMS_LOB.OPEN(dest_loc, DBMS_LOB.LOB_READWRITE);
      DBMS_LOB.LOADFROMFILE(
      dest_lob => dest_loc
      ,src_lob => src_loc
      ,amount => DBMS_LOB.getLength(src_loc)
     );
      DBMS_LOB.CLOSE(dest_loc);
      DBMS_LOB.CLOSE(src_loc);
     — Update report_post set file_size = amount where REPORT_SEQUENCE_ID = var_report_sequence_id;
      COMMIT;
    end;
END;

Leave a Reply

Your email address will not be published. Required fields are marked *