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;

Answer 1

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.