--
-- pl_sql_copy_blob.txt
--

-- Demo of using PL/SQL to copy a BLOB datatype.
-- I prefer to use exp/imp to copy BLOBs around but PL/SQL can do it.

-- 12:11:03 SQL> desc message
--  Name                       Null?    Type
--  -------------------------- -------- ------------------
--  MESSAGEID                  NOT NULL NUMBER(38)
--  MESSAGE                             BLOB
--

-- I like to add schema names to db objects as some protection
-- against corrupting good data.

CREATE OR REPLACE PROCEDURE bikle.tstblob
AS
  sql_stmt  VARCHAR2(3999);
  CURSOR cursor1 IS
    SELECT
      MESSAGEID
      ,MESSAGE
   FROM source.message;
BEGIN
  sql_stmt := 'INSERT INTO bikle.message
    (
      MESSAGEID
      ,MESSAGE
    )
    VALUES
    (
      :1
      ,:2
    )';

  FOR aRecord IN cursor1 LOOP
    EXECUTE IMMEDIATE sql_stmt USING
      aRecord.MESSAGEID
      ,aRecord.MESSAGE;
  END LOOP;
END;
/

-- Now, do it.
SELECT COUNT(*) from bikle.message;
exec bikle.tstblob
SELECT COUNT(*) from bikle.message;
-- ROLLBACK;
-- COMMIT;