0

I want to insert Multiple BLOB file at once ​​in "USER_PROFILE" table using loop by procedure/function. Working on Oracle DB using SQLdeveloper

  • BLOB Files:

    1. user.resetpassword.email.body.html
    2. password.expiry.notification.email.html
    3. password.expiry.notification.subject.txt
    4. user.resetpassword.email.subject.txt

  • BLOB FILE DIRECTORY : U_PROFILE

  • BLOB column name : PROP_VALUE

  • Unique key : PROP_KEY (This column naming conventions will be same with BLOB file names)

    CREATE TABLE "USER_PROFILE" ( "USER_PROFILE_PID" VARCHAR2(40 BYTE) , "PROP_KEY" VARCHAR2(100 BYTE) , "PROP_VALUE" BLOB , "MODIFIED_DTS" DATE , "BUILD_VERSION" VARCHAR2(100 BYTE) , "DESCRIPTION" VARCHAR2(4000 BYTE) )

    ​;

    INSERT INTO user_profile ( user_profile_pid , prop_key , modified_dts , build_version , description ) VALUES ( 'CTP-1000' , 'password.expiry.notification.email' , NULL , '1.2' , 'User Account Expiry notification' );

    INSERT INTO user_profile ( user_profile_pid , prop_key , modified_dts , build_version , description ) VALUES ( 'CTP-1001' , 'password.expiry.notification.subject' , NULL , '1.2' , 'User Account Expiry notification subject' );

thatjeffsmith
  • 20,522
  • 6
  • 37
  • 120
Shanny
  • 1
  • 1
  • See this https://stackoverflow.com/questions/7489359/how-can-i-insert-into-a-blob-column-from-an-insert-statement-in-sqldeveloper - otherwise you will want to look at something like sqlldr to batch load blobs to a table – thatjeffsmith Jun 08 '20 at 13:01
  • Shared answer is a different prospect. I want to insert multiple files at one using loop. – Shanny Jun 08 '20 at 13:35
  • yet my suggestion stands...there's no, 'insert' statement that will take a file on the client and insert it as a blob on the database – thatjeffsmith Jun 08 '20 at 13:36
  • I have heard about use program "DBMS_LOB.loadfromfile" . – Shanny Jun 08 '20 at 14:07
  • yes, and here's an example - https://oracle-base.com/articles/8i/import-blob - note that the blob files will need to already be on the server – thatjeffsmith Jun 08 '20 at 14:18
  • I am able to insert with query, but I need to insert multiple files using file name.Can you help me for that, – Shanny Jun 15 '20 at 03:56
  • shanny - add a column to track file names and update as needed, that's the most reliable way of handling files in the db – thatjeffsmith Jun 15 '20 at 17:24

0 Answers0