Creating Type for WM_CONCAT IN 12C

CREATE OR REPLACE TYPE wm_concat_impl
   AUTHID CURRENT_USER
AS OBJECT (
   curr_str   VARCHAR2 (32767),
   STATIC FUNCTION odciaggregateinitialize (sctx IN OUT wm_concat_impl)
      RETURN NUMBER,
   MEMBER FUNCTION odciaggregateiterate (
      SELF   IN OUT   wm_concat_impl,
      p1     IN       VARCHAR2
   )
      RETURN NUMBER,
   MEMBER FUNCTION odciaggregateterminate (
      SELF          IN       wm_concat_impl,
      returnvalue   OUT      VARCHAR2,
      flags         IN       NUMBER
   )
      RETURN NUMBER,
   MEMBER FUNCTION odciaggregatemerge (
      SELF    IN OUT   wm_concat_impl,
      sctx2   IN       wm_concat_impl
   )
      RETURN NUMBER
);
/

CREATE OR REPLACE TYPE BODY wm_concat_impl
IS
   STATIC FUNCTION odciaggregateinitialize (sctx IN OUT wm_concat_impl)
      RETURN NUMBER
   IS
   BEGIN
      sctx := wm_concat_impl (NULL);
      RETURN odciconst.success;
   END;
   MEMBER FUNCTION odciaggregateiterate (
      SELF   IN OUT   wm_concat_impl,
      p1     IN       VARCHAR2
   )
      RETURN NUMBER
   IS
   BEGIN
      IF (curr_str IS NOT NULL)
      THEN
         curr_str := curr_str || ',' || p1;
      ELSE
         curr_str := p1;
      END IF;

      RETURN odciconst.success;
   END;
   MEMBER FUNCTION odciaggregateterminate (
      SELF          IN       wm_concat_impl,
      returnvalue   OUT      VARCHAR2,
      flags         IN       NUMBER
   )
      RETURN NUMBER
   IS
   BEGIN
      returnvalue := curr_str;
      RETURN odciconst.success;
   END;
   MEMBER FUNCTION odciaggregatemerge (
      SELF    IN OUT   wm_concat_impl,
      sctx2   IN       wm_concat_impl
   )
      RETURN NUMBER
   IS
   BEGIN
      IF (sctx2.curr_str IS NOT NULL)
      THEN
         SELF.curr_str := SELF.curr_str || ',' || sctx2.curr_str;
      END IF;

      RETURN odciconst.success;
   END;
END;
/

CREATE OR REPLACE FUNCTION wm_concat (p1 VARCHAR2)
   RETURN VARCHAR2
   AGGREGATE USING wm_concat_impl;
/

How to exit a file for a period of time automatically

In new form instance create a trigger for timer

DECLARE

   x   timer;

BEGIN

   IF TO_CHAR (SYSDATE, 'hh24:mi') BETWEEN TO_CHAR (

                                              TO_DATE ('1120', 'hh24:mi'),

                                              'hh24:mi')

                                       AND TO_CHAR (

                                              TO_DATE ('1140', 'hh24:mi'),

                                              'hh24:mi')

   THEN

      x := CREATE_TIMER ('x', 1000, repeat);

   END IF;

END;

And create another trigger when timer expired

DECLARE

   timer_id   TIMER;

BEGIN

   IF TO_CHAR (SYSDATE, 'hh24:mi') BETWEEN TO_CHAR (

                                              TO_DATE ('1120', 'hh24:mi'),

                                              'hh24:mi')

                                       AND TO_CHAR (

                                              TO_DATE ('1140', 'hh24:mi'),

                                              'hh24:mi')

   THEN

      EXIT_FORM;

   END IF;

END;

Protecting Duplicate Entry in oracle Forms

Create a function
-----------------------------------
Function F_comparison (P1 varchar2, P2 varchar2)
Return number is
answer number :=0;
BEGIN
 IF P1 = P2 then
  answer := 1;
 END IF;
return(answer);
END;
----------------------
Create an item ctrl_item in control block and initialize this before validating with validate string(:block_name.item_name||:block_name.category||:block_name.color).
Then  Create a formula column (Match_found) in transaction block with formula F_comparison (:ctrl.ctrl_item, :block_name.item_name||:block_name.category||:block_name.color).
Then create a summary item in control block (Match_found) with the summary of (Match_found) transaction block.
At last create a validate trigger in transaction block

BEGIN
:ctrl.ctrl_item := :block_name.item_name||:block_name.category||:block_name.color;
IF :ctrl.Match_found > 1 then
   message('This Item is duplicated.');
   Raise form_trigger_failure ;
END IF;
END;

Insert data from one table to another table

insert into new_table ( select * from old_table);

Finding Table Name referenced by a constraint.

SELECT owner,
       constraint_name,
       constraint_type,
       table_name,
       r_owner,
       r_constraint_name
  FROM all_constraints
 WHERE     constraint_type = 'R'
       AND OWNER = 'Schema name'
       AND r_constraint_name IN
              (SELECT constraint_name
                 FROM all_constraints
                WHERE constraint_type IN ('P', 'U') AND table_name = 'Table Name');