Convert String to Array / List in PL/SQL

CREATE OR REPLACE PACKAGE parse
/*
 Generalized delimited string parsing package

 Author: Steven Feuerstein, steven@stevenfeuerstein.com

 Latest version always available on PL/SQL Obsession:

 www.ToadWorld.com/SF

 Click on "Trainings, Seminars and Presentations" and
 then download the demo.zip file.

 Modification History
 Date Change
 10-APR-2009 Add support for nested list variations

 Notes:
 * This package does not validate correct use of delimiters.
 It assumes valid construction of lists.
 * Import the Q##PARSE.qut file into an installation of
 Quest Code Tester 1.8.3 or higher in order to run
 the regression test for this package.

*/
IS
 SUBTYPE maxvarchar2_t IS VARCHAR2 (32767);

 /*
 Each of the collection types below correspond to (are returned by)
 one of the parse functions.

 items_tt - a simple list of strings
 nested_items_tt - a list of lists of strings
 named_nested_items_tt - a list of named lists of strings

 This last type also demonstrates the power and elegance of string-indexed
 collections. The name of the list of elements is the index value for
 the "outer" collection.
 */
 TYPE items_tt IS TABLE OF maxvarchar2_t
 INDEX BY PLS_INTEGER;

 TYPE nested_items_tt IS TABLE OF items_tt
 INDEX BY PLS_INTEGER;

 TYPE named_nested_items_tt IS TABLE OF items_tt
 INDEX BY maxvarchar2_t;

 /*
 Parse lists with a single delimiter.
 Example: a,b,c,d

 Here is an example of using this function:

 DECLARE
 l_list parse.items_tt;
 BEGIN
 l_list := parse.string_to_list ('a,b,c,d', ',');
 END;
 */
 FUNCTION string_to_list (string_in IN VARCHAR2, delim_in IN VARCHAR2)
 RETURN items_tt;

 /*
 Parse lists with nested delimiters.
 Example: a,b,c,d|1,2,3|x,y,z

 Here is an example of using this function:

 DECLARE
 l_list parse.nested_items_tt;
 BEGIN
 l_list := parse.string_to_list ('a,b,c,d|1,2,3,4', '|', ',');
 END;
 */
 FUNCTION string_to_list (
 string_in IN VARCHAR2,
 outer_delim_in IN VARCHAR2,
 inner_delim_in IN VARCHAR2
 )
 RETURN nested_items_tt;

 /*
 Parse named lists with nested delimiters.
 Example: letters:a,b,c,d|numbers:1,2,3|names:steven,george

 Here is an example of using this function:

 DECLARE
 l_list parse.named_nested_items_tt;
 BEGIN
 l_list := parse.string_to_list ('letters:a,b,c,d|numbers:1,2,3,4', '|', ':', ',');
 END;
 */
 FUNCTION string_to_list (
 string_in IN VARCHAR2,
 outer_delim_in IN VARCHAR2,
 name_delim_in IN VARCHAR2,
 inner_delim_in IN VARCHAR2
 )
 RETURN named_nested_items_tt;

 PROCEDURE display_list (string_in IN VARCHAR2, delim_in IN VARCHAR2 := ',');

 PROCEDURE display_list (
 string_in IN VARCHAR2,
 outer_delim_in IN VARCHAR2,
 inner_delim_in IN VARCHAR2
 );

 PROCEDURE display_list (
 string_in IN VARCHAR2,
 outer_delim_in IN VARCHAR2,
 name_delim_in IN VARCHAR2,
 inner_delim_in IN VARCHAR2
 );

 PROCEDURE show_variations;

 /* Helper function for automated testing */
 FUNCTION nested_eq (
 list1_in IN items_tt,
 list2_in IN items_tt,
 nulls_eq_in IN BOOLEAN
 )
 RETURN BOOLEAN;
END parse;
/

