StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<pre><code>CREATE OR REPLACE PACKAGE IH_FORMS_TRIAL.STRING_TO_TABLE IS type grs_list_row is record ( varchar_list varchar2(512), int_list number, date_list date ); type grs_list_tab is table of grs_list_row; FUNCTION ft_string_to_table(av2_list varchar2, av2_delimiter varchar2 := ',', av2_list_type varchar2 := 'V', av2_date_mask varchar2 := 'DD-MON-YY') return grs_list_tab PIPELINED; END STRING_TO_TABLE; / CREATE OR REPLACE package body IH_FORMS_TRIAL.STRING_TO_TABLE IS FUNCTION ft_string_to_table(av2_list varchar2, av2_delimiter varchar2 := ',', av2_list_type varchar2 := 'V', av2_date_mask varchar2 := 'DD-MON-YY') return grs_list_tab PIPELINED IS /********************************************************************************************************** http://www.oracle.com/technology/sample_code/tech/pl_sql/htdocs/x/Table_Functions_Cursor_Expressions/Pipelined_Table_Functions.htm http://www.akadia.com/services/ora_pipe_functions.html PIPLELINED TABLE FUNCTION PURPOSE - ------------------ This function takes a string as input and returns a table. The table that is returned will normally be used in an SQL "IN" clause ===================================================================================== ARGUMENTS ------------------ av2_list - this is a comma delimited list of values that will be converted into single rows of a table av2_delimiter - this is a character value and should only be one character long. It is the delimiter that is between valid values in the av2_list The default value is a comma ',' av2_list_type - This function can return various types of lists or tables For this parameter A value of 'V' will return a table of varchar2 A value of 'I' will return a table of integers A value of 'D' will return a table of dates av2_date_mask - This is required if the value of av2_list_type is 'D' for date The date mask will be used by the Oracle built-in TO_DATE function A default value of 'DD-MON-YY' is used ===================================================================================== RETURNS Table of values for input to an IN portion of a WHERE clause ===================================================================================== EXAMPLES SELECT * FROM <TABLE> WHERE <VARCHAR_COLUMN> IN (select varchar_list from table (ih_core_owner.core.ft_string_to_table.ft_string_to_table('has986, abc454'))); SELECT * FROM <TABLE> WHERE <INTEGER_COLUMN> IN (select int_list from table (ih_core_owner.core.ft_string_to_table.ft_string_to_table('1,2,3,4,5,6,7,8,9', ',', 'I'))); SELECT * FROM <TABLE> WHERE <DATE_COLUMN> IN (select date_list from table (ih_core_owner.core.ft_string_to_table.ft_string_to_table('2010-03-04, 2010-03-05', ',', 'D', 'YYYY-MM-DD'))); ===================================================================================== TEST CASES select varchar_list from table (ih_core_owner.core.ft_string_to_table.ft_string_to_table('has986, abc454', ',', 'V')); select int_list from table (ih_core_owner.core.ft_string_to_table.ft_string_to_table('1,2,3,4,5,6,7,8,9', ',', 'I')); select date_list from table (ih_core_owner.core.ft_string_to_table.ft_string_to_table('04-mar-10, 05-mar-10', ',', 'D')); select date_list from table (ih_core_owner.core.ft_string_to_table.ft_string_to_table('2010-03-04, 2010-03-05', ',', 'D', 'YYYY-MM-DD')); test using and invalid list type Use Y instead of V, I or D Should produce an error select varchar_list from table (ih_core_owner.core.ft_string_to_table.ft_string_to_table('has986, abc454', ',', 'Y')); test using a date format that does not match the date format passed Should produce an error select date_list from table (ih_core_owner.core.ft_string_to_table.ft_string_to_table('2010-03-04, 2010-03-05', ',', 'D', 'YYYY-MON-DD')); select date_list from table (ih_core_owner.core.ft_string_to_table.ft_string_to_table('2010-MAR-04, 2010-MAR-05', ',', 'D', 'YYYY-MM-DD')); ---------- ===================================================================================== REVISION HISTORY Called by misc systems ---------------------------------------------------------------- Modification History Date User Description ------------ -------------- ---------------------------------- 2006-03-03 HarvS Initial Release 2010-04-09 HarvS Translated from SQL Server to ORACLE Combined functions that returned lists of varchar, integer, and date into one function with optional parameters REVISION HISTORY ---------------- Build Version - 11.02.01.001 Build Date - 08-June-2010 Modified By - has986 Description - Created ******************************************************************************/ --local variable of type grs_list_row lrs_row grs_list_row; E_INVALID_LIST_TYPE EXCEPTION; li_delimiter_position int; li_previous_delimiter_position int; lv2_value varchar2(512); BEGIN if av2_list_type not in ('V', 'I', 'D') THEN raise E_INVALID_LIST_TYPE; end if; li_delimiter_position := 1; li_previous_delimiter_position := 1; li_delimiter_position := INSTR(av2_list, av2_delimiter, li_delimiter_position); while li_delimiter_position > 0 loop lv2_value := substr(av2_list, li_previous_delimiter_position, (li_delimiter_position - li_previous_delimiter_position)); --Trim the value lv2_value := RTRIM(LTRIM(lv2_value)); if length(lv2_value) > 0 THEN if av2_list_type = 'V' then --varchar lrs_row.varchar_list := lv2_value; elsif av2_list_type = 'I' then --integer lrs_row.int_list := to_number(lv2_value); elsif av2_list_type = 'D' then --date lrs_row.date_list := to_date(lv2_value, av2_date_mask); end if; pipe row ( lrs_row ); END IF; --set the new delimiter positions li_previous_delimiter_position := li_delimiter_position + 1; li_delimiter_position := INSTR(av2_list, av2_delimiter, li_delimiter_position + 1); END loop; --Get the last value lv2_value := SUBSTR(av2_list, li_previous_delimiter_position, length(av2_list)); --Trim the value lv2_value := RTRIM(LTRIM(lv2_value)); if length(lv2_value) > 0 THEN --Insert the value into the in memory table if av2_list_type = 'V' then --varchar lrs_row.varchar_list := lv2_value; elsif av2_list_type = 'I' then --integer lrs_row.int_list := to_number(lv2_value); elsif av2_list_type = 'D' then --date lrs_row.date_list := to_date(lv2_value, av2_date_mask); end if; pipe row ( lrs_row ); END IF; return; EXCEPTION WHEN E_INVALID_LIST_TYPE then /* The developer should be notified of this error during the development phase. */ raise_application_error (-20001, av2_list_type || ' is not a valid type. Valid types are (V, I, or D)' ); WHEN OTHERS THEN RAISE; END ft_string_to_table; end string_to_table; / select * FROM table( string_to_table.ft_string_to_table('1, 2, 3', ',', 'I')); select * FROM table( string_to_table.ft_string_to_table('fred, wilma, betty, barney', ',', 'V')); select * FROM table( string_to_table.ft_string_to_table('2011-5-1, 1950-1-1, 1960-1-2, 2023-12-1', ',', 'D', 'yyyy-mm-dd')); </code></pre> <p>Hope this works for you. I have seen some other code that does this as well. For what its worth, this is much easier to do in Microsoft SQL Server</p> <p>Harv Sather</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload