Link to home
Start Free TrialLog in
Avatar of Prisoner362670
Prisoner362670

asked on

Get a table-name where the value of a certain column is a certain value

Greeting to All Experts,
I hope that one you can point me in the right direction this morning.

I am trying to delete a certain customer number from all tables.
I have created a subquery which returns all of the tables which contain the customerId field.  The subquery seems to work when I isolate it returning 253 table names.

However, I can't figure out how to get the table_name where the customerId field = '1234'.
iSQLPlus returns the error that 'customerId' is an invalid identfier.

My code so far:
SELECT table_name
FROM (SELECT TABLE_name FROM USER_TAB_COLUMNS WHERE COLUMN_name='CustomerId')
where CustomerId = '1234';

Any assistance that you can offer would be most gratefully received.
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

First, you need caps for Oracle object names:

WHERE COLUMN_name='CUSTOMERID'

If you then want to look up tables where a column has a certain value, you need dynamic sql.

If no other Expert posts a solution I'll see what I can come up with later.
try this...
ee.txt
or this...
ee.txt
Avatar of Prisoner362670

ASKER

I don't mean to leave ya'll hanging but I got pulled into another project for a few days.  I will be back tomorrow--I have printed out the two possible solutions so that I can take a look at them this evening.
ERROR at line 2:
ORA-00923: FROM keyword not found where expected
what query generated that error?
Different errors today from both codesets pasted below....

first codeset with string 'A015363' and '/' added.  I also ran with line 9 'WHERE column_name = 'A015363':
SQL> DECLARE
  2      v_search   varchar2(50) := 'A015363';
  3      v_dummy    number;
  4  BEGIN
  5      DBMS_OUTPUT.put_line(RPAD('Table Name', 35) || 'Column Name');
  6  
  7     FOR x IN (  SELECT   table_name, column_name
  8                    FROM   user_tab_columns
  9                   WHERE   data_type IN ('VARCHAR2')      -- add other string types if needed
 10                ORDER BY   table_name, column_name)
 11      LOOP
 12           EXECUTE IMMEDIATE   'select count(*) from '
 13                           || x.table_name
 14                           || ' where '
 15                           || x.column_name
 16                           || ' = '''
 17                           || v_search
 18                           || ''' and rownum = 1'
 19              INTO   v_dummy;
 20  
 21          IF v_dummy = 1
 22          THEN
 23              DBMS_OUTPUT.put_line(RPAD(x.table_name, 35) || x.column_name);
 24          END IF;
 25      END LOOP;
 26  END;
 27  /
DECLARE
*
ERROR at line 1:
ORA-00933: SQL command not properly ended
ORA-06512: at line 12

second codeset with column_name 'JCRTNO' and = 'A015363':

SQL> SELECT table_name,
  2         TO_NUMBER(
  3             EXTRACTVALUE(
  4                 xmltype(
  5                     DBMS_XMLGEN.
  6                      getxml(
  7                            'select count(*) X from '
  8                         || table_name
  9                         || ' where '
 10                         || column_name
 11                         || ' = ''A015363'''
 12                     )
 13                 ),
 14                 '/ROWSET/ROW/X'
 15             )
 16         )
 17             COUNT
 18  FROM all_tab_cols
 19  --- if you know the column name put it here
 20  -- or, if you want to search all columns then remove the where clause
 21  WHERE column_name = 'JCRTNO';  
ERROR:
ORA-19202: Error occurred in XML processing
ORA-01722: invalid number
ORA-06512: at "SYS.DBMS_XMLGEN", line 176
ORA-06512: at line 1
no rows selected
When I just ran my own little version look what I got at line 176...

ORA-19202: Error occurred in XML processing
ORA-04063: view "a_specific_table_name" has errors
ORA-06512: at "SYS.DBMS_XMLGEN", line 176
ORA-06512: at line 1

when I ...

Select * from a_specific_table_name;

I get:

ERROR at line 1:
ORA-04063: view "a_specific_table_name" has errors
I ran the first block and got no errors at all,  here's a modified version to capture the statement that fails.


DECLARE
    v_search   VARCHAR2(50) := 'A015363';
    v_dummy    NUMBER;
    v_sql      VARCHAR2(1000);
