Categories:: System Functions (Information)

SYSTEM$REFERENCE¶

Returns a reference to an object (a table, view, or function). When you execute SQL actions on a reference to an object, the actions are performed using the role of the user who created the reference.

See also:: SYSTEM$QUERY_REFERENCE

Syntax¶

SYSTEM$REFERENCE('<object_type>', '<object_identifier>',
  [ , <use_session_scope> [ , '<privilege>' [ , '<privilege>' ... ] ] ] )

Copy

Arguments¶

Required

object_type

Type of the object. You can specify one of the following values:

table
view
function

object_identifier

Identifier for the object. If the identifier contains spaces or special characters, the entire string must be enclosed in double quotes. Identifiers enclosed in double quotes are also case-sensitive.

For more details about identifiers, see Identifier Requirements.

Optional

use_session_scope

If TRUE, specifies that the reference should be valid for the duration for the session. If this is FALSE or omitted, the reference is valid within the context in which it was created. See Specifying the Scope of the Reference.

Note: If you need to specify the privilege argument, the use_session_scope argument is required.

Default value: FALSE

privilege

Additional privilege that is needed to perform an SQL action on the object.

For example, suppose that you are passing the reference for a table to a stored procedure that inserts rows into that table. Specify 'INSERT' to confer the INSERT privilege on that table to the stored procedure.

By default, a reference confers the following privileges, based on the type of the object:

Object Type

Default Privilege Conferred

TABLE

SELECT

VIEW

SELECT

FUNCTION

USAGE

Object Type	Default Privilege Conferred
TABLE	SELECT
VIEW	SELECT
FUNCTION	USAGE

To specify more than one additional privilege, pass each privilege name as an additional argument to the function. For example, to confer the INSERT, UPDATE, and TRUNCATE privileges:

CALL myprocedure( SYSTEM$REFERENCE('TABLE', 'table_with_different_owner', true, 'INSERT'. 'UPDATE', 'TRUNCATE'));

Copy

Note that you cannot specify OWNERSHIP or ALL as privileges.

Returns¶

A reference to an object.

Usage Notes¶

Examples¶

See A Simple Example.