Thanks for the question, Marek.
Asked: November 30, 2022 - 8:10 am UTC
Last updated: December 01, 2022 - 4:52 am UTC
Version: 19.17.0.1.0
Viewed 100+ times
You Asked
Hi
I use this table function for string split (a space is used as a separator)
create or replace FUNCTION fmv_space_to_table(p_list IN VARCHAR2)
RETURN fmv_test_type
AS
l_string VARCHAR2(32767) := p_list || ' ';
l_comma_index PLS_INTEGER;
l_index PLS_INTEGER := 1;
l_tab fmv_test_type := fmv_test_type();
BEGIN
LOOP
l_comma_index := INSTR(l_string, ' ', l_index);
EXIT
WHEN l_comma_index = 0;
l_tab.EXTEND;
l_tab(l_tab.COUNT) := TRIM(SUBSTR(l_string,
l_index,
l_comma_index - l_index
)
);
l_index := l_comma_index + 1;
END LOOP;
RETURN l_tab;
END fmv_space_to_table;
The table function works fine:
select * from table( fmv_space_to_table( 'A2345 123456 7890 2344'))
Output:
A2345
123456
7890
2344
When table function "fmv_space_to_table" is used in a query with only number values, it works fine
e.g.
...
s.productnumber IN ( select * from table( fmv_space_to_table( '123456 7890')) )
When table function "fmv_space_to_table" is used in a query with letter + number values, it doesn't work
e.g.
...
s.productnumber IN ( select * from table( fmv_space_to_table( 'A2345')) )
Error:
ORA-00904: "A2345": ungültige ID
cause of error:
my table function returns as output: A2345
but in this case is needed: 'A2345'
Question:
How can the table function "fmv_space_to_table" be extended so that it can also be used with letters + numbers Values?
and Connor said...
I suspect you have either a datatype mismatch or you are forgetting to add the quotes
SQL> create or replace FUNCTION fmv_space_to_table(p_list IN VARCHAR2)
2 RETURN sys.odcivarchar2list
3 AS
4 l_string VARCHAR2(32767) := p_list || ' ';
5 l_comma_index PLS_INTEGER;
6 l_index PLS_INTEGER := 1;
7 l_tab sys.odcivarchar2list := sys.odcivarchar2list();
8 BEGIN
9 LOOP
10 l_comma_index := INSTR(l_string, ' ', l_index);
11 EXIT
12 WHEN l_comma_index = 0;
13 l_tab.EXTEND;
14 l_tab(l_tab.COUNT) := TRIM(SUBSTR(l_string,
15 l_index,
16 l_comma_index - l_index
17 )
18 );
19 l_index := l_comma_index + 1;
20 END LOOP;
21 RETURN l_tab;
22 END fmv_space_to_table;
23 /
Function created.
SQL>
SQL>
SQL> select * from table( fmv_space_to_table( 'A2345 123456 7890 2344'));
COLUMN_VALUE
----------------------------------------------------------------------------------------------------------------------------------
A2345
123456
7890
2344
4 rows selected.
SQL>
SQL> select * from table( fmv_space_to_table( 'A2345'));
COLUMN_VALUE
----------------------------------------------------------------------------------------------------------------------------------
A2345
1 row selected.
SQL>
SQL> select * from emp
2 where ename in ( select * from table( fmv_space_to_table( 'A2345')) );
no rows selected
SQL>
SQL> select * from emp
2 where empno in ( select * from table( fmv_space_to_table( 'A2345')) );
where empno in ( select * from table( fmv_space_to_table( 'A2345')) )
*
ERROR at line 2:
ORA-01722: invalid number
SQL>
SQL>
SQL> select * from emp
2 where empno in ( select * from table( fmv_space_to_table( A2345)) );
where empno in ( select * from table( fmv_space_to_table( A2345)) )
*
ERROR at line 2:
ORA-00904: "A2345": invalid identifier
SQL>
SQL>If its the last case, this suggests the query is being built dynamically ... you want to be very careful of that. Opens up lots of security risks.
Rating
(1 rating)