Code that accepts object names from users or configuration has to work out what a name really refers to: which schema, which object, through which synonym. DBMS_UTILITY has three procedures for this. NAME_TOKENIZE splits a name into its parts, NAME_RESOLVE finds the object a name points to, and CANONICALIZE puts a name into the form stored in the data dictionary.
Code for This Guide
The main examples are in the examples/pkg-utilities folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
They come from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
dbms_utility.name_tokenize(name, a, b, c, dblink, next_position);
dbms_utility.name_resolve(name, context, schema, part1, part2, dblink,
part1_type, object_number);
dbms_utility.canonicalize(name, canonical_name, length);The context of NAME_RESOLVE says what kind of object to look for: 1 for PL/SQL code and 2 for a table or view are the common ones.
Tokenize, Resolve, Canonicalize
Example:
declare
a varchar2(128); b varchar2(128); c varchar2(128); dblink varchar2(128);
v_next binary_integer;
v_schema varchar2(128); v_part1 varchar2(128); v_part2 varchar2(128);
v_type number; v_objno number;
begin
dbms_utility.name_tokenize('nimbus."Booking API".add_ticket@loopback', a, b, c, dblink,
v_next);
dbms_output.put_line('tokens: ' || a || ' | ' || b || ' | ' || c || ' | ' || dblink);
dbms_utility.name_resolve('flights', 2, v_schema, v_part1, v_part2, dblink, v_type,
v_objno);
dbms_output.put_line('resolved: ' || v_schema || '.' || v_part1 || ' (type '
|| v_type || ')');
dbms_utility.canonicalize('nimbus."Flights"', v_part1, 100);
dbms_output.put_line('canonical: ' || v_part1);
end;
/Output:
tokens: NIMBUS | Booking API | ADD_TICKET | LOOPBACK resolved: NIMBUS.FLIGHTS (type 2) canonical: "NIMBUS"."Flights" PL/SQL procedure successfully completed.
- NAME_TOKENIZE splits the name into schema, object, subprogram, and database link, uppercasing unquoted parts and keeping the case of "Booking API". It only parses; nothing has to exist.
- NAME_RESOLVE finds FLIGHTS in NIMBUS, with type 2, a table.
- CANONICALIZE returns the quoted, dictionary form, uppercasing only the unquoted part.
Resolve Through a Synonym
Example:
declare
v_schema varchar2(128); v_part1 varchar2(128); v_part2 varchar2(128);
v_dblink varchar2(128); v_type number; v_objno number;
begin
-- context 1 = PL/SQL: the public synonym DBMS_OUTPUT leads to the SYS package
dbms_utility.name_resolve('dbms_output.put_line', 1, v_schema, v_part1, v_part2,
v_dblink, v_type, v_objno);
dbms_output.put_line(v_schema || '.' || v_part1 || '.' || v_part2
|| ' (type ' || v_type || ')');
end;
/Output:
SYS.DBMS_OUTPUT.PUT_LINE (type 9) PL/SQL procedure successfully completed.
With context 1, dbms_output.put_line resolves through the public synonym to the package SYS.DBMS_OUTPUT and its subprogram PUT_LINE. Type 9 means a package.
Things to Know
- NAME_RESOLVE raises an error when the name does not resolve to an object of that context, which makes it a check as well as a lookup.
- The type values follow the dictionary object types: 2 table, 7 procedure, 8 function, 9 package.
- To check a name before using it in dynamic SQL, DBMS_ASSERT is the simpler choice.
Related Guides
Conclusion
NAME_TOKENIZE splits names, NAME_RESOLVE finds what a name points to through synonyms and schemas, and CANONICALIZE gives the dictionary form. Use them when code must work with object names it does not know in advance.
