Solved

Oracle SQL Syntax

Posted on 2016-09-19
8
76 Views
Last Modified: 2016-09-21
Hello Experts,

Is there a way to search an Oracle 12c database to find a specific value in a specific column for all tables that have that column? For example, if the column name is CASE_ID and the value that I am looking for is the string 'abcdefghijk' , I want to know all tables that have the CASE_ID column populated with that value.
0
Comment
Question by:jsmith08
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
8 Comments
 
LVL 35

Accepted Solution

by:
johnsone earned 500 total points
ID: 41804936
As a one time exercise, I would do it this way:
SELECT 'select ''' 
       || owner 
       || ''' owner, ''' 
       || table_name 
       || ''' table_name, count(1) match_count from ' 
       || owner 
       || '.' 
       || table_name 
       || ' where case_id = ''abcdefghijk'';' 
FROM   dba_tab_columns 
WHERE  column_name = 'CASE_ID'; 

Open in new window

This will give you a list of SQL commands to run.  Spool the output from this query into a file and that will give you a script to run.  Then just run that script file and it will give you a count for every table that contains a column named CASE_ID.
0
 
LVL 32

Expert Comment

by:awking00
ID: 41804962
Using PL/SQL with dynamic sql can also get you what you need.
declare
v_sql varchar2(255);
v_search varchar2(255) := 'abcdefghijk';
v_cnt pls_integer;
begin
for t in
(select table_name from user_tables
 where column_name = 'CASE_ID'
 order by table_name)
loop
v_sql = 'select count(*) from '||t.table_name||' where case_id = '||v_search;
execute immediate v_sql into v_cnt;
if v_cnt > 0 then
 dbms_output.put_line(t.table_name||' is populated with '||v_search;
end if;
end loop;
end;
/
You may want to change user_tables to all_tables or dba_tables depending on your need and privileges.
0
 
LVL 35

Expert Comment

by:johnsone
ID: 41804988
You don't need nested loops in PL/SQL to do it.  Use the same query generating SQL that you would use to spool the statements, then just loop through the results.
DECLARE 
    v_cnt   PLS_INTEGER; 
    v_owner VARCHAR2(30); 
    v_table VARCHAR2(30); 
BEGIN 
    FOR c1rec IN (SELECT 'select ''' 
                         || owner 
                         || ''' owner, ''' 
                         || table_name 
                         || ''' table_name, count(1) match_count from ' 
                         || owner 
                         || '.' 
                         || table_name 
                         || ' where case_id = ''abcdefghijk''' stmt 
                  FROM   dba_tab_columns 
                  WHERE  column_name = 'CASE_ID') LOOP 
        EXECUTE IMMEDIATE c1rec.stmt INTO v_owner, 
                                          v_table, 
                                          v_cnt; 

        dbms_output.Put_line(v_owner 
                             || '.' 
                             || v_table 
                             || ' = ' 
                             || v_cnt); 
    END LOOP; 
END; 

/ 

Open in new window

This is the beauty of using SQL to generate SQL statements.  You don't need to keep iterating through loops to get to the statement.  You need dynamic SQL anyway, just run one query to generate the full statement for you.

I don't care for doing it this way.  Dynamic SQL isn't very efficient and DBMS_OUTPUT can be troublesome if there are a lot of tables involved (you can blow out the internal buffer pretty easily, even at maximum).  Scripts and spooling get around this.
0
How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

 

Author Comment

