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.
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.
try this...
ee.txt
ee.txt
or this...
ee.txt
ee.txt
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.
ASKER
ERROR at line 2:
ORA-00923: FROM keyword not found where expected
ORA-00923: FROM keyword not found where expected
what query generated that error?
ASKER
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_na me, 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
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(
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(
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
ASKER
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
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('Erro r!!');
DBMS_OUTPUT.put_line(SQLER RM);
DBMS_OUTPUT.put_line(v_sql );
END;
IF v_dummy = 1
THEN
DBMS_OUTPUT.put_line(RPAD( x.table_na me, 35) || x.column_name);
END IF;
END LOOP;
END;
/
DECLARE
v_search VARCHAR2(50) := 'A015363';
v_dummy NUMBER;
v_sql VARCHAR2(1000);
BEGIN
DBMS_OUTPUT.put_line(RPAD(
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('Erro
DBMS_OUTPUT.put_line(SQLER
DBMS_OUTPUT.put_line(v_sql
END;
IF v_dummy = 1
THEN
DBMS_OUTPUT.put_line(RPAD(
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')
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')
ASKER
"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
ASKER
PL/SQL procedure successfully completed. (!!!!!!!!!!!!!!)
Just one question- where does it write out to?
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
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
ASKER
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_VIE W where DIED OF DISEASE =
'A015363' and rownum = 1
Error!!
ORA-00920: invalid relational operator
select count(*) from PROTOCOL_SURV_ANALYSIS_VIE W where FIRST FAILURE = 'A015363'
and rownum = 1
Error!!
ORA-00920: invalid relational operator
select count(*) from PROTOCOL_SURV_ANALYSIS_VIE W where SCORE LOCAL = 'A015363'
and rownum = 1
Error!!
ORA-00920: invalid relational operator
select count(*) from PROTOCOL_SURV_ANALYSIS_VIE W where SCORE OPP BREAST =
'A015363' and rownum = 1
PL/SQL procedure successfully completed.
Table Name Column Name
Error!!
ORA-00933: SQL command not properly ended
select count(*) from BIN$4qdNCNipRmSYHsWlAfoGWA
and rownum = 1
Error!!
ORA-00933: SQL command not properly ended
select count(*) from BIN$y6qcGxqYTQ6PtHyDnU7iXg
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_VIE
'A015363' and rownum = 1
Error!!
ORA-00920: invalid relational operator
select count(*) from PROTOCOL_SURV_ANALYSIS_VIE
and rownum = 1
Error!!
ORA-00920: invalid relational operator
select count(*) from PROTOCOL_SURV_ANALYSIS_VIE
and rownum = 1
Error!!
ORA-00920: invalid relational operator
select count(*) from PROTOCOL_SURV_ANALYSIS_VIE
'A015363' and rownum = 1
PL/SQL procedure successfully completed.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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('Erro r!!');
DBMS_OUTPUT.put_line(SQLER RM);
DBMS_OUTPUT.put_line(v_sql );
END;
IF v_dummy = 1
THEN
DBMS_OUTPUT.put_line(RPAD( x.table_na me, 35) || x.column_name);
END IF;
END LOOP;
END;
/
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(
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('Erro
DBMS_OUTPUT.put_line(SQLER
DBMS_OUTPUT.put_line(v_sql
END;
IF v_dummy = 1
THEN
DBMS_OUTPUT.put_line(RPAD(
END IF;
END LOOP;
END;
/
ASKER
I would like to award more points (1000+) due to the persistance that the expert extended to me over several weeks.
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.