Home » SQL & PL/SQL » SQL & PL/SQL » how to check blob..... (9i)
how to check blob..... [message #342010] Thu, 21 August 2008 01:13 Go to next message
singh.neerajin
Messages: 26
Registered: April 2008
Location: noida
Junior Member

Hi all,

illust table with alias A and B are on different schema.

illust B table has a blob column ILLUSIMAGE and i want to write a query to check the data consistency.

but we cannt check blob (WHERE B.ILLUSIMAGE IS NULL;) through dblink.

can anyone suggest me how to use check blob on other schema.

please help me its urgent.

SELECT a.illusid,A.ILLUSNAME FROM ILLUST A
INNER JOIN
ILLUST@epc_target B ON A.ILLUSID=B.ILLUSID WHERE B.ILLUSIMAGE IS NULL;


thanks
neeraj

[Updated on: Thu, 21 August 2008 01:16]

Report message to a moderator

Re: how to check blob..... [message #431257 is a reply to message #342010] Mon, 16 November 2009 06:20 Go to previous message
sajith741
Messages: 11
Registered: November 2009
Location: Singapore
Junior Member
You can only insert across the DB Link for LOBs; Select will give rise to ORA-22992: cannot use LOB locators selected from remote tables. The workaround is to avoid the same through SQL - You could try with Java code as shown below:
import java.sql.*;
import java.io.*;
import oracle.sql.*;

public class ....
{
public static Blob getBlob
{
Blob srcbloc;
DriverManager.......;
Connection conn_src = ...........
Statement stmt = ...
ResultSet rs_src = stmt.executeQuery("
Select pic from tab_pics where id = 1");
srcblob = ((OracleResultSet)rs_src).getBLOB(1);

return srcBlob;
}
public static void loadBlob throws exception
{
DriverManager.......;
Connection conn_dest = ...........;
OraclePreparedStatement stmt = (OraclePreparedStatement) 
conn.prepareStatement("insert into tab_pic values(1, ?)");
stmt.setBlob(1, getBlob);
ops.execute();
conn_dest.commit();
}
}



Good luck!

Cheers,
Sajith

[Updated on: Mon, 16 November 2009 06:39] by Moderator

Report message to a moderator

Previous Topic: How to Execute a procedure
Next Topic: PLSQL_OPTIMIZE_LEVEL
Goto Forum:
  


Current Time: Sat Feb 15 07:53:25 CST 2025