CREATE OR REPLACE PACKAGE BODY parse
IS
 FUNCTION string_to_list (string_in IN VARCHAR2, delim_in IN VARCHAR2)
 RETURN items_tt
 IS
 c_end_of_list CONSTANT PLS_INTEGER := -99;
 l_item maxvarchar2_t;
 l_startloc PLS_INTEGER := 1;
 items_out items_tt;

 PROCEDURE add_item (item_in IN VARCHAR2)
 IS
 BEGIN
 IF item_in = delim_in
 THEN
 /* We don't put delimiters into the collection. */
 NULL;
 ELSE
 items_out (items_out.COUNT + 1) := item_in;
 END IF;
 END;

 PROCEDURE get_next_item (
 string_in IN VARCHAR2,
 start_location_io IN OUT PLS_INTEGER,
 item_out OUT VARCHAR2
 )
 IS
 l_loc PLS_INTEGER;
 BEGIN
 l_loc := INSTR (string_in, delim_in, start_location_io);

 IF l_loc = start_location_io
 THEN
 /* A null item (two consecutive delimiters) */
 item_out := NULL;
 ELSIF l_loc = 0
 THEN
 /* We are at the last item in the list. */
 item_out := SUBSTR (string_in, start_location_io);
 ELSE
 /* Extract the element between the two positions. */
 item_out :=
 SUBSTR (string_in, start_location_io, l_loc - start_location_io);
 END IF;

 IF l_loc = 0
 THEN
 /* If the delimiter was not found, send back indication
 that we are at the end of the list. */
 start_location_io := c_end_of_list;
 ELSE
 /* Move the starting point for the INSTR search forward. */
 start_location_io := l_loc + 1;
 END IF;
 END get_next_item;
 BEGIN
 IF string_in IS NULL OR delim_in IS NULL
 THEN
 /* Nothing to do except pass back the empty collection. */
 NULL;
 ELSE
 LOOP
 get_next_item (string_in, l_startloc, l_item);
 add_item (l_item);
 EXIT WHEN l_startloc = c_end_of_list;
 END LOOP;
 END IF;

 RETURN items_out;
 END string_to_list;

 FUNCTION string_to_list (
 string_in IN VARCHAR2,
 outer_delim_in IN VARCHAR2,
 inner_delim_in IN VARCHAR2
 )
 RETURN nested_items_tt
 IS
 l_elements items_tt;
 l_return nested_items_tt;
 BEGIN
 /* Separate out the different lists. */
 l_elements := string_to_list (string_in, outer_delim_in);

 /* For each list, parse out the separate items
 and add them to the end of the list of items
 for that list. */
 FOR indx IN 1 .. l_elements.COUNT
 LOOP
 l_return (l_return.COUNT + 1) :=
 string_to_list (l_elements (indx), inner_delim_in);
 END LOOP;

 RETURN l_return;
 END string_to_list;

 FUNCTION string_to_list (
 string_in IN VARCHAR2,
 outer_delim_in IN VARCHAR2,
 name_delim_in IN VARCHAR2,
 inner_delim_in IN VARCHAR2
 )
 RETURN named_nested_items_tt
 IS
 c_name_position CONSTANT PLS_INTEGER := 1;
 c_items_position CONSTANT PLS_INTEGER := 2;
 l_elements items_tt;
 l_name_and_values items_tt;
 l_return named_nested_items_tt;
 BEGIN
 /* Separate out the different lists. */
 l_elements := string_to_list (string_in, outer_delim_in);

 FOR indx IN 1 .. l_elements.COUNT
 LOOP
 /* Extract the name and the list of items that go with
 the name. This collection always has just two elements:
 index 1 - the name
 index 2 - the list of values
 */
 l_name_and_values := string_to_list (l_elements (indx), name_delim_in);
 /*
 Use the name as the index value for this list.
 */
 l_return (l_name_and_values (c_name_position)) :=
 string_to_list (l_name_and_values (c_items_position), inner_delim_in);
 END LOOP;

 RETURN l_return;
 END string_to_list;

 PROCEDURE display_list (string_in IN VARCHAR2, delim_in IN VARCHAR2 := ',')
 IS
 l_items items_tt;
 BEGIN
 DBMS_OUTPUT.put_line ('Parse "' || string_in || '" using "' || delim_in
 || '"'
 );
 l_items := string_to_list (string_in, delim_in);

 FOR indx IN 1 .. l_items.COUNT
 LOOP
 DBMS_OUTPUT.put_line ('> ' || indx || ' = ' || l_items (indx));
 END LOOP;
 END display_list;

 PROCEDURE display_list (
 string_in IN VARCHAR2,
 outer_delim_in IN VARCHAR2,
 inner_delim_in IN VARCHAR2
 )
 IS
 l_items nested_items_tt;
 BEGIN
 DBMS_OUTPUT.put_line ( 'Parse "'
 || string_in
 || '" using "'
 || outer_delim_in
 || '-'
 || inner_delim_in
 || '"'
 );
 l_items := string_to_list (string_in, outer_delim_in, inner_delim_in);

 FOR outer_index IN 1 .. l_items.COUNT
 LOOP
 DBMS_OUTPUT.put_line ( 'List '
 || outer_index
 || ' contains '
 || l_items (outer_index).COUNT
 || ' elements'
 );

 FOR inner_index IN 1 .. l_items (outer_index).COUNT
 LOOP
 DBMS_OUTPUT.put_line ( '> Value '
 || inner_index
 || ' = '
 || l_items (outer_index) (inner_index)
 );
 END LOOP;
 END LOOP;
 END display_list;

 PROCEDURE display_list (
 string_in IN VARCHAR2,
 outer_delim_in IN VARCHAR2,
 name_delim_in IN VARCHAR2,
 inner_delim_in IN VARCHAR2
 )
 IS
 l_items named_nested_items_tt;
 l_index maxvarchar2_t;
 BEGIN
 DBMS_OUTPUT.put_line ( 'Parse "'
 || string_in
 || '" using "'
 || outer_delim_in
 || '-'
 || name_delim_in
 || '-'
 || inner_delim_in
 || '"'
 );
 l_items :=
 string_to_list (string_in, outer_delim_in, name_delim_in,
 inner_delim_in);
 l_index := l_items.FIRST;

 WHILE (l_index IS NOT NULL)
 LOOP
 DBMS_OUTPUT.put_line ( 'List "'
 || l_index
 || '" contains '
 || l_items (l_index).COUNT
 || ' elements'
 );

 FOR inner_index IN 1 .. l_items (l_index).COUNT
 LOOP
 DBMS_OUTPUT.put_line ( '> Value '
 || inner_index
 || ' = '
 || l_items (l_index) (inner_index)
 );
 END LOOP;

 l_index := l_items.NEXT (l_index);
 END LOOP;
 END display_list;

 PROCEDURE show_variations
 IS
 PROCEDURE show_header (title_in IN VARCHAR2)
 IS
 BEGIN
 DBMS_OUTPUT.put_line (RPAD ('=', 60, '='));
 DBMS_OUTPUT.put_line (title_in);
 DBMS_OUTPUT.put_line (RPAD ('=', 60, '='));
 END show_header;
 BEGIN
 show_header ('Single Delimiter Lists');
 display_list ('a,b,c');
 display_list ('a;b;c', ';');
 display_list ('a,,b,c');
 display_list (',,b,c,,');
 show_header ('Nested Lists');
 display_list ('a,b,c,d|1,2,3|x,y,z', '|', ',');
 show_header ('Named, Nested Lists');
 display_list ('letters:a,b,c,d|numbers:1,2,3|names:steven,george',
 '|',
 ':',
 ','
 );
 END;

 FUNCTION nested_eq (
 list1_in IN items_tt,
 list2_in IN items_tt,
 nulls_eq_in IN BOOLEAN
 )
 RETURN BOOLEAN
 IS
 l_return BOOLEAN := list1_in.COUNT = list2_in.COUNT;
 l_index PLS_INTEGER := 1;
 BEGIN
 WHILE (l_return AND l_index IS NOT NULL)
 LOOP
 l_return := list1_in (l_index) = list2_in (l_index);
 l_index := list1_in.NEXT (l_index);
 END LOOP;

 RETURN l_return;
 EXCEPTION
 WHEN NO_DATA_FOUND
 THEN
 RETURN FALSE;
 END nested_eq;
