WITH TEST_DATA AS (
SELECT 'sdfsdfsdfsdf98000'
||CHR(10)||'sdfsdfsdfsdf98001'
||CHR(10)||'sdfsdfsdfsdf98002'
||CHR(10)||'sdfsdfsdfsdf98036'
||CHR(10)||'sdfsdfsdfsdf98037'
||CHR(10)||'sdfsdfsdfsdf98038' str
FROM DUAL
)
SELECT * FROM (
SELECT REGEXP_SUBSTR(str,'[^\r]+',1,LEVEL) AS line_data
FROM TEST_DATA
CONNECT BY LEVEL 0 THEN
INSERT INTO ttt (t) VALUES (SUBSTR(v_list, 1, l_idx - 1));
v_list := substr(v_list, l_idx + LENGTH(p_sep));-- 超过32767时如何办
else
INSERT INTO ttt (t) VALUES (substr(v_list,1,30));
EXIT;
end if;
END LOOP;
commit;
END SP_split;
/ |
