1

For example, I have an sql query 

SELECT * 
FROM EMPLOYEE 
INNER JOIN DEPARTMENT 
ON EMPLOYEE.DEP_ID = DEPARTMENT.DEP_ID

Now, i want to get all the column name of the return result: DEP_ID, EMP_ID,.....

I'm developing a j2ee web like http://www.w3schools.com/sql/trysql.asp?filename=trysql_select_all where you input sql query and get result table

Pரதீப்
  • 91,748
  • 19
  • 131
  • 172
user2090487
  • 107
  • 1
  • 3
  • 7
  • 2
    Use [`ResultSetMetdata.getColumnName()`](http://docs.oracle.com/javase/7/docs/api/java/sql/ResultSetMetaData.html#getColumnName%28int%29) –  Mar 04 '15 at 10:33
  • 1
    To do it in database side, you could use `dbms_sql.describe_columns2`. See the answer for more details and examples. – Lalit Kumar B Mar 04 '15 at 10:56
  • Why have you tagged this with [tag:oracle-sqldeveloper] if you're developing a Java app? If you are using JDBC, [here's an example](http://stackoverflow.com/q/696782/266304), but with that tag it isn't quite clear if this is a duplicate? – Alex Poole Mar 04 '15 at 11:53
  • Probably by the tags I considered it to be something related to Oracle PL/SQL and answered accordingly. I completely missed to see that W3Fools link. – Lalit Kumar B Mar 04 '15 at 13:50

2 Answers2

2

There is a way to get all the column names of a query, using dbms_sql.describe_columns2. But, it has to be done in PL/SQL.

For example,

I want to get the list of all columns of the following SQL:

SELECT emp.empno, emp.ename, dept.deptno 
FROM emp 
INNER JOIN dept 
ON emp.deptno = dept.deptno

The following anonymous block would list down all the column names in the exact order they are in the select list:

SQL> set serveroutput on
SQL> DECLARE
  2    l_cursor NUMBER := dbms_sql.open_cursor;
  3    l_ignore NUMBER;
  4    l_desc dbms_sql.desc_tab2;
  5    l_cnt NUMBER;
  6  BEGIN
  7    dbms_sql.parse( l_cursor, 'SELECT emp.empno, emp.ename, dept.deptno
  8  FROM emp
  9  INNER JOIN dept
 10  ON emp.deptno = dept.deptno', dbms_sql.native );
 11    dbms_sql.describe_columns2( l_cursor, l_cnt, l_desc );
 12    FOR i IN 1 .. l_cnt
 13    LOOP
 14      dbms_output.put_line( 'Column ' || i || ' is "' || l_desc(i).col_name || '"' );
 15    END LOOP;
 16    dbms_sql.close_cursor( l_cursor );
 17  END;
 18  /
Column 1 is "EMPNO"
Column 2 is "ENAME"
Column 3 is "DEPTNO"

PL/SQL procedure successfully completed.

SQL>

It would also give you the ALIASES for the column names as well:

SQL> DECLARE
  2    l_cursor NUMBER := dbms_sql.open_cursor;
  3    l_ignore NUMBER;
  4    l_desc dbms_sql.desc_tab2;
  5    l_cnt NUMBER;
  6  BEGIN
  7    dbms_sql.parse( l_cursor, 'SELECT emp.empno employee_id, emp.ename employee_name, dept.deptno department_no
  8  FROM emp
  9  INNER JOIN dept
 10  ON emp.deptno = dept.deptno', dbms_sql.native );
 11    dbms_sql.describe_columns2( l_cursor, l_cnt, l_desc );
 12    FOR i IN 1 .. l_cnt
 13    LOOP
 14      dbms_output.put_line( 'Column ' || i || ' is "' || l_desc(i).col_name || '"' );
 15    END LOOP;
 16    dbms_sql.close_cursor( l_cursor );
 17  END;
 18  /
Column 1 is "EMPLOYEE_ID"
Column 2 is "EMPLOYEE_NAME"
Column 3 is "DEPARTMENT_NO"

PL/SQL procedure successfully completed.

SQL>

Since you are using SELECT *`, you could also list down the column names from [DBA|ALL|USER]_TAB_COLUMNS:

SQL> SELECT column_name FROM user_tab_columns WHERE table_name IN ('EMP','DEPT');

COLUMN_NAME
--------------------------------------------------------------------------------
EMPNO
ENAME
JOB
MGR
HIREDATE
SAL
COMM
DEPTNO
DEPTNO
DNAME
LOC

11 rows selected.

This is only valid since you are using SELECT *, else you need to use the anonymous block as I have shown above.

Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124
0

You could query the ALL_TAB_COLUMNS table to get all the columns within the EMPLOYEE table

Not sure if you want these for formatting or not, so not exactly sure of your desired final result

To control that the columns and values align, you may need to use Dynamic SQL to replace the * in your SELECT statement to control the order the columns are returned

Mike
  • 2,391
  • 6
  • 33
  • 72