select specific columns based on passed parameter information
A comment first: when using dynamic SQL it’s a good rule of thumb to only use it in situations with a small number of iterations. If you intend to do lots of looping (and in this case, “lots” is measured in the 10’s) then you want to rethink your strategy and what you need to do. That would be my advice on when to use dynamic sql.
You can certainly select columns based on such an input parameter. For example, it’s easy enough to define your input parameter structure in a way that your code parser can handle:
In that example, your parser separates the values based on the colon.
Building your sql string is equally easy. The primary difficulty will be in how you wish to receive the information. The primary dynamic sql method you’d likely use would probably be “execute immediate”. It is formed as:
execute immediate (sql_query_without_semi_colon) into ret_value1, ret_value2;
As you can see, the return values are defined external to your actual sql query. This affects the flexibility of what you can drop in. In the above example, you must always call 2 columns for return values or your sql will error out.
A solution to that is to form your multiple columns into another delimited value. So… what you’d like:
Select column1 from first_query;
Select column1, column2, column3 from second_query;
Select column1, column2, … columnN from third_query;
To build for all those situations you need a compromise:
Select column1 as full_data from first_query;
Select column1 || ‘:’ || column2 || ‘:’ || column3 as full_data from second_query;
Select column1 || ‘:’ || column2 || … || columnN as full_data from third_query;
However… you knew there’d be a however didn’t ya? However depending on the size of the columns you can quickly run out of space in your loading variable so you’ll have to take that issue into consideration.
I have not touched on all the issues you’ll need to deal with. I’ve only shown sufficient issues, and the fact that each issue generally has a solution, that should give you pause for thought and maybe consider alternative solutions for your problem.
Sometimes it really isn’t a good idea to try and code a single procedure to try and do everything… ok, in my humble opinion, it’s never a good idea
This communication, including any attached documentation, is intended only for the person or entity to which it is addressed, and may contain confidential, personal and/or privileged information. Any unauthorized disclosure, copying, or taking action on the contents is strictly prohibited. If you have received this message in error, please contact us immediately so we may correct our records. Please then delete or destroy the original transmission and any subsequent reply.