Merge with select with multiple rows

Question

I have a query which every time runs, selects the rows of user_triggers which are related to a table(p_table_name_in). I want to run this procedure every day and I want to just insert new rows, not all rows again. but when I install this oackage , I get this error:

ORA-00932 (130: 21): PL / SQL: ORA-00932: Inconsistent data types: CLOB expected, LONG received (line 31)

and when I try to change TRIGGER_BODY AS BODY_TRIGGER to TO_LOB(TRIGGER_BODY) AS BODY_TRIGGER I get this error:

ORA-00932 (111: 29): PL / SQL: ORA-00932: Inconsistent data types: - expected, LONG received (line 12)

procedure:

PROCEDURE save_trigger_definitions ( p_table_name_in in VARCHAR2 ) IS        
BEGIN                  
        MERGE INTO hot_utils_reload_triggers t1
        USING
        (
        SELECT TRIGGER_NAME ,
                            TABLE_NAME , 
                            STATUS , 
                            DESCRIPTION,
                            TRIGGER_BODY AS BODY_TRIGGER,
                            WHEN_CLAUSE 
                FROM user_triggers
        )t2
        ON(t2.TABLE_NAME like upper(p_table_name_in))
        WHEN MATCHED THEN UPDATE SET
            t1.DESCRIPTION = t2.DESCRIPTION,
            t1.WHEN_CLAUSE = t2.WHEN_CLAUSE
        WHEN NOT MATCHED THEN 
            INSERT (TRIGGER_NAME,
                    TABLE_NAME, 
                    STATUS, 
                    DESCRIPTION,
                    BODY_TRIGGER,
                    WHEN_CLAUSE)
            VALUES (t2.TRIGGER_NAME,
                    t2.TABLE_NAME, 
                    t2.STATUS, 
                    t2.DESCRIPTION, 
                    t2.BODY_TRIGGER, 
                    t2.WHEN_CLAUSE); 
            commit;
END save_trigger_definitions;

Barbaros Özhan · Accepted Answer · 2021-01-17T18:31:38.833

0

It's also interesting to me that Oracle does not allow to use TO_LOB within a SELECT or MERGE Statement, while does for INSERT. Thus you can seperately use INSERT and MERGE with only the part containing MATCHED part such as

CREATE OR REPLACE PROCEDURE save_trigger_definitions ( p_table_name_in in VARCHAR2 ) IS    
BEGIN  
    INSERT INTO hot_utils_reload_triggers
        (trigger_name,
         table_name,
         status,
         description,
         body_trigger,
         when_clause)
    SELECT trigger_name,
           table_name,
           status,
           description,
           TO_LOB(trigger_body),
           when_clause
      FROM user_triggers
     WHERE table_name LIKE UPPER(p_table_name_in)
       AND NOT EXISTS ( SELECT 1 
                          FROM hot_utils_reload_triggers 
                         WHERE trigger_name = u.trigger_name
                           AND table_name = u.table_name
                           AND status = u.status );

    UPDATE hot_utils_reload_triggers h
       SET h.description = description, h.when_clause = when_clause
     WHERE table_name LIKE UPPER(p_table_name_in);

    COMMIT;
END;
/

assuming that you don't want duplicated rows for some columns such as trigger_name,table_name,status, I have added a subquery for them after NOT EXISTS clause.

Ref1

Ref2

Using DBMS_REDEFINITION.START_REDEF_TABLE() might be another alternative for LONG to LOB conversion cases.

edited Jan 17 '21 at 18:31

answered Jan 15 '21 at 17:18

Barbaros Özhan

59,113
10
31
55

Thank you for your answer. But the problem with your code is when a duplicate row comes into the table, then program will raise an error and will not continue. – mona shiri Jan 17 '21 at 13:21
Hi @monashiri , I didn't see any problem when [reproduced](https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=c7a35f86889700e368ab08c959f29334) except for some little parts that I missed which I've added just now. – Barbaros Özhan Jan 17 '21 at 13:38
1

@BarbarosÖzhan you are considering the table without primary key, if you add keys to your table your code will return exception https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=4539f5985b238fabd9fcacd0062ad5b7 – Alireza Jan 17 '21 at 17:33
well, just noticed that you say not all rows again , but tell me please what criteria do you need for duplication? @monashiri . Should they be unique for six columns ? I've edited the answer depending on three columns. – Barbaros Özhan Jan 17 '21 at 18:32
@BarbarosÖzhan thank you, I think it works now. – mona shiri Jan 18 '21 at 07:42

Merge with select with multiple rows

1 Answers1