asked on

select table_name, from all tables, where column_name = 'vendorname' and vendorname = 'vendor'

Is there a way to retrieve the information that I am looking for here in this statement:

1 select
2 ' '||table_name|| ';'
3 from dba_tab_columns where column_name = 'VE_CD'

''||TABLE_NAME||';'
--------------------------------
.......
VC;
VE;
VE$VE_ADDR_TP;
VE$VE_SRT;
VE$VE_TP;
VE2CO;
VE2UP_CHG;
VE_AUDIT;
VE_CMNT;
VE_LEAD$MNR;
.......

91 rows selected

From this information I would like to add:

and ve_cd = '&vendor'

But, this is the error I receive:

and column_name.ve_cd = '3M'
*
ERROR at line 4:
ORA-00904: "COLUMN_NAME"."VE_CD": invalid identifier

Basically, instead of a count, I would like the statement to retrieve the table names where the vendor 3M lives.

slightwv (䄆 Netminder)

You cannot do this with a simple join.

The select from user_tables gets the object names. Then you need to select from those objects.

There is a neat trick using XML with dynamic SQL that will get what you want.

You will just need to tweak it to use your table select.

https://www.experts-exchange.com/questions/28069987/identifying-columns-in-all-the-tables-with-a-specific-value.html?anchorAnswerId=39001854#a39001854

drozeveld

ASKER

Would you be willing to highlight this statement where I need to insert MY INFO/DATA.

SELECT table_name,
column_name,
TO_NUMBER(
EXTRACTVALUE(
DBMS_XMLGEN.getxmltype(
'select count(*) X from ' || table_name || ' where ' || column_name || ' = -1'
),
'/ROWSET/ROW/X'
)
)
COUNT
FROM all_tab_cols
WHERE owner = 'MY_SCHEMA'
AND table_name LIKE '%FCT'
AND column_name IN ('REG_ID', 'USR_ID', 'CAT_ID', 'SPR_ID')
ORDER BY table_name, column_name;

slightwv (䄆 Netminder)

This will be untested but...

Well you already have the query for the tables and columns so

change:
FROM all_tab_cols
WHERE owner = 'MY_SCHEMA'
AND table_name LIKE '%FCT'
AND column_name IN ('REG_ID', 'USR_ID', 'CAT_ID', 'SPR_ID')

to:
FROM dba_tab_columns where column_name = 'VE_CD'

then you are looking for a specific code so,

change:
'select count(*) X from ' || table_name || ' where ' || column_name || ' = -1'

to:
'select count(*) X from ' || table_name || ' where ' || column_name || ' = ''3M'''

drozeveld

ASKER

I'm able to get the statement to run with what I think is my data:

1 SELECT table_name,
2 column_name,
3 TO_NUMBER(
4 EXTRACTVALUE(
5 DBMS_XMLGEN.getxmltype(
6 'select count(*) X from ' || table_name || ' where ' ||
column_name || ' = ve_cd '
7 ),
8 '/ROWSET/ROW/X'
9 )
10 )
11 COUNT
12 FROM dba_tab_columns
13 WHERE column_name = 'VE_CD'
14 AND table_name LIKE '%FCT'
15 AND column_name = '&VE_CD'
16* ORDER BY table_name, column_name
live> /
Enter value for ve_cd: LAZB
old 15: AND column_name = '&VE_CD'
new 15: AND column_name = 'LAZB'

no rows selected

live>

But, I know for a fact that something should return for our Vendor LAZB.

I'm confused by what I need to enter in the place of FCT in line 14.

I understand that my table = dba_tab_columns
and my column_name = VE_CD, but I don't necessarily know what my table name is, and to list them all, I would need to write 91 statements for all the tables that VE_CD lives in.

Help. :) Thanks!!

slightwv (䄆 Netminder)

I think this is the problem:
WHERE column_name = 'VE_CD'

You hard code the column name here then add an additional check here "AND column_name = '&VE_CD'".

A single column name cannot be equal to 'VE_CD' and 'LAZB' at the same time.

>>I'm confused by what I need to enter in the place of FCT in line 14.

You don't enter anything if you want to look in ALL tables that has the column name.

If all 91 end with 'FCT' then what you have should be correct.

See if this test case is more what you are after. It prompts you for the column to look in for a hard-coded value of 'Z'.

drop table tab1 purge;
create table tab1( co1l char(1));

drop table tab2 purge;
create table tab2(co1l char(1), LAZB char(1));

drop table tab3 purge;
create table tab3(LAZB char(1), col2 char(1));


insert into tab1 values('a');
insert into tab2 values('a','Z');
insert into tab2 values('a','Z');
insert into tab2 values('a','Z');
insert into tab2 values('a','Z');
insert into tab3 values('Z','a');
insert into tab3 values('Z','a');
commit;


SELECT table_name,
  column_name,
  TO_NUMBER(
  EXTRACTVALUE(
  DBMS_XMLGEN.getxmltype(
  'select count(*) X from ' || table_name || ' where ' || column_name || ' = ''Z'''
  ),
  '/ROWSET/ROW/X'
  )
 )
 COUNT
 FROM dba_tab_columns
 WHERE column_name = '&VE_CD'
ORDER BY table_name, column_name
/

Open in new window

Output from that test case:

TABLE_NAME
--------------------------------------------------------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
     COUNT
----------
TAB2
LAZB
         4

TAB3
LAZB
         2

Open in new window

drozeveld

ASKER

This is what it returns:

ERROR at line 1:
ORA-00933: SQL command not properly ended

live> create table tab1( co1l char(1))
*
ERROR at line 1:
ORA-00955: name is already used by an existing object

live> live> drop table tab2 purge
*
ERROR at line 1:
ORA-00933: SQL command not properly ended

live> create table tab2(co1l char(1), LAZB char(1))
*
ERROR at line 1:
ORA-00955: name is already used by an existing object

live> live> drop table tab3 purge
*
ERROR at line 1:
ORA-00933: SQL command not properly ended

live> create table tab3(LAZB char(1), col2 char(1))
*
ERROR at line 1:
ORA-00955: name is already used by an existing object

live> live> live>
1 row created.

live>
1 row created.

live>
1 row created.

live>
1 row created.

live>
1 row created.

live>
1 row created.

live>
1 row created.

live>
Commit complete.

slightwv (䄆 Netminder)

I ran what I posted using sqlplus against an 11g database.

What version of Oracle are you using?
What tool are you using to run this?

Can you try sqlplus?

drozeveld

ASKER

This is the version I'm running.

SQL*Plus: Release 9.2.0.1.0 - Production on Tue Apr 22 16:37:21 2014

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

We have SQL in our dba menu, so I was using that as the administrator. I tried in SQL*Plus under my OraHome92 Application Development and I receive an error message on line 12.

I think I'm receiving the error because I'm logging into SQL*Plus as the user, and not the administrator. I don't know the administrator password, because I don't have to use one through my DBA menu. I've tried a few combinations, but I'm having no luck getting in. I'll have to contact my support center.

Thanks for your help today.

slightwv (䄆 Netminder)

not sure if the XML solution will work under 9i but let's hold off on that for a minute.

live> live> drop table tab2 purge
*
ERROR at line 1:
ORA-00933: SQL command not properly ended

Remove the 'purge' on the drop commands. I'm pretty sure that is 10g and above syntax.

drozeveld

ASKER

I was able to make this statement run. I was also able to enter a vendor and the statement gave me counts:

Enter value for ve_cd: LAZB
old 13: WHERE column_name = '&VE_CD'
new 13: WHERE column_name = 'LAZB'

TABLE_NAME COLUMN_NAME COUNT
------------------------------ ------------------------------ ----------
TAB2 LAZB 4
TAB2 LAZB 4
TAB3 LAZB 2
TAB3 LAZB 2

