Web Development

Ask something
Question Locked

Oracle - Select statement with partial column names?

0 helpful
2 replies

by f.gruman 1 view

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I have inherited a database that has some tables that are wide (60-80 columns each). Many of these columns are due to the original designers adding in user-defined field functionality. They were kind enough to start all of these columns with UDFy_xxxx, where y can be D, C, N (Date, Character, Number, respectively).

I have a request to generate some queries from these columns. My question is whether there is a way to perform a regular expression against the column name in the select statement to return a dynamic set of columns. I am trying to avoid using dynamic SQL.

e.g. Select % from mytable where ...

I have tried to do a cursor operation to query column names from user_tab_cols, but still couldn't come up with a clean way of adding these values into the column definition.

Any help would be appreciated.

Regards,
Frank

2 replies

by marv732

If your column names are not known until runtime then use a shell script to spool a list of column names out to a file then use unix regular expressions to build the query, pump it into an sql file and then run sqlplus to execute it.
If you know the column names and the queries are static (say for regular reports) then just bite the bullet and write them out into a script or package and run from there.

Greg

This discussion is closed to new replies.