Solved

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

Posted on 2014-04-22
21
909 Views
Last Modified: 2014-04-23
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.
0
Comment
Question by:drozeveld
  • 10
  • 10
21 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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.

http://www.experts-exchange.com/Database/Oracle/Q_28069987.html#a39001854
0
 
LVL 1

Author Comment

by:drozeveld
Comment Utility
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;
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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'''
0
 
LVL 1

Author Comment

by:drozeveld
Comment Utility
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!!
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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

0
 
LVL 1

Author Comment

by:drozeveld
Comment Utility
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.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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?
0
 
LVL 1

Author Comment

by:drozeveld
Comment Utility
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.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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.
0
 
LVL 1

Author Comment

by:drozeveld
Comment Utility
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.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>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.
0
 
LVL 1

Author Comment

by:drozeveld
Comment Utility
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.  :)
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
Comment Utility
Try this one:
--
drop table tab1 purge;
create table tab1( co1l char(1), dummy_column varchar2(10));

drop table tab2 purge;
create table tab2(co1l char(1), VE_CD varchar2(10));

drop table tab3 purge;
create table tab3(VE_CD varchar2(10), col2 char(1));


--tab1 should never be returned since the column name isn't correct
insert into tab1 values('a','3M');
insert into tab1 values('a','LABZ');
-- has LABZ and 3M
insert into tab2 values('a','Z');
insert into tab2 values('a','LABZ');
insert into tab2 values('a','3M');
insert into tab2 values('a','Z');
-- has LABZ only
insert into tab3 values('LABZ','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 VE_CD=''&Vendor_code'''
  ),
  '/ROWSET/ROW/X'
  )
 )
 COUNT
 FROM dba_tab_columns
 WHERE column_name = 'VE_CD'
ORDER BY table_name, column_name
/

Open in new window

0
 
LVL 1

Author Comment

by:drozeveld
Comment Utility
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.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>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.
0
 
LVL 1

Author Comment

by:drozeveld
Comment Utility
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?
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>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.
0
 
LVL 1

Author Comment

by:drozeveld
Comment Utility
TAB2 is a fictitious table from which the statement created, though, right?
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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.
0
 
LVL 1

Author Closing Comment

by:drozeveld
Comment Utility
Taking out the words PURGE for version 9xxxx worked!!  Thank you!!
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

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…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
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 information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

744 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now