| SDO_GTYPE count and group by based on tables in all_tab_cols? [message #609233] |
Tue, 04 March 2014 04:51  |
 |
psnepvangers
Messages: 2
Registered: March 2014
Location: Nederland
|
Junior Member |
|
|
Hello all,
I've got the following query:
SELECT GEO.GEOMETRY.SDO_GTYPE "TYPE GEOMETRY", COUNT(*) FROM SCHEMA.TREES GEO GROUP BY GEO.GEOMETRY.SDO_GTYPE;
Now, I want to do this to every table where the column GEOMETRY exists:
SELECT TABLE_NAME FROM ALL_TAB_COLS WHERE COLUMN_NAME = 'GEOMETRY' AND OWNER = '&SCHEMA';
This will result in the following:
TABLE_NAME
TREES
GREENPATCHES
Now, I can't seem to find out how I can run the first query depending on what I get in the second query. Can anyone help me with this?
Thanks in advance,
Peter
|
|
|
|
|
|
|
|
|
|
| Re: SDO_GTYPE count and group by based on tables in all_tab_cols? [message #609298 is a reply to message #609295] |
Wed, 05 March 2014 04:06  |
 |
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Quote:This seems to be a select count on each table where the GEOMETRY column exists.
Yes it is as you said:
Quote:I want to do this to every table where the column GEOMETRY exists:
Quote:(see the first query).
Your query which does not give you what you want does not help us to understand what you actually want.
Quote: My plan is however to use the query to find out which tables have what SDO object types,
Something like:
select owner, table_name, data_type, count(*)
from dba_tab_columns
where data_type_owner = 'MDSYS'
group by owner, table_name, data_type
order by owner, table_name, data_type
/
SQL> select owner, table_name, data_type, count(*)
2 from dba_tab_columns
3 where data_type_owner = 'MDSYS'
4 group by owner, table_name, data_type
5 order by owner, table_name, data_type
6 /
OWNER TABLE_NAME DATA_TYPE COUNT(*)
------------------------------ ------------------------------ ------------------------------ ----------
MDSYS ALL_SDO_GEOM_METADATA SDO_DIM_ARRAY 1
MDSYS ALL_SDO_INDEX_METADATA SDO_GEOMETRY 1
MDSYS ALL_SDO_STYLES SDO_GEOMETRY 1
MDSYS CS_SRS SDO_GEOMETRY 1
MDSYS DBA_SDO_STYLES SDO_GEOMETRY 1
MDSYS MY_SDO_INDEX_METADATA SDO_GEOMETRY 1
MDSYS SDO_COORD_OP_PARAM_VALS XMLTYPE 1
MDSYS SDO_COORD_REF_SYS SDO_GEOMETRY 1
MDSYS SDO_COORD_REF_SYSTEM SDO_GEOMETRY 1
MDSYS SDO_CS_SRS SDO_GEOMETRY 1
MDSYS SDO_GEOM_METADATA_TABLE SDO_DIM_ARRAY 1
MDSYS SDO_INDEX_METADATA_TABLE SDO_GEOMETRY 1
MDSYS SDO_STYLES_TABLE SDO_GEOMETRY 1
MDSYS SDO_TOPO_METADATA_TABLE SDO_TOPO_GEOMETRY_LAYER_ARRAY 1
MDSYS USER_SDO_GEOM_METADATA SDO_DIM_ARRAY 1
MDSYS USER_SDO_INDEX_METADATA SDO_GEOMETRY 1
MDSYS USER_SDO_STYLES SDO_GEOMETRY 1
OE CUSTOMERS SDO_GEOMETRY 1
OE WAREHOUSES SDO_GEOMETRY 1
[Updated on: Wed, 05 March 2014 04:06] Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
