Home » SQL & PL/SQL » SQL & PL/SQL » PLSQL to SQL (Release 9.2.0.8.0)
PLSQL to SQL [message #376517] Wed, 17 December 2008 12:18 Go to next message
Habeeb
Messages: 57
Registered: August 2000
Member
Hi All,
I want to compare common tables in all the schemas.
the table names have the schema attached at the end.
Example:
Schema DON table name TABLE_NAME_DON
Schema ACS table name TABLE_NAME_ACS
I have written a plsql which does the work, but can I get the same results in a query...
the plsql is as below..
 DECLARE
    CURSOR c1 IS
    SELECT distinct owner
    FROM   all_tables
    WHERE  owner in ('ACS', 'ACSGQ');
    CURSOR c2(p_owner varchar2) IS
    SELECT table_name
    FROM   all_tables
    WHERE  owner = p_owner
    AND    table_name LIKE 'TBL_%';
    CURSOR c3(p_owner varchar2,
              p_table_name varchar2) IS
    SELECT table_name
    FROM   all_tables
    WHERE  table_name LIKE 'TBL_%'
    AND    owner in ('ACS', 'ACSGQ')
    AND    owner <> p_owner
    AND    SUBSTR(table_name, 1, INSTR(table_name, '_', -1) -1) =
           SUBSTR(p_table_name, 1, INSTR(p_table_name, '_', -1) -1);
 BEGIN
    FOR c1rec IN c1 LOOP
        FOR c2rec IN c2(c1rec.owner) LOOP
            FOR c3rec in c3(c1rec.owner, c2rec.table_name) LOOP
                dbms_output.put_line('Owner: ' || c1rec.owner  || ' Table Name is: ' || c3rec.table_name);
            END LOOP;
       END LOOP;
   END LOOP;
 END;


Thanks...

Habeeb
Re: PLSQL to SQL [message #376520 is a reply to message #376517] Wed, 17 December 2008 12:32 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
May be i underestimate the problem.
Would just an intersection along with all the filters do that?
Re: PLSQL to SQL [message #376532 is a reply to message #376517] Wed, 17 December 2008 13:10 Go to previous messageGo to next message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
What kind of comparision you want , Column name change , data type change so on..

It can be simply done by following.

SELECT *
  FROM all_tab_columns a
 WHERE EXISTS (
         SELECT 'X'
           FROM all_tab_column b
          WHERE a.column_name = b.column_name
            AND a.datatype = b.data_type
            AND b.table_name = 'TABLE_NAME_DON'
            AND owner = 'DON')
   AND b.table_name = 'TABLE_NAME_ACS'
   AND owner = 'ACS'


Thanks
Trivendra
Re: PLSQL to SQL [message #376535 is a reply to message #376520] Wed, 17 December 2008 13:24 Go to previous messageGo to next message
Habeeb
Messages: 57
Registered: August 2000
Member
thanks...
the problem with the intersection is the table names are not same they have the schema name at the end.
so if the table name is EMPLOY_DON
and table_name is EMPLOY_ACS
the query should select
owner: DON table: EMPLOY_DON
owner: ACS table: EMPLOY_ACS
but if DON has a table NOT_FOUND_DON
and in ACS there is no table like NOT_FOUND% then
NOT_FOUND should be in the resultant query...

I hope I was able to explain a little better...

Habeeb
Re: PLSQL to SQL [message #376541 is a reply to message #376517] Wed, 17 December 2008 14:02 Go to previous messageGo to next message
Habeeb
Messages: 57
Registered: August 2000
Member
thanks...
I cannot hard code table names or owner.
if I write this statement in simple plain english it would be like this.
select all common table names from all_tables from the following owners ('DON', 'ACS', 'PIT'.....)
where the tables are same after taking out the schema names.
tables names for are example:

schema don
table1_don
table2_don
table3_don
...
...
table50_don

schema acs
table1_acs
table2_acs
table3_acs
...
...
table55_acs

schema pit
table1_pit
table2_pit
table3_pit
...
...
table100_pit

now my query should show only
tables from table1 to table50 as these are the only common tables after substr of the schema.

Habeeb
Re: PLSQL to SQL [message #376544 is a reply to message #376517] Wed, 17 December 2008 14:06 Go to previous messageGo to next message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
Are comparing DON schema form ACS and PIT

and _DON, _ACS and _PIT are post fix

[Updated on: Wed, 17 December 2008 14:07]

Report message to a moderator

Re: PLSQL to SQL [message #376545 is a reply to message #376517] Wed, 17 December 2008 14:16 Go to previous messageGo to next message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
May be this can help you ...

with all_tables as (
select 'TABLE1_DON' table_name ,'DON' owner from dual union all
select 'TABLE9_DON' table_name ,'DON' owner from dual union all
select 'TABLE10_DON' table_name ,'DON' owner from dual union all
select 'TABLE1_ACS' table_name ,'ACS' owner from dual union all
select 'TABLE2_ACS' table_name ,'ACS' owner from dual union all
select 'TABLE3_ACS' table_name ,'ACS' owner from dual union all
select 'TABLE1_PIT' table_name ,'PIT' owner from dual union all
select 'TABLE2_PIT' table_name ,'PIT' owner from dual)
SELECT *
  FROM all_tables a
 WHERE EXISTS (
         SELECT 'X'
           FROM all_tables b
          WHERE SUBSTR (a.table_name, 1, INSTR (table_name, '_ACS')) =
                    SUBSTR (b.table_name, 1, INSTR (b.table_name, DECODE (owner, 'ACS', '_ACS', 'PIT', '_PIT')))
            AND owner IN ('ACS', 'PIT'))
   AND owner = 'DON'




TABLE_NAME  OWN
----------- ---
TABLE1_DON  DON

1 row selected.


Is this you want.

Thanks
Trivendra

[Updated on: Wed, 17 December 2008 14:18]

Report message to a moderator

Re: PLSQL to SQL [message #376548 is a reply to message #376545] Wed, 17 December 2008 15:09 Go to previous messageGo to next message
Habeeb
Messages: 57
Registered: August 2000
Member
Very close:
Something like this:

TABLE_NAME OWN
----------- ---
TABLE1_DON DON
TABLE1_ACS ACS
TABLE1_PIT PIT

But there are some more then thousand tables combined in all the schema's.
SELECT *
  FROM all_tables a
 WHERE EXISTS (
         SELECT 'X'
           FROM all_tables b
          WHERE SUBSTR (a.table_name, 1, INSTR (table_name, '_ACS')) =
                    SUBSTR (b.table_name, 1, INSTR (b.table_name, DECODE (owner, 'ACS', '_ACS', 'PIT', '_PIT')))
            AND owner IN ('ACS', 'PIT'))
   AND owner = 'DON'


in the where clause the comparison is with '_ACS' this is has to be in a loop like mode first compare schema no 1 with all other schema's then compare schema 2 with the rest and so on, in every comparision pick the common table names as schema 1 may have some comman tables in schema 2 but not in schema 3 where as schema 2 may have some common table in schema 3 and 5.
the plsql I wrote works fine but there is lot of duplicates.
Thanks again to all..

Habeeb
Re: PLSQL to SQL [message #376587 is a reply to message #376517] Wed, 17 December 2008 23:36 Go to previous messageGo to next message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
Hi,

use ALL_TABLES and USER_TABLES.

Thanks
Trivnedra

Re: PLSQL to SQL [message #376766 is a reply to message #376517] Thu, 18 December 2008 08:57 Go to previous messageGo to next message
Habeeb
Messages: 57
Registered: August 2000
Member
This is the query I wrote.
SELECT SUBSTR(table_name,1,INSTR(table_name,'_',-1)-1), count(*)
FROM dba_tables 
WHERE owner IN ('ACS', 'ACSGQ', 'AG07', 'NSCG', 'SBO', 'GQVAL06', 'PTS', 'WIPSQA')
GROUP BY SUBSTR(table_name,1,INSTR(table_name,'_',-1)-1) 
HAVING count(*)> 1 
ORDER BY 2 DESC

It is very hard for me to go and physically verify that I am getting what I want. But to me it looks like the group by will do the thing, the only thing is that it does not show which is the owner.

thanks

Habeeb
Re: PLSQL to SQL [message #376776 is a reply to message #376766] Thu, 18 December 2008 09:24 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Quote:

SELECT SUBSTR(table_name,1,INSTR(table_name,'_',-1)-1), count(*)
FROM dba_tables
WHERE owner IN ('ACS', 'ACSGQ', 'AG07', 'NSCG', 'SBO', 'GQVAL06', 'PTS', 'WIPSQA')
GROUP BY SUBSTR(table_name,1,INSTR(table_name,'_',-1)-1)
HAVING count(*)> 1
ORDER BY 2 DESC


I don't think this will give you the required output what you require. Don't you think you are missing a condition regarding this owner. To translate the above query in plain english words it will be something like this.

List all the table_name where there exist atleast more than one table in the entire database (even in the same schema) whose first few characters are the same. But as I could see from your existing pl/sql you want to count it across the schema and output only when the same table name prefix is shared between schemas. Just to prove my point execute the following code and you will understand what I am trying to point to
with t
as
(select 'TABLE_1' table_name, 'ACS' owner from dual union all
 select 'TABLE_2', 'ACS' from dual union all
 select 'TABLE_NAME_1', 'ACS' from dual union all
 select 'TABLE_NAME_2', 'ACSGQ' from dual
)
SELECT SUBSTR(table_name,1,INSTR(table_name,'_',-1)-1), count(*)
FROM t
WHERE owner IN ('ACS', 'ACSGQ', 'AG07', 'NSCG', 'SBO', 'GQVAL06', 'PTS', 'WIPSQA')
GROUP BY SUBSTR(table_name,1,INSTR(table_name,'_',-1)-1) 
HAVING count(*)> 1 
ORDER BY 2 DESC

I will leave that part to you to solve it.

Regards

Raj
Re: PLSQL to SQL [message #376831 is a reply to message #376776] Thu, 18 December 2008 14:38 Go to previous message
Habeeb
Messages: 57
Registered: August 2000
Member
I checked with three different options.
Option 1.
the first PL/SQL I created I verified with that but instead of the
dbms_output.put_line('Owner: ' || c1rec.owner  || ' Table Name is: ' || c3rec.table_name);

is used a insert statement by creating a table temp, with owner and table_name as col
  insert into temp(owner, table_name)
  values (c3rec.owner, c3rec.table_name);

now this table had a lot of duplicate rows for example
Schema tabl_name
ACS TABLE_1_ACS
ACS TABLE_1_ACS
ACS TABLE_1_ACS
..SO ON
so in order to get rid of all those duplicate i used the below statement
Delete from temp
where rowid not in 
      (select min(rowid)
       from temp
       group by table_name);

Option 2.
then I checked with your statement, what you are doing with the "WITH" statement is a creation of dynamic view.
I created a view with the union all statement and then executed the below statement
select SUBSTR(a.table_name,1,INSTR(a.table_name,'_',-1)-1), b.owner
from all_schema_tables_view a, all_schema_tables_view b
where SUBSTR(a.table_name,1,INSTR(a.table_name,'_',-1)-1) = SUBSTR(b.table_name,1,INSTR(b.table_name,'_',-1)-1)
and   a.owner <> b.owner
group by SUBSTR(a.table_name,1,INSTR(a.table_name,'_',-1)-1), b.owner

Option 3.
SELECT SUBSTR(table_name,1,INSTR(table_name,'_',-1)-1), count(*)
FROM dba_tables 
WHERE owner IN ('ACS', 'ACSGQ', 'AG07', 'NSCG', 'SBO', 'GQVAL06', 'PTS', 'WIPSQA')
GROUP BY SUBSTR(table_name,1,INSTR(table_name,'_',-1)-1) 
HAVING count(*)> 1 
ORDER BY 2 DESC

all three are showing the exact tables.

Thanks to all..

Habeeb
Previous Topic: Question about tablespace
Next Topic: adding space between rows
Goto Forum:
  


Current Time: Fri Dec 09 12:02:03 CST 2016

Total time taken to generate the page: 0.11297 seconds