| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: copying long raw column to blob column
In article <74mbho$d0u$1_at_nnrp1.dejanews.com>,
mgogala_at_rocketmail.com wrote:
> 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.
>
>
> -----------== Posted via Deja News, The Discussion Network ==----------
> http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
> Mladen,
Thanks for the info. I have tried the dbms_lob.read and write also with no luck either. I guess I have a couple of questions about the parameters for the read and write.
pos - I understand this is the starting position, is this a zero or one by default? If not how do I get this number?
amount - I assume this is the total size of the long raw which is 1024 in my case. If not how do I get this number?
The last question is how to define lobs and the lobd that you are reading and writing to, I assumed that these were blobs based on the example in the Oracle Documentation. I have posted my script below.
1 DECLARE
2
3 lobd BLOB; 4 lobs BLOB; 5 buffer long raw(1024); 6 pos INTEGER := 0; 7 amt binary_INTEGER := 1024;8
18 for dl_rec in c_dl_data loop 19 EXIT WHEN c_dl_data%NOTFOUND; 20 SELECT fp_data INTO lobs 21 FROM bac2.finger_print_data 22 WHERE customer_id = dl_rec.customer_id; 23 24 DBMS_LOB.READ(lobs, amt, pos, buffer);25
27 SELECT finger_print_data INTO lobd 28 FROM bac.finger_print_data 29 WHERE id_num = dl_rec.drivers_lic_no 30 AND id_state = dl_rec.dln_state_code 31 for update; 32 33 DBMS_LOB.WRITE(lobd, amt, pos, buffer); 34 35 COMMIT; 36 end loop;
SELECT fp_data INTO lobs
*
ORA-06550: line 20, column 29: PLS-00385: type mismatch found at 'LOBS' in SELECT...INTO statement ORA-06550: line 20, column 9:
ORA-06550: line 27, column 36: PLS-00385: type mismatch found at 'LOBD' in SELECT...INTO statement ORA-06550: line 27, column 6:
-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Wed Dec 09 1998 - 13:23:26 CST
 |
 |