'1,2,3'
처럼 되어있는 문자열을
'1','2','3'
으로 변환하는 함수를 만들었는데요
함수를 실행하는 과정에서 ORA-00932 에러가 나서 질문드립니다
==함수==
CREATE OR REPLACE FUNCTION f_string_to_rows (input_text IN VARCHAR2)
RETURN VARCHAR2
IS
v_regexp VARCHAR2 (255);
BEGIN
SELECT MY_REGEXP
INTO v_regexp
FROM ( SELECT REGEXP_SUBSTR (HELLO,
'[^,]+',
1,
LEVEL)
AS MY_REGEXP
FROM (SELECT input_text AS HELLO FROM DUAL) A
CONNECT BY LEVEL <= LENGTH (REGEXP_REPLACE (HELLO, '[^,]+')) + 1) A
WHERE A.MY_REGEXP IS NOT NULL;
RETURN v_regexp;
END;
==실행==
select *
from 테이블
where start_t in (
select data as col1
from table(cast(f_string_to_rows( 'a,b,c' ) as tp_simple_table))
)
;
안녕하세요. 저는 아래 순서로 실행해보니 00932오류가 발생했습니다.
CREATE OR REPLACE TYPE tp_simple_table AS TABLE OF VARCHAR2(255); CREATE OR REPLACE FUNCTION f_string_to_rows (input_text IN VARCHAR2) RETURN VARCHAR2 IS v_regexp VARCHAR2 (255); BEGIN SELECT MY_REGEXP INTO v_regexp FROM ( SELECT REGEXP_SUBSTR (HELLO, '[^,]+', 1, LEVEL) AS MY_REGEXP FROM (SELECT input_text AS HELLO FROM DUAL) A CONNECT BY LEVEL <= LENGTH (REGEXP_REPLACE (HELLO, '[^,]+')) + 1) A WHERE A.MY_REGEXP IS NOT NULL; RETURN v_regexp; END; select data as col1 from table(cast(f_string_to_rows( 'a,b,c' ) as tp_simple_table)) ERROR at line 2: ORA-00932: inconsistent datatypes: expected - got CHAR
테이블 함수는 저도 익숙하진 않아서 좀 찾아보니..아래에 작성법이 있어서 따라 만들어 봤습니다.
http://www.gurubee.net/lecture/2238
https://paulzipblog.wordpress.com/2019/10/13/regexp_split/
create or replace type TY_Strings as table of varchar2(4000); / create or replace function regexp_split(source_string varchar2, pattern varchar2 default null, position pls_integer default 1, match_parameter varchar2 default null, subexp pls_integer default 0) return TY_Strings is vString varchar2(4000); vOccurrence pls_integer := 1; vResult TY_Strings := TY_Strings(); begin if source_string is not null and pattern is not null then loop vString := regexp_substr(source_string, pattern, 1, vOccurrence, match_parameter, subexp); exit when vString is null; vResult.Extend; vResult(vOccurrence) := vString; vOccurrence := vOccurrence + 1; end loop; end if; return vResult; end; / select column_value as col1 from table(regexp_split('a,b,c', '[^,]+', 1, 'i')) COL1 ---------------------------------------------------------------------------------------------------- a b c
다른 좋은 방법도 있을 것 같습니다.