E
Oracle Files SQL View Reference

This appendix describes the eight SQL views installed with Oracle Files. Topics include:

Using SQL Views

Oracle Files provides eight SQL views that you can examine to see the effects of changes made to the Oracle Files schema. For example, if you use XML to quickly add new users, you can examine the view to confirm the changes. Typically, you would use the views as you're developing and debugging custom code. The views provide a means to confirm programmatic changes to the schema.

The eight default views are owned by the Oracle Files schema owner, ifssys (default schema name; yours may be different). These read-only SQL views are created with the Oracle Files schema and are for informational purposes only. Grant the SELECT permission to users who need access to these views.

Note:

The Oracle Files views should be used exclusively by system administrators. The data in the views bypasses the Oracle Files ACL security; therefore, caution should be taken in granting users access to these views.

The names of objects in the view may not be unique. Therefore, IDs are provided. Any column of type date has two columns in the view, for example:

CREATE_DATE--An internal representation of the create date.
CREATE_DATE_GMT--The standard Oracle9i platform date format, based on Greenwich Mean Time.

The Views

These eight schema views are provided. Each view is described in a separate table which provides the name of the column, whether the column can be null, the type of column, and the length for each column.

View: ifs_versioned_documents

This view shows a list of all documents which are versioned.

Column Name	Null?	Type of Column and Length
`Modified_By`	Yes	`VARCHAR2 (700)`
`Modifier_ID`	Yes	`NUMBER (20)`
`Creator`	Yes	`VARCHAR (700)`
`Creator_ID`	Yes	`NUMBER (20)`
`Owner`	Yes	`VARCHAR (700)`
`Owner_ID`	Yes	`NUMBER (20)`
`FAMILY`	Yes	`VARCHAR2(700)`
`FAMILY_ID`	Yes	`NUMBER(20)`
`FORMAT`	Yes	`VARCHAR2(700)`
`FORMAT_ID`	Yes	`NUMBER(20)`
`DOCUMENT_SIZE`	Yes	`NUMBER(20)`
`CHARACTERSET`	Yes	`VARCHAR2(40)`
`LANGUAGE`	Yes	`VARCHAR2(40)`
`MEDIA`	Yes	`VARCHAR2(700)`
`MEDIA_ID`	Yes	`NUMBER(20)`
`ACL`	Yes	`VARCHAR2(700)`
`ACL_ID`	Yes	`NUMBER(20)`
`NAME`	Yes	`VARCHAR2(700)`
`DOCUMENT_ID`	Yes	`NUMBER(20)`
`DESCRIPTION`	Yes	`VARCHAR2(2000)`
`CREATE_DATE`	Yes	`NUMBER(20)`
`CREATE_DATE_GMT`	Yes	`DATE`
`MODIFIED_DATE`	Yes	`NUMBER(20)`
MODIFIED_DATE_GMT	Yes	DATE
`EXPIRATION_DATE`	Yes	`NUMBER(20)`
`EXPIRATION_DATE_GMT`	Yes	`DATE`
`LOCKED`	Yes	`NUMBER(1)`
`READ_BY_OWNER`	Yes	`NUMBER(1)`

View: ifs_all_documents

This view shows a list of all documents.

Column Name	Null?	Type of Column and Length
`MODIFIED_BY`	Yes	`VARCHAR2 (700)`
`MODIFIER_ID`	Yes	`NUMBER (20)`
`CREATOR`	Yes	`VARCHAR (700)`
`CREATOR_ID`	Yes	`NUMBER (20)`
`OWNER`	Yes	`VARCHAR (700)`
`OWNER_ID`	Yes	`NUMBER (20)`
`FORMAT`	Yes	`VARCHAR2(700)`
`FORMAT_ID`	Yes	`NUMBER(20)`
`DOCUMENT_SIZE`	Yes	`NUMBER(20)`
`CHARACTERSET`	Yes	`VARCHAR2(40)`
`LANGUAGE`	Yes	`VARCHAR2(40)`
`MEDIA`	Yes	`VARCHAR2(700)`
`MEDIA_ID`	Yes	`NUMBER(20)`
`ACL`	Yes	`VARCHAR2(700)`
`ACL_ID`	Yes	`NUMBER(20)`
`NAME`	Yes	`VARCHAR2(700)`
`DOCUMENT_ID`	Yes	`NUMBER(20)`
`DESCRIPTION`	Yes	`VARCHAR2(2000)`
`CREATE_DATE`	Yes	`NUMBER(20)`
`CREATE_DATE_GMT`	Yes	`DATE`
`MODIFIED_DATE`	Yes	`NUMBER(20)`
`MODIFIED_DATE_GMT`	Yes	`DATE`
`EXPIRATION_DATE`	Yes	`NUMBER(20)`
`EXPIRATION_DATE_GMT`	Yes	`DATE`
`LOCKED`	Yes	`NUMBER(1)`
`READ_BY_OWNER`	Yes	`NUMBER(1)`

View: ifs_folders

This view shows all folders and their attributes.

Column Name	Null?	Type of Column and Length
`MODIFIED_BY`	Yes	`VARCHAR2 (700)`
`MODIFIER_ID`	Yes	`NUMBER (20)`
`CREATOR`	Yes	`VARCHAR (700)`
`CREATOR_ID`	Yes	`NUMBER (20)`
`OWNER`	Yes	`VARCHAR (700)`
`OWNER_ID`	Yes	`NUMBER (20)`
`ACL`	Yes	`VARCHAR2(700)`
`ACL_ID`	Yes	`NUMBER(20)`
`NAME`	Yes	`VARCHAR2(700)`
`FOLDER_ID`	Yes	`NUMBER(20)`
`DESCRIPTION`	Yes	`VARCHAR2(2000)`
`CREATE_DATE`	Yes	`NUMBER(20)`
`CREATE_DATE_GMT`	Yes	`DATE`
`MODIFIED_DATE`	Yes	`NUMBER(20)`
`MODIFIED_DATE_GMT`	Yes	`DATE`
`EXPIRATION_DATE`	Yes	`NUMBER(20)`
`EXPIRATION_DATE_GMT`	Yes	`DATE`
`LOCKED`	Yes	`NUMBER(1)`

View: ifs_folder_items

This view lists all folders and the top level items.

Column Name	Null?	Type of Column and Length
`CREATOR`	Yes	`VARCHAR (700)`
`CREATOR_ID`	Yes	`NUMBER (20)`
`OWNER`	Yes	`VARCHAR (700)`
`OWNER_ID`	Yes	`NUMBER (20)`
`TYPE`	No	`VARCHAR2(700)`
`NAME`	Yes	`VARCHAR2(700)`
`DESCRIPTION`	Yes	`VARCHAR2(2000)`
`CREATE_DATE`	Yes	`NUMBER(20)`
`CREATE_DATE_GMT`	Yes	`DATE`
`OBJECT_ID`	Yes	`VARCHAR2(700)`
`ACL`	Yes	`VARCHAR2(700)`
`ACL_ID`	Yes	`NUMBER(20)`
`FOLDER_NAME`	Yes	`VARCHAR2 (700)`
`FOLDER_ID`	Yes	`NUMBER(20)`

View: ifs_users

This view lists all users.

