Containers
column name format A40 trunc
select id,name from ci_container_elements
/
This will output all names of all containers, even subcontainers. You can do something fancy to get the hierarchy if you need (but I’d just use the report then). Output looks like this:
ID NAME
--------- -----------------
1.773E+36 SYSTEM FOLDER
7.302E+35 TUTORIAL
7.341E+35 Moxcey
7.415E+35 MIS2000 (6)
Copy the NAME you need exactly as it appears for the other queries.
REM DES_ENT.SQL by Mike Moxcey
REM ====================================================
REM This gets the list of entities based on a container name
REM You can get the container name by running DES_CONTAINER.SQL
REM ===================================================
select ENT.name, ENT.short_name
from ci_folder_members APPENT,
ci_entities
ENT
where APPENT.member_object=ENT.id
and APPENT.folder_reference in
(select id from ci_container_elements
where name = 'MIS2000 (6)'
)
order by name
/
You can see how I copy&pasted the container name directly into
the query. There are probably ways to do this differently, but I'm
only ever working in one container at a time so this is quick enough.
Sample output:
SQL> start des_ent
NAME
SHORT_NAME
-------------------- ----------
ACTIVITY
ACT
ACTIVITY UOM ACTUOM
ADDRESS
ADDR
ADDRESS CONTACT TYPE ADDCONTTYP
rem DES_ENTATT.SQL by Mike
Moxcey
rem ==================================================
rem To get Entity Names and Attributes for a quick and dirty report
rem ==================================================
break on entity skip 1
column entity format A20 trunc
column attribute format a20 trunc
set pagesize 60 linesize 60
spool des_entatt.lst
select ENT.name Entity, ATT.name Attribute, ATT.format
from ci_folder_members APPENT,
ci_entities
ENT, ci_attributes ATT
where APPENT.member_object=ENT.id
and ENT.id = ATT.entity_reference
and APPENT.folder_reference in
(select id from ci_container_elements
where name = 'MIS2000 (6)'
)
order by ENT.name, ATT.name
/
spool off
Sample output
SQL> start des_entatt
ENTITY
ATTRIBUTE
FORMAT
-------------------- -------------------- ----------
ACTIVITY
ABBR
VARCHAR2
CLASS
VARCHAR2
ID
NUMBER
NAME
VARCHAR2
REMARKS
VARCHAR2
ADDRESS
DIRECTIONS
VARCHAR2
ID
NUMBER
LINE 1
VARCHAR2
LINE 2
VARCHAR2
LINE 3
VARCHAR2
POSTAL CODE NUMBER
rem DES_ENTATTDOM.SQL by
Mike Moxcey
rem ======================================================
rem To get Entity Names, Attributes,
rem and Domains for a quick and dirty report
rem ======================================================
break on entity skip 1
column entity format A20 trunc
column attribute format a20 trunc
column domain like entity
set pagesize 60 linesize 60
spool des_entattdom.lst
select ENT.name Entity, ATT.name Attribute, ATT.format, DOM.name
Domain
from ci_folder_members APPENT,
ci_entities
ENT,
ci_attributes
ATT, ci_domains DOM
where APPENT.member_object=ENT.id
and ENT.id = ATT.entity_reference
and ATT. domain_reference = DOM.id(+)
and APPENT.folder_reference in
(select id from ci_container_elements
where name = 'MIS2000 (6)'
)
order by ENT.name, ATT.name
/
spool off
Sample Output. It shows those that don’t have domains, too.
EMPLOYEE WORK ROLE ABBR
VARCHAR2 ABBREVIATION
ID
NUMBER ID_NR
NAME
VARCHAR2 NAME
REMARKS
VARCHAR2 REMARKS
GEOGRAPHIC AREA GSA CITY CODE
VARCHAR2
GSA CITY NAME VARCHAR2
GSA COUNTY CODE VARCHAR2
GSA COUNTY NAME VARCHAR2
GSA LOCATION CODE VARCHAR2
GSA STATE ABBREV CHAR
STATE_CODE
GSA STATE CODE VARCHAR2
GSA STATE NAME VARCHAR2
ID
NUMBER ID_NR
rem DES_DOMVALS.SQL by Mike
Moxcey
rem ======================================================
rem To get Domains that have values in them
rem ======================================================
set pagesize 60 linesize 60
spool des_domvals.lst
ttitle left 'Domains with Values' skip 2
column domain format A15 trunc
column attribute like domain
column low_value like domain
column abbreviation like domain
break on domain on attribute
select dom.name Domain, att.name attribute, atv.low_value,
atv.abbreviation
from ci_attribute_values
atv, ci_domains DOM,
ci_folder_members
APPENT, ci_entities ENT,
ci_attributes
ATT
where APPENT.member_object=ENT.id
and ENT.id = ATT.entity_reference
and ATT.domain_reference = DOM.id
and atv.domain_reference= DOM.id
and APPENT.folder_reference in
(select id from ci_container_elements
where name = 'MIS2000 (6)'
)
order by 1, 2
/
spool off
Sample Output.
Domains with Values
DOMAIN ATTRIBUTE
LOW_VALUE ABBREVIATION
--------------- --------------- --------------- ---------------
LKP_LANDCLASS ABBR
Private Land PRIV
BLM Land BLM
Forest Service FS
Indian Land BIA
Other Public La PUB
rem DES_ATTVALS.SQL by Mike
Moxcey
rem ======================================================
rem To get Attributes that have values in them
rem ======================================================
set pagesize 60 linesize 60
spool des_attvals.lst
column name format A20
column low_value like name
ttitle left 'Domains with Values' skip 2
select ENT.name, ATT.name, ATTVAL.abbreviation, ATTVAL.low_value
from ci_attribute_values
ATTVAL, ci_attributes ATT,
ci_folder_members
APPENT, ci_entities ENT
where attribute_reference = ATT.id
and APPENT.member_object=ENT.id
and ENT.id = ATT.entity_reference
and APPENT.folder_reference in
(select id from ci_container_elements
where name = 'MIS2000 (6)'
)
/
spool off
No sample output because I've cleaned it up and have put all values
into domains, not in attributes.