Search Oracle Database for data using PL/SQL

I know that a piece of data is present somewhere in the database and most often I don't know where is it exactly located in the jungle of tables. The following are such situations.

i) A string is appearing in an Oracle Forms screen, I want to know which table it is stored. And if it is a non-database item Oracle diagnostics does not provide much help.

ii)I have an interface concurrent program which takes a flat file data and loads in certain tables. I want to know which table a particular information is stored.

I used to read the code or simply guess the table name and search in ALL_TABLES and then query in the possible tables. Being too lazy to do that, I wrote the following PL/SQL procedure which will do the task for me. The inputs to the program are

i) Search_string :- The data you want to search for
ii) Data_type :- The type of the data. NUMBER,VARCHAR2 and DATE are the possible options

The output of the program will be the names of the tables and columns where the given data is present. Remember to turn on the output. (SET SERVEROUTPUT ON)

DECLARE

  CURSOR c_column IS

    SELECT table_name

          ,column_name

          ,data_type

    FROM   all_tab_columns atc

    WHERE  1 = 1

    AND    data_type = UPPER('&Data_type') --the possible options are NUMBER, VARCHAR2 and DATE. You won't get any result if some other value is entered

          --   and owner in ('')   -- Add the names of the possible schemas where you expect to find the data. If you comment this line, it may take a lot of time for the search

          -- and table_name like upper('%') --Use this condition if you have any clues on the table name. (Eg :- Table name contains word 'INVOICE'). This will improve the efficiency

          -- and column_name like upper('%')--Use this condition if you have any clues on the column name. (Eg :- Column name contains word 'DATE'). This will improve the efficiency

    AND    EXISTS (SELECT 1 FROM all_tables at WHERE at.table_name = atc.table_name

    and atc.TABLE_NAME like 'FND%'); -- This condition ensures that only tables are searched, not the views

  v_search_string VARCHAR2(100) := '&Search_string';

  v_count         NUMBER;

  v_sql_stmt      VARCHAR2(2000);

  e_table_col_not_found EXCEPTION;

  e_invalid_data_type   EXCEPTION;

  PRAGMA EXCEPTION_INIT(e_table_col_not_found

                       ,-942);

