Home » SQL & PL/SQL » SQL & PL/SQL » finding size of remote blobs (oracle 10)
finding size of remote blobs [message #318323] Tue, 06 May 2008 08:50 Go to next message
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
Re: finding size of remote blobs [message #318329 is a reply to message #318323] Tue, 06 May 2008 08:56 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
DeeDee wrote on Tue, 06 May 2008 15:50
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


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
Re: finding size of remote blobs [message #318331 is a reply to message #318323] Tue, 06 May 2008 08:58 Go to previous messageGo to next message
Frank Naude
Messages: 4502
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.
Re: finding size of remote blobs [message #318333 is a reply to message #318331] Tue, 06 May 2008 09:02 Go to previous messageGo to next message
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!
Re: finding size of remote blobs [message #318334 is a reply to message #318333] Tue, 06 May 2008 09:04 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
DeeDee wrote on Tue, 06 May 2008 16:02
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!


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?
Re: finding size of remote blobs [message #318335 is a reply to message #318334] Tue, 06 May 2008 09:08 Go to previous messageGo to next message
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!
Re: finding size of remote blobs [message #318364 is a reply to message #318323] Tue, 06 May 2008 10:19 Go to previous message
BlackSwan
Messages: 25036
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?
Previous Topic: delete
Next Topic: sql query to check if passed value is number or character
Goto Forum:
  


Current Time: Mon Dec 05 02:37:35 CST 2016

Total time taken to generate the page: 0.15300 seconds