BEGIN
    DBMS_OUTPUT.put_line(RPAD('Table Name', 35) || 'Column Name');

    FOR x IN (SELECT   table_name, column_name
                  FROM user_tab_columns
                 WHERE data_type IN ('VARCHAR2')                 -- add other string types if needed
              ORDER BY table_name, column_name)
    LOOP
        v_sql      :=
               'select count(*) from '
            || x.table_name
            || ' where '
            || x.column_name
            || ' = '''
            || v_search
            || ''' and rownum = 1';

        BEGIN
            EXECUTE IMMEDIATE v_sql INTO v_dummy;
        EXCEPTION
            WHEN OTHERS
            THEN
                DBMS_OUTPUT.put_line('Error!!');
                DBMS_OUTPUT.put_line(SQLERRM);
                DBMS_OUTPUT.put_line(v_sql);
        END;

        IF v_dummy = 1
        THEN
            DBMS_OUTPUT.put_line(RPAD(x.table_name, 35) || x.column_name);
        END IF;
    END LOOP;
END;
/
you could add a filter to either version to check for invalid views and skip them.


AND  table_name not in (select object_name from user_objects where status != 'VALID')

or,  if you are using the ALL views instead of the USER views


AND  (owner, table_name) not in (select owner,object_name from all_objects where status != 'VALID')
"a_specific_table_name' has a datatype of the column I'm looking at as 'undefined'.
is it actually a view? and if so, is the view correct? maybe it's referring to a table or column that doesn't exist
PL/SQL procedure successfully completed. (!!!!!!!!!!!!!!)
Just one question- where does it write out to?
dbms output buffer

if you're using sqlplus, before executing the procedure....

set serveroutput on


if you're using other tools like toad or pl/sql developer there are tabs to display the output but you need to turn on output capture
turning 'set serveroutput on' leads to:
Table Name                         Column Name
Error!!
ORA-00933: SQL command not properly ended
select count(*) from BIN$4qdNCNipRmSYHsWlAfoGWA==$0 where V_TABLES = 'A015363'
and rownum = 1
Error!!
ORA-00933: SQL command not properly ended
select count(*) from BIN$y6qcGxqYTQ6PtHyDnU7iXg==$0 where V_TABLES = 'A015363'
and rownum = 1
DEMOGRAPHY_ORIG                    JCRTNO
HODG_CLINICAL_TAB                  JCRTNO
HODG_COMPLICATIONS_TAB             JCRTNO
HODG_FOLLOW_UP_TAB                 JCRTNO
HODG_STAGING_TAB                   JCRTNO
HODG_TREATMENT_TAB                 JCRTNO
Error!!
ORA-01858: a non-numeric character was found where a numeric was expected
select count(*) from ISOTOPE1 where CURRENT_DATE = 'A015363' and rownum = 1
Error!!
ORA-00920: invalid relational operator
select count(*) from PROTOCOL_SURV_ANALYSIS_VIEW where DIED OF DISEASE =
'A015363' and rownum = 1
Error!!
ORA-00920: invalid relational operator
select count(*) from PROTOCOL_SURV_ANALYSIS_VIEW where FIRST FAILURE = 'A015363'
and rownum = 1
Error!!
ORA-00920: invalid relational operator
select count(*) from PROTOCOL_SURV_ANALYSIS_VIEW where SCORE LOCAL = 'A015363'
and rownum = 1
Error!!
ORA-00920: invalid relational operator
select count(*) from PROTOCOL_SURV_ANALYSIS_VIEW where SCORE OPP BREAST =
'A015363' and rownum = 1

PL/SQL procedure successfully completed.
ASKER CERTIFIED SOLUTION
Avatar of Sean Stuber
Sean Stuber

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Here is the final working code--it never occured to me that an identifier field used in multiple tables would have different data_types.  The code below outputs to the screen the same return set that I developed manually.  I have also modified this same code to output to a text file.

As you can tell I am somewhat of a duffer in PL/SQL but I had a great learning experience with this project.  I have all respect and gratitude for you sdstuber!

Working code tested in Oracle 10g-

set serveroutput on

DECLARE
    v_search   VARCHAR2(50) := 'A015363';
    v_dummy    NUMBER;
    v_sql      VARCHAR2(1000);
BEGIN
    DBMS_OUTPUT.put_line(RPAD('Table Name', 35) || 'Column Name');

    FOR x IN (SELECT   table_name, column_name
                  FROM user_tab_columns
                 WHERE column_name = 'JCRTNO'
                  AND data_type IN ('VARCHAR2','CHAR') -- add other string types if needed
              ORDER BY table_name, column_name)
    LOOP
        v_sql      :=
               'select count(*) from '
            || x.table_name
            || ' where '
            || x.column_name
            || ' = '''
            || v_search
            || ''' and rownum = 1';

        BEGIN
            EXECUTE IMMEDIATE v_sql INTO v_dummy;
        EXCEPTION
            WHEN OTHERS
            THEN
                DBMS_OUTPUT.put_line('Error!!');
                DBMS_OUTPUT.put_line(SQLERRM);
                DBMS_OUTPUT.put_line(v_sql);
        END;

        IF v_dummy = 1
        THEN
            DBMS_OUTPUT.put_line(RPAD(x.table_name, 35) || x.column_name);
        END IF;
    END LOOP;
END;
/
I would like to award more points (1000+) due to the persistance that the expert extended to me over several weeks.