Results 1 to 7 of 7

Thread: Blob/clob issue

  1. #1
    Join Date
    Apr 2012
    Posts
    3

    Unanswered: Blob/clob issue

    Hi,
    I have written the following trigger to insert the data to another table in another machine(DB2) from machine (DB1).

    Trigger body:
    create or replace trigger BANNER_MASTER_TRG after insert on BANNER_MASTER for each row
    begin
    insert into BANNER_MASTER@DBLINK (BANNERID,CONTENT_TYPE_ID,STARTTIME,ENDTIME,IMAGE_ SIZE,BANNER_NO,SOURCE_PATH,STATUS,D_CONTENT_ID,D_C ONTENT_TYPE,D_CONTENT_NAME,CIRCLEID) values (:NEW.BANNERID,:NEW.CONTENT_TYPE_ID,:NEW.STARTTIME ,:NEW.ENDTIME,:NEW.IMAGE_SIZE,:NEW.BANNER_NO,:NEW. SOURCE_PATH,:NEW.STATUS,:NEW.D_CONTENT_ID,:NEW.D_C ONTENT_TYPE,:NEW.D_CONTENT_NAME,:NEW.CIRCLEID);
    update BANNER_MASTER@DBLINK set BLOCK_TITLE=:new.BLOCK_TITLE where BANNERID=:new.BANNERID;
    end;
    /

    Results:
    Trigger gets created succesfully.

    Result post insertion:
    ERROR at line 1:
    ORA-02055: distributed update operation failed; rollback required
    ORA-00942: table or view does not exist
    ORA-02063: preceding line from DBLINK
    ORA-06512: at "ODP.BANNER_MASTER_TRG", line 3
    ORA-04088: error during execution of trigger 'ODP.BANNER_MASTER_TRG'

    Description of the table is as follows:
    SQL> desc banner_master
    Name Null? Type
    ----------------------------------------------------- -------- ------------------------------------
    BANNERID NUMBER(38)
    BLOCK_TITLE CLOB
    CONTENT_TYPE_ID NUMBER(38)
    STARTTIME VARCHAR2(200)
    ENDTIME VARCHAR2(200)
    IMAGEPATH CLOB
    IMAGENAME CLOB
    IMAGE_SIZE NUMBER(38)
    BANNER_NO NUMBER(38)
    SOURCE_PATH VARCHAR2(250)
    STATUS VARCHAR2(55)
    D_CONTENT_ID NUMBER(38)
    D_CONTENT_TYPE NUMBER(38)
    D_CONTENT_NAME VARCHAR2(50)
    CIRCLEID NUMBER(38)

    Can somebody help for the errors

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    Quote Originally Posted by siramitsharma View Post
    Hi,
    I have written the following trigger to insert the data to another table in another machine(DB2) from machine (DB1).
    ... Etc ...
    Result post insertion:
    ERROR at line 1:
    ORA-02055: distributed update operation failed; rollback required
    ORA-00942: table or view does not exist
    ORA-02063: preceding line from DBLINK
    ORA-06512: at "ODP.BANNER_MASTER_TRG", line 3
    ORA-04088: error during execution of trigger 'ODP.BANNER_MASTER_TRG'
    ... Etc ...
    Can somebody help for the errors
    Check if you have at least select permissions on the table at db link.
    Code:
    SELECT COUNT(*) FROM banner_master@dblink;
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Apr 2012
    Posts
    3
    I have permissions, below is the output.

    SQL> SELECT COUNT(*) FROM banner_master@dblink;

    COUNT(*)
    ----------
    15

    Also while selecting the records, it gives error as i am using CLOB in it. Below is the query:
    SQL> select * from banner_master@dblink;
    ERROR:
    ORA-22992: cannot use LOB locators selected from remote tables

    no rows selected

    Hope you are now clear of the requirement of updating the other DB in other machine/server which has CLOB. Please help

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    why INSERT followed by UPDATE?
    why not single INSERT like below?

    Code:
    INSERT INTO BANNER_MASTER@dblink 
                (BANNERID, 
                 CONTENT_TYPE_ID, 
                 STARTTIME, 
                 ENDTIME, 
                 IMAGE_SIZE, 
                 BANNER_NO, 
                 SOURCE_PATH, 
                 STATUS, 
                 D_CONTENT_ID, 
                 D_CONTENT_TYPE, 
                 D_CONTENT_NAME, 
                 BLOCK_TITLE,
                 CIRCLEID) 
    VALUES      (:NEW.bannerid, 
                 :NEW.content_type_id, 
                 :NEW.starttime, 
                 :NEW.endtime, 
                 :NEW.image_size, 
                 :NEW.banner_no, 
                 :NEW. source_path, 
                 :NEW.status, 
                 :NEW.d_content_id, 
                 :NEW.d_c ontent_type, 
                 :NEW.d_content_name, 
                 :new.BLOCK_TITLE,
                 :NEW.circleid);
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Apr 2012
    Posts
    3
    due to LOBs, cannot do insert over dblink, so segregated in two steps, in first step inserted non-lob's value & then in 2nd step, the lob's value only. Do you have any other value suggestion for insertion over dblink for LOBs

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >ORA-06512: at "ODP.BANNER_MASTER_TRG", line 3
    It appears user ODP owns this trigger?
    Which schema owns local BANNER_MASTER table?
    Which schema owns remote BANNER_MASTER table?
    Which schema name is used within "DBLINK" object?
    Which schema is doing the DML that results in trigger firing?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    Quote Originally Posted by siramitsharma View Post
    ... Etc ...
    Hope you are now clear of the requirement of updating the other DB in other machine/server which has CLOB. Please help
    If your requirement is to keep both the remote and local tables "in synch", perhaps a better solution would be to use any of the Oracle replication options like:
    • Materialized view(s)
    • Streams
    • Multimaster Replication

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

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