CODEWITHSUNDEEP
sqloracle-database
Muhammad Hewedy
Muhammad Hewedy

Reputation: 30078

Oracle: how to run this query (generated column names)

I need to run a query on generated generated column names.

Here's the query:

select 'col_'||4 from  MY_TABLE

Note:

  1. "4" is a variable that is passed to this query from within the Java code
  2. MY_TABLE is a table that contain columns with names (col_4, col_5, etc..)

Upvotes: 0

Views: 1614

Answers (3)

João Júnior
João Júnior

Reputation: 884

This code generates a SELECT that returns the tables with their column name:

SELECT
'SELECT ' ||(
    SELECT
        LISTAGG(
            c.TABLE_NAME || '.' || c.COLUMN_NAME || ' AS "' || c.TABLE_NAME || '.' || c.COLUMN_NAME || '"',
            ', '
        ) WITHIN GROUP(
        ORDER BY
            c.TABLE_NAME
        ) "TABLE_NAMES"
    FROM
        USER_TAB_COLS c
    WHERE
        TABLE_NAME IN(
            'PESSOA',
            'PESSOA_FISICA',
            'PESSOA_JURIDICA'
        )
)|| 'FROM PERSON;'
FROM
  DUAL;

Upvotes: 1

Codo
Codo

Reputation: 78905

To run a dynamic SELECT statement, you have two choices:

For single row selects, you use EXECUTE IMMEDIATE ... INTO:

EXECUTE IMMEDIATE 'select col_' || l_num  || ' from  MY_TABLE WHERE id = 37' INTO l_result;

For selecting multiple rows, you can use a dynamic cursor:

DECLARE
   TYPE MyCurType IS REF CURSOR;
   my_cv   MyCurType;
BEGIN
   OPEN emp_cv FOR 'select col_' || l_num  || ' from  MY_TABLE';
   ...
END;

Upvotes: 1

Michael Pakhantsov
Michael Pakhantsov

Reputation: 25390

Inside Oracle you need use dynamic SQL. (YourVariable value is 4 for your example)

EXECUTE IMMEDIATE ' select col_' || YourVariable  || ' from  MY_TABLE ';

From Java you can build any SQL and execute them

Upvotes: 3

Related Questions