NAME_RESOLVE procedure - Obtain the schema and other membership information for a database object

The NAME_RESOLVE procedure provides the capability to obtain the schema and other membership information of a database object. Synonyms are resolved to their base objects.

Syntax

Parameters

name

An input argument of type VARCHAR(1024) that specifies the name of the database object to resolve. Can be specified in the format:

[[ a.]b.]c[@dblink ]

context

An input argument of type INTEGER. Set to the following values:

1 - to resolve a function, procedure, or module name;
2 - to resolve a table, view, sequence, or synonym name; or
3 - to resolve a trigger name.

schema

An output argument of type VARCHAR(128) that specifies the name of the schema containing the object specified by name.

part1

An output argument of type VARCHAR(128) that specifies the name of the resolved table, view, sequence, trigger, or module.

part2

An output argument of type VARCHAR(128) that specifies the name of the resolved function or procedure (including functions and procedures within a module).

dblink

An output argument of type VARCHAR(128) that specifies name of the database link (if @dblink is specified in name).

part1_type

An output argument of type INTEGER. Returns the following values:

2 - resolved object is a table;
4 - resolved object is a view;
6 - resolved object is a sequence;
7 - resolved object is a stored procedure;
8 - resolved object is a stored function;
9 - resolved object is a module or a function or procedure within a module; or
12 - resolved object is a trigger.

object_number

An output argument of type INTEGER that specifies the object identifier of the resolved database object.

Authorization

EXECUTE privilege on the DBMS_UTILITY module.

Examples

Example 1: The following stored procedure is used to display the returned values of the NAME_RESOLVE procedure for various database objects.

SET SERVEROUTPUT ON@

CREATE OR REPLACE PROCEDURE name_resolve(
  IN p_name VARCHAR(4096), 
  IN p_context DECFLOAT )
BEGIN
  DECLARE v_schema VARCHAR(30);
  DECLARE v_part1 VARCHAR(30);
  DECLARE v_part2 VARCHAR(30);
  DECLARE v_dblink VARCHAR(30);
  DECLARE v_part1_type DECFLOAT;
  DECLARE v_objectid DECFLOAT;

  CALL DBMS_UTILITY.NAME_RESOLVE(p_name, p_context, v_schema, v_part1, v_part2,
    v_dblink, v_part1_type, v_objectid);
  CALL DBMS_OUTPUT.PUT_LINE('name      : ' || p_name);
  CALL DBMS_OUTPUT.PUT_LINE('context   : ' || p_context);
  CALL DBMS_OUTPUT.PUT_LINE('schema    : ' || v_schema);
  IF v_part1 IS NULL THEN
    CALL DBMS_OUTPUT.PUT_LINE('part1     : NULL');
  ELSE
    CALL DBMS_OUTPUT.PUT_LINE('part1     : ' || v_part1);
  END IF;
  IF v_part2 IS NULL THEN
    CALL DBMS_OUTPUT.PUT_LINE('part2     : NULL');
  ELSE
    CALL DBMS_OUTPUT.PUT_LINE('part2     : ' || v_part2);
  END IF;
  IF v_dblink IS NULL THEN
    CALL DBMS_OUTPUT.PUT_LINE('dblink    : NULL');
  ELSE
    CALL DBMS_OUTPUT.PUT_LINE('dblink    : ' || v_dblink);
  END IF;
  CALL DBMS_OUTPUT.PUT_LINE('part1 type: ' || v_part1_type);
  CALL DBMS_OUTPUT.PUT_LINE('object id : ' || v_objectid);
END@

DROP TABLE S1.T1@
CREATE TABLE S1.T1 (C1 INT)@

CREATE OR REPLACE PROCEDURE S2.PROC1
BEGIN
END@

CREATE OR REPLACE MODULE S3.M1@
ALTER MODULE S3.M1 PUBLISH FUNCTION F1() RETURNS BOOLEAN
BEGIN
  RETURN TRUE;
END@

CALL NAME_RESOLVE( 'S1.T1', 2 )@
CALL NAME_RESOLVE( 'S2.PROC1', 2 )@
CALL NAME_RESOLVE( 'S2.PROC1', 1 )@
CALL NAME_RESOLVE( 'PROC1', 1 )@
CALL NAME_RESOLVE( 'M1', 1 )@
CALL NAME_RESOLVE( 'S3.M1.F1', 1 )@

This example results in the following output:

SET SERVEROUTPUT ON
DB20000I  The SET SERVEROUTPUT command completed successfully.

