Posted on

Oracle – Find Keywords in All Tables and Columns

A PL-SQL script for finding keywords in all tables and columns of an Oracle database.

SET SERVEROUTPUT ON SIZE 100000

DECLARE
   v_match_count   INTEGER;
   v_keyword       VARCHAR2(100) := 'keywordtofind';
BEGIN
   FOR t
      IN (SELECT owner, table_name, column_name
            FROM all_tab_columns
           WHERE     owner = 'XXX_ADMIN'
                 AND data_type LIKE '%CHAR%'
                 AND data_length >= LENGTH(v_keyword)
                 AND table_name LIKE 'XXX_%')
   LOOP
      EXECUTE IMMEDIATE
            'SELECT COUNT(*) FROM '
         || t.owner
         || '.'
         || t.table_name
         || ' WHERE LOWER('
         || t.column_name
         || ') LIKE ''%'''
         || ' || LOWER(:1) || '
         || '''%'''
         INTO v_match_count
         USING v_keyword;

      IF v_match_count > 0
      THEN
         DBMS_OUTPUT.put_line (
            t.table_name || ' ' || t.column_name || ' ' || v_match_count);
      END IF;
   END LOOP;
END;
/
Posted on

Oracle – Forms

How to change the properties of a field?

-- Change the background color
SET_ITEM_PROPERTY ('my_field', background_color, 'r215g215b215');
-- Hide field
SET_ITEM_PROPERTY ('my_field', visable, property_false);
-- Enable field
SET_ITEM_PROPERTY ('my_field', enabled, property_true);