CODEWITHSUNDEEP
sqloracle-database
Arda Nalbant
Arda Nalbant

Reputation: 491

Finding lines where a table is inserted and updated with dba_source

I want to find all insert, update , and merge lines in whole dba

what i tried:

SELECT owner, name, type,line,text
FROM dba_source where regexp_like(text,'into my_table')
union all
SELECT owner, name, type,line,text
FROM dba_source where regexp_like(text,'update my_table')

but nothing returns from my select function.

EDIT: The return must contain specific table. Return should not contain tables like "my_table_2" or "my_table_temp"

Upvotes: 0

Views: 906

Answers (4)

MT0
MT0

Reputation: 167867

As I mentioned in comments, one issue could be the case of the text. Another could be the number of white-space characters between the characters.

SELECT owner,
       name,
       type,
       line,
       text
FROM   dba_source
WHERE  REGEXP_LIKE(
         text,
         '(into|update)\s+table_name(\s|$)',
             -- Match both conditions and any amount of white-space
         'i' -- Case insensitive
       )

You then need to deal with other case where:

  • The table identifier is on the next line from the INTO or UPDATE keyword;
  • The table identifier has a schema prefix SCHEMA_NAME.TABLE_NAME; or
  • The an identifier is quoted "TABLE_NAME" or "SCHEMA_NAME"."TABLE_NAME";
  • A combination of the above.

Which would give you an expression like:

SELECT owner,
       name,
       type,
       line,
       text
FROM   dba_source
WHERE  REGEXP_LIKE(
         text,
         '(into|update)\s+(("?)schema_name\3\.)?("?)table_name\4(\s|$)',
             -- Match both conditions, schema, quotes and any amount of white-space
         'i' -- Case insensitive
       )

You could also use LAG or LEAD to look for statements spread across lines.

Upvotes: 2

Sudipta Mondal
Sudipta Mondal

Reputation: 2572

You are not using a very good method to search. You will not get any results, if the source is saved in the below way - 

insert
  into
 any_table

Because there are multiple new lines, so your query from dba_source/all_source will fail.

A better way to find that is - 

select * from dba_dependencies
 where referenced_name = 'TABLE_NAME|SYNONYM_NAME'
   and referenced_type = 'TABLE|SYNONYM'

Example,

suppose you have a PROCEDURE XYZ, which has the below construct

create or replace procedure xyz
begin    
    insert 
      into 
    any_table ....
end;

Then, if you run the below query

select * from dba_dependencies
 where referenced_name = 'any_table'
   and referenced_type = 'TABLE';

The result of the above query will give you 'XYZ' as a record.

Upvotes: 3

Thorsten Kettner
Thorsten Kettner

Reputation: 94884

There are several things to consider:

  1. there may be space between the keyword INSERT and the table name
  2. there may be a schema name preceding the table name (my_schema.my_table)
  3. the table name may be in upper or lower case or a mix of them
  4. excluding my_table_xxx for a search on my_table, as you already mentioned

Here is a query for this:

SELECT owner, name, type, line, text
FROM dba_source 
WHERE regexp_like(text, '(update|into)[[:space:]]+my_table($|\s|\W)', 'i')
   OR regexp_like(text, '(update|into)[[:space:]]+[[:alpha:]]+\.my_table($|\s|\W)', 'i');

However, this still doesn't cover the case where you have INSERT in one line and the table name in the next.

Upvotes: 1

prabhat mishra
prabhat mishra

Reputation: 202

Try this:-

SELECT owner, name, type,line,text
FROM dba_source where regexp_like(UPPER(text),'INTO YOUR_TABLE_NAME')
union all
SELECT owner, name, type,line,text
FROM dba_source where regexp_like(UPPER(text),'UPDATE YOUR_TABLE_NAME');

Upvotes: 0

Related Questions