Procedure for System Record of Timestapping (created time)

PROCEDURE P_RecordHistory IS
  curr_block varchar2(30) := name_in('system.trigger_block');
  rec_status varchar2(10) := name_in('system.record_status');
BEGIN
    if rec_status = 'INSERT' then
        if not id_null(find_item(curr_block||'.'||'SS_CREATOR')) then
            copy(name_in('global.g_emp_no'), curr_block||'.'||'SS_CREATOR');
        end if;
        if not id_null(find_item(curr_block||'.'||'SS_CREATED_ON')) then
            copy(to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'), curr_block||'.'||'SS_CREATED_ON');
        end if;
    elsif rec_status = 'CHANGED' then
        if not id_null(find_item(curr_block||'.'||'SS_MODIFIER')) then
            copy(name_in('global.g_emp_no'), curr_block||'.'||'SS_MODIFIER');
        end if;
        if not id_null(find_item(curr_block||'.'||'SS_MODIFIED_ON')) then
            copy(to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'), curr_block||'.'||'SS_MODIFIED_ON');
        end if;
    end if;
    if not id_null(find_item(curr_block||'.'||'SS_OG_ON')) then
        copy(k_variable.og_no, curr_block||'.'||'SS_OG_ON');
    end if;
END;

Export Empty Table in Oracle 11G

/*ALLOCATE EXTENT  COMMAND TO EXPORT EMPTY TABLE*/ 

SELECT 'alter table ' ||  table_name || ' allocate extent;'
from dba_tables where SEGMENT_CREATED = 'NO';

Record update with Timestamp

UPDATE table1 t1
   SET rec_sl =
          (SELECT rn
             FROM (SELECT ROWID,
                          ROW_NUMBER () OVER (ORDER BY ss_created_on) AS rn
                     FROM table1) t2
            WHERE t2.ROWID = t1.ROWID);

Creating Dynmic system identification number with table name and record creation time

CREATE OR REPLACE FUNCTION f_sys_id (p_table IN VARCHAR2, p_time VARCHAR2)
   RETURN VARCHAR2
IS
   sql_stmt   VARCHAR2 (150);
   RESULT     VARCHAR2 (20);
BEGIN
   sql_stmt :=
         'SELECT '
      || TO_CHAR (SYSDATE, 'RRRRMMDDHH24MISS')
      || '||'
      || ' lpad(COUNT(*)+1,6,0) from '
      || p_table
      || ' where '
      || 'to_char ('
      || p_time
      || ','
      || '''rrrrmmddhh24miss'
      || ''')='
      || TO_CHAR (SYSDATE, 'RRRRMMDDHH24MISS');

   EXECUTE IMMEDIATE sql_stmt
                INTO RESULT;

   RETURN RESULT;
END;
/

Email Address Validation in Oracle Forms

SELECT (CASE
           WHEN REGEXP_LIKE ('jsd.jf@hotmail.com', '^\w+(\.\w+)*+@\w+(\.\w+)+$')
              THEN 'true'
           ELSE 'false'
        END
       ) emai_validity
  FROM DUAL;

Creating List Dynamically

DECLARE
   gr_id     recordgroup;
   errcode   NUMBER;
BEGIN
   gr_id :=
      CREATE_GROUP_FROM_QUERY
         ('org_id',
          'SELECT ORG_NAME, TO_CHAR(ORG_ID) FROM USRWRKAREA_V
               WHERE USR_ID = :global.g_emp_no');
   errcode := POPULATE_GROUP (gr_id);
   POPULATE_LIST ('CTRL.ORG_ID', 'ORG_ID');
end;

Current record number in the block of oracle Forms.

SYSTEM.CURSOR_RECORD represents the number of the record where the cursor is located. This number represents the record's current physical order in the block's list of records. The value is always a character string.

IF

 :System.Cursor_Record = '1'

   THEN 

Go_Item('orders.total');

 ELSE

Previous_Item;

END IF;

Queries to find the Child or Parent tables of a table

Queries to find the Child or Parent tables of a table

Many times during maintenance activities on the tables you would be required to find the child or parent tables on a table. 

Following queries would help you in finding out them.

1) Query to find the child tables

SELECT p.table_name PARENT_TABLE, c.table_name CHILD_TABLE FROM dba_constraints p, dba_constraints c WHERE (p.constraint_type = 'P' OR p.constraint_type = 'U') AND c.constraint_type = 'R' AND p.constraint_name = c.r_constraint_name AND p.table_name = UPPER('&PARENT_TABLE_NAME') and p.owner='&PARENT_TABLE_SCHEMA';
2) Query to find the parent tables of a table
SELECT c.table_name CHILD_TABLE, p.table_name PARENT_TABLE,c.owner CHILD_TABLE_OWNER,p.owner PARENT_TABLE_OWNER FROM dba_constraints p, dba_constraints c WHERE (p.constraint_type = 'P' OR p.constraint_type = 'U') AND c.constraint_type = 'R' AND p.constraint_name = c.r_constraint_name AND c.table_name = UPPER('&CHILD_TABLE_NAME') and c.owner='&CHILE_TABLE_SCHEMA'; --This condition is required in case you have multiple tables with same name

