StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Here is some code that will construct dynamic SQL from the mapping definitions. For simplicity I have used the EMP table as table A, with column DEPTNO serving as the type. </p> <pre><code>declare q long; rc sys_refcursor; first boolean := true; l_field1 varchar2(100); l_field2 varchar2(100); function mapcol (p_field_mapping varchar2) return varchar2 is l_retval varchar2(32); begin if p_field_mapping is not null then l_retval := 'to_char(a.' || p_field_mapping || ')'; else l_retval := 'null'; end if; return l_retval; end; begin -- Construct dynamic SQL for r_map in (select * from mapping_table) loop if first then first := false; else q := q || ' union all '; end if; q := q || 'select '; q := q || mapcol(r_map.field_1_mapping) || ', '; q := q || mapcol(r_map.field_2_mapping); q := q || ' from emp a where a.deptno = ' || r_map.type; end loop; -- Run SQL and show results dbms_output.put_line('SQL = ' || q); dbms_output.put_line(''); dbms_output.put_line('Results'); dbms_output.put_line('-------'); open rc for q; loop fetch rc into l_field1, l_field2; exit when rc%notfound; dbms_output.put_line(l_field1 || ', ' || l_field2); end loop; end; </code></pre> <p>I then created this mapping table:</p> <pre><code>SQL> create table mapping_table (type integer, 2> field_1_mapping varchar2(30), field_2_mapping varchar2(30)); Table created. SQL> insert into mapping_table values (10, 'ENAME', 'SAL'); 1 row created. SQL> insert into mapping_table values (20, 'SAL', 'JOB'); 1 row created. SQL> insert into mapping_table values (30, 'JOB', 'HIREDATE'); 1 row created. SQL> commit; Commit complete. </code></pre> <p>When I run it (with SERVEROUTPUT ON in SQL Plus) I get:</p> <pre><code>SQL = select to_char(a.ENAME), to_char(a.SAL) from emp a where a.deptno = 10 union all select to_char(a.SAL), to_char(a.JOB) from emp a where a.deptno = 20 union all select to_char(a.JOB), to_char(a.HIREDATE) from emp a where a.deptno = 30 Results ------- CLARK, 7450 KING, 10000 TEST, MILLER, 6500 BINNSY, 100 FARMER, 123 7975, MANAGER 4566, ANALYST 8000, ANALYST 5000, janitor SALESMAN, SALESMAN, 22-FEB-1981 SALESMAN, 28-SEP-1981 MANAGER, 01-MAY-1981 SALESMAN, 08-SEP-1981 MANAGER, 19-JUL-2008 PL/SQL procedure successfully completed. </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload