How to insert picture field into table?

Question

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 21-Jan-13 21:03pm

surkhi

Add a Solution

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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Sudhakar Shinde · Answer 1 · 2013-03-18T21:55:00

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;
/