Home » SQL & PL/SQL » SQL & PL/SQL » INSERT BLOB IN ORACLE 10G
icon9.gif  INSERT BLOB IN ORACLE 10G [message #220254] Mon, 19 February 2007 22:09 Go to next message
tico
Messages: 7
Registered: February 2007
Location: MEXICO
Junior Member
Im triyig to insert a .jpg into a blob colum in a bd oracle 10g
it with the finaly to will see it on a report readin de table and showing the image, but the problem is that i can't load the image into the table, because the next pl/sql send me an error in the dbms_lob package, and i dont know wy.
If you know other form to show an image on the report, this image will live on a server unix thats the problem and the reason that's wy im trying to do this.
Or if you know how can i load the image on oracle10g
the image lives on the server unix

the pl is there

DECLARE
l_bfile BFILE;
l_blob BLOB;
file_r BINARY_INTEGER;
BEGIN
dbms_output.put_line('inicio'||' '||sqlerrm);
INSERT INTO GIS_LOGOS VALUES ( 'id1', 'lalo.gif', empty_blob(), null)
RETURNING logo into l_blob;
dbms_output.put_line('inserto'||' '||sqlerrm);
l_bfile := BFILENAME('/usr/local/oracle/gold/apps/goldappl/xxgis/11.5.0/load/ar/comesco', 'lalo.gif');
dbms_output.put_line('enruta'||' '||sqlerrm);
DBMS_LOB.CREATETEMPORARY(l_blob,true);
DBMS_LOB.open(l_bfile, file_r);
dbms_output.put_line('abre'||' '||sqlerrm);
DBMS_LOB.loadfromfile(l_blob,l_bfile,DBMS_LOB.getlength(l_bfile));
dbms_output.put_line('lee'||' '||sqlerrm);
DBMS_LOB.fileclose(l_bfile);
dbms_output.put_line('cierra'||' '||sqlerrm);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
RAISE;
END;

Report message to a moderator

Re: INSERT BLOB IN ORACLE 10G [message #220274 is a reply to message #220254] Tue, 20 February 2007 00:14 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
remove your exception block, since it does nothing but obscure the linenumber where the error occurred.
Then execute it again and copy-paste the outcome here

Report message to a moderator

Re: INSERT BLOB IN ORACLE 10G [message #220388 is a reply to message #220274] Tue, 20 February 2007 08:59 Go to previous messageGo to next message
tico
Messages: 7
Registered: February 2007
Location: MEXICO
Junior Member
here it without the exception and send the same error wathever line respect de dbms_lob

DECLARE
l_bfile BFILE;
l_blob BLOB;
file_r BINARY_INTEGER;
BEGIN
dbms_output.put_line('inicio'||' '||sqlerrm);
INSERT INTO GIS_LOGOS VALUES ( 'id1', 'lalo.gif', empty_blob(), null)
RETURNING logo into l_blob;
dbms_output.put_line('inserto'||' '||sqlerrm);
l_bfile := BFILENAME('/usr/local/oracle/gold/apps/goldappl/xxgis/11.5.0/load/ar/comesco', 'lalo.gif');
dbms_output.put_line('enruta'||' '||sqlerrm);
DBMS_LOB.open(l_bfile, file_r);
dbms_output.put_line('abre'||' '||sqlerrm);
DBMS_LOB.loadfromfile(l_blob,l_bfile,DBMS_LOB.getlength(l_bfile));
dbms_output.put_line('lee'||' '||sqlerrm);
DBMS_LOB.fileclose(l_bfile);
dbms_output.put_line('cierra'||' '||sqlerrm);
COMMIT;
END;
/

regards

Report message to a moderator

Re: INSERT BLOB IN ORACLE 10G [message #220393 is a reply to message #220254] Tue, 20 February 2007 09:12 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
Umm

I think Frank wanted you to actually execute the code and then post the error message here.

Report message to a moderator

Re: INSERT BLOB IN ORACLE 10G [message #220395 is a reply to message #220393] Tue, 20 February 2007 09:23 Go to previous messageGo to next message
tico
Messages: 7
Registered: February 2007
Location: MEXICO
Junior Member
SQL>
SQL>
SQL> DECLARE
2 l_bfile BFILE;
3 l_blob BLOB;
4 file_r BINARY_INTEGER;
5 BEGIN
6 dbms_output.put_line('inicio'||' '||sqlerrm);
7 INSERT INTO GIS_LOGOS VALUES ( 'id1', 'lalo.gif', empty_blob(), null)
8 RETURNING logo into l_blob;
9 dbms_output.put_line('inserto'||' '||sqlerrm);
10 l_bfile := BFILENAME('/usr/local/oracle/gold/apps/goldappl/xxgis/11.5.0/load/ar/comesco', 'lalo
.gif');
11 dbms_output.put_line('enruta'||' '||sqlerrm);
12 DBMS_LOB.open(l_bfile, file_r);
13 dbms_output.put_line('abre'||' '||sqlerrm);
14 DBMS_LOB.loadfromfile(l_blob,l_bfile,DBMS_LOB.getlength(l_bfile));
15 dbms_output.put_line('lee'||' '||sqlerrm);
16 DBMS_LOB.fileclose(l_bfile);
17 dbms_output.put_line('cierra'||' '||sqlerrm);
18 COMMIT;
19 END;
20 /
DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: invalid LOB locator specified: ORA-22275
ORA-06512: at "SYS.DBMS_LOB", line 672
ORA-06512: at line 12

Report message to a moderator

Re: INSERT BLOB IN ORACLE 10G [message #220512 is a reply to message #220395] Wed, 21 February 2007 00:57 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
file_r is not assigned a value. This is not a valid open_mode for dbms_lob.open.
Now you see how valuable it is to know the exact linenumber where the error occurred, so don't use nonsense exception handlers anymore!

Report message to a moderator

Re: INSERT BLOB IN ORACLE 10G [message #220734 is a reply to message #220512] Wed, 21 February 2007 17:01 Go to previous messageGo to next message
tico
Messages: 7
Registered: February 2007
Location: MEXICO
Junior Member
i ran the pl with this change and send me this error
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01460: unimplemented or unreasonable conversion requested
ORA-06512: at "SYS.DBMS_LOB", line 672
ORA-06512: at line 12

what does it mean?
Regards

Report message to a moderator

Re: INSERT BLOB IN ORACLE 10G [message #220770 is a reply to message #220734] Thu, 22 February 2007 00:27 Go to previous message
Frank
Messages: 7901
Registered: March 2000
Senior Member
tico wrote on Thu, 22 February 2007 00:01
i ran the pl with this change and send me this error


With what change?

Report message to a moderator

Previous Topic: query performance
Next Topic: Calling stored procedure from excel
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Tue Sep 02 05:28:19 CDT 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.