Use dbms_utility.comma_to_table, dbms_utility.table_to_comma to parse comma delimited value : dbms_utility « System Packages « Oracle PL/SQL Tutorial






SQL>
SQL> set echo on
SQL>
SQL> declare
  2      type vcArray is table of varchar2(4000);
  3
  4      l_names  vcArray := vcArray( 'emp,dept,bonus',
  5                                   'a,  b  ,   c',
  6                                   '"1", "6", "9"',
  7                                   '"This is a long string","b",c');
  8      l_tablen number;
  9      l_tab    dbms_utility.uncl_array;
 10  begin
 11      for i in 1 .. l_names.count
 12      loop
 13          dbms_output.put_line( ' ' || '[' || l_names(i) || ']' );
 14      begin
 15          dbms_utility.comma_to_table( l_names(i),l_tablen, l_tab );
 16
 17          for j in 1..l_tablen
 18          loop
 19              dbms_output.put_line( '[' || l_tab(j) || ']' );
 20          end loop;
 21          l_names(i) := null;
 22          dbms_utility.table_to_comma( l_tab,l_tablen, l_names(i) );
 23          dbms_output.put_line( l_names(i) );
 24      exception
 25          when others then dbms_output.put_line( sqlerrm );
 26      end;
 27      end loop;
 28  end;
 29  /
 [emp,dept,bonus]
[emp]
[dept]
[bonus]
emp,dept,bonus
 [a,  b  ,   c]
[a]
[  b  ]
[   c]
a,  b  ,   c
 ["1", "6", "9"]
["1"]
[ "6"]
[ "9"]
"1", "6", "9"
 ["This is a long string","b",c]
["This is a long string"]
["b"]
[c]
"This is a long string","b",c

PL/SQL procedure successfully completed.

SQL>








31.30.dbms_utility
31.30.1.dbms_utility.get_hash_value
31.30.2.Use dbms_utility.name_resolve to find a package
31.30.3.Use dbms_utility.name_tokenize to split a name
31.30.4.Call dbms_utility.db_version to get the version and its compatible version
31.30.5.Use dbms_utility.comma_to_table, dbms_utility.table_to_comma to parse comma delimited value
31.30.6.Call dbms_utility.get_parameter_value to get the value of utl_file_dir
31.30.7.Call dbms_utility.get_time twice
31.30.8.Call dbms_utility.analyze_schema analyze schema
31.30.9.Assign value from dbms_utility.get_time to a number variable
31.30.10.Call dbms_utility.get_time twice to time the insert statement
31.30.11.Timing log
31.30.12.dbms_utility.format_error_stack
31.30.13.DBMS_UTILITY.FORMAT_CALL_STACK and DBMS_UTILITY.FORMAT_ERROR_STACK