Click here to Skip to main content
15,921,622 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
<br />
sql>CREATE TABLE lob_table (id NUMBER, doc BLOB);<br />
<br />
Table created<br />
sql>INSERT INTO lob_table VALUES (1, EMPTY_BLOB());<br />
<br />
1 row created<br />
<br />
sql>DECLARE<br />
2  src_lob  BFILE := BFILENAME('image_dir', 'D:\image_dir\image1.jpeg');<br />
3  dest_lob BLOB;<br />
4  BEGIN<br />
5  INSERT INTO lob_table VALUES(2, EMPTY_BLOB())<br />
6     RETURNING doc INTO dest_lob;<br />
7  DBMS_LOB.OPEN(src_lob, DBMS_LOB.LOB_READONLY);<br />
8  DBMS_LOB.LoadFromFile( DEST_LOB => dest_lob,<br />
9                         SRC_LOB  => src_lob,<br />
10                         AMOUNT   => DBMS_LOB.GETLENGTH(src_lob) );<br />
11  DBMS_LOB.CLOSE(src_lob);<br />
12  COMMIT;<br />
13  END;<br />
14  /<br />
<br />
<br />
dbms_lob.close(src_lob);<br />
*<br />
ERROR at line 11:<br />
ORA-06550: line 11, column 1:<br />
PLS-00103: Encountered the symbol "DBMS_LOB" when expecting one of the<br />
following:<br />
:= . ( % ;<br />



This kind of error occurred in procedure
Please help
Posted
Comments
Kishor Deshpande 22-Jan-13 3:06am    
Are you using any front end to insert it? I mean Java or .NET?
surkhi 22-Jan-13 4:13am    
YaIt's VB6...........
I know it's really older but my project requirement is same....
Is there any easier way to insert pictures using blob??????????

1 solution

Below is the code to insert an image into the table.
Create a table to store the blob.
SQL
CREATE TABLE tabblobs
( 	id varchar2(255),
	blob_col blob
);

Create a logical directory in the database.
SQL
CREATE OR REPLACE directory MY_FILES AS 'd:\images';

Create a procedure to load the blob from the physical disk using the logical
directory. The gif "ORATEST.gif" must exist in d:\images.
SQL
CREATE OR REPLACE procedure insert_img AS
flob bfile;
blob blob;
BEGIN
	INSERT INTO tabblobs VALUES ( 'MyGif', empty_blob() )
	RETURN blob_col INTO blob;

	flob := bfilename( 'MY_FILES', 'ORATEST.gif' );
	dbms_lob.fileopen(flob, dbms_lob.file_readonly);
	dbms_lob.loadfromfile( blob, flob, dbms_lob.getlength(flob) );
	dbms_lob.fileclose(flob);
	COMMIT;
END;
/
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900