NAME_TOKENIZE procedure - Parse the given name into its component parts

The NAME_TOKENIZE procedure parses a name into its component parts. Names without double quotes are put into uppercase, and double quotes are stripped from names with double quotes.

Syntax

Parameters

name

An input argument of type VARCHAR(1024) that specifies the string containing a name in the following format:

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

a

An output argument of type VARCHAR(128) that returns the leftmost component.

b

An output argument of type VARCHAR(128) that returns the second component, if any.

c

An output argument of type VARCHAR(128) that returns the third component, if any.

dblink

An output argument of type VARCHAR(32672) that returns the database link name.

nextpos

An output argument of type INTEGER that specifies the position of the last character parsed in name.

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_TOKENIZE procedure for various names.

SET SERVEROUTPUT ON@

CREATE OR REPLACE PROCEDURE name_tokenize(
  IN p_name VARCHAR(100) )
BEGIN
  DECLARE v_a VARCHAR(30);
  DECLARE v_b VARCHAR(30);
  DECLARE v_c VARCHAR(30);
  DECLARE v_dblink VARCHAR(30);
  DECLARE v_nextpos INTEGER;

  CALL DBMS_UTILITY.NAME_TOKENIZE(p_name, v_a, v_b, v_c, v_dblink, v_nextpos);
  CALL DBMS_OUTPUT.PUT_LINE('name   : ' || p_name);
  IF v_a IS NULL THEN
    CALL DBMS_OUTPUT.PUT_LINE('a      : NULL');
  ELSE
    CALL DBMS_OUTPUT.PUT_LINE('a      : ' || v_a);
  END IF;
  IF v_b IS NULL THEN
    CALL DBMS_OUTPUT.PUT_LINE('b      : NULL');
  ELSE
    CALL DBMS_OUTPUT.PUT_LINE('b      : ' || v_b);
  END IF;
  IF v_c IS NULL THEN
    CALL DBMS_OUTPUT.PUT_LINE('c      : NULL');
  ELSE
    CALL DBMS_OUTPUT.PUT_LINE('c      : ' || v_c);
  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;
  IF v_nextpos IS NULL THEN
    CALL DBMS_OUTPUT.PUT_LINE('nextpos: NULL');
  ELSE
    CALL DBMS_OUTPUT.PUT_LINE('nextpos: ' || v_nextpos);
  END IF;
END@

CALL name_tokenize( 'b' )@
CALL name_tokenize( 'a.b' )@
CALL name_tokenize( '"a".b.c' )@
CALL name_tokenize( 'a.b.c@d' )@
CALL name_tokenize( 'a.b."c"@"d"' )@

This example results in the following output:

SET SERVEROUTPUT ON
DB20000I  The SET SERVEROUTPUT command completed successfully.

CREATE OR REPLACE PROCEDURE name_tokenize(
  IN p_name VARCHAR(100) )
BEGIN
  DECLARE v_a VARCHAR(30);
  DECLARE v_b VARCHAR(30);
  DECLARE v_c VARCHAR(30);
  DECLARE v_dblink VARCHAR(30);
  DECLARE v_nextpos INTEGER;

  CALL DBMS_UTILITY.NAME_TOKENIZE(p_name, v_a, v_b, v_c, v_dblink, v_nextpos);
  CALL DBMS_OUTPUT.PUT_LINE('name   : ' || p_name);
  IF v_a IS NULL THEN
    CALL DBMS_OUTPUT.PUT_LINE('a      : NULL');
  ELSE
    CALL DBMS_OUTPUT.PUT_LINE('a      : ' || v_a);
  END IF;
  IF v_b IS NULL THEN
    CALL DBMS_OUTPUT.PUT_LINE('b      : NULL');
  ELSE
    CALL DBMS_OUTPUT.PUT_LINE('b      : ' || v_b);
  END IF;
  IF v_c IS NULL THEN
    CALL DBMS_OUTPUT.PUT_LINE('c      : NULL');
  ELSE
    CALL DBMS_OUTPUT.PUT_LINE('c      : ' || v_c);
  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;
  IF v_nextpos IS NULL THEN
    CALL DBMS_OUTPUT.PUT_LINE('nextpos: NULL');
  ELSE
    CALL DBMS_OUTPUT.PUT_LINE('nextpos: ' || v_nextpos);
  END IF;
END
DB20000I  The SQL command completed successfully.

CALL name_tokenize( 'b' )

  Return Status = 0

name   : b
a      : B
b      : NULL
c      : NULL
dblink : NULL
nextpos: 1

CALL name_tokenize( 'a.b' )

  Return Status = 0

name   : a.b
a      : A
b      : B
c      : NULL
dblink : NULL
nextpos: 3

CALL name_tokenize( '"a".b.c' )

  Return Status = 0

name   : "a".b.c
a      : a
b      : B
c      : C
dblink : NULL
nextpos: 7

CALL name_tokenize( 'a.b.c@d' )

  Return Status = 0

name   : a.b.c@d
a      : A
b      : B
c      : C
dblink : D
nextpos: 7

CALL name_tokenize( 'a.b."c"@"d"' )

  Return Status = 0

name   : a.b."c"@"d"
a      : A
b      : B
c      : c
dblink : d
nextpos: 11