copying long raw column to blob column

From: <patp_at_pswtech.com>
Date: Wed, 09 Dec 1998 15:05:43 GMT
Message-ID: <74m3k5$5h4$1@nnrp1.dejanews.com>

I'm trying to a database conversion from an old schema to a new schema. One of the changes that were made was changing a long raw datatype to a blob. I have tried doing this using the dbms_lob.copy, dbms_lob.read and dbms_lob.write. I have RTFM and this like it should be possible. Here is my attempted run using the dbms_lob.copy

  1 DECLARE
  2

  3      lobd     BLOB;
  4      lobs     BLOB;
  5      amt      INTEGER := 1024;

  6
  7 cursor c_dl_data is
  8 select a.drivers_lic_no,a.dln_state_code,a.customer_id   9 from bac2.customer a, bac2.finger_print_data b  10 where a.customer_id=b.customer_id;  11
 12 dl_rec c_dl_data%ROWTYPE;
 13
 14
 15 BEGIN

 16     for dl_rec in c_dl_data loop
 17        EXIT WHEN c_dl_data%NOTFOUND;
 18             SELECT finger_print_data INTO lobd
 19             FROM bac.finger_print_data
 20             WHERE id_num = dl_rec.drivers_lic_no
 21             AND   id_state = dl_rec.dln_state_code
 22             FOR UPDATE;
 23 /*  fp_data is the long_raw datatype i'm trying to read in. */
 24             SELECT fp_data INTO lobs
 25             FROM bac2.finger_print_data
 26             WHERE customer_id = dl_rec.customer_id;
 27             DBMS_LOB.COPY(lobd, lobs, amt);
 28             COMMIT;
 29      end loop;

 30* END; /* End of Program */
SQL> /

        SELECT finger_print_data INTO lobd
                                      *

ERROR at line 18:

ORA-06550: line 18, column 36:
PLS-00385: type mismatch found at 'LOBD' in SELECT...INTO statement
ORA-06550: line 18, column 6:

PL/SQL: SQL Statement ignored

ORA-06550: line 23, column 26:
PLS-00385: type mismatch found at 'LOBS' in SELECT...INTO statement
ORA-06550: line 23, column 6:

PL/SQL: SQL Statement ignored

Thanks in advance

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Wed Dec 09 1998 - 09:05:43 CST