Toad World® Forums

Execution time dynamic Oracle Query

Message from: *Vic

I’m looking to create a stored procedure that will select specific columns
based on passed parameter information. The number of columns can be different
from query to query. Is there any way to do this?


Historical Messages

Author: Vic Lenza
Date: Thu Dec 08 09:32:15 PST 2011
I’m looking to create a stored procedure that will select specific columns
based on passed parameter information. The number of columns can be different
from query to query. Is there any way to do this?

__


You could do create a dynamic sql statement in your stored procedure.

Such as:

lString := ‘ select ‘ Col1 || ‘ , ‘ || Col2 … ||
‘, ‘ || ColN ‘ from tablename’;

Execute immediate lString;

Read up on dynamic sql. You can probably figure out your solution to your
problem.

james

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:

Column1:column2:column3

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 :slight_smile:

Roger S.

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.

Message from: whgwannabe

Agreed Roger, several ways to do it, each with their own pitfalls. I haven’t
tried it in a while, but to be truly dynamic both in and out I think dbms_sql is
still “a” way, but probably the simplest is using dynamic sql to pass
the columns into a procedure that returns a ref cursor so you also have dyamic
output. There are good examples and discussion at
http://docs.oracle.com/cd/B10500_01/appdev.920/a96590/adg09dyn.htm


Historical Messages

Author: Chris Johnson
Date: Thu Dec 08 11:06:03 PST 2011
Agreed Roger, several ways to do it, each with their own pitfalls. I haven’t
tried it in a while, but to be truly dynamic both in and out I think dbms_sql is
still “a” way, but probably the simplest is using dynamic sql to pass
the columns into a procedure that returns a ref cursor so you also have dyamic
output. There are good examples and discussion at
http://docs.oracle.com/cd/B10500_01/appdev.920/a96590/adg09dyn.htm
__

Author: Simoneau, Roger
Date: Thu Dec 08 09:58:23 PST 2011

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: Column1:column2:column3 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 :slight_smile: Roger S. 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.
__

Author: Davis, James A (HSS)
Date: Thu Dec 08 09:32:15 PST 2011
You could do create a dynamic sql statement in your stored procedure. Such as:
lString := ‘ select ‘ Col1 || ‘ , ‘ || Col2 … ||
‘, ‘ || ColN ‘ from tablename’; Execute immediate
lString; Read up on dynamic sql. You can probably figure out your solution to
your problem. james
__

Author: Vic Lenza
Date: Thu Dec 08 09:32:15 PST 2011
Message from: *Vic I’m looking to create a stored procedure that will select
specific columns based on passed parameter information. The number of columns
can be different from query to query. Is there any way to do this?
_______________________________________ Historical Messages Author: Vic Lenza
Date: Thu Dec 08 09:32:15 PST 2011 I’m looking to create a stored procedure
that will select specific columns based on passed parameter information. The
number of columns can be different from query to query. Is there any way to do
this? __ _______________________________________
__


Agreed Roger, several ways to do it, each with their own pitfalls. I haven’t tried it in a while, but to be truly dynamic both in and out I think dbms_sql is still “a” way, but probably the simplest is using dynamic sql to pass the columns into a procedure that returns a ref cursor so you also have dyamic output. There are good examples and discussion at http://docs.oracle.com/cd/B10500_01/appdev.920/a96590/adg09dyn.htm