Home » SQL & PL/SQL » SQL & PL/SQL » ORA-04067 clob2blob procedure error (winxp, 10g xe)
ORA-04067 clob2blob procedure error [message #337414] Wed, 30 July 2008 17:34 Go to next message
nacan
Messages: 7
Registered: July 2008
Location: cancada
Junior Member

hi?

i'd like to convert clob(contain xml type)field to blob field.

and copied some source plsql code from this site.
clob2blob procedure.

and i executed dbmslob.sql package already.

i tried blow :

DECLARE
v_clob CLOB;
v_blob BLOB;
BEGIN
SELECT layout INTO v_clob FROM ed_page_template WHERE name = 'ESSAY_LPI';
SELECT layout1 INTO v_blob FROM ed_page_template WHERE name = 'ESSAY_LPI' FOR UPDATE;
clob2blob (v_clob, v_blob);

END;


ORA-04067: can't executable, package body "LPICEL.DBMS_LOB"
dosen't exist.
ORA-06508: PL/SQL: can't find program unit being called(: "LPICEL.DBMS_LOB").
ORA-06512: "LPICEL.CLOB2BLOB", at 11 lines
ORA-06512: at 7 lines


create or replace procedure CLOB2BLOB (p_clob in out nocopy clob, p_blob in out nocopy blob) is
-- transforming CLOB a BLOB
l_off number default 1;
l_amt number default 4096;
l_offWrite number default 1;
l_amtWrite number;
l_str varchar2(4096 char);
begin
begin
loop
dbms_lob.read ( p_clob, l_amt, l_off, l_str );
l_amtWrite := utl_raw.length ( utl_raw.cast_to_raw( l_str) );
dbms_lob.write( p_blob, l_amtWrite, l_offWrite,
utl_raw.cast_to_raw( l_str ) );

l_offWrite := l_offWrite + l_amtWrite;

l_off := l_off + l_amt;
l_amt := 4096;
end loop;

exception
when no_data_found then
NULL;
end;
end;

clob field size is about 23k byte.

does the size of clob field cause this probelm??

[Updated on: Wed, 30 July 2008 17:37]

Report message to a moderator

Re: ORA-04067 clob2blob procedure error [message #337415 is a reply to message #337414] Wed, 30 July 2008 17:50 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
>does the size of clob field cause this probelm??
NO
Package/procedure ownership causes this problem.
Re: ORA-04067 clob2blob procedure error [message #337417 is a reply to message #337415] Wed, 30 July 2008 17:54 Go to previous messageGo to next message
nacan
Messages: 7
Registered: July 2008
Location: cancada
Junior Member

i always login lpicel user who has dba role.

i created dbmslob.sql and clob2blob procedure with lpicel user.

what should i check next?

i checked status procedure of the user_object table.

it is valid.
Re: ORA-04067 clob2blob procedure error [message #337419 is a reply to message #337414] Wed, 30 July 2008 18:01 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above

>what should i check next?
The error message clearly states what is the problem.
Read & THINK about what is says.

[Updated on: Wed, 30 July 2008 18:17] by Moderator

Report message to a moderator

Re: ORA-04067 clob2blob procedure error [message #337421 is a reply to message #337414] Wed, 30 July 2008 18:32 Go to previous messageGo to next message
nacan
Messages: 7
Registered: July 2008
Location: cancada
Junior Member

the dbms_lob package exist.

and right owership.

is plsql procedure name casesensitive??

what's means package body dose not exist?


and looked at clob2blob at 11 line.

11 -> dbms_lob.read ( p_clob, l_amt, l_off, l_str );
PROCEDURE read(lob_loc IN BLOB,
amount IN OUT NOCOPY INTEGER,
offset IN INTEGER,
buffer OUT RAW);

looked at 7 line
7 -> CLOB2BLOB (v_clob, v_blob);

i can't figure it out what the problem is.

[Updated on: Wed, 30 July 2008 18:33]

Report message to a moderator

Re: ORA-04067 clob2blob procedure error [message #337422 is a reply to message #337414] Wed, 30 July 2008 18:36 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
>the dbms_lob package exist.
I agree.

>and right owership.
& which ownership is that?

>ORA-04067: can't executable, package body "LPICEL.DBMS_LOB" dosen't exist.

Write a simple declarative sentence that explains what this error says.
Re: ORA-04067 clob2blob procedure error [message #337423 is a reply to message #337414] Wed, 30 July 2008 18:50 Go to previous messageGo to next message
nacan
Messages: 7
Registered: July 2008
Location: cancada
Junior Member

ownership is LPICEL

i executed the sentence.

GRANT DBA TO LPICEL;

isn't that owership of the clob2blob ?

and

i don't under stand last part of what you said.
  • Attachment: snap1.jpg
    (Size: 239.86KB, Downloaded 107 times)
Re: ORA-04067 clob2blob procedure error [message #337424 is a reply to message #337414] Wed, 30 July 2008 19:01 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
using sqlplus along with CUT & PASTE (& <code_tags>) post results from:
select owner , object_type from dba_objects where object_name = 'DBMS_LOB'

Re: ORA-04067 clob2blob procedure error [message #337427 is a reply to message #337424] Wed, 30 July 2008 19:12 Go to previous messageGo to next message
nacan
Messages: 7
Registered: July 2008
Location: cancada
Junior Member

hum....
the result is

owner object_type
SYS PACKAGE
SYS PACKAGE BODY
PUBLIC SYNONYM
LPICEL PACKAGE

LPICEL doesn't have Package body....

you are right.


who is SYS ?

but every table is created by LIPCEL user.

how come lpicel user doesn't have package body?

do i have to update SYS TO LPICEL user
for using package body ??



Re: ORA-04067 clob2blob procedure error [message #337429 is a reply to message #337414] Wed, 30 July 2008 20:09 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above

How/where/why did LPICEL DBMS_LOB PACKAGE header come from?

Based upon this whole thread. it appear you have no clear concept how Oracle manages data security.

I suggest you read the Concepts Manual before proceeding.

http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/toc.htm


Since can not or will not follow simple directions, You're On Your Own (YOYO)!

[Updated on: Wed, 30 July 2008 20:17] by Moderator

Report message to a moderator

Re: ORA-04067 clob2blob procedure error [message #337439 is a reply to message #337414] Wed, 30 July 2008 22:06 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
did you bother to GOOGLE the error number? There are plenty of posts on the web about what this means. You will find you get answers much faster when you do a little of your own research. I do not mean this comment as a criticism, only that it can take days of back and forth talking on a message board to get an answer to a problem, but if the problem is a common one, then you can usually find and answer in 10 mintues on the web by reading other peoples web pages.

This particular error is most often caused by incorrect privileges. For example, try these from sqlplus:

desc LPICEL.DBMS_LOB

select *
from dba_users
where username = 'LPICEL'
/

select owner,object_type,object_name
from dba_objects
where object_name = 'DBMS_LOB'
/

then tell us what you find. You should be able to figure out your problem after you do these two things and think about it for a bit.

Good luck, Kevin
Re: ORA-04067 clob2blob procedure error [message #337444 is a reply to message #337414] Wed, 30 July 2008 22:27 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
Kevin,
Please look up this thread for posting by me as to:
Re: ORA-04067 clob2blob procedure error [message #337424 is a reply to message #337414 ]

Some folks are clue resistant.
Others folks are clue repellent.
You be the judge.

Does ">who is SYS ?" help you decide?
Re: ORA-04067 clob2blob procedure error [message #337447 is a reply to message #337414] Wed, 30 July 2008 22:39 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
hahaha! everytime I see you on the boards you make me laugh. Thanks man.
Re: ORA-04067 clob2blob procedure error [message #337470 is a reply to message #337427] Thu, 31 July 2008 00:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
who is SYS ?

Database Concepts

Regards
Michel
Re: ORA-04067 clob2blob procedure error [message #337479 is a reply to message #337414] Thu, 31 July 2008 01:04 Go to previous messageGo to next message
nacan
Messages: 7
Registered: July 2008
Location: cancada
Junior Member

isn't it good to made you laugh during busy daily routine.
Sad
Re: ORA-04067 clob2blob procedure error [message #337564 is a reply to message #337479] Thu, 31 July 2008 04:40 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
DBMS_LOB is a package that comes with the database. It it owned by the user SYS and is almost always installed as part of database creation.
You don't need to create a new version of it in your own schema.

If you don't know much about the SYS user, then we can just leave it at that - don't try to connect as SYS, just accept that it exists and owns things.

Drop the DBMS_LOB package header in your schema and (assuming that you've got EXECUTE privs on DBMS LOB, which you should have) everything should work ok.
Re: ORA-04067 clob2blob procedure error [message #337577 is a reply to message #337414] Thu, 31 July 2008 05:20 Go to previous message
nacan
Messages: 7
Registered: July 2008
Location: cancada
Junior Member

really thanks.
your article give me a big help.

actually, i'm not good at sql/ plsql and oracle stuff.

i knew specified user has all priviledge about all works.

i'm just developer application.

i took over sources that access to oracle database.

but i have to to that.

even if it is database, php, xml, VB, delphi or etc.

my action might be appeared inpolite by another expert.

but i'm in a hurry.

so many people and works push on me in every case of works.

Previous Topic: ORA-06502:number or value error:character string buffer too small(13327)
Next Topic: How to track DML operations
Goto Forum:
  


Current Time: Sat Dec 03 00:58:23 CST 2016

Total time taken to generate the page: 0.26352 seconds