1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Ora-22992 workaround

Discussion in 'SQL PL/SQL' started by 13478, May 5, 2016.

  1. 13478

    13478 Active Member

    Messages:
    31
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    Louisana
    Hello,

    When using a db link to call a clob column in remote database.
    Get error:
    ORA-22992: cannot use LOB locators selected from remote tables

    according to : Doc ID 436707.1,
    shows workaround is: to receive the LOB objects into variables defined as CHAR (if CLOB),

    example in Doc 436707.1 shows:


    1.1 From the remote database:


    create user cris identified by cris;
    grant connect, resource to cris;
    conn cris/cris
    create table test(id number, obj clob);
    insert into test values(1,'sdfsdfsfd');
    insert into test values(2, 'sdfsdfsfdvfgdfvgdfvdf');
    commit;

    1.2 From the local database:


    create user cris identified by cris;
    grant connect, resource, create database link to cris;
    conn cris/cris
    create database link torem using 'identification of the remote database in the tnsnames.ora';

    set serveroutput on

    declare
    my_ad varchar(6000);
    BEGIN
    SELECT obj INTO my_ad FROM test@torem where id=1;
    dbms_output.put_line(my_ad);
    END;
    /

    Return to our case...
    In our case,
    the query is:

    SELECT A.UIDY, A.SUBJ, A.ACTN_CD, A.SOLI_ID, A.RPSE_DT, A.DOC_RM_DT, A.NIC_ID, A.VEND_ID, A.SET_ASDE, CONCAT(CONCAT(A.DSCR, '<BR>'), B.EXTD_DSCR) AS DSCR
    FROM MF_ANNC@MMM_LNK A, MF_WORLD@MMM_LNK B WHERE A.UIDY=B.DOC_ID;

    the column EXTD_DSCR is clob,
    the question here is how could we modify above query as workaround shows?

    Thank you very much!








    cc: (that is what I did, but not for above query)

    declare

    CURSOR MF_WORLD_cur IS
    SELECT B.EXTD_DSCR
    FROM MF_HELLO@MMM_LNK A, MF_WORLD@MMM_LNK B WHERE A.UIDY=B.DOC_ID;
    my_ad varchar(10000);

    BEGIN
    open MF_WORLD_cur;
    FETCH MF_WORLD_cur INTO my_ad;
    dbms_output.put_line(my_ad);
    close MF_WORLD_cur;
    END;
    /
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi.
    In documentation it is told that :

    Code (Text):
    LOB Restrictions

    LOB columns are subject to the following restrictions:

    Distributed LOBs are not supported. Therefore, you cannot use a remote locator in SELECT or WHERE clauses of queries or in functions of the DBMS_LOB package.

    The following syntax is not supported for LOBs:

    SELECT lobcol FROM table1@remote_site;
    INSERT INTO lobtable SELECT type1.lobattr FROM
    table1@remote_site;
    SELECT DBMS_LOB.getlength(lobcol) FROM table1@remote_site;


    (This statement produces error: ORA-22992 cannot use LOB locators selected from remote tables.)

    However, you can use a remote locator in others parts of queries that reference LOBs. The following syntax is supported on remote LOB columns:

    CREATE TABLE t AS SELECT * FROM table1@remote_site;
    INSERT INTO t SELECT * FROM table1@remote_site;
    UPDATE t SET lobcol = (SELECT lobcol FROM table1@remote_site);
    INSERT INTO table1@remote_site ...
    UPDATE table1@remote_site ...
    DELETE FROM table1@remote_site ...


    For the first three types of statement, which contain subqueries, only standalone LOB columns are allowed in the select list. SQL functions or DBMS_LOB APIs on LOBs are not supported. For example, the following statement is supported:

    CREATE TABLE AS SELECT clob_col FROM tab@dbs2;


    However, the following statement is not supported:

    CREATE TABLE AS SELECT dbms_lob.substr(clob_col) from tab@dbs2;
    try this ...
    Code (SQL):
    DECLARE
    my_ad VARCHAR(4000);
    BEGIN
    EXECUTE immediate '
    SELECT substr(obj,1,4000)  FROM test@torem where id=:id'

    INTO my_ag USING 1;
    dbms_output.put_line(my_ad);
    END;

    try this
    Code (SQL):

    DECLARE
    my_ad VARCHAR(4000);
    BEGIN
    SELECT (SELECT substr(obj,1,4000)  FROM test@torem WHERE id=1) INTO my_ad FROM DUAL;
    dbms_output.put_line(my_ad);
    END;
     
     
    Last edited: May 6, 2016
  3. 13478

    13478 Active Member

    Messages:
    31
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    Louisana
    Hello Krasnoslobodtsev,

    Thank you very much.

    As to below query,

    SELECT A.UIDY, A.SUBJ, A.ACTN_CD, A.SOLI_ID, A.RPSE_DT, A.DOC_RM_DT, A.NIC_ID, A.VEND_ID, A.SET_ASDE, CONCAT(CONCAT(A.DSCR, '<BR>'), B.EXTD_DSCR) AS DSCR
    FROM MF_ANNC@MMM_LNK A, MF_WORLD@MMM_LNK B WHERE A.UIDY=B.DOC_ID;

    (*please note: column EXTD_DSCR in table MF_WORLD is CLOB column)

    How could we modify it to work?

    Thank you so much again!!!














     
  4. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi.

    For a test...

    Code (SQL):
    SELECT A.UIDY, A.SUBJ, A.ACTN_CD, A.SOLI_ID, A.RPSE_DT, A.DOC_RM_DT, A.NIC_ID, A.VEND_ID, A.SET_ASDE, CONCAT(CONCAT(A.DSCR, '<BR>'), dbms_lob.substr@MMM_LNK(B.EXTD_DSCR,4000,1) ) AS DSCR
    FROM MF_ANNC@MMM_LNK A, MF_WORLD@MMM_LNK B WHERE A.UIDY=B.DOC_ID;
     
     
    Last edited: May 9, 2016
  5. 13478

    13478 Active Member

    Messages:
    31
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    Louisana
    Hello Krasnoslobodtsev,

    It works, Genius.

    Thank you so much!!!