CREATE OR REPLACE PROCEDURE name_resolve(
  IN p_name VARCHAR(4096), 
  IN p_context DECFLOAT )
BEGIN
  DECLARE v_schema VARCHAR(30);
  DECLARE v_part1 VARCHAR(30);
  DECLARE v_part2 VARCHAR(30);
  DECLARE v_dblink VARCHAR(30);
  DECLARE v_part1_type DECFLOAT;
  DECLARE v_objectid DECFLOAT;

  CALL DBMS_UTILITY.NAME_RESOLVE(p_name, p_context, v_schema, v_part1, v_part2,
    v_dblink, v_part1_type, v_objectid);
  CALL DBMS_OUTPUT.PUT_LINE('name      : ' || p_name);
  CALL DBMS_OUTPUT.PUT_LINE('context   : ' || p_context);
  CALL DBMS_OUTPUT.PUT_LINE('schema    : ' || v_schema);
  IF v_part1 IS NULL THEN
    CALL DBMS_OUTPUT.PUT_LINE('part1     : NULL');
  ELSE
    CALL DBMS_OUTPUT.PUT_LINE('part1     : ' || v_part1);
  END IF;
  IF v_part2 IS NULL THEN
    CALL DBMS_OUTPUT.PUT_LINE('part2     : NULL');
  ELSE
    CALL DBMS_OUTPUT.PUT_LINE('part2     : ' || v_part2);
  END IF;
  IF v_dblink IS NULL THEN
    CALL DBMS_OUTPUT.PUT_LINE('dblink    : NULL');
  ELSE
    CALL DBMS_OUTPUT.PUT_LINE('dblink    : ' || v_dblink);
  END IF;
  CALL DBMS_OUTPUT.PUT_LINE('part1 type: ' || v_part1_type);
  CALL DBMS_OUTPUT.PUT_LINE('object id : ' || v_objectid);
END
DB20000I  The SQL command completed successfully.

DROP TABLE S1.T1
DB20000I  The SQL command completed successfully.

CREATE TABLE S1.T1 (C1 INT)
DB20000I  The SQL command completed successfully.

CREATE OR REPLACE PROCEDURE S2.PROC1
BEGIN
END
DB20000I  The SQL command completed successfully.

CREATE OR REPLACE MODULE S3.M1
DB20000I  The SQL command completed successfully.

ALTER MODULE S3.M1 PUBLISH FUNCTION F1() RETURNS BOOLEAN
BEGIN
  RETURN TRUE;
END
DB20000I  The SQL command completed successfully.

CALL NAME_RESOLVE( 'S1.T1', 2 )

  Return Status = 0

name      : S1.T1
context   : 2
schema    : S1      
part1     : T1
part2     : NULL
dblink    : NULL
part1 type: 2
object id : 8

CALL NAME_RESOLVE( 'S2.PROC1', 2 )
SQL0204N  "S2.PROC1" is an undefined name.  SQLSTATE=42704

CALL NAME_RESOLVE( 'S2.PROC1', 1 )

  Return Status = 0

name      : S2.PROC1
context   : 1
schema    : S2      
part1     : PROC1
part2     : NULL
dblink    : NULL
part1 type: 7
object id : 66611

CALL NAME_RESOLVE( 'PROC1', 1 )

  Return Status = 0

name      : PROC1
context   : 1
schema    : S2      
part1     : NULL
part2     : PROC1
dblink    : NULL
part1 type: 7
object id : 66611

CALL NAME_RESOLVE( 'M1', 1 )

  Return Status = 0

name      : M1
context   : 1
schema    : S3      
part1     : NULL
part2     : M1
dblink    : NULL
part1 type: 9
object id : 16

CALL NAME_RESOLVE( 'S3.M1.F1', 1 )

  Return Status = 0

name      : S3.M1.F1
context   : 1
schema    : S3      
part1     : M1
part2     : F1
dblink    : NULL
part1 type: 9
object id : 16

Example 2: Resolve a table accessed by a database link. Note that NAME_RESOLVE does not check the validity of the database object on the remote database. It merely echoes back the components specified in the name argument.

BEGIN
    name_resolve('sample_schema.emp@sample_schema_link',2);
END;

name      : sample_schema.emp@sample_schema_link
context   : 2
schema    : SAMPLE_SCHEMA
part1     : EMP
part2     :
dblink    : SAMPLE_SCHEMA_LINK
part1 type: 0
object id : 0