Data Syncronising with Procedure Between two Database

CREATE OR REPLACE PROCEDURE P_DATA_SYNC
  AS
  CURSOR ins_table
   IS
      SELECT    'INSERT INTO '
             || t.table_name
             || ' (SELECT * FROM '
             || t.table_name
             || ' WHERE SYNC_FG = 0)' sqlstmt
        FROM user_tables t;
BEGIN
   FOR c1 IN ins_table
   LOOP
      EXECUTE IMMEDIATE  (c1.sqlstmt);
   END LOOP;
END;

Trigger to login when forms builder runtime demands invalid username/password

Just type the trigger in
Form level > logon instance
logon('ichri','ichri@rcsl',false);

Execute Query Depending on Tree Node Value

DECLARE
   vi_htree   item;
BEGIN
   vi_htree := FIND_ITEM ('BLK_TR.TR');
   :ctrl.ac_code :=
      ftree.GET_TREE_NODE_PROPERTY (vi_htree,
                                    :SYSTEM.trigger_node,
                                    ftree.node_value
                                   );

 /*  SELECT ac_code, ac_name, node_lvl, group_fg,
          stat_fg, ac_type
     INTO :ctrl.ac_code, :ctrl.ac_name, :ctrl.node_lvl, :ctrl.group_fg,
          :ctrl.stat_fg, :ctrl.ac_type
     FROM ac_coa
    WHERE ac_code = :ctrl.ac_code AND onr_id = :ctrl1.onr_id;*/

   SET_BLOCK_PROPERTY ('AC_COA',
                       default_where,
                          'AC_PCODE = '
                       || :ctrl.ac_code
                       || 'AND ONR_ID = '
                       || :ctrl1.onr_id
                      );
   GO_BLOCK ('AC_COA');
   CLEAR_BLOCK;
   EXECUTE_QUERY;
EXCEPTION
   WHEN OTHERS
   THEN
      p_message (SQLERRM);
END;

Record Group for Populating Tree