Column Name	Null?	Type of Column and Length
`MODIFIED_BY`	Yes	`VARCHAR2 (700)`
`MODIFIER_ID`	Yes	`NUMBER (20)`
`CREATOR`	Yes	`VARCHAR (700)`
`CREATOR_ID`	Yes	`NUMBER (20)`
`OWNER`	Yes	`VARCHAR (700)`
`OWNER_ID`	Yes	`NUMBER (20)`
`ACL`	Yes	`VARCHAR2(700)`
`ACL_ID`	Yes	`NUMBER(20)`
`NAME`	Yes	`VARCHAR2(700)`
`USER_ID`	Yes	`NUMBER(20)`
`DESCRIPTION`	Yes	`VARCHAR2(2000)`
`CREATE_DATE`	Yes	`NUMBER(20)`
`CREATE_DATE_GMT`	Yes	`DATE`
`DISTINGUISHED_NAME`	Yes	`VARCHAR2(700)`
`ADMIN_ENABLED`	Yes	`NUMBER(1)`
`CREDENTIAL_MANAGER`	Yes	`VARCHAR2(700)`
`UNIQUE_NAME`	Yes	`VARCHAR2(700)`
`HOME_FOLDER_NAME`	Yes	`VARCHAR2(700)`
`HOME_FOLDER_ID`	Yes	`NUMBER(20)`

View: ifs_groups

This view lists all groups.

Column Name	Null?	Type of Column and Length
`MODIFIED_BY`	Yes	`VARCHAR2 (700)`
`MODIFIER_ID`	Yes	`NUMBER (20)`
`CREATOR`	Yes	`VARCHAR (700)`
`CREATOR_ID`	Yes	`NUMBER (20)`
`OWNER`	Yes	`VARCHAR (700)`
`OWNER_ID`	Yes	`NUMBER (20)`
`NAME`	Yes	`VARCHAR2(700)`
`GROUP_ID`	Yes	`NUMBER(20)`
`DESCRIPTION`	Yes	`VARCHAR2(2000)`
`CREATE_DATE`	Yes	`NUMBER(20)`
`CREATE_DATE_GMT`	Yes	`DATE`
`ACL`	Yes	`VARCHAR2(700)`
`ACL_ID`	Yes	`NUMBER(20)`

View: ifs_group_members

This view lists groups and their direct members.

Column Name	Null?	Type of Column and Length
`MEMBER_NAME`	Yes	`VARCHAR2 (700)`
`MEMBER_ID`	Yes	`NUMBER (20)`
`MEMBER_DESCRIPTION`	Yes	`VARCHAR (700)`
`MEMBER_CREATE_DATE`	Yes	`NUMBER (20)`
`MEMBER_CREATE_DATE_GMT`	Yes	`DATE`
`GROUP_ID`	Yes	`NUMBER(20)`
`TYPE`	No	`VARCHAR2(2000)`

View: ifs_acls

This view lists all ACLs in Oracle Files.

Column Name	Null?	Type of Column and Length
`CONTROLLING_ACL_NAME`	Yes	`VARCHAR2 (700)`
`CONTROLLING_ACL_ID`	Yes	`NUMBER (20)`
`NAME`	Yes	`VARCHAR2(700)`
`ACL_ID`	Yes	`NUMBER(20)`
`DESCRIPTION`	Yes	`VARCHAR2(2000)`
`CREATE_DATE`	Yes	`NUMBER(20)`
`CREATE_DATE_GMT`	Yes	`DATE`
`MODIFIED_BY`	Yes	`VARCHAR (700)`
`MODIFIER_ID`	Yes	`NUMBER (20)`
`CREATOR`	Yes	`VARCHAR (700)`
`CREATOR_ID`	Yes	`NUMBER (20)`
`OWNER`	Yes	`VARCHAR2(700)`
`OWNER_ID`	Yes	`NUMBER(20)`

Example: Selecting Attributes of a Document

This example uses ifs_versioned_documents to select attributes. To accomplish this, the following command is run:

SQL> select name, format, document_size, media, document_id, acl, owner from 
ifs_versioned_documents

The following table displays the output.

Name	Format	Document_Size	Media	ACL	Document_ID	Owner
Business Plan	MS Word	11954	IndexedBlob	Published	7902	jsmith
June Report	Postscript	121	NonIndexedBlob	Private	7936	lfrench
Oracle logo	JPEG	121	NonIndexedBlob	Published	7926	pbrown

E Oracle Files SQL View Reference