CODEWITHSUNDEEP
sqloracle-database
Rnet
Rnet

Reputation: 5040

Oracle dynamic query

I've a simple query, vanilla select statement to which I want to filter the values provided by the user. 

SELECT A,B,C,D,E FROM TAB
WHERE ....

Here the WHERE is not fixed i.e the user may input values for C, so only C should be filtered, or D or E so on. The problem is due to the user telling- filter callerID between 1 and 10 etc, but the database column has a different name. So to form a working query I have to map callerID to the columnName. As this would be in a procedure I've thought of passing the csv of userInputColumnNames, csv of dbColumns and filter begin and start. Then laboriously extract the values and match the correct db column name and form the query. This works, but however this is very cumbersome and not clean. Could there be a better way of doing this?

Upvotes: 0

Views: 382

Answers (1)

roartechs
roartechs

Reputation: 1355

Do the column names in the table change? Or are columns in the table added/removed? If not, you can generate a number to map to each column in the table like:

SQL> SELECT column_name, ROW_NUMBER() OVER (PARTITION BY 1 ORDER BY column_name) "COLUMN_NUMBER"
  2  FROM dba_tab_columns
  3  WHERE table_name='DBA_USERS'
  4  
baim> /

COLUMN_NAME                    COLUMN_NUMBER
------------------------------ -------------
ACCOUNT_STATUS                             1
CREATED                                    2
DEFAULT_TABLESPACE                         3
EXPIRY_DATE                                4
EXTERNAL_NAME                              5
INITIAL_RSRC_CONSUMER_GROUP                6
LOCK_DATE                                  7
PASSWORD                                   8
PROFILE                                    9
TEMPORARY_TABLESPACE                      10
USERNAME                                  11
USER_ID                                   12

12 rows selected.

Then when the user selects column 9, you know it maps to the "PROFILE" column.

If the column names can change, or if columns are added/dropped dynamically, then this won't work though.

Upvotes: 1

Related Questions