hi all,
i have a tablewhose structure is as given below.
desc demo_lob
Name Null? Type
----------------------------------------- -------- ------------------
KEY NOT NULL NUMBER
BLOB_COL BLOB
CLOB_COL CLOB
BFILE_COL BINARY FILE LOB
---------------------------------------------------------------------
Now after inserting a row through the following cmd
-----------
insert into demo_lob
values(1,empty_blob(),empty_clob(),bfilename('andy_dir',test.txt));
1 row inserted
------------------------
now supoose i check the file length
create or replace procedure testbfile(v_key number) as
v_loc BFILE;
v_len INTEGER;
begin
select bfile_col into v_loc from demo_lob where key=v_key;
v_len := dbms_lob.GETLENGTH(v_loc);
dbms_output.put_line(v_len);
end;
/
execute testbfile(1)
556
PL/SQL successfully completed
this is returning the actual file
------------
the length given here is the the actual size of the file that i have stored i the bfile locator
however supoosing i want to copy the data from the bfile to the clob_col which iam doing thru the following code
--
CREATE OR REPLACE PROCEDURE loadBFILE_proc(v_key number) IS
Dest_loc CLOB;
Src_loc BFILE := BFILENAME('ANDY_DIR', 'test.txt');
Amount INTEGER:=DBMS_LOB.GETLENGTH(Src_loc);
BEGIN
dbms_output.put_line(Amount);
SELECT clob_col INTO Dest_loc FROM demo_lob WHERE key = v_key FOR UPDATE;
/* Opening the LOB */
DBMS_LOB.OPEN(Src_loc, DBMS_LOB.LOB_READONLY);
DBMS_LOB.LOADFROMFILE(Dest_loc, Src_loc, Amount);
/* Closing the LOB is mandatory if you have opened it: */
DBMS_LOB.CLOSE(Src_loc);
COMMIT;
END;
/
--------------
execute loadBFILE_proc(1);
544
PL/SQL procedure successfully completed.
Now when I do
select clob_col() from test_lob wherekey=1;
the data that it is returning is not the whole data that is stored in the file , though pls mind the length it si showing is the actual file length what i am supposed to do?????
kindly help.