BEGIN

  dbms_output.put_line('Searching for ' || v_search_string);

  FOR r_column IN c_column

  LOOP

    BEGIN

      IF r_column.data_type = 'VARCHAR2'

      THEN

        v_sql_stmt := 'SELECT COUNT(ROWID) FROM ' || r_column.table_name || ' WHERE ' || r_column.column_name || ' like ''' || v_search_string || '''';

      ELSIF r_column.data_type = 'NUMBER'

      THEN

        v_sql_stmt := 'SELECT COUNT(ROWID) FROM ' || r_column.table_name || ' WHERE ' || r_column.column_name || '= to_number(''' || v_search_string || ''')';

      ELSIF r_column.data_type = 'DATE'

      THEN

        v_sql_stmt := 'SELECT COUNT(ROWID) FROM ' || r_column.table_name || ' WHERE ' || r_column.column_name || '= to_date(''' || v_search_string ||

                      ''',''DD-MON-YYYY'')';

      END IF;

      EXECUTE IMMEDIATE v_sql_stmt

        INTO v_count;

      IF (v_count > 0)

      THEN

        dbms_output.put_line('Found in Table ' || r_column.table_name || ' Column :-  ' || r_column.column_name);

      END IF;

    EXCEPTION

      WHEN e_table_col_not_found THEN

        dbms_output.put_line('Error. Skipping Column ' || r_column.column_name || ' of table ' || r_column.table_name);

    END;

  END LOOP;

  dbms_output.put_line('Search Over');

END;

The program contains a cursor which will loop through all the tables and columns present in the database. For each of the table and column, the program will form a dynamic SQL query to see whether the data is present in the particular column.

The program is awfully slow if it searches the entire Oracle Applications database, so it will be better to narrow the search by providing extra conditions in the main cursor. The possible conditions are

i) OWNER IN ('ABC', 'XYZ') :- If you have any clues on the module where you expect the data to be present use the schema(s) corresponding to the module. If you don't have any idea, at least you can avoid certain schema using something like OWNER NOT IN ('SYS')

ii) TABLE_NAME LIKE UPPER('%ABC%') :- If you can guess the probable strings in table name use this condition

iii)COLUMN_NAME LIKE UPPER('%ABC%') :- If you can guess the probable strings in column name use this condition

The procedure has been very much helpful for me when used appropriately.

SQL Query to print Calendar of any month

Here is an SQL query to print Calendar in the traditional style. Always aligning to 5 rows.

select nvl(decode(to_char(trunc(trunc(MY_DATE,'MON'),'D')+(rownum-1)*7,'MM'),to_char(MY_DATE,'MM'),to_char(trunc(trunc(MY_DATE,'MON'),'D')+(rownum-1)*7,'DD'), null),decode(to_char(trunc(trunc(MY_DATE,'MON'),'D')+35,'MM'),to_char(MY_DATE,'MM'),to_char(trunc(trunc(MY_DATE,'MON'),'D')+35,'DD'), null)) SUN ,

nvl(decode(to_char(trunc(trunc(MY_DATE,'MON'),'D')+(rownum-1)*7+1,'MM'),to_char(MY_DATE,'MM'),to_char(trunc(trunc(MY_DATE,'MON'),'D')+(rownum-1)*7+1,'DD'), null),decode(to_char(trunc(trunc(MY_DATE,'MON'),'D')+36,'MM'),to_char(MY_DATE,'MM'),to_char(trunc(trunc(MY_DATE,'MON'),'D')+36,'DD'), null)) MON ,

decode(to_char(trunc(trunc(MY_DATE,'MON'),'D')+(rownum-1)*7+2,'MM'),to_char(MY_DATE,'MM'),to_char(trunc(trunc(MY_DATE,'MON'),'D')+(rownum-1)*7+2,'DD'), null) TUE ,

decode(to_char(trunc(trunc(MY_DATE,'MON'),'D')+(rownum-1)*7+3,'MM'),to_char(MY_DATE,'MM'),to_char(trunc(trunc(MY_DATE,'MON'),'D')+(rownum-1)*7+3,'DD'), null) WED ,

decode(to_char(trunc(trunc(MY_DATE,'MON'),'D')+(rownum-1)*7+4,'MM'),to_char(MY_DATE,'MM'),to_char(trunc(trunc(MY_DATE,'MON'),'D')+(rownum-1)*7+4,'DD'), null) THU ,

decode(to_char(trunc(trunc(MY_DATE,'MON'),'D')+(rownum-1)*7+5,'MM'),to_char(MY_DATE,'MM'),to_char(trunc(trunc(MY_DATE,'MON'),'D')+(rownum-1)*7+5,'DD'), null) FRI,

decode(to_char(trunc(trunc(MY_DATE,'MON'),'D')+(rownum-1)*7+6,'MM'),to_char(MY_DATE,'MM'),to_char(trunc(trunc(MY_DATE,'MON'),'D')+(rownum-1)*7+6,'DD'), null) SAT

from dual, (select to_date('01-&Month-&Year','DD-MON-YYYY') MY_DATE from dual)

connect by level<=5

Script to register Table and Columns in Oracle Applications

Last day I was trying to register a flex field on a custom table. I could not find my custom table in the Tables LOV. Then it occurred to me that I have to register my custom table for it to be available in any LOV in apps. So I navigated to the Table Registration screen to find it read only .

After a bit of browsing I found that I had to use an API for the purpose. So I used ad_dd.register_table API to do the same. That was not the end and now I had to register each of my 40 plus columns one by executing ad_dd.register_column API. I knew there should be an easier way. I googled but could not find the code to register the table as well as columns in one short.

Finally I wrote the script myself.
CREATE OR REPLACE PROCEDURE register_table (
table_name VARCHAR2,
application_short_name VARCHAR2
)
AS
status VARCHAR2 (10);

CURSOR c_columns ( p_table_name all_tab_columns.table_name%TYPE )
IS
SELECT column_name,
data_type,
data_length,
nullable,
ROWNUM,
data_precision,
data_scale
FROM all_tab_columns
WHERE table_name = p_table_name;

CURSOR c_constraints (p_table_name all_tab_columns.table_name%TYPE,
p_application_short_name all_tab_columns.owner%TYPE )
IS
SELECT constraint_name,
table_name,
status
FROM all_constraints
WHERE table_name = p_table_name AND owner = p_application_short_name AND constraint_type = 'P';

CURSOR c_constraint_columns (
p_table_name all_tab_columns.table_name%TYPE,
p_application_short_name all_tab_columns.owner%TYPE
)
IS
SELECT acc.constraint_name,
acc.column_name,
acc.POSITION
FROM all_cons_columns acc, all_constraints ac
WHERE ac.constraint_name = acc.constraint_name
AND ac.table_name = p_table_name
AND ac.constraint_type = 'P'
AND ac.owner = p_application_short_name;
BEGIN

DBMS_OUTPUT.put_line ('Registering Table '|| table_name ||'in application ' || application_short_name);

ad_dd.register_table (p_appl_short_name => application_short_name,
p_tab_name => table_name,
p_tab_type => 'T'
);

FOR r_columns IN c_columns (table_name)
LOOP

DBMS_OUTPUT.put_line ('Registering Column '|| r_columns.column_name);

ad_dd.register_column (p_appl_short_name => application_short_name,
p_tab_name => table_name,
p_col_name => r_columns.column_name,
p_col_seq => r_columns.ROWNUM,
p_col_type => r_columns.data_type,
p_col_width => r_columns.data_length,
p_nullable => r_columns.nullable,
p_translate => 'N',
p_precision => r_columns.data_precision,
p_scale => r_columns.data_scale
);

END LOOP;

FOR r_constraints IN c_constraints (table_name, application_short_name)
LOOP

DBMS_OUTPUT.put_line ('Creating Primary Key Constraint ' || r_constraints.constraint_name);

SELECT DECODE (r_constraints.status,
'ENABLED', 'Y',
'N'
)
INTO status
FROM DUAL;

ad_dd.register_primary_key (p_appl_short_name => application_short_name,
p_key_name => r_constraints.constraint_name,
p_tab_name => table_name,
p_description => 'Primary Key for Table '|| table_name,
p_key_type => 'D',
p_audit_flag => 'N',
p_enabled_flag => status
);
END LOOP;

FOR r_constraint_columns IN c_constraint_columns (table_name, application_short_name)
LOOP

DBMS_OUTPUT.put_line ( 'Registering Primary Key Column '||
r_constraint_columns.column_name||
' for Constraint '||
r_constraint_columns.constraint_name);

ad_dd.register_primary_key_column (p_appl_short_name => application_short_name,
p_key_name => r_constraint_columns.constraint_name,
p_tab_name => table_name,
p_col_name => r_constraint_columns.column_name,
p_col_sequence => r_constraint_columns.POSITION
);
END LOOP;

END register_table;

The procedure will register the table, all the columns of the table and the primary key in Apps. It can be executed as
begin
register_table('XXAP_TEST_TABLE','SQLAP');
end;

Now that the script is in place, I could register the table and columns in single shot and I could get the table and columns in the LOVs.