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.

Note

As an alternative to calling this function, you can use the TABLE keyword, if you need to create a reference to an object that you don’t plan to modify (for example, if you are passing in a table that the stored procedure will query) and you want that reference to be valid for the scope of the call (rather than for the entire session). See Using the TABLE keyword to create a reference to a table, view, or query.

See also:

SYSTEM$QUERY_REFERENCE

语法

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

实参

必填

'object_type'

对象的类型。您可以指定以下值中的一个：

  • api_integration
  • compute_pool
  • database
  • external_access_integration
  • external_table
  • external_volume
  • function
  • materialized_view
  • policy
  • pipe
  • procedure
  • row_access_policy
  • secret
  • schema
  • table
  • tag
  • task
  • view
  • warehouse
'object_identifier'

对象的标识符。如果标识符包含空格或特殊字符，则整个字符串必须放在双引号内。放在双引号内的标识符也区分大小写。

For more details about identifiers, see Identifier requirements.

可选

'reference_scope'

指定引用的范围。

If 'CALL' or omitted, specifies that the reference is valid within the context in which it was created. See Specifying the scope of the reference.

If 'SESSION', specifies that the reference should be valid for the duration for the session.

If 'PERSISTENT', specifies that the reference should be valid until the object is dropped. See persistent references.

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

有效值：

  • 'CALL'
  • 'SESSION'
  • 'PERSISTENT'

Default value: 'CALL'

'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.

For a list of supported objects and privileges, see Supported object types and privileges for references.

若要指定多个其他权限，请将每个权限名称作为其他实参传递给函数。例如，要授予 INSERT、UPDATE 和 TRUNCATE 权限，请执行以下操作：

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

请注意，不能指定 OWNERSHIP 或 ALL 作为权限。

返回

可以用作标识符的引用的序列化字符串表示形式。

使用说明

The 'object_type' argument must match the type of the object specified by object_identifier.

故障排除

以下场景可以帮助您解决可能发生的问题。

Error
505028 (42601): Object type <object_type> does not match the specified type <type_of_the_specified_object> for reference creation
Cause

If you try to create a reference using the SYSTEM$REFERENCE function and the object_type argument does not match the type of the object specified by object_identifier, the function fails. For example, if the object_type argument is TABLE, but object_identifier resolves to an object type other than TABLE (for example, VIEW), the function fails.

Solution

Verify that the type of the object specified by object_identifier matches the object_type argument. For a list of supported object types, see Supported object types and privileges for references.

示例

See Background: The problem with passing objects and queries to stored procedures.