CODEWITHSUNDEEP
sqloracle-databaseoracle11g
MetallicPriest
MetallicPriest

Reputation: 30843

How to select columns on the basis of column names in Oracle

I tried the following, but that doesn't work.

select (
    select COLUMN_NAME from ALL_TAB_COLUMNS 
    WHERE COLUMN_NAME LIKE '%_<SOME_SUFFIX>' 
    AND OWNER = '<SCHEMA>' AND TABLE_NAME = '<TABLE_NAME>'
)
FROM <SCHEMA>.<TABLE_NAME>

Upvotes: 0

Views: 69

Answers (1)

Littlefoot
Littlefoot

Reputation: 143103

In this example, I'm composing a select statement based on list of columns from a table (passed to function as a parameter) whose name satisfies certain condition. As it is Scott's emp table and its columns don't have any suffix, I'm choosing columns whose name contains letter E.

Based on that list of columns (aggregated with listagg), l_str variable contains a full select statement which is - in turn - a source for refcursor.

SQL> CREATE OR REPLACE FUNCTION f_test (par_table_name IN VARCHAR2)
  2     RETURN SYS_REFCURSOR
  3  IS
  4     l_cols  VARCHAR2 (100);
  5     l_str   VARCHAR2 (200);
  6     rc      SYS_REFCURSOR;
  7  BEGIN
  8     -- list of column names that satisfy some condition; for example, have some suffix or, as
  9     -- in my example, have a letter "E" in their name
 10     SELECT LISTAGG (column_name, ', ') WITHIN GROUP (ORDER BY column_name)
 11       INTO l_cols
 12       FROM user_tab_columns
 13      WHERE     table_name = DBMS_ASSERT.sql_object_name (par_table_name)
 14            AND INSTR (column_name, 'E') > 0;
 15
 16     l_str :=
 17           'select '
 18        || l_cols
 19        || ' from '
 20        || DBMS_ASSERT.sql_object_name (par_table_name);
 21
 22     OPEN rc FOR l_str;
 23
 24     RETURN rc;
 25  END;
 26  /

Function created.

Testing:

SQL> select f_test('EMP') from dual;

F_TEST('EMP')
--------------------
CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

    DEPTNO      EMPNO ENAME      HIREDATE
---------- ---------- ---------- ----------
        20       7369 SMITH      17/12/1980
        30       7499 ALLEN      20/02/1981
        30       7521 WARD       22/02/1981
        20       7566 JONES      02/04/1981
        30       7654 MARTIN     28/09/1981
        30       7698 BLAKE      01/05/1981
        10       7782 CLARK      09/06/1981
        20       7788 SCOTT      09/12/1982
        10       7839 KING       17/11/1981
        30       7844 TURNER     08/09/1981
        20       7876 ADAMS      12/01/1983
        30       7900 JAMES      03/12/1981
        20       7902 FORD       03/12/1981
        10       7934 MILLER     23/01/1982

14 rows selected.

    
SQL> select f_test('DEPT') from dual;

F_TEST('DEPT')
--------------------
CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

    DEPTNO DNAME
---------- --------------
        10 ACCOUNTING
        20 RESEARCH
        30 SALES
        40 OPERATIONS


SQL>

Upvotes: 3

Related Questions