END;
/

How to exit from a procedure in Oracle

To exit from function is easy, just put “RETURN”

CREATE OR REPLACE FUNCTION MIS.FC_GET_ACCRUAL_COM(VAGR IN VARCHAR2) RETURN NUMBER IS
VNUM NUMBER;
BEGIN
SELECT AGR INTO VNUM FROM TAGR WHERE AGR = ‘AA’;

IF VNUM = 0 THEN
RETURN 1;
ELSE
RETURN 2;
END IF;
RETURN VNUM;
EXCEPTION WHEN OTHERS THEN
RETURN 0;
END;
/

To exit from procedure also easy, just use “RAISE”

CREATE OR REPLACE PROCEDURE TEST_EXIT
IS
HOI EXCEPTION;
BEGIN
RAISE HOI;
DBMS_OUTPUT.PUT_LINE (‘hoi’);
EXCEPTION
WHEN HOI
THEN
NULL;
END;

Generate Insert Statement Script to Extract Data from Oracle Table

Here, you can use the following code to generate insert statement to extract the existing data from an oracle table. The generated script can be run at a later time to re-create your data. The code handles only date, char, varchar2, and numeric data types. I got this code from googling, original version by Ashish Kumar, but i have modify user_tables to ALL_TABLES, user_tab_columns to ALL_COLUMNS, because i want to get insert script from another user schema, not only from my user schema.

Continue reading