OraFAQ Forum: SQL & PL/SQL » finding size of remote blobs

Home » SQL & PL/SQL » SQL & PL/SQL » finding size of remote blobs (oracle 10)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

finding size of remote blobs [message #318323]

Tue, 06 May 2008 08:50

DeeDee
Messages: 14
Registered: March 2008

Junior Member

Hi,
Since you guys answered my previous questions so.. um competently (is that a word?) I hop you can help me with this one as well: How do I find the size of a blob in a remote DB connected via a DBLink?

Getting the size for a local blob is no problem, using DBMS_LOB.getLenght(), but when I try to use this with remote blobs I get the "remote lob locator" error..

Thanks for your help in advance!!

DeeDee

Report message to a moderator

Re: finding size of remote blobs [message #318329 is a reply to message #318323]

Tue, 06 May 2008 08:56

MarcS
Messages: 312
Registered: March 2007
Location: Antwerp

Senior Member

DeeDee wrote on Tue, 06 May 2008 15:50

If you have access to the remote database or better said:if you have privileges to add stored packages/functions/procedures you could write a pl/sql function which would call DBMS_Lob.GetLength() on the local object.
Then you just have to call the function remotely.

HTH
Marc

Report message to a moderator

Re: finding size of remote blobs [message #318331 is a reply to message #318323]

Tue, 06 May 2008 08:58

Frank Naude
Messages: 4581
Registered: April 1998

Senior Member

Write a function on the remote database to find and return the blob's size. You can then call the function through the DB link.

Report message to a moderator

Re: finding size of remote blobs [message #318333 is a reply to message #318331]

Tue, 06 May 2008 09:02

DeeDee
Messages: 14
Registered: March 2008

Junior Member

ok, thanks, that makes sense!..
I do have access to the remote DB, so I could do that even though I'd rather not, since I need the sizes just to test whether a migration worked correctly.. Is this the only way of getting the sizes?
I'll ask my boss if he thinks its worth it.. and I'm open for any other suggestion!

Thanks for the answers so far!

Report message to a moderator

Re: finding size of remote blobs [message #318334 is a reply to message #318333]

Tue, 06 May 2008 09:04

MarcS
Messages: 312
Registered: March 2007
Location: Antwerp

Senior Member

DeeDee wrote on Tue, 06 May 2008 16:02

Well, create the function before you start your report and drop it afterwards.

I'm pretty sure that's the only way to get the information you need.
Unless you run your report against the remote database instead?

Report message to a moderator

Re: finding size of remote blobs [message #318335 is a reply to message #318334]

Tue, 06 May 2008 09:08

DeeDee
Messages: 14
Registered: March 2008

Junior Member

well, what I do is I compare the sizes of the blobs.. if I run it from the other db I'll have the same problem again to access the now local data Smile

Thanks for the help!

Report message to a moderator

Re: finding size of remote blobs [message #318364 is a reply to message #318323]

Tue, 06 May 2008 10:19

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>I'll have the same problem again to access the now local data
Not if you do it right & "plan ahead".
You get the size of the "local" blob & pass it into the remote function.
The remote function obtains the size of the blob now local to it & compares the results to what was passed into it.
The remote function returns either 'MATCH' or 'NOMATCH'.
Why make the problem harder than it really is?

Report message to a moderator

Previous Topic:	delete
Next Topic:	sql query to check if passed value is number or character

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Dec 03 17:57:42 CST 2024