Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: copying long raw column to blob column

Re: copying long raw column to blob column

From: <mgogala_at_rocketmail.com>
Date: Wed, 09 Dec 1998 17:20:59 GMT
Message-ID: <74mbho$d0u$1@nnrp1.dejanews.com>


In article <74m3k5$5h4$1_at_nnrp1.dejanews.com>,   patp_at_pswtech.com wrote:
> 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
>

No go! Several things:
a) BLOB you are trying to use here is JUST A LOCATOR, not actual data

   itself. If you do select ... into blob ... for update, then you can    use dbms_lob.read and dbms_lob.write to access the actual data. b) There is no provision within PL/SQL for handling LONG columns.

   You must write a program (in C/C++) to do that. You'll retrieve data    by using piecewise fetch and as you retrieve it, write it to LOB by using    OCILobWrite or soemthing similar.

--
Mladen Gogala

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US