I am looking to split the string (comma separated) in column into multiple columns by using oracle procedure.
I am looking for the dynamic query as the number of columns need to be split will be unknown.
1
-
1Splitting into columns, not rows? What will happen to the results - are you doing something with them in PL/SQL, or intending to pass the results back to a caller - if the latter how will they know how many columns to expect?Alex Poole– Alex Poole2016-05-25 19:22:28 +00:00Commented May 25, 2016 at 19:22
Add a comment
|
2 Answers
It cannot be done with static SQL, unless you want to make assumptions about the maximum number of columns.
If you can make some assumptions about the maximum number of columns, you can just return the values in, say, 100 output variables named x_part1, x_part2, ... x_part100. Ugly but a lot easier. You could also return x_count to tell the caller how many there were.
Otherwise, it can only be done using dynamic SQL (e.g., EXECUTE IMMEDIATE) and then return a SYS_REFCURSOR to your caller.
But then, they'll have to use DBMS_SQL to convert the SYS_REFCURSOR to a DBMS_SQL cursor and then use that package to figure out how many columns are, what they are named, and then, ultimately, to fetch all the data. A real pain.
DYNAMIC SQL EXAMPLE (as requested)
DECLARE
i NUMBER := 0;
l_column_list VARCHAR2 (32000) := NULL;
l_sql VARCHAR2 (32000);
l_rc SYS_REFCURSOR;
BEGIN
FOR r IN (WITH test_data AS (SELECT 'a,b,ccc,ddddd,ee,,ggggg' test_string FROM DUAL),
parse1 AS
(SELECT test_string,
INSTR (',' || test_string || ',',
',',
1,
LEVEL)
start_pos,
INSTR (',' || test_string || ',',
',',
1,
LEVEL + 1)
- 2
end_pos
FROM test_data
CONNECT BY ROWNUM <= LENGTH (REGEXP_REPLACE (test_string, '[^,]', '')) + 1)
SELECT SUBSTR (test_string, start_pos, end_pos - start_pos + 1) field_part
FROM parse1) LOOP
i := i + 1;
IF l_column_list IS NOT NULL THEN
l_column_list := l_column_list || ',';
END IF;
l_column_list := l_column_list || '''' || r.field_part || ''' as PART_' || LPAD (TO_CHAR (i), 3, '0');
END LOOP;
l_sql := 'SELECT ' || l_column_list || ' FROM DUAL';
DBMS_OUTPUT.put_line (l_sql);
OPEN l_rc FOR l_sql;
-- Return l_rc to the caller
END;
12 Comments
Kumar
Thank you for your response. Can i have some sample to create the dynamic SQL.
Kumar
Hi, It is working fine, but the output is coming as select statement, which i want to insert into another table. can you tell me is there anyway to do that?
Kumar
I mean to fetch the columns from the output of this procedure and to insert into another table by mapping the exact columns
Matthew McPeak
That's where is gets really hard. You need to use DBMS_SQL extensively for that. If you can put a hard-limit on the number of possible columns, then you could
CREATE TYPE mytype AS OBJECT... and then change the SELECT to SELECT mytype(' || l_column_list || ') FROM DUAL. That way, you know you're only getting one column in the select, can fetch it, and then can insert the mytype fields into your table.Kumar
Is there any other way that we can add the insert statement in the for loop of above code itself, if both the tables are of same structure.
|
SET SERVEROUTPUT ON SIZE 1000000
DECLARE
v_grades NUMBER;
v_sql1 VARCHAR2(32767);
v_sql2 VARCHAR2(32767);
BEGIN
-- SELECT COUNT (grade) INTO v_grades FROM example3 group by name;
SELECT MAX(grade_ct) INTO v_grades FROM
(SELECT COUNT (grade) as grade_ct, name FROM example3 group by name);
v_sql1 := 'INSERT INTO example2(name';
v_sql2 := 'SELECT name';
FOR i IN 1..v_grades LOOP
v_sql1 := v_sql1 || ',grade' || i;
v_sql2 := v_sql2 || ',MAX(DECODE(dr,' || i || ',grade))';
END LOOP;
v_sql1 := v_sql1 || ') ';
v_sql2 := v_sql1 || v_sql2
|| ' FROM (SELECT name,grade,ROW_NUMBER()OVER (PARTITION BY name ORDER BY grade) dr FROM example3) GROUP BY name';
-- DBMS_OUTPUT.PUT_LINE (v_sql2);
EXECUTE IMMEDIATE v_sql2;
END;
/
finally, i modified the code accordingly and is working.