c2r: Col to Row

Lets see what solution can be given when it comes to publish complete column to row.

Prerequisites:

create a type c_2_r of type table with one

CREATE OR REPLACE TYPE c_2_r_obj AS OBJECT 
(   disp_data  varchar2(4000) );

CREATE OR REPLACE TYPE c_2_r as table of c_2_r_obj ;

Code:

CREATE OR REPLACE function
c2r
(
in_tbl_name in varchar2, -- Name of the Table(No Joined tables allowed yet)
in_sel_col_list varchar2, --list of the selection cols with in_seprator as a separating character [Use * is all are required]
in_whr_col_name in varchar2, --list of the 'where col names' with in_seprator as a separating character
in_whr_col_val in varchar2, --list of the 'where col values' with in_seprator as a separating character
in_separator in char default null -- Use any sensible separator like ; , $ etc
)
RETURN c_2_r
PIPELINED
IS
TYPE RefCurTyp IS REF CURSOR;
cur RefCurTyp;
v_statement VARCHAR2(1000);
colval CLOB;
rowval CLOB;

--col_name user_tab_columns.COLUMN_NAME%TYPE;
col_name varchar2(4000);

v_op_details RefCurTyp;
v_sql varchar2(4000);

/* Section used for looping the Select list and build a logic for the same*/
v_count_col_name_main number(10); -- count of the main col name list may or may not be used. reserved for future use
v_startval_col_name_main number(10); -- Start val
v_currposs_col_name_main number(10); -- Current Cursor Position
v_trimlen_col_name_main number(10); -- Length to Trim
v_sel_col_name_main varchar2(50); -- Main Current Selected col name
v_final_col_list varchar2(4000); -- Final col list obtained
v_sel_col_list varchar2(4000); -- input list

v_count_col_name number(10);
v_startval_col_name number(10);
v_currposs_col_name number(10);
v_trimlen_col_name number(10);
v_whr_col_name varchar2(50);

v_count_col_val number(10);
v_startval_col_val number(10);
v_currposs_col_val number(10);
v_trimlen_col_val number(10);
v_whr_col_val varchar2(50);
v_where_clause varchar2(100);

v_whr_col_name_ip varchar2(4000);
v_whr_col_val_ip varchar2(4000);
v_count number(10);
BEGIN

v_count:=0;
v_startval_col_name_main :=1;
v_count_col_name_main := 0;
v_trimlen_col_name_main :=0;

v_sql := '
select column_name
from user_tab_columns
where table_name = upper( ''' || in_tbl_name || ''')
and data_type not in (''DATE'',''LONG'') ';

IF in_sel_col_list <> '*' THEN
BEGIN

v_sel_col_list := in_sel_col_list;

if substr(v_sel_col_list,length(v_sel_col_list)-1) != in_separator then
v_sel_col_list := v_sel_col_list || in_separator;
end if;

while v_startval_col_name_main < length(in_sel_col_list)
loop
select length(substr(v_sel_col_list,v_startval_col_name_main,instr(v_sel_col_list,in_separator,v_startval_col_name_main) - v_startval_col_name_main)) into v_trimlen_col_name_main from dual;
select upper(substr(v_sel_col_list,v_startval_col_name_main ,v_trimlen_col_name_main )) into v_sel_col_name_main from dual;
v_final_col_list := v_final_col_list || '''' || v_sel_col_name_main || ''',';
v_startval_col_name_main := v_startval_col_name_main + v_trimlen_col_name_main + 1;
end loop;

v_final_col_list := substr(v_final_col_list,1,length(v_final_col_list)-1);
v_sql := v_sql || ' and column_name in (' || v_final_col_list || ')';
END;
END IF;

DBMS_OUTPUT.PUT_LINE ( 'v_sql = ' || v_sql );

OPEN V_OP_DETAILS for v_sql;
LOOP
FETCH V_OP_DETAILS INTO col_name;
EXIT WHEN V_OP_DETAILS%NOTFOUND;

v_count:= v_count + 1;

v_statement := 'select '||col_name||' from '||in_tbl_name;

if in_whr_col_name != '*' then

if v_count = 1 then
begin

v_startval_col_name :=1;
v_count_col_name := 0;
v_trimlen_col_name :=0;

v_startval_col_val :=1;
v_count_col_val := 0;
v_trimlen_col_val :=0;

v_whr_col_name_ip := in_whr_col_name;
v_whr_col_val_ip := in_whr_col_val;

if substr(v_whr_col_name_ip,length(v_whr_col_name_ip)-1) != in_separator then
v_whr_col_name_ip := v_whr_col_name_ip || in_separator;
end if;

if substr(v_whr_col_val_ip,length(v_whr_col_val_ip)-1) != in_separator then
v_whr_col_val_ip := v_whr_col_val_ip || in_separator;
end if;

while v_startval_col_name < length(v_whr_col_name_ip)
loop

select length(substr(v_whr_col_name_ip,v_startval_col_name,instr(v_whr_col_name_ip,in_separator,v_startval_col_name) - v_startval_col_name)) into v_trimlen_col_name from dual;
select substr(v_whr_col_name_ip,v_startval_col_name ,v_trimlen_col_name ) into v_whr_col_name from dual;
v_startval_col_name := v_startval_col_name + v_trimlen_col_name + 1;

select length(substr(v_whr_col_val_ip,v_startval_col_val,instr(v_whr_col_val_ip,in_separator,v_startval_col_val) - v_startval_col_val)) into v_trimlen_col_val from dual;
select substr(v_whr_col_val_ip,v_startval_col_val ,v_trimlen_col_val ) into v_whr_col_val from dual;
v_startval_col_val := v_startval_col_val + v_trimlen_col_val + 1;

v_where_clause := v_where_clause || ' ' || v_whr_col_name || ' = ' || '''' || v_whr_col_val || ''' AND ';

end loop;

v_where_clause := substr(v_where_clause,1,length(v_where_clause)-4);

end;
end if;

v_statement := v_statement || ' where ' || v_where_clause;
end if;

rowval := null;
OPEN cur for v_statement;
LOOP
FETCH cur INTO colval;
EXIT WHEN cur%NOTFOUND;
rowval:=rowval|| to_char(colval) || nvl(in_separator,'');
END LOOP;

CLOSE cur;

PIPE ROW( col_name||': '|| substr(rowval,1,length(rowval)-1));
END LOOP;

RETURN;
END;
/