DECLARE
v_test_varray VARCHAR_VARRAY_TYPE := VARCHAR_VARRAY_TYPE('57610',
'61368',
'73111',
'37208',
'57639');
V_SQL VARCHAR2(32767);
V_SQL_IN VARCHAR2(32767);
V_SQL_ROWNUM VARCHAR2(32767);
V_ORDER VARCHAR2(32767);
TYPE number_index_by_string IS TABLE OF NUMBER INDEX BY VARCHAR2(10);
v_evt_id_list NUMBER_INDEX_BY_STRING;
Begin
--orderArray VARCHAR_VARRAY_TYPE%type := VARCHAR_VARRAY_TYPE('11','22','33','44','55');
/*
FOR i IN v_test_varray.FIRST .. v_test_varray.LAST LOOP
IF v_test_varray.EXISTS(i) THEN
DBMS_OUTPUT.put_line(i);
END IF;
END LOOP;
*/
V_ORDER := Func_LIST_TO_ORDER(v_test_varray, 'A.COL_NAME');
DBMS_OUTPUT.put_line('V_ORDER='||V_ORDER);
V_SQL := 'SELECT * FROM TABLEA A'
|| ' WHERE '
|| ''
|| ''
|| ''
|| ''
;
V_SQL := V_SQL || ' ( ' ||
FUNC_LIST_TO_IN_SQL(v_test_varray,
'COL_NAME',
'''') || ')';
V_SQL := V_SQL ||
' ORDER by ( ' || V_ORDER || ')';
DBMS_OUTPUT.put_line('V_SQL='||V_SQL);
--- SET v_evt_id_list number_index_by_string
FOR i IN v_test_varray.FIRST .. v_test_varray.LAST
LOOP
IF v_test_varray.EXISTS (i)
THEN
DBMS_OUTPUT.put_line (i);
v_evt_id_list(v_test_varray(i)) := i;
END IF;
END LOOP;
--- TEST v_evt_id_list number_index_by_string
FOR i IN v_evt_id_list.FIRST .. v_evt_id_list.LAST
LOOP
IF v_evt_id_list.EXISTS (i)
THEN
DBMS_OUTPUT.put_line (i||' = '||v_evt_id_list(i) );
END IF;
END LOOP;
-- GET v_evt_id_list number_index_by_string
DBMS_OUTPUT.put_line (v_evt_id_list('73111') );