The choice of simple SQL will not work for you in this scenario. Although quite capable of producing formatted output, the nesting here would cause the issue. Try a select along the lines of:
select name || ' ' || lpad(emp_no) from emp
By using this you can get out the output you need. Now to get the nested details you would need to use something like PL/SQL, where you can use some looping to produce the necessary output and using DBMS_OUTPUT. This should provide you with the flexibility to produce what you will need.
Dig Deeper on Oracle business intelligence and analytics
Related Q&A from Ian Abramson
A SearchOracle.com member asks why a low-cost query has a slower speed than expected. Continue Reading
We are working on the development of a datamart (in 9i) which takes data from two source systems. Since this is a transaction system, there is a lot ... Continue Reading
I am new to Oracle's ETL tool and I need information on data migration using Oracle ETL. Continue Reading