Is it possible to refer to column names via bind variables in Oracle?

Question

I am trying to refer to a column name to order a query in an application communicating with an Oracle database. I want to use a bind variable so that I can dynamically change what to order the query by.

The problem that I am having is that the database seems to be ignoring the order by column.

Does anyone know if there is a particular way to refer to a database column via a bind variable or if it is even possible?

e.g my query is SELECT * FROM PERSON ORDER BY :1 (where :1 will be bound to PERSON.NAME) The query is not returning results in alphabetical order, I am worried that the database is interpreting this as:- SELECT * FROM PERSON ORDER BY 'PERSON.NAME' which will obviously not work.

Any suggestions are much appreciated.

Answer 1

No. You cannot use bind variables for table or column names.

This information is needed to create the execution plan. Without knowing what you want to order by, it would be impossible to figure out what index to use, for example.

Instead of bind variables, you have to directly interpolate the column name into the SQL statement when your program creates it. Assuming that you take precautions against SQL injection, there is no downside to that.

Update: If you really wanted to jump through hoops, you could probably do something like

order by decode(?, 'colA', colA, 'colB', colB)

but that is just silly. And slow. Don't.

Answer 2

As you are using JDBC. You can rewrite your code, to something without bind variables. This way you can also dynamically change the order-by e.g.:

    String query = "SELECT * FROM PERS ";
    if (condition1){
      query = query+ " order by name ";
    // insert more if/else or case statements
    } else {
      query = query+ " order by other_column ";
    }
    Statement select = conn.createStatement();
    ResultSet result = select.executeQuery(query);

Or even

    String columnName = getColumnName(input);
    Statement select = conn.createStatement();
    ResultSet result = select.executeQuery("SELECT * FROM PERS ORDER BY "+columnName);

Answer 3

ResultSet result = select.executeQuery("SELECT * FROM PERS ORDER BY "+columnName);

will always be a NEW statement to the database. That means it is, like Thilo already explained, impossible to "reorder" an already bound, calculated, prepared, parsed statement. When using this result set over and over in your application and the only thing, which changes over time is the order of the presentation, try to order the set in your client code. Otherwise, dynamic SQL is fine, but comes with a huge footprint.