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.
>>-DBMS_UTILITY.NAME_RESOLVE--(--name--,--context--,--schema--,--part1--,--> >--part2--,--dblink--,--part1_type--,--object_number--)--------><
[[ a.]b.]c[@dblink ]
EXECUTE privilege on the DBMS_UTILITY module.
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 )@
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
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