0

Having exception from oracle while running following query ORA-22992: cannot use LOB locators selected from remote tables

        SELECT 
            OLU.CNIC,
            OLU.LR_USER_NAME NAME,
            OLU.FATHERS_NAME,
            OLU.ADDRESS,
            BATCH_VERIF.CREATED_DATE BATCH_DATE,
            TO_DATE(OLU.ATTRIBUTE9, 'mm/dd/yyyy hh:mi:ss AM')  TRANSLATION_DATE
    FROM ORION.ORN_LR_USER@LIV OLU
    INNER JOIN
    (
        SELECT MAX(BATCH_ID) BATCH_ID, CNIC
        FROM ORION.ORN_LR_BATCH_CNIC@LIV
        WHERE IMPORT_DATE IS NOT NULL AND IMPORTED = 'T'
        GROUP BY CNIC
    ) BATCH_CNIC
            ON (BATCH_CNIC.CNIC = OLU.CNIC
                    AND NVL (OLU.IS_BATCH_VERIFIED, 'F') = 'T' 
                    AND NVL (OLU.ATTRIBUTE10, 'F') = 'T'
                    AND OLU.ATTRIBUTE9 IS NOT NULL) 
        INNER JOIN ORION.ORN_FILE_BATCH_VERIF@LIV BATCH_VERIF
            ON BATCH_VERIF.BATCH_ID = BATCH_CNIC.BATCH_ID;

but after commenting TRANSLATION_DATE it is working fine, neither is the column of lob type.. what could be the reason?

        SELECT 
            OLU.CNIC,
            OLU.LR_USER_NAME NAME,
            OLU.FATHERS_NAME,
            OLU.ADDRESS,
            BATCH_VERIF.CREATED_DATE BATCH_DATE
            --TO_DATE(OLU.ATTRIBUTE9, 'mm/dd/yyyy hh:mi:ss AM')  TRANSLATION_DATE
    FROM ORION.ORN_LR_USER@LIV OLU
    INNER JOIN
    (
        SELECT MAX(BATCH_ID) BATCH_ID, CNIC
        FROM ORION.ORN_LR_BATCH_CNIC@LIV
        WHERE IMPORT_DATE IS NOT NULL AND IMPORTED = 'T'
        GROUP BY CNIC
    ) BATCH_CNIC
            ON (BATCH_CNIC.CNIC = OLU.CNIC
                    AND NVL (OLU.IS_BATCH_VERIFIED, 'F') = 'T' 
                    AND NVL (OLU.ATTRIBUTE10, 'F') = 'T'
                    AND OLU.ATTRIBUTE9 IS NOT NULL) 
        INNER JOIN ORION.ORN_FILE_BATCH_VERIF@LIV BATCH_VERIF
            ON BATCH_VERIF.BATCH_ID = BATCH_CNIC.BATCH_ID;
Salman
  • 11
  • 1
  • 4
  • ATTRIBUTE9 is the varchar2 column – Salman Jul 11 '17 at 11:50
  • 1
    If I understand you correctly, you're saying ATTRIBUTE9 is not a CLOB column. Are you sure? If so, are any columns in the referenced tables of type CLOB? – APC Jul 11 '17 at 12:15
  • yea, ATTRIBUTE9 is not a CLOB column. There are CLOB columns in referenced table but they are not being selected in any query. If you compare both scripts you will notice the difference i want to share. – Salman Jul 11 '17 at 12:33
  • What size is `ATTRIBUTE9`, and what are the database character sets? I think it's possible that a large string (e.g. `varchar2(4000)` can cause this if the character sets are different, and multiple bytes are allocated over the link for each character; it effectively becomes an implicit CLOB. Long time since i saw that though, I'll try to dig something up. – Alex Poole Jul 11 '17 at 12:36
  • size of ATTRIBUTE9 512 Byte, size of this column is no matter because i have selected this column before and found no issue.. problem arise when i converted this column into date type. – Salman Jul 11 '17 at 13:02
  • Which version and patch level of Oracle are you using? (The first line from `select banner from v$version`) – Alex Poole Jul 12 '17 at 09:14
  • Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi – Salman Jul 13 '17 at 05:10

1 Answers1

0

"problem arise when i converted this column into date type"

Hmmm, sounds like it may be an Oracle bug. But maybe you can work around it with this trick:

TO_DATE(substr(OLU.ATTRIBUTE9, 1, 22), 'mm/dd/yyyy hh:mi:ss AM') TRANSLATION_DATE
APC
  • 144,005
  • 19
  • 170
  • 281