LOBs & DBLink - Page 2
DBAsupport.com Forums - Powered by vBulletin
Page 2 of 2 FirstFirst 12
Results 11 to 18 of 18

Thread: LOBs & DBLink

  1. #11
    Join Date
    Apr 2006
    Posts
    377
    The problem comes into play when you try to select the actual LOB locator remotely, however straight INSERTs aren't a problem.

    Code:
    SQL> desc test_clob1
     Name                                      Null?    Type
     ----------------------------------------- -------- -----------------------
     A1                                                 NUMBER
     A2                                                 CLOB
    
    
    SQL> desc test_clob2@remotedb
     Name                                      Null?    Type
     ----------------------------------------- -------- -----------------------
     A1                                                 NUMBER
     A2                                                 CLOB
    
    
    SQL> declare
      2     big_text varchar2(32767) := rpad('#',32767,'#');
      3  begin
      4       insert into test_clob1 values (1, big_text );
      5  end;
      6  /
    
    PL/SQL procedure successfully completed.
    
    SQL> select a1, dbms_lob.getlength(a2) from test_clob1;
    
            A1 DBMS_LOB.GETLENGTH(A2)
    ---------- ----------------------
             1                  32767
    
    SQL> select count(*) from test_clob2@remotedb;
    
      COUNT(*)
    ----------
             0
    
    SQL> insert into test_clob2@remotedb
      2  select * from test_clob1;
    
    1 row created.
    
    SQL> select a1, dbms_lob.getlength(a2) from test_clob2@remotedb;
    select a1, dbms_lob.getlength(a2) from test_clob2@remotedb
                                  *
    ERROR at line 1:
    ORA-22992: cannot use LOB locators selected from remote tables
    
    
    SQL> commit;
    
    Commit complete.
    However, if I do a COMMIT and then go to the remote database and do the same query, I get the following:

    Code:
    SQL> select a1, dbms_lob.getlength(a2) from test_clob2;
    
            A1 DBMS_LOB.GETLENGTH(A2)
    ---------- ----------------------
             1                  32767

  2. #12
    Join Date
    Mar 2004
    Location
    DC,USA
    Posts
    650
    Quote Originally Posted by tvrdileep
    Hai Basan.
    I only see Transparent Gateways for DB2, MS-SQL Server etc.

    Please let me know more about this.
    You are right.

    Quote Originally Posted by tvrdileep
    Also, How to check whether my database is using Generic Connectivity or not?
    You can see HS folder under Oracle_home and in tnsnames file service name for tg4msql (for ms sqlserver) listed with HS = ok.

    Quote Originally Posted by tvrdileep
    Also, you said that you are able to access LOBS using PL/SQL, TRIGGERS etc using DBLinks. Are you able to use direct SQL also to SELECT data from remote Database using DBLink?
    No, only through procedures and triggers.
    "What is past is PROLOGUE"

  3. #13
    Join Date
    Oct 2001
    Location
    Doha, Qatar
    Posts
    103
    Hai Everybody.
    I came to know from above discussions that we can only INSERT into LOB Column of remote database table using DBLink. SELECT is not possible using DBLink.

    I would like to what is the workaround for this? I want to do both SELECT and INSERT into remote database using DBLink. I mean if its possible by using JAVA/PL SQL, can anyone tell me what is the procedure?

    regards,
    Dileep Tallam.

  4. #14
    Join Date
    Sep 2005
    Posts
    278
    Code:
    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();
    	}
    }
    You can access this two procedures even from PL/SQL by publishing it

  5. #15
    Join Date
    Oct 2001
    Location
    Doha, Qatar
    Posts
    103
    Hai Tabreaz.
    Thanks for your info. One of our development guy said that he could neither SELECT/INSERT data from/into LOB column of a remote database table using DBLink. Could you tell me if its possible by using DBLink?

  6. #16
    Join Date
    Sep 2005
    Posts
    278
    Try it out using PL/SQL or Java Stored procedure, no resources for testing such.

    If you face any problem, then someone for sure help you in specific area.

  7. #17
    Join Date
    Oct 2001
    Location
    Doha, Qatar
    Posts
    103
    Hai.
    I have read that if one uses JDBC OCI Drivers then we can use java.sql and java streams for SELECT/INSERT of LOB data and if one uses JDBC Thin Drivers then we have to use PL/SQL and DBMS_LOB Package. I would like to if I'm correct?


    regards,
    Dileep Tallam.

  8. #18
    Join Date
    Sep 2005
    Posts
    278
    Dont know what you waiting for, just test it once. All the alternatives.

    All the best

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width