p_PopulateTree('BLK_TR.TR','SELECT 1,level,ac_name,NULL,ac_code
         FROM AC_COA
         WHERE onr_id = :ctrl1.onr_id
   START WITH ac_pcode = 0
   CONNECT BY PRIOR ac_code = ac_pcode');

-- Record Group
SELECT 1,level,ac_name,NULL,ac_code
         FROM AC_COA
         WHERE onr_id = :ctrl1.onr_id
   START WITH ac_pcode = 0
   CONNECT BY PRIOR ac_code = ac_pcode

Populating Tree by Procedure

PROCEDURE p_populatetree (p_htree VARCHAR2, p_clause VARCHAR2)
IS
   htree        item;
   rg_prop_id   recordgroup;
   rg_prop      VARCHAR2 (100);
   v_ignore     NUMBER;
   node         ftree.node;
   state        VARCHAR2 (100);
   area_type    NUMBER;
BEGIN
   htree := FIND_ITEM (p_htree);
   rg_prop_id := FIND_GROUP ('rg_prop');

   IF NOT ID_NULL (rg_prop_id)
   THEN
      DELETE_GROUP (rg_prop_id);
   END IF;

   rg_prop_id := CREATE_GROUP_FROM_QUERY ('rg_prop', p_clause);
   v_ignore := POPULATE_GROUP (rg_prop_id);
   ftree.SET_TREE_PROPERTY (htree, ftree.record_group, rg_prop_id);
   node := ftree.FIND_TREE_NODE (htree, '');

   WHILE NOT ftree.ID_NULL (node)
   LOOP
      state := ftree.GET_TREE_NODE_PROPERTY (htree, node, ftree.node_state);

      IF state = ftree.expanded_node
      THEN
         ftree.SET_TREE_NODE_PROPERTY (htree,
                                       node,
                                       ftree.node_state,
                                       ftree.collapsed_node
                                      );
      END IF;

      node :=
         ftree.FIND_TREE_NODE (htree,
                               '',
                               ftree.find_next,
                               ftree.node_label,
                               '',
                               node
                              );
   END LOOP;
END;

Aggregate data from a number of rows into a single row by Function WM_CONCAT

The Oracle PL/SQL WM_CONCAT function is used to aggregate data from a number of rows into a single row, giving a list of data associated with a specific value. In effect, it cross-tabulates a comma delimited list.

 select uc.table_name, uc.constraint_name, wm_concat(column_name) column_name
 FROM user_cons_columns ucc, user_constraints uc
   WHERE uc.constraint_name = ucc.constraint_name
     AND uc.constraint_type IN ('P', 'U')
     group by uc.table_name, uc.constraint_name
ORDER BY 1, 2;

Seperating Column Name with user defined symbol

SELECT TABLE_NAME,
       LISTAGG(TABLE_NAME, '-') WITHIN GROUP (ORDER BY TABLE_NAME) text
  FROM user_constraints uc
 GROUP BY TABLE_NAME;

Getting Constraint Name from Oracle Database

SELECT   uc.table_name, uc.constraint_name, column_name
    FROM user_cons_columns ucc, user_constraints uc
   WHERE uc.constraint_name = ucc.constraint_name
     AND uc.constraint_type IN ('P', 'U')
ORDER BY 1, 2;

Query Table Name where specific column not exist in table.

SELECT    'ALTER TABLE '
       || table_name
       || ' ADD (
   SS_CREATOR      NUMBER(10) DEFAULT 1001 NOT NULL
  ,SS_CREATED_ON   DATE  DEFAULT SYSDATE NOT NULL
  ,SS_MODIFIER     NUMBER(10)
  ,SS_MODIFIED_ON  DATE);'
  FROM user_tables
 WHERE table_name NOT IN (SELECT DISTINCT table_name
                                     FROM user_tab_cols
                                    WHERE column_name = 'SS_CREATED_ON');

Updating a table for a series of number by PL/SQL in oracle Database

DECLARE
   CURSOR gdwn
   IS
      SELECT *
        FROM godown; --(Table name) 
BEGIN
   FOR c1 IN gdwn
   LOOP
      UPDATE godown
         SET tp_id = (SELECT NVL (MAX (tp_id), 0) + 1
                        FROM godown)
       WHERE godown_id = c1.godown_id;

      COMMIT;
   END LOOP;
END;

Showing lov conditionally in oracle forms

Showing lov conditionally in oracle forms

declare
V_LOV    Boolean;
BEGIN
if :Block_name.Column_name  = 'X' THEN   
  V_LOV := SHOW_LOV('LOV45');
ELSE IF :Block_name.Column_name  = 'Y' THEN
  V_LOV :=  SHOW_LOV('LOV47');
ELSE
:Block_name.Column_name := 'NOLOV' ;  END IF ;
end if ;
end;

Passing table as variable in function

Passing table as variable in function

--Dynamic function table name as variable
CREATE OR REPLACE FUNCTION f_sys_id (p_table IN VARCHAR2)
   RETURN VARCHAR2
IS
   sql_stmt   VARCHAR2 (100);
   RESULT     VARCHAR2 (20);
BEGIN
   sql_stmt :=
         'SELECT '
      || TO_CHAR (SYSDATE, 'RRRRMMDDHH24MISS')
      || '||'
      || 'lpad(COUNT(*)+1,6,0) from '
      || p_table
      || ' where sys_id = '
      || TO_CHAR (SYSDATE, 'RRRRMMDDHH24MISS');

   EXECUTE IMMEDIATE sql_stmt
                INTO RESULT;

   RETURN RESULT;
END;
/