Pl/Sql- Get Column Names from a Query

How can I get column names from a table in Oracle?

You can query the USER_TAB_COLUMNS table for table column metadata.

SELECT table_name, column_name, data_type, data_length
FROM USER_TAB_COLUMNS
WHERE table_name = 'MYTABLE'

Pl/SQL- Get column names from a query

I believe you can use DESCRIBE_COLUMNS to do this. Just pass in the cursor and the other required parameters.

http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_sql.htm#i1026120

declare
    v_sql varchar2(32767) := 'select 1 column1, 2 column2 from dual';
    v_cursor_id integer;
    v_col_cnt integer;
    v_columns dbms_sql.desc_tab;
begin
    v_cursor_id := dbms_sql.open_cursor;
    dbms_sql.parse(v_cursor_id, v_sql, dbms_sql.native);
    dbms_sql.describe_columns(v_cursor_id, v_col_cnt, v_columns);

    for i in 1 .. v_columns.count loop
        dbms_output.put_line(v_columns(i).col_name);
    end loop;

    dbms_sql.close_cursor(v_cursor_id);
exception when others then
    dbms_sql.close_cursor(v_cursor_id);
    raise;
end;
/

Output:
COLUMN1
COLUMN2

Oracle query to fetch column names

The Oracle equivalent for information_schema.COLUMNS is USER_TAB_COLS for tables owned by the current user, ALL_TAB_COLS or DBA_TAB_COLS for tables owned by all users.

Tablespace is not equivalent to a schema, neither do you have to provide the tablespace name.

Providing the schema/username would be of use if you want to query ALL_TAB_COLS or DBA_TAB_COLS for columns OF tables owned by a specific user. in your case, I'd imagine the query would look something like:

String sqlStr= "
SELECT column_name
  FROM all_tab_cols
 WHERE table_name = 'USERS'
   AND owner = '" +_db+ "'
   AND column_name NOT IN ( 'PASSWORD', 'VERSION', 'ID' )"

Note that with this approach, you risk SQL injection.

EDIT: Uppercased the table- and column names as these are typically uppercase in Oracle; they are only lower- or mixed case if created with double quotes around them.

How to get column names from a query?

You can try this on a tJavaRow, following your DBInput component :

for (java.lang.reflect.Field field: row1.getClass().getDeclaredFields()) {
context.columnName = field.getName();
      System.out.println("Field name is " + context.columnName );      
           }

Spotted on talend help center here : https://community.talend.com/t5/Design-and-Development/resolved-how-to-get-the-column-names-in-a-data-flow/td-p/99172

You can extend this, and put the column list on your outputflow :

//add this inside the loop, and 'columnNames' as an output row in tJavaRow schema

             output_row.columnNames+=context.columnName+";";

With a tNormalize after tJavaRow, you shoud get the expected result.

How to get all columns' names for all the tables in Oracle?

You can use

SELECT *
  FROM user_tab_cols
 ORDER BY table_name, column_name, column_id

after connecting to the schema from which you want to get the related information.

Search an Oracle database for tables with specific column names?

To find all tables with a particular column:

select owner, table_name from all_tab_columns where column_name = 'ID';

To find tables that have any or all of the 4 columns:

select owner, table_name, column_name
from all_tab_columns
where column_name in ('ID', 'FNAME', 'LNAME', 'ADDRESS');

To find tables that have all 4 columns (with none missing):

select owner, table_name
from all_tab_columns
where column_name in ('ID', 'FNAME', 'LNAME', 'ADDRESS')
group by owner, table_name
having count(*) = 4;

Get column or alias name from a provided query in Oracle sql

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.

---

Related Topics