cannot dsiplay bfile data-URGENT

From: Ravindra <ravindra_at_navin.com>
Date: Thu, 09 May 2002 15:23:22 -0800
Message-ID: <F001.0045DCE5.20020509152322@fatcity.com>

 

I am not able to display the bmp stored in the BFILE.I followed the following steps.I have been following the NOTE from metalink and the oracle app dev guide.  

CREATE TABLE empbfiles (id NUMBER PRIMARY KEY, empname VARCHAR2(20),

                           photo BFILE);

   /
   CREATE OR REPLACE DIRECTORY empbfiles AS '/u02/ora';
/

INSERT INTO empbfiles VALUES (1, 'Smith',bfilename('EMPBFILES', 'smith.bmp'));
/
 

select count(*) from empbfiles where id=1;
/

  COUNT(*)

---

         1
I am using the following procedure(got this from the oracle app devlopers guide) to display the bfile data  

CREATE OR REPLACE PROCEDURE displayBFILE_proc IS Lob_loc BFILE;
Buffer RAW(1024);
Amount BINARY_INTEGER := 1024;
Position INTEGER := 1;
BEGIN
/* Select the LOB: */

SELECT photo INTO Lob_loc
FROM empbfiles WHERE id = 1;
/* Opening the BFILE: */

DBMS_LOB.OPEN (Lob_loc, DBMS_LOB.LOB_READONLY); LOOP
DBMS_LOB.READ (Lob_loc, Amount, Position, Buffer);
/* Display the buffer contents: */

DBMS_OUTPUT.PUT_LINE(utl_raw.cast_to_varchar2(Buffer)); Position := Position + Amount;
END LOOP;
/* Closing the BFILE: */

DBMS_LOB.CLOSE (Lob_loc);
END;   when I execute the above procedure I am getting the following error  

ERROR at line 1:

ORA-01403: no data found
ORA-06512: at "SYS.DBMS_LOB", line 656
ORA-06512: at "HHNEWBUILD.DISPLAYBFILE_PROC", line 13
ORA-06512: at line 1

What am i missing or doing wrong.

Ravi  

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Ravindra
  INET: ravindra_at_navin.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Thu May 09 2002 - 18:23:22 CDT