Реализовать иерархический запрос

CREATE OR REPLACE TYPE t_region_type IS object (id INTEGER, name VARCHAR2(32000), pid INTEGER);
CREATE OR REPLACE TYPE t_region_tab IS TABLE OF t_region_type;
 
CREATE OR REPLACE PROCEDURE MAIN_INS (pTab t_region_tab, pReg_name VARCHAR2) IS  
   my_code NUMBER;
   my_errm VARCHAR2(32000);
   my_path VARCHAR2(100);
  BEGIN
 
    SELECT LTRIM(SYS_CONNECT_BY_PATH(name,'/'), '/') 
      INTO my_path
      FROM TABLE(pTab) t
     WHERE UPPER(t.name) = UPPER(pReg_name)
      START WITH pid IS NULL
      CONNECT BY PRIOR id = pid;
 
   DBMS_OUTPUT.put_line(my_path);    
 
EXCEPTION 
   WHEN OTHERS THEN
     my_code := SQLCODE;
     my_errm := SQLERRM;
     DBMS_OUTPUT.put_line('Ошибка');    
END;
 
-- вызов
DECLARE
 t_reg_tab t_region_tab:= t_region_tab();
BEGIN
 
 t_reg_tab.extend(6);
 t_reg_tab(1):= t_region_type(1, 'Россия', NULL);
 t_reg_tab(2):= t_region_type(2, 'Москва', 1);
 t_reg_tab(3):= t_region_type(3, 'Санкт-Петербург', 1);
 t_reg_tab(4):= t_region_type(4, 'Краснодарский край', 1);
 t_reg_tab(5):= t_region_type(5, 'Краснодар', 4);
 t_reg_tab(6):= t_region_type(6, 'Воронежская область', 1);
 
 MAIN_INS(t_reg_tab, 'Краснодар');
 MAIN_INS(t_reg_tab, 'Стамбул');
END;

Решение

-- -----------------------------------------------------------------------
DO $$
BEGIN
	CREATE TYPE t_region_type AS (id INTEGER, name VARCHAR(32000), pid INTEGER);
	EXCEPTION WHEN duplicate_object THEN return;
END
$$;
 
-- -----------------------------------------------------------------------
CREATE OR REPLACE FUNCTION get_region_tab() RETURNS VARCHAR AS $fun$
DECLARE t_name VARCHAR = 't_region_tab';
BEGIN
	EXECUTE('CREATE TEMPORARY TABLE ' || t_name || '( p1 t_region_type );');
EXCEPTION WHEN OTHERS THEN
	EXECUTE('TRUNCATE ' || t_name || ';');
RETURN t_name;
END
$fun$ LANGUAGE plpgsql;
 
-- -----------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE new_region (tt VARCHAR, d t_region_type) AS $proc$
BEGIN
	EXECUTE('INSERT INTO ' || tt || ' VALUES ($$' || d || '$$);');
END
$proc$ LANGUAGE plpgsql;
 
-- -----------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE main_ins (tt VARCHAR, pReg_name VARCHAR) AS $proc$
DECLARE
	my_path VARCHAR(32000);
BEGIN
	EXECUTE('WITH RECURSIVE temp1(id, name, pid, path) AS (
			SELECT 
				(t1.t_reg_tab).id AS id,
				(t1.t_reg_tab).name AS name,
				(t1.t_reg_tab).pid AS pid,
				CAST((t1.t_reg_tab).name AS VARCHAR(32000)) AS path
			FROM ' || tt || ' AS t1
			WHERE UPPER((t1.t_reg_tab).name) = UPPER(''' || pReg_name || ''')
			UNION
			SELECT 
				(t2.t_reg_tab).id AS id,
				(t2.t_reg_tab).name AS name,
				(t2.t_reg_tab).pid AS pid,
				CAST(temp1.path || ''/'' || (t2.t_reg_tab).name AS VARCHAR(32000)) AS path
			FROM t_region_tab AS t2 JOIN temp1 ON temp1.pid = (t2.t_reg_tab).id
		)
		SELECT path my_path FROM temp1 WHERE pid IS NULL;
	') INTO my_path;
 
	RAISE INFO '%',my_path;
 
EXCEPTION WHEN OTHERS THEN 
	RAISE INFO 'Ошибка';
END
$proc$ LANGUAGE plpgsql;
 
-- -----------------------------------------------------------------------
DO $$
DECLARE 
	tt VARCHAR = get_region_tab();
BEGIN
	CALL new_region(tt,(1, 'Россия', NULL));
	CALL new_region(tt,(2, 'Москва', 1));
	CALL new_region(tt,(3, 'Санкт-Петербург', 1));
	CALL new_region(tt,(4, 'Краснодарский край', 1));
	CALL new_region(tt,(5, 'Краснодар', 4));
	CALL new_region(tt,(6, 'Воронежская область', 1));
 
	CALL main_ins(tt, 'Краснодар');
	CALL main_ins(tt, 'Стамбул');
END
$$;

-- -----------------------------------------------------------------------
DO $$
BEGIN
	CREATE TYPE t_region_type AS (id INTEGER, name VARCHAR(32000), pid INTEGER);
	EXCEPTION WHEN duplicate_object THEN return;
END
$$;
 
-- -----------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE main_ins (tt t_region_type[], pReg_name VARCHAR(32000)) AS $proc$
DECLARE
	my_path VARCHAR;
BEGIN
	WITH RECURSIVE temp1(id, name, pid, path) AS (
		SELECT
			(t1).id AS id,
			(t1).name AS name,
			(t1).pid AS pid,
			CAST((t1).name AS VARCHAR) AS path
		FROM unnest(tt) AS t1
		WHERE UPPER((t1).name) = UPPER(pReg_name)
		UNION
		SELECT 
			(t2).id AS id,
			(t2).name AS name,
			(t2).pid AS pid,
			CAST(temp1.path || '/' || (t2).name AS VARCHAR) AS path
		FROM unnest(tt) AS t2 JOIN temp1 ON temp1.pid = (t2).id
	)
	SELECT path INTO my_path FROM temp1 WHERE pid IS NULL;
 
	RAISE INFO '%',my_path;
 
EXCEPTION WHEN OTHERS THEN 
	RAISE INFO 'Ошибка';
END
$proc$ LANGUAGE plpgsql;
 
-- -----------------------------------------------------------------------
DO $$
DECLARE 
	t_reg_tab t_region_type[];
 
BEGIN
	t_reg_tab[1] = (1, 'Россия', NULL);
	t_reg_tab[2] = (2, 'Москва', 1);
	t_reg_tab[3] = (3, 'Санкт-Петербург', 1);
	t_reg_tab[4] = (4, 'Краснодарский край', 1);
	t_reg_tab[5] = (5, 'Краснодар', 4);
	t_reg_tab[6] = (6, 'Воронежская область', 1);
 
	CALL main_ins2(t_reg_tab, 'Краснодар');
	CALL main_ins2(t_reg_tab, 'Стамбул');
END
$$;