Unfortunately, this leaves me right where I began with my question. I'm trying to figure out how to have the system tell me what tables the vendor LAZB still lives in.

slightwv (䄆 Netminder)

>>I'm trying to figure out how to have the system tell me what tables the vendor LAZB still lives in.

If LAZB is the vendor code you are looking for then my last example isn't correct. I got confused from your post above http:#a40016085

Give me a few minutes to tweak the example.

drozeveld

ASKER

I was using vendor LAZB as a test because I know there should still be data in our system for that vendor.

I would like the statement to tell me if any vendor that I choose is still in our system, and then if it is, tell me which tables it's living in so I can figure out why it is still there after I have run all of my pre-programmed purging reports.

Hopefully that makes it clearer. :)

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

drozeveld

ASKER

Well, I could see that there were errors - something about error on line 1 - number to large to insert maybe?

The reason I can't be more specific is because SQL Plus went into Not Responding mode and I had to kill it.

slightwv (䄆 Netminder)

>>Well, I could see that there were errors - something about error on line 1 - number to large to insert maybe?

I ran what I posted using 11gR2.

Once you get reconnected, try it again. The XML in 9i was VERY buggy. You may be hitting a bug.

drozeveld

ASKER

Yay!! This is what I've been looking for! I had to take the "PURGE" commands out. This is what I got:

.....

TABLE_NAME        COLUMN_NAME      COUNT
------------------------------      ------------------------------      ----------
SPO_LIST_GRP        VE_CD      0
SPO_LIST_GRP_TMP        VE_CD      0
SPO_OPTION        VE_CD      0
SPO_OPTION_TMP        VE_CD      0
SPO_OPT_CMNT        VE_CD      0
SPO_OPT_VAL        VE_CD      0
SPO_OPT_VAL_TMP        VE_CD      0
SPO_REVERSE_CONDITIONS        VE_CD      0
SPO_REVERSE_CONDITIONS_TMP      VE_CD      0
SPSR_GTEMP        VE_CD      0
TAB2        VE_CD      1

TABLE_NAME        COLUMN_NAME      COUNT
------------------------------      ------------------------------      ----------
TAB3        VE_CD      0
UNSUITABLE_IVC2RCV        VE_CD      0
VC        VE_CD      0
VE        VE_CD      1
VE$VE_ADDR_TP        VE_CD      1
VE$VE_SRT        VE_CD      0
VE$VE_TP        VE_CD      1
VE2CO        VE_CD      0
VE2UP_CHG        VE_CD      0
VE_AUDIT VE_CD      0
VE_CMNT        VE_CD      0

TABLE_NAME        COLUMN_NAME      COUNT
------------------------------      ------------------------------      ----------
VE_LEAD$MNR VE_CD      0
VE_MO_HST        VE_CD      0
VE_PUR_PMT_HST        VE_CD      16
VE_YTD_PMT_HST        VE_CD      4
WW_PO_AUDIT1_V        VE_CD      0

93 rows selected.

Only question I have now, is what does it mean when there is a count in the TAB2 table, highlighted in Bold above?

slightwv (䄆 Netminder)

>>what does it mean when there is a count in the TAB2 table, highlighted in Bold above?

That means that the VE_CD column in TAB2 had 1 row with the vendor code you were looking for.

drozeveld

ASKER

TAB2 is a fictitious table from which the statement created, though, right?

slightwv (䄆 Netminder)

TAB2 was just a table for the test case I mocked up.

Typically you don't run test cases that create and drop tables in a 'real' database or at least not as the real user.

You should have a development database with a development user to test things like this.

What if I named my test table employees? First thing my script does is drop the table. If you ran it in a real system, you might accidently drop a VERY IMPORTANT table.

drozeveld

ASKER

Taking out the words PURGE for version 9xxxx worked!! Thank you!!