by:jsmith08
ID: 41805148
When I try the 1st suggestion:
SELECT 'select '''
       || owner
       || ''' owner, '''
       || table_name
       || ''' table_name, count(1) match_count from '
       || owner
       || '.'
       || table_name
       || ' where case_id = ''abcdefghijk'';'
FROM   all_tab_columns
WHERE  column_name = 'CASE_ID';

it appears to be returning a row for every table that has that column name regardless of
whether or not my value really exists in that table because when I do a simple select *
from... on several of the tables, it doesn't return any rows.

When I try the 2nd suggestion:
declare
v_sql varchar2(255);
v_search varchar2(255) := 'abcdefghijk';
v_cnt pls_integer;
begin
for t in
(select table_name from all_tab_columns
 where column_name = 'CASE_ID'
 order by table_name)
loop
v_sql = 'select count(*) from '||t.table_name||' where case_id = '||v_search;
execute immediate v_sql into v_cnt;
if v_cnt > 0 then
 dbms_output.put_line(t.table_name||' is populated with '||v_search;
end if;
end loop;
end;
/

I get the following error report:

Error report:
ORA-06550: line 11, column 7:
PLS-00103: Encountered the symbol "=" when expecting one of the following:

   := . ( @ % ;
ORA-06550: line 12, column 1:
PLS-00103: Encountered the symbol "EXECUTE"
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

When I try the 3rd suggestion:

DECLARE
    v_cnt   PLS_INTEGER;
    v_owner VARCHAR2(30);
    v_table VARCHAR2(30);
BEGIN
    FOR c1rec IN (SELECT 'select '''
                         || owner
                         || ''' owner, '''
                         || table_name
                         || ''' table_name, count(1) match_count from '
                         || owner
                         || '.'
                         || table_name
                         || ' where case_id = ''abcdefghijk''' stmt
                  FROM   all_tab_columns
                  WHERE  column_name = 'CASE_ID') LOOP
        EXECUTE IMMEDIATE c1rec.stmt INTO v_owner,
                                          v_table,
                                          v_cnt;

        dbms_output.Put_line(v_owner
                             || '.'
                             || v_table
                             || ' = '
                             || v_cnt);
    END LOOP;
END;

/

I  get the following error report:

Error report:
ORA-00933: SQL command not properly ended
ORA-06512: at line 17
00933. 00000 -  "SQL command not properly ended"
*Cause:    
*Action:
0
 
LVL 35

Expert Comment

by:johnsone
ID: 41805156
The first one should return a row for every table that has a column CASE_ID in it.  There is a count on each one.  You can always filter out the counts of 0 from the output.

This is the second one with the syntax errors corrected:
DECLARE 
    v_sql    VARCHAR2(255); 
    v_search VARCHAR2(255) := 'abcdefghijk'; 
    v_cnt    PLS_INTEGER; 
BEGIN 
    FOR t IN (SELECT table_name 
              FROM   all_tab_columns 
              WHERE  column_name = 'CASE_ID' 
              ORDER  BY table_name) LOOP 
        v_sql := 'select count(*) from ' 
                 ||t.table_name 
                 ||' where case_id = ' 
                 ||v_search; 

        EXECUTE IMMEDIATE v_sql INTO v_cnt; 

        IF v_cnt > 0 THEN 
          dbms_output.Put_line(t.table_name 
                               ||' is populated with ' 
                               ||v_search); 
        END IF; 
    END LOOP; 
END; 

/ 

Open in new window

However, since ALL_TAB_COLUMNS is used, no owner is selected or added to the dynamic query, so it will fail if the user you are running the block as does not own the table.

The third one works perfectly fine on my system.  No errors.
0
 
LVL 32

Expert Comment

by:awking00
ID: 41805377
Sorry for the confusion. v_sql = 'select ...' was meant to be v_sql := 'select ...'
johnsone, thanks for the correction.
0
 
LVL 35

Expert Comment

by:johnsone
ID: 41805400
There was also a missing ) on the DBMS_OUTPUT function call.

No problem.  I already had a window open so just threw it in to get the syntax fixed.

I'm not sure why people that ask questions aren't capable of figuring that out on their own.  Most of the time we just free type these in.
0
 

Author Comment

by:jsmith08
ID: 41808841
Thank you both for your help. The first suggestion ended up being the best option for my level of privileges.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question