How to Create a Duplicate Record In Interactive/Classic Report Oracle APEX

Here is Small Tip How to Create a Duplicate Record In Interactive/Classic Report Oracle APEX. For that first wee need to create a Apex Collection which will be fire on page Load.

Copy Code

DECLARE
   v_collection_name   VARCHAR2 (30) := 'DUPLICATE';
BEGIN
   apex_collection.create_or_truncate_collection (
      p_collection_name   => v_collection_name);

   FOR x IN (SELECT * FROM emp)
   LOOP
      apex_collection.add_member (p_collection_name   => v_collection_name,
                                  p_c001              => x.empno,
                                  p_c002              => x.ename,
                                  p_c003              => x.deptno,
                                  p_c004              => x.sal);
   END LOOP;
END;

Now Based on Collection “DUPLICATE”, we will Create an Interactive Report. Define column attribute in the Property Editor in security section and OFF Escape special characters.

Copy Code

  SELECT seq_id,
         APEX_ITEM.text (p_idx       => 1,
                         p_value     => seq_id,
                         p_item_id   => 'f01_' || c001 || '')
            seq_id_display,
         c001 empno,
         APEX_ITEM.text (p_idx       => 2,
                         p_value     => c002,
                         p_item_id   => 'f02_' || c001 || '')
            ename,
         APEX_ITEM.text (p_idx       => 3,
                         p_value     => c003,
                         p_item_id   => 'f03_' || c001 || '')
            dept,
         APEX_ITEM.text (p_idx       => 4,
                         p_value     => c004,
                         p_item_id   => 'f04_' || c001 || '')
            sal,
         seq_id copy_link
    FROM apex_collections
   WHERE collection_name = 'DUPLICATE'
ORDER BY seq_id

Next step is to create a page item “P1_SEQ_ID” which will perform a dynamic action for duplicating a record.
Define another column SEQ_ID as COPY_LINK and the column type will be link.

Target URL:= javascript:$s("P1_SEQ_ID","#SEQ_ID#");

Link Text:=  <div class="tooltip fa fa-files-o call_collection" ria-hidden="true">    
             <span class="tooltiptext">Duplicate Record</span>  
             </div>

Create Item Change event dynamic action for generating duplicate record.

Copy Code

DECLARE
   v_collection_name   VARCHAR2 (30) := 'DUPLICATE';
   v_empno NUMBER;
BEGIN
SELECT EMP_SEQ.NEXTVAL
     INTO v_empno
     FROM DUAL;
    FOR x IN (SELECT *  FROM
    	        apex_collections
    	         where collection_name= 'DUPLICATE'
    	          and seq_id= :P1_SEQ_ID)
      LOOP
      apex_collection.add_member (p_collection_name      => v_collection_name,
                                  p_c001                 => v_empno,
                                  p_c002                 => x.c002,
                                  p_c003                 => x.c003,
                                  p_c004                 => x.c004
                                 );

        end loop;
END;

Copy and paste below CSS in page inline. This is only use for showing tooltip on duplicate link column.

Copy Code

.tooltip {
    position: relative;
    display: inline-block;
    border-bottom: 1px dotted black;
}
.tooltip .tooltiptext {
    visibility: hidden;
    width: 120px;
    background-color: black;
    color: #fff;
    text-align: center;
    border-radius: 6px;
    padding: 5px 0;
    font-size: 12px;
    /* Position the tooltip */
    position: absolute;
    z-index: 1;
    left: 20;
}
.tooltip:hover .tooltiptext {
    visibility: visible;
}

Demo

How to Create a Duplicate Record In Interactive/Classic Report Oracle APEX

You might Like