SET_FILTER Procedure

SET_FILTER specifies restrictions on the objects to be retrieved, for example, the object name or schema.

Syntax

PROCEDURE set_filter
  (handle IN NUMBER,
   name IN VARCHAR2,
   value IN VARCHAR2);
PROCEDURE set_filter		
  (handle IN NUMBER,
   name IN VARCHAR2,
   value IN BOOLEAN DEFAULT TRUE);

Parameters

Table 28-4 SET_FILTER Parameters

Parameter	Description
handle	The handle returned from OPEN.
name	The name of the filter. For each filter, Table 28-5 lists the object_type it applies to, its name, its datatype (text or Boolean) and its meaning or effect (including its default value, if any).
value	The value of the filter.

Table 28-5 SET_FILTER: Filters

Object Type	Name	Datatype	Meaning
Named objects	NAME	text	Objects with this exact name are selected.
	NAME_EXPR	text	The filter value is the right-hand side of a SQL comparison, i.e., a SQL comparison operator (=,!=, etc.) and the value compared against. The value must contain parentheses and quotation marks where appropriate. In particular, two single quotes (not a double quote) are needed to represent an apostrophe. For example: 'IN (''DEPT'',''EMP'')' The filter value is combined with the object attribute corresponding to the object name to produce a WHERE condition in the query that fetches the objects. In the example above, objects named DEPT and EMP are retrieved. By default, all named objects of object_type are selected.
Schema objects	SCHEMA	text	Objects in this schema are selected.
	SCHEMA_EXPR	text	The filter value is the right-hand side of a SQL comparison. The filter value is combined with the object attribute corresponding to the object schema to produce a WHERE condition in the query that fetches the objects. See NAME_EXPR for syntax details. Default: - if BASE_OBJECT_SCHEMA is specified (see below), then objects in that schema are selected; - otherwise, objects in the current schema are selected. See "Security" .
PACKAGE, TYPE	SPECIFICATION	Boolean	If TRUE, retrieve the package or type specification. Defaults to TRUE.
	BODY	Boolean	If TRUE, retrieve the package or type body. Defaults to TRUE.
TABLE	TABLESPACE	text	Tables in this tablespace (or having this as their default tablespace) are selected.
	TABLESPACE_EXPR	text	The filter value is the right-hand side of a SQL comparison. The filter value is combined with the object attribute corresponding to the object tablespace or default tablespace to produce a WHERE condition in the query that fetches the objects. See NAME_EXPR for syntax details. By default, objects in all tablespaces are selected.
INDEX, OBJECT_GRANT, TRIGGER	BASE_OBJECT_NAME	text	Indexes, triggers, or privileges are selected that are defined or granted on objects with this name. Specify SCHEMA for triggers on schemas. Specify DATABASE for database triggers.
	BASE_OBJECT_SCHEMA	text	Indexes, triggers, or privileges are selected that are defined or granted on objects in this schema. If BASE_OBJECT_NAME is specified with a value other than SCHEMA or DATABASE, this defaults to the current schema.
INDEX, TRIGGER	SYSTEM_GENERATED	Boolean	If TRUE, select indexes or triggers even if they are system-generated. If FALSE, omit system-generated indexes or triggers. Defaults to TRUE.
OBJECT_GRANT, SYSTEM_GRANT	GRANTEE	text	Privileges are selected that are granted to this user or role. Specify PUBLIC for grants to PUBLIC.
OBJECT_GRANT	GRANTOR	text	Privileges are selected that are granted by this user.
SYNONYM	LONGNAME	text	A synonym name longer than 30 characters. Synonyms with this exact name are selected. If the synonym name is 30 characters or less, the NAME filter must be used.
	LONGNAME_EXPR	text	The filter value is the right-hand side of a SQL comparison. The filter value is combined with the object attribute corresponding to the long name of the object to produce a WHERE condition in the query that fetches the objects. See NAME_EXPR for syntax details. By default no filtering is done on the long name of a synonym.
All objects	CUSTOM_FILTER	text	The text of a WHERE condition. The condition is appended to the query that fetches the objects. By default, no custom filter is used. The other filters are intended to meet the needs of the majority of users. Use CUSTOM_FILTER when no defined filters exists for your purpose. Of necessity such a filter depends on the detailed structure of the UDTs and views used in the query that are defined in admin/catmeta.sql. Because filters may change from version to version, upward compatibility is not guaranteed.

Exceptions

• INVALID_ARGVAL. A NULL or invalid value was supplied for an input parameter. The error message text identifies the parameter.

• INVALID_OPERATION. SET_FILTER was called after the first call to FETCH_xxx for the OPEN context. After the first call to FETCH_xxx is made, no further calls to SET_FILTER for the current OPEN context are permitted.

• INCONSISTENT_ARGS. The filter name is not valid for the object type associated with the OPEN context, or the filter value is the wrong datatype.

Security

With SET_FILTER, you can specify the schema of objects to be retrieved, but security considerations may override this specification. If the caller is SYS or has SELECT_CATALOG_ROLE, then any object can be retrieved; otherwise, only the following can be retrieved:

• Schema objects owned by the caller

• Public synonyms

• System privileges granted to the caller or to PUBLIC

• Grants on objects for which the caller is owner, grantor or grantee (either explicitly or as PUBLIC).

If you request objects that you are not privileged to retrieve, no exception is raised; the object is not retrieved, as if it did not exist.

Usage Notes

These rules apply to dependent objects such as triggers, grants, and indexes.

• When connected as a nonprivileged user: If BASE_OBJECT_NAME is specified as a filter, BASE_OBJECT_SCHEMA defaults to the current schema:

  dbms_metadata.set_filter(h,'BASE_OBJECT_NAME','EMP');
  

• When connected as a privileged user with SELECT_CATALOG_ROLE: The schema defaults to BASE_OBJECT_SCHEMA if specified; otherwise it defaults to the current schema. For example, to see all indexes in SCOTT that are defined on SCOTT.EMP, the filters are:

  dbms_metadata.set_filter(h,'BASE_OBJECT_NAME','EMP');
  dbms_metadata.set_filter(h,'BASE_OBJECT_SCHEMA','SCOTT');
  
  

  To see indexes in other schemas:

  dbms_metadata.set_filter(h,'SCHEMA_EXPR','LIKE ''%''');
  
  

Some indexes and triggers are system generated (such as indexes used to enforce unique constraints). Set the SYSTEM_GENERATED filter to FALSE so that you do not retrieve them.