Toad World® Forums

Execution time dynamic Oracle Query

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?

Message from: whgwannabe

I did this exact thing a few years ago. An app I was finishing required about 20
reports, but upon analysis they were mostly the same columns, just sorted and
summarized differently. I wrote an HTML page to let them pick the columns they
wanted to see, the sort order, break levels etc., then I’d string together
which columns they checked, build the dort and group by etc., then used dbms_sql
to execute the string. It’s tedious to set up, but once written, it’s
easy to maintain. Flexible criteria in, flexible reporting out.

These days I’d do it with execute_immediate, but back in the 8i days that
was how I did it.


Historical Messages

Author: Chris Johnson
Date: Thu Dec 08 10:42:59 PST 2011
I did this exact thing a few years ago. An app I was finishing required about 20
reports, but upon analysis they were mostly the same columns, just sorted and
summarized differently. I wrote an HTML page to let them pick the columns they
wanted to see, the sort order, break levels etc., then I’d string together
which columns they checked, build the dort and group by etc., then used dbms_sql
to execute the string. It’s tedious to set up, but once written, it’s
easy to maintain. Flexible criteria in, flexible reporting out.

These days I’d do it with execute_immediate, but back in the 8i days that
was how I did it.
__

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?

__


I did this exact thing a few years ago. An app I was finishing required about 20 reports, but upon analysis they were mostly the same columns, just sorted and summarized differently. I wrote an HTML page to let them pick the columns they wanted to see, the sort order, break levels etc., then I’d string together which columns they checked, build the dort and group by etc., then used dbms_sql to execute the string. It’s tedious to set up, but once written, it’s easy to maintain. Flexible criteria in, flexible reporting out.

These days I’d do it with execute_immediate, but back in the 8i days that was how I did it.

Message from: *Vic

I’m calling a middle tier stored procedure and will have all the criteria
available via parameters so no need for html. Just need to know how to construct
and execute. I’m new to ORACLE and don’t have it down yet. Any examples
would be great!


Historical Messages

Author: Vic Lenza
Date: Thu Dec 08 11:07:33 PST 2011
I’m calling a middle tier stored procedure and will have all the criteria
available via parameters so no need for html. Just need to know how to construct
and execute. I’m new to ORACLE and don’t have it down yet. Any examples
would be great!
__

Author: Chris Johnson
Date: Thu Dec 08 10:42:59 PST 2011
I did this exact thing a few years ago. An app I was finishing required about 20
reports, but upon analysis they were mostly the same columns, just sorted and
summarized differently. I wrote an HTML page to let them pick the columns they
wanted to see, the sort order, break levels etc., then I’d string together
which columns they checked, build the dort and group by etc., then used dbms_sql
to execute the string. It’s tedious to set up, but once written, it’s
easy to maintain. Flexible criteria in, flexible reporting out.

These days I’d do it with execute_immediate, but back in the 8i days that
was how I did it.
__

Author: Chris Johnson
Date: Thu Dec 08 10:42:59 PST 2011
Message from: whgwannabe I did this exact thing a few years ago. An app I was
finishing required about 20 reports, but upon analysis they were mostly the same
columns, just sorted and summarized differently. I wrote an HTML page to let
them pick the columns they wanted to see, the sort order, break levels etc.,
then I’d string together which columns they checked, build the dort and
group by etc., then used dbms_sql to execute the string. It’s tedious to set
up, but once written, it’s easy to maintain. Flexible criteria in, flexible
reporting out. These days I’d do it with execute_immediate, but back in the
8i days that was how I did it. _______________________________________
Historical Messages Author: Chris Johnson Date: Thu Dec 08 10:42:59 PST 2011 I
did this exact thing a few years ago. An app I was finishing required about 20
reports, but upon analysis they were mostly the same columns, just sorted and
summarized differently. I wrote an HTML page to let them pick the columns they
wanted to see, the sort order, break levels etc., then I’d string together
which columns they checked, build the dort and group by etc., then used dbms_sql
to execute the string. It’s tedious to set up, but once written, it’s
easy to maintain. Flexible criteria in, flexible reporting out. These days
I’d do it with execute_immediate, but back in the 8i days that was how I did
it. __ 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? __ _______________________________________
__

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?

__


Any examples would be great!

The simplest example is as James outlined. You can use that as a starting point.

Tip 1: Built the “after” SQL string exactly as you want it.

Tip 2: Then place the SQL string into a variable.

When dealing with the quotes so you can get them the way you want them you can
send the text value through a general sql statement:

Select built_text from dual;

If it successfully runs, it’ll produce an output string. You can visually
check the output string – or run it as-is – to see if the structure
is exactly what you want.

Final tip: since your new, play with execute immediate first. See how it works.
After you’re comfortable with it, then apply the more complex queries you
want into a logical structure.

Roger S.

I’m calling a middle tier stored procedure and will have all the criteria available via parameters so no need for html. Just need to know how to construct and execute. I’m new to ORACLE and don’t have it down yet. Any examples would be great!

Message from: *Vic

declare

l_nam varchar2 ( 1000 ) default ‘’ ;

begin

l_nam := ‘select name from saleable_items where si_division =
‘‘LH’’’ ;

execute immediate l_nam ;

Select built_text from dual ;

end;

Tried this simple query and its giving an error on built_text being an invalid
identifier.


Historical Messages

Author: Vic Lenza
Date: Thu Dec 08 11:45:35 PST 2011

declare

l_nam varchar2 ( 1000 ) default ‘’ ;

begin

l_nam := ‘select name from saleable_items where si_division =
‘‘LH’’’ ;

execute immediate l_nam ;

Select built_text from dual ;

end;

Tried this simple query and its giving an error on built_text being an invalid
identifier.

__

Author: Vic Lenza
Date: Thu Dec 08 11:07:33 PST 2011
I’m calling a middle tier stored procedure and will have all the criteria
available via parameters so no need for html. Just need to know how to construct
and execute. I’m new to ORACLE and don’t have it down yet. Any examples
would be great!
__

Author: Simoneau, Roger
Date: Thu Dec 08 11:07:33 PST 2011

Any examples would be great! The simplest example is as James outlined. You
can use that as a starting point. Tip 1: Built the “after” SQL
string exactly as you want it. Tip 2: Then place the SQL string into a variable.
When dealing with the quotes so you can get them the way you want them you can
send the text value through a general sql statement: Select built_text from
dual; If it successfully runs, it’ll produce an output string. You can
visually check the output string – or run it as-is – to see if the
structure is exactly what you want. Final tip: since your new, play with execute
immediate first. See how it works. After you’re comfortable with it, then
apply the more complex queries you want into a logical structure. Roger S.
__

Author: Vic Lenza
Date: Thu Dec 08 11:07:33 PST 2011
Message from: *Vic I’m calling a middle tier stored procedure and will have
all the criteria available via parameters so no need for html. Just need to know
how to construct and execute. I’m new to ORACLE and don’t have it down
yet. Any examples would be great! _______________________________________
Historical Messages Author: Vic Lenza Date: Thu Dec 08 11:07:33 PST 2011 I’m
calling a middle tier stored procedure and will have all the criteria available
via parameters so no need for html. Just need to know how to construct and
execute. I’m new to ORACLE and don’t have it down yet. Any examples
would be great! __ Author: Chris Johnson Date: Thu Dec 08 10:42:59 PST 2011 I
did this exact thing a few years ago. An app I was finishing required about 20
reports, but upon analysis they were mostly the same columns, just sorted and
summarized differently. I wrote an HTML page to let them pick the columns they
wanted to see, the sort order, break levels etc., then I’d string together
which columns they checked, build the dort and group by etc., then used dbms_sql
to execute the string. It’s tedious to set up, but once written, it’s
easy to maintain. Flexible criteria in, flexible reporting out. These days
I’d do it with execute_immediate, but back in the 8i days that was how I did
it. __ Author: Chris Johnson Date: Thu Dec 08 10:42:59 PST 2011 Message from:
whgwannabe I did this exact thing a few years ago. An app I was finishing
required about 20 reports, but upon analysis they were mostly the same columns,
just sorted and summarized differently. I wrote an HTML page to let them pick
the columns they wanted to see, the sort order, break levels etc., then I’d
string together which columns they checked, build the dort and group by etc.,
then used dbms_sql to execute the string. It’s tedious to set up, but once
written, it’s easy to maintain. Flexible criteria in, flexible reporting
out. These days I’d do it with execute_immediate, but back in the 8i days
that was how I did it. _______________________________________ Historical
Messages Author: Chris Johnson Date: Thu Dec 08 10:42:59 PST 2011 I did this
exact thing a few years ago. An app I was finishing required about 20 reports,
but upon analysis they were mostly the same columns, just sorted and summarized
differently. I wrote an HTML page to let them pick the columns they wanted to
see, the sort order, break levels etc., then I’d string together which
columns they checked, build the dort and group by etc., then used dbms_sql to
execute the string. It’s tedious to set up, but once written, it’s easy
to maintain. Flexible criteria in, flexible reporting out. These days I’d do
it with execute_immediate, but back in the 8i days that was how I did it. __
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? __ _______________________________________ __ 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? __ _______________________________________
__

Author: Chris Johnson
Date: Thu Dec 08 10:42:59 PST 2011
I did this exact thing a few years ago. An app I was finishing required about 20
reports, but upon analysis they were mostly the same columns, just sorted and
summarized differently. I wrote an HTML page to let them pick the columns they
wanted to see, the sort order, break levels etc., then I’d string together
which columns they checked, build the dort and group by etc., then used dbms_sql
to execute the string. It’s tedious to set up, but once written, it’s
easy to maintain. Flexible criteria in, flexible reporting out.

These days I’d do it with execute_immediate, but back in the 8i days that
was how I did it.
__

Author: Chris Johnson
Date: Thu Dec 08 10:42:59 PST 2011
Message from: whgwannabe I did this exact thing a few years ago. An app I was
finishing required about 20 reports, but upon analysis they were mostly the same
columns, just sorted and summarized differently. I wrote an HTML page to let
them pick the columns they wanted to see, the sort order, break levels etc.,
then I’d string together which columns they checked, build the dort and
group by etc., then used dbms_sql to execute the string. It’s tedious to set
up, but once written, it’s easy to maintain. Flexible criteria in, flexible
reporting out. These days I’d do it with execute_immediate, but back in the
8i days that was how I did it. _______________________________________
Historical Messages Author: Chris Johnson Date: Thu Dec 08 10:42:59 PST 2011 I
did this exact thing a few years ago. An app I was finishing required about 20
reports, but upon analysis they were mostly the same columns, just sorted and
summarized differently. I wrote an HTML page to let them pick the columns they
wanted to see, the sort order, break levels etc., then I’d string together
which columns they checked, build the dort and group by etc., then used dbms_sql
to execute the string. It’s tedious to set up, but once written, it’s
easy to maintain. Flexible criteria in, flexible reporting out. These days
I’d do it with execute_immediate, but back in the 8i days that was how I did
it. __ 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? __ _______________________________________
__

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?

__


Vic,

Close…

Execute immediate l_nam into build_text;

This will only work for single values or a set of values, not records. For that
you will need a cursor (or something like it).

Chris

declare

begin

execute immediate l_nam;

end;

Tried this simple query and its giving an error on built_text being an invalid identifier.

l_nam varchar2(1000) default ‘’;l_nam := ‘select name from saleable_items where si_division = ‘‘LH’’’;Select built_text from dual;

Tried this simple query and its giving
an error on built_text being an invalid
identifier.

In the snippet you provided, you haven’t defined “built_text” which is why the invalid identifier error.

If you wish to see the text as it would look inside the anonymous block, replace the “built_text” line with:

Dbms_output.put_line('SQL query: ’ || l_nam);

Since you’re not loading anything with your execute immediate statement, the rest of it should work as is.

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: *Vic

Have the string built, but I can’t seem to execute it properly. Any syntax
to get this going would be appreciated.


Historical Messages

Author: Vic Lenza
Date: Thu Dec 08 12:46:34 PST 2011
Have the string built, but I can’t seem to execute it properly. Any syntax
to get this going would be appreciated.
__

Author: Vic Lenza
Date: Thu Dec 08 11:45:35 PST 2011

declare

l_nam varchar2 ( 1000 ) default ‘’ ;

begin

l_nam := ‘select name from saleable_items where si_division =
‘‘LH’’’ ;

execute immediate l_nam ;

Select built_text from dual ;

end;

Tried this simple query and its giving an error on built_text being an invalid
identifier.

__

Author: christopher.tryon
Date: Thu Dec 08 11:45:35 PST 2011
Vic, Close… Execute immediate l_nam into build_text; This will only work
for single values or a set of values, not records. For that you will need a
cursor (or something like it). Chris
__

Author: Vic Lenza
Date: Thu Dec 08 11:45:35 PST 2011
Message from: *Vic declare l_nam varchar2 ( 1000 ) default ‘’ ; begin
l_nam := ‘select name from saleable_items where si_division =
‘‘LH’’’ ; execute immediate l_nam ; Select built_text from
dual ; end; Tried this simple query and its giving an error on built_text being
an invalid identifier. _______________________________________ Historical
Messages Author: Vic Lenza Date: Thu Dec 08 11:45:35 PST 2011 declare l_nam
varchar2 ( 1000 ) default ‘’ ; begin l_nam := ‘select name from
saleable_items where si_division = ‘‘LH’’’ ; execute
immediate l_nam ; Select built_text from dual ; end; Tried this simple query and
its giving an error on built_text being an invalid identifier. __ Author: Vic
Lenza Date: Thu Dec 08 11:07:33 PST 2011 I’m calling a middle tier stored
procedure and will have all the criteria available via parameters so no need for
html. Just need to know how to construct and execute. I’m new to ORACLE and
don’t have it down yet. Any examples would be great! __ Author: Simoneau,
Roger Date: Thu Dec 08 11:07:33 PST 2011 >> Any examples would be great! The
simplest example is as James outlined. You can use that as a starting point. Tip
1: Built the “after” SQL string exactly as you want it. Tip 2: Then
place the SQL string into a variable. When dealing with the quotes so you can
get them the way you want them you can send the text value through a general sql
statement: Select built_text from dual; If it successfully runs, it’ll
produce an output string. You can visually check the output string – or
run it as-is – to see if the structure is exactly what you want. Final
tip: since your new, play with execute immediate first. See how it works. After
you’re comfortable with it, then apply the more complex queries you want
into a logical structure. Roger S. __ Author: Vic Lenza Date: Thu Dec 08
11:07:33 PST 2011 Message from: *Vic I’m calling a middle tier stored
procedure and will have all the criteria available via parameters so no need for
html. Just need to know how to construct and execute. I’m new to ORACLE and
don’t have it down yet. Any examples would be great!
_______________________________________ Historical Messages Author: Vic Lenza
Date: Thu Dec 08 11:07:33 PST 2011 I’m calling a middle tier stored
procedure and will have all the criteria available via parameters so no need for
html. Just need to know how to construct and execute. I’m new to ORACLE and
don’t have it down yet. Any examples would be great! __ Author: Chris
Johnson Date: Thu Dec 08 10:42:59 PST 2011 I did this exact thing a few years
ago. An app I was finishing required about 20 reports, but upon analysis they
were mostly the same columns, just sorted and summarized differently. I wrote an
HTML page to let them pick the columns they wanted to see, the sort order, break
levels etc., then I’d string together which columns they checked, build the
dort and group by etc., then used dbms_sql to execute the string. It’s
tedious to set up, but once written, it’s easy to maintain. Flexible
criteria in, flexible reporting out. These days I’d do it with
execute_immediate, but back in the 8i days that was how I did it. __ Author:
Chris Johnson Date: Thu Dec 08 10:42:59 PST 2011 Message from: whgwannabe I did
this exact thing a few years ago. An app I was finishing required about 20
reports, but upon analysis they were mostly the same columns, just sorted and
summarized differently. I wrote an HTML page to let them pick the columns they
wanted to see, the sort order, break levels etc., then I’d string together
which columns they checked, build the dort and group by etc., then used dbms_sql
to execute the string. It’s tedious to set up, but once written, it’s
easy to maintain. Flexible criteria in, flexible reporting out. These days
I’d do it with execute_immediate, but back in the 8i days that was how I did
it. _______________________________________ Historical Messages Author: Chris
Johnson Date: Thu Dec 08 10:42:59 PST 2011 I did this exact thing a few years
ago. An app I was finishing required about 20 reports, but upon analysis they
were mostly the same columns, just sorted and summarized differently. I wrote an
HTML page to let them pick the columns they wanted to see, the sort order, break
levels etc., then I’d string together which columns they checked, build the
dort and group by etc., then used dbms_sql to execute the string. It’s
tedious to set up, but once written, it’s easy to maintain. Flexible
criteria in, flexible reporting out. These days I’d do it with
execute_immediate, but back in the 8i days that was how I did it. __ 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? __ _______________________________________ __ 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? __ _______________________________________ __ Author:
Chris Johnson Date: Thu Dec 08 10:42:59 PST 2011 I did this exact thing a few
years ago. An app I was finishing required about 20 reports, but upon analysis
they were mostly the same columns, just sorted and summarized differently. I
wrote an HTML page to let them pick the columns they wanted to see, the sort
order, break levels etc., then I’d string together which columns they
checked, build the dort and group by etc., then used dbms_sql to execute the
string. It’s tedious to set up, but once written, it’s easy to maintain.
Flexible criteria in, flexible reporting out. These days I’d do it with
execute_immediate, but back in the 8i days that was how I did it. __ Author:
Chris Johnson Date: Thu Dec 08 10:42:59 PST 2011 Message from: whgwannabe I did
this exact thing a few years ago. An app I was finishing required about 20
reports, but upon analysis they were mostly the same columns, just sorted and
summarized differently. I wrote an HTML page to let them pick the columns they
wanted to see, the sort order, break levels etc., then I’d string together
which columns they checked, build the dort and group by etc., then used dbms_sql
to execute the string. It’s tedious to set up, but once written, it’s
easy to maintain. Flexible criteria in, flexible reporting out. These days
I’d do it with execute_immediate, but back in the 8i days that was how I did
it. _______________________________________ Historical Messages Author: Chris
Johnson Date: Thu Dec 08 10:42:59 PST 2011 I did this exact thing a few years
ago. An app I was finishing required about 20 reports, but upon analysis they
were mostly the same columns, just sorted and summarized differently. I wrote an
HTML page to let them pick the columns they wanted to see, the sort order, break
levels etc., then I’d string together which columns they checked, build the
dort and group by etc., then used dbms_sql to execute the string. It’s
tedious to set up, but once written, it’s easy to maintain. Flexible
criteria in, flexible reporting out. These days I’d do it with
execute_immediate, but back in the 8i days that was how I did it. __ 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? __ _______________________________________ __ 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? __ _______________________________________
__

Author: Vic Lenza
Date: Thu Dec 08 11:07:33 PST 2011
I’m calling a middle tier stored procedure and will have all the criteria
available via parameters so no need for html. Just need to know how to construct
and execute. I’m new to ORACLE and don’t have it down yet. Any examples
would be great!
__

Author: Simoneau, Roger
Date: Thu Dec 08 11:07:33 PST 2011

Any examples would be great! The simplest example is as James outlined. You
can use that as a starting point. Tip 1: Built the “after” SQL
string exactly as you want it. Tip 2: Then place the SQL string into a variable.
When dealing with the quotes so you can get them the way you want them you can
send the text value through a general sql statement: Select built_text from
dual; If it successfully runs, it’ll produce an output string. You can
visually check the output string – or run it as-is – to see if the
structure is exactly what you want. Final tip: since your new, play with execute
immediate first. See how it works. After you’re comfortable with it, then
apply the more complex queries you want into a logical structure. Roger S.
__

Author: Vic Lenza
Date: Thu Dec 08 11:07:33 PST 2011
Message from: *Vic I’m calling a middle tier stored procedure and will have
all the criteria available via parameters so no need for html. Just need to know
how to construct and execute. I’m new to ORACLE and don’t have it down
yet. Any examples would be great! _______________________________________
Historical Messages Author: Vic Lenza Date: Thu Dec 08 11:07:33 PST 2011 I’m
calling a middle tier stored procedure and will have all the criteria available
via parameters so no need for html. Just need to know how to construct and
execute. I’m new to ORACLE and don’t have it down yet. Any examples
would be great! __ Author: Chris Johnson Date: Thu Dec 08 10:42:59 PST 2011 I
did this exact thing a few years ago. An app I was finishing required about 20
reports, but upon analysis they were mostly the same columns, just sorted and
summarized differently. I wrote an HTML page to let them pick the columns they
wanted to see, the sort order, break levels etc., then I’d string together
which columns they checked, build the dort and group by etc., then used dbms_sql
to execute the string. It’s tedious to set up, but once written, it’s
easy to maintain. Flexible criteria in, flexible reporting out. These days
I’d do it with execute_immediate, but back in the 8i days that was how I did
it. __ Author: Chris Johnson Date: Thu Dec 08 10:42:59 PST 2011 Message from:
whgwannabe I did this exact thing a few years ago. An app I was finishing
required about 20 reports, but upon analysis they were mostly the same columns,
just sorted and summarized differently. I wrote an HTML page to let them pick
the columns they wanted to see, the sort order, break levels etc., then I’d
string together which columns they checked, build the dort and group by etc.,
then used dbms_sql to execute the string. It’s tedious to set up, but once
written, it’s easy to maintain. Flexible criteria in, flexible reporting
out. These days I’d do it with execute_immediate, but back in the 8i days
that was how I did it. _______________________________________ Historical
Messages Author: Chris Johnson Date: Thu Dec 08 10:42:59 PST 2011 I did this
exact thing a few years ago. An app I was finishing required about 20 reports,
but upon analysis they were mostly the same columns, just sorted and summarized
differently. I wrote an HTML page to let them pick the columns they wanted to
see, the sort order, break levels etc., then I’d string together which
columns they checked, build the dort and group by etc., then used dbms_sql to
execute the string. It’s tedious to set up, but once written, it’s easy
to maintain. Flexible criteria in, flexible reporting out. These days I’d do
it with execute_immediate, but back in the 8i days that was how I did it. __
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? __ _______________________________________ __ 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? __ _______________________________________
__

Author: Chris Johnson
Date: Thu Dec 08 10:42:59 PST 2011
I did this exact thing a few years ago. An app I was finishing required about 20
reports, but upon analysis they were mostly the same columns, just sorted and
summarized differently. I wrote an HTML page to let them pick the columns they
wanted to see, the sort order, break levels etc., then I’d string together
which columns they checked, build the dort and group by etc., then used dbms_sql
to execute the string. It’s tedious to set up, but once written, it’s
easy to maintain. Flexible criteria in, flexible reporting out.

These days I’d do it with execute_immediate, but back in the 8i days that
was how I did it.
__

Author: Chris Johnson
Date: Thu Dec 08 10:42:59 PST 2011
Message from: whgwannabe I did this exact thing a few years ago. An app I was
finishing required about 20 reports, but upon analysis they were mostly the same
columns, just sorted and summarized differently. I wrote an HTML page to let
them pick the columns they wanted to see, the sort order, break levels etc.,
then I’d string together which columns they checked, build the dort and
group by etc., then used dbms_sql to execute the string. It’s tedious to set
up, but once written, it’s easy to maintain. Flexible criteria in, flexible
reporting out. These days I’d do it with execute_immediate, but back in the
8i days that was how I did it. _______________________________________
Historical Messages Author: Chris Johnson Date: Thu Dec 08 10:42:59 PST 2011 I
did this exact thing a few years ago. An app I was finishing required about 20
reports, but upon analysis they were mostly the same columns, just sorted and
summarized differently. I wrote an HTML page to let them pick the columns they
wanted to see, the sort order, break levels etc., then I’d string together
which columns they checked, build the dort and group by etc., then used dbms_sql
to execute the string. It’s tedious to set up, but once written, it’s
easy to maintain. Flexible criteria in, flexible reporting out. These days
I’d do it with execute_immediate, but back in the 8i days that was how I did
it. __ 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? __ _______________________________________
__

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?

__


Have the string built, but I can’t seem to execute it properly. Any syntax to get this going would be appreciated.

Message from: reedda

Try using Dynamic SQL in your procedure. Dave


Historical Messages

Author: David Reed
Date: Fri Dec 09 08:57:24 PST 2011
Vic: Do you have a copy of Steve Feuerstein’s Oracle PL/SQL Programming 4th
Edition? There is an excellent section on Dynamic SQL. That is the bible for
PL/SQL developers. Dave
__

Author: David Reed
Date: Fri Dec 09 08:57:19 PST 2011
Try using Dynamic SQL in your procedure. Dave
__

Author: Vic Lenza
Date: Thu Dec 08 12:46:34 PST 2011
Have the string built, but I can’t seem to execute it properly. Any syntax
to get this going would be appreciated.
__

Author: Vic Lenza
Date: Thu Dec 08 12:46:34 PST 2011
Message from: *Vic Have the string built, but I can’t seem to execute it
properly. Any syntax to get this going would be appreciated.
_______________________________________ Historical Messages Author: Vic Lenza
Date: Thu Dec 08 12:46:34 PST 2011 Have the string built, but I can’t seem
to execute it properly. Any syntax to get this going would be appreciated. __
Author: Vic Lenza Date: Thu Dec 08 11:45:35 PST 2011 declare l_nam varchar2 (
1000 ) default ‘’ ; begin l_nam := ‘select name from saleable_items
where si_division = ‘‘LH’’’ ; execute immediate l_nam ;
Select built_text from dual ; end; Tried this simple query and its giving an
error on built_text being an invalid identifier. __ Author: christopher.tryon
Date: Thu Dec 08 11:45:35 PST 2011 Vic, Close… Execute immediate l_nam
into build_text; This will only work for single values or a set of values, not
records. For that you will need a cursor (or something like it). Chris __
Author: Vic Lenza Date: Thu Dec 08 11:45:35 PST 2011 Message from: *Vic declare
l_nam varchar2 ( 1000 ) default ‘’ ; begin l_nam := ‘select name
from saleable_items where si_division = ‘‘LH’’’ ; execute
immediate l_nam ; Select built_text from dual ; end; Tried this simple query and
its giving an error on built_text being an invalid identifier.
_______________________________________ Historical Messages Author: Vic Lenza
Date: Thu Dec 08 11:45:35 PST 2011 declare l_nam varchar2 ( 1000 ) default
‘’ ; begin l_nam := ‘select name from saleable_items where
si_division = ‘‘LH’’’ ; execute immediate l_nam ; Select
built_text from dual ; end; Tried this simple query and its giving an error on
built_text being an invalid identifier. __ Author: Vic Lenza Date: Thu Dec 08
11:07:33 PST 2011 I’m calling a middle tier stored procedure and will have
all the criteria available via parameters so no need for html. Just need to know
how to construct and execute. I’m new to ORACLE and don’t have it down
yet. Any examples would be great! __ Author: Simoneau, Roger Date: Thu Dec 08
11:07:33 PST 2011 >> Any examples would be great! The simplest example is as
James outlined. You can use that as a starting point. Tip 1: Built the
“after” SQL string exactly as you want it. Tip 2: Then place the SQL
string into a variable. When dealing with the quotes so you can get them the way
you want them you can send the text value through a general sql statement:
Select built_text from dual; If it successfully runs, it’ll produce an
output string. You can visually check the output string – or run it as-is
– to see if the structure is exactly what you want. Final tip: since your
new, play with execute immediate first. See how it works. After you’re
comfortable with it, then apply the more complex queries you want into a logical
structure. Roger S. __ Author: Vic Lenza Date: Thu Dec 08 11:07:33 PST 2011
Message from: *Vic I’m calling a middle tier stored procedure and will have
all the criteria available via parameters so no need for html. Just need to know
how to construct and execute. I’m new to ORACLE and don’t have it down
yet. Any examples would be great! _______________________________________
Historical Messages Author: Vic Lenza Date: Thu Dec 08 11:07:33 PST 2011 I’m
calling a middle tier stored procedure and will have all the criteria available
via parameters so no need for html. Just need to know how to construct and
execute. I’m new to ORACLE and don’t have it down yet. Any examples
would be great! __ Author: Chris Johnson Date: Thu Dec 08 10:42:59 PST 2011 I
did this exact thing a few years ago. An app I was finishing required about 20
reports, but upon analysis they were mostly the same columns, just sorted and
summarized differently. I wrote an HTML page to let them pick the columns they
wanted to see, the sort order, break levels etc., then I’d string together
which columns they checked, build the dort and group by etc., then used dbms_sql
to execute the string. It’s tedious to set up, but once written, it’s
easy to maintain. Flexible criteria in, flexible reporting out. These days
I’d do it with execute_immediate, but back in the 8i days that was how I did
it. __ Author: Chris Johnson Date: Thu Dec 08 10:42:59 PST 2011 Message from:
whgwannabe I did this exact thing a few years ago. An app I was finishing
required about 20 reports, but upon analysis they were mostly the same columns,
just sorted and summarized differently. I wrote an HTML page to let them pick
the columns they wanted to see, the sort order, break levels etc., then I’d
string together which columns they checked, build the dort and group by etc.,
then used dbms_sql to execute the string. It’s tedious to set up, but once
written, it’s easy to maintain. Flexible criteria in, flexible reporting
out. These days I’d do it with execute_immediate, but back in the 8i days
that was how I did it. _______________________________________ Historical
Messages Author: Chris Johnson Date: Thu Dec 08 10:42:59 PST 2011 I did this
exact thing a few years ago. An app I was finishing required about 20 reports,
but upon analysis they were mostly the same columns, just sorted and summarized
differently. I wrote an HTML page to let them pick the columns they wanted to
see, the sort order, break levels etc., then I’d string together which
columns they checked, build the dort and group by etc., then used dbms_sql to
execute the string. It’s tedious to set up, but once written, it’s easy
to maintain. Flexible criteria in, flexible reporting out. These days I’d do
it with execute_immediate, but back in the 8i days that was how I did it. __
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? __ _______________________________________ __ 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? __ _______________________________________ __ Author:
Chris Johnson Date: Thu Dec 08 10:42:59 PST 2011 I did this exact thing a few
years ago. An app I was finishing required about 20 reports, but upon analysis
they were mostly the same columns, just sorted and summarized differently. I
wrote an HTML page to let them pick the columns they wanted to see, the sort
order, break levels etc., then I’d string together which columns they
checked, build the dort and group by etc., then used dbms_sql to execute the
string. It’s tedious to set up, but once written, it’s easy to maintain.
Flexible criteria in, flexible reporting out. These days I’d do it with
execute_immediate, but back in the 8i days that was how I did it. __ Author:
Chris Johnson Date: Thu Dec 08 10:42:59 PST 2011 Message from: whgwannabe I did
this exact thing a few years ago. An app I was finishing required about 20
reports, but upon analysis they were mostly the same columns, just sorted and
summarized differently. I wrote an HTML page to let them pick the columns they
wanted to see, the sort order, break levels etc., then I’d string together
which columns they checked, build the dort and group by etc., then used dbms_sql
to execute the string. It’s tedious to set up, but once written, it’s
easy to maintain. Flexible criteria in, flexible reporting out. These days
I’d do it with execute_immediate, but back in the 8i days that was how I did
it. _______________________________________ Historical Messages Author: Chris
Johnson Date: Thu Dec 08 10:42:59 PST 2011 I did this exact thing a few years
ago. An app I was finishing required about 20 reports, but upon analysis they
were mostly the same columns, just sorted and summarized differently. I wrote an
HTML page to let them pick the columns they wanted to see, the sort order, break
levels etc., then I’d string together which columns they checked, build the
dort and group by etc., then used dbms_sql to execute the string. It’s
tedious to set up, but once written, it’s easy to maintain. Flexible
criteria in, flexible reporting out. These days I’d do it with
execute_immediate, but back in the 8i days that was how I did it. __ 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? __ _______________________________________ __ 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? __ _______________________________________ __ Author:
Vic Lenza Date: Thu Dec 08 11:07:33 PST 2011 I’m calling a middle tier
stored procedure and will have all the criteria available via parameters so no
need for html. Just need to know how to construct and execute. I’m new to
ORACLE and don’t have it down yet. Any examples would be great! __ Author:
Simoneau, Roger Date: Thu Dec 08 11:07:33 PST 2011 >> Any examples would be
great! The simplest example is as James outlined. You can use that as a starting
point. Tip 1: Built the “after” SQL string exactly as you want it.
Tip 2: Then place the SQL string into a variable. When dealing with the quotes
so you can get them the way you want them you can send the text value through a
general sql statement: Select built_text from dual; If it successfully runs,
it’ll produce an output string. You can visually check the output string
– or run it as-is – to see if the structure is exactly what you
want. Final tip: since your new, play with execute immediate first. See how it
works. After you’re comfortable with it, then apply the more complex
queries you want into a logical structure. Roger S. __ Author: Vic Lenza Date:
Thu Dec 08 11:07:33 PST 2011 Message from: *Vic I’m calling a middle tier
stored procedure and will have all the criteria available via parameters so no
need for html. Just need to know how to construct and execute. I’m new to
ORACLE and don’t have it down yet. Any examples would be great!
_______________________________________ Historical Messages Author: Vic Lenza
Date: Thu Dec 08 11:07:33 PST 2011 I’m calling a middle tier stored
procedure and will have all the criteria available via parameters so no need for
html. Just need to know how to construct and execute. I’m new to ORACLE and
don’t have it down yet. Any examples would be great! __ Author: Chris
Johnson Date: Thu Dec 08 10:42:59 PST 2011 I did this exact thing a few years
ago. An app I was finishing required about 20 reports, but upon analysis they
were mostly the same columns, just sorted and summarized differently. I wrote an
HTML page to let them pick the columns they wanted to see, the sort order, break
levels etc., then I’d string together which columns they checked, build the
dort and group by etc., then used dbms_sql to execute the string. It’s
tedious to set up, but once written, it’s easy to maintain. Flexible
criteria in, flexible reporting out. These days I’d do it with
execute_immediate, but back in the 8i days that was how I did it. __ Author:
Chris Johnson Date: Thu Dec 08 10:42:59 PST 2011 Message from: whgwannabe I did
this exact thing a few years ago. An app I was finishing required about 20
reports, but upon analysis they were mostly the same columns, just sorted and
summarized differently. I wrote an HTML page to let them pick the columns they
wanted to see, the sort order, break levels etc., then I’d string together
which columns they checked, build the dort and group by etc., then used dbms_sql
to execute the string. It’s tedious to set up, but once written, it’s
easy to maintain. Flexible criteria in, flexible reporting out. These days
I’d do it with execute_immediate, but back in the 8i days that was how I did
it. _______________________________________ Historical Messages Author: Chris
Johnson Date: Thu Dec 08 10:42:59 PST 2011 I did this exact thing a few years
ago. An app I was finishing required about 20 reports, but upon analysis they
were mostly the same columns, just sorted and summarized differently. I wrote an
HTML page to let them pick the columns they wanted to see, the sort order, break
levels etc., then I’d string together which columns they checked, build the
dort and group by etc., then used dbms_sql to execute the string. It’s
tedious to set up, but once written, it’s easy to maintain. Flexible
criteria in, flexible reporting out. These days I’d do it with
execute_immediate, but back in the 8i days that was how I did it. __ 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? __ _______________________________________ __ 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? __ _______________________________________ __ Author:
Chris Johnson Date: Thu Dec 08 10:42:59 PST 2011 I did this exact thing a few
years ago. An app I was finishing required about 20 reports, but upon analysis
they were mostly the same columns, just sorted and summarized differently. I
wrote an HTML page to let them pick the columns they wanted to see, the sort
order, break levels etc., then I’d string together which columns they
checked, build the dort and group by etc., then used dbms_sql to execute the
string. It’s tedious to set up, but once written, it’s easy to maintain.
Flexible criteria in, flexible reporting out. These days I’d do it with
execute_immediate, but back in the 8i days that was how I did it. __ Author:
Chris Johnson Date: Thu Dec 08 10:42:59 PST 2011 Message from: whgwannabe I did
this exact thing a few years ago. An app I was finishing required about 20
reports, but upon analysis they were mostly the same columns, just sorted and
summarized differently. I wrote an HTML page to let them pick the columns they
wanted to see, the sort order, break levels etc., then I’d string together
which columns they checked, build the dort and group by etc., then used dbms_sql
to execute the string. It’s tedious to set up, but once written, it’s
easy to maintain. Flexible criteria in, flexible reporting out. These days
I’d do it with execute_immediate, but back in the 8i days that was how I did
it. _______________________________________ Historical Messages Author: Chris
Johnson Date: Thu Dec 08 10:42:59 PST 2011 I did this exact thing a few years
ago. An app I was finishing required about 20 reports, but upon analysis they
were mostly the same columns, just sorted and summarized differently. I wrote an
HTML page to let them pick the columns they wanted to see, the sort order, break
levels etc., then I’d string together which columns they checked, build the
dort and group by etc., then used dbms_sql to execute the string. It’s
tedious to set up, but once written, it’s easy to maintain. Flexible
criteria in, flexible reporting out. These days I’d do it with
execute_immediate, but back in the 8i days that was how I did it. __ 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? __ _______________________________________ __ 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? __ _______________________________________
__

Author: Simoneau, Roger
Date: Thu Dec 08 12:21:44 PST 2011

Tried this simple query and its giving >> an error on built_text being an
invalid >> identifier. In the snippet you provided, you haven’t defined
“built_text” which is why the invalid identifier error. If you wish to see the
text as it would look inside the anonymous block, replace the “built_text” line
with: Dbms_output.put_line('SQL query: ’ || l_nam); Since you’re not loading
anything with your execute immediate statement, the rest of it should work as
is. 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: Vic Lenza
Date: Thu Dec 08 11:45:35 PST 2011

declare

l_nam varchar2 ( 1000 ) default ‘’ ;

begin

l_nam := ‘select name from saleable_items where si_division =
‘‘LH’’’ ;

execute immediate l_nam ;

Select built_text from dual ;

end;

Tried this simple query and its giving an error on built_text being an invalid
identifier.

__

Author: christopher.tryon
Date: Thu Dec 08 11:45:35 PST 2011
Vic, Close… Execute immediate l_nam into build_text; This will only work
for single values or a set of values, not records. For that you will need a
cursor (or something like it). Chris
__

Author: Vic Lenza
Date: Thu Dec 08 11:45:35 PST 2011
Message from: *Vic declare l_nam varchar2 ( 1000 ) default ‘’ ; begin
l_nam := ‘select name from saleable_items where si_division =
‘‘LH’’’ ; execute immediate l_nam ; Select built_text from
dual ; end; Tried this simple query and its giving an error on built_text being
an invalid identifier. _______________________________________ Historical
Messages Author: Vic Lenza Date: Thu Dec 08 11:45:35 PST 2011 declare l_nam
varchar2 ( 1000 ) default ‘’ ; begin l_nam := ‘select name from
saleable_items where si_division = ‘‘LH’’’ ; execute
immediate l_nam ; Select built_text from dual ; end; Tried this simple query and
its giving an error on built_text being an invalid identifier. __ Author: Vic
Lenza Date: Thu Dec 08 11:07:33 PST 2011 I’m calling a middle tier stored
procedure and will have all the criteria available via parameters so no need for
html. Just need to know how to construct and execute. I’m new to ORACLE and
don’t have it down yet. Any examples would be great! __ Author: Simoneau,
Roger Date: Thu Dec 08 11:07:33 PST 2011 >> Any examples would be great! The
simplest example is as James outlined. You can use that as a starting point. Tip
1: Built the “after” SQL string exactly as you want it. Tip 2: Then
place the SQL string into a variable. When dealing with the quotes so you can
get them the way you want them you can send the text value through a general sql
statement: Select built_text from dual; If it successfully runs, it’ll
produce an output string. You can visually check the output string – or
run it as-is – to see if the structure is exactly what you want. Final
tip: since your new, play with execute immediate first. See how it works. After
you’re comfortable with it, then apply the more complex queries you want
into a logical structure. Roger S. __ Author: Vic Lenza Date: Thu Dec 08
11:07:33 PST 2011 Message from: *Vic I’m calling a middle tier stored
procedure and will have all the criteria available via parameters so no need for
html. Just need to know how to construct and execute. I’m new to ORACLE and
don’t have it down yet. Any examples would be great!
_______________________________________ Historical Messages Author: Vic Lenza
Date: Thu Dec 08 11:07:33 PST 2011 I’m calling a middle tier stored
procedure and will have all the criteria available via parameters so no need for
html. Just need to know how to construct and execute. I’m new to ORACLE and
don’t have it down yet. Any examples would be great! __ Author: Chris
Johnson Date: Thu Dec 08 10:42:59 PST 2011 I did this exact thing a few years
ago. An app I was finishing required about 20 reports, but upon analysis they
were mostly the same columns, just sorted and summarized differently. I wrote an
HTML page to let them pick the columns they wanted to see, the sort order, break
levels etc., then I’d string together which columns they checked, build the
dort and group by etc., then used dbms_sql to execute the string. It’s
tedious to set up, but once written, it’s easy to maintain. Flexible
criteria in, flexible reporting out. These days I’d do it with
execute_immediate, but back in the 8i days that was how I did it. __ Author:
Chris Johnson Date: Thu Dec 08 10:42:59 PST 2011 Message from: whgwannabe I did
this exact thing a few years ago. An app I was finishing required about 20
reports, but upon analysis they were mostly the same columns, just sorted and
summarized differently. I wrote an HTML page to let them pick the columns they
wanted to see, the sort order, break levels etc., then I’d string together
which columns they checked, build the dort and group by etc., then used dbms_sql
to execute the string. It’s tedious to set up, but once written, it’s
easy to maintain. Flexible criteria in, flexible reporting out. These days
I’d do it with execute_immediate, but back in the 8i days that was how I did
it. _______________________________________ Historical Messages Author: Chris
Johnson Date: Thu Dec 08 10:42:59 PST 2011 I did this exact thing a few years
ago. An app I was finishing required about 20 reports, but upon analysis they
were mostly the same columns, just sorted and summarized differently. I wrote an
HTML page to let them pick the columns they wanted to see, the sort order, break
levels etc., then I’d string together which columns they checked, build the
dort and group by etc., then used dbms_sql to execute the string. It’s
tedious to set up, but once written, it’s easy to maintain. Flexible
criteria in, flexible reporting out. These days I’d do it with
execute_immediate, but back in the 8i days that was how I did it. __ 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? __ _______________________________________ __ 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? __ _______________________________________ __ Author:
Chris Johnson Date: Thu Dec 08 10:42:59 PST 2011 I did this exact thing a few
years ago. An app I was finishing required about 20 reports, but upon analysis
they were mostly the same columns, just sorted and summarized differently. I
wrote an HTML page to let them pick the columns they wanted to see, the sort
order, break levels etc., then I’d string together which columns they
checked, build the dort and group by etc., then used dbms_sql to execute the
string. It’s tedious to set up, but once written, it’s easy to maintain.
Flexible criteria in, flexible reporting out. These days I’d do it with
execute_immediate, but back in the 8i days that was how I did it. __ Author:
Chris Johnson Date: Thu Dec 08 10:42:59 PST 2011 Message from: whgwannabe I did
this exact thing a few years ago. An app I was finishing required about 20
reports, but upon analysis they were mostly the same columns, just sorted and
summarized differently. I wrote an HTML page to let them pick the columns they
wanted to see, the sort order, break levels etc., then I’d string together
which columns they checked, build the dort and group by etc., then used dbms_sql
to execute the string. It’s tedious to set up, but once written, it’s
easy to maintain. Flexible criteria in, flexible reporting out. These days
I’d do it with execute_immediate, but back in the 8i days that was how I did
it. _______________________________________ Historical Messages Author: Chris
Johnson Date: Thu Dec 08 10:42:59 PST 2011 I did this exact thing a few years
ago. An app I was finishing required about 20 reports, but upon analysis they
were mostly the same columns, just sorted and summarized differently. I wrote an
HTML page to let them pick the columns they wanted to see, the sort order, break
levels etc., then I’d string together which columns they checked, build the
dort and group by etc., then used dbms_sql to execute the string. It’s
tedious to set up, but once written, it’s easy to maintain. Flexible
criteria in, flexible reporting out. These days I’d do it with
execute_immediate, but back in the 8i days that was how I did it. __ 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? __ _______________________________________ __ 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? __ _______________________________________
__

Author: Vic Lenza
Date: Thu Dec 08 11:07:33 PST 2011
I’m calling a middle tier stored procedure and will have all the criteria
available via parameters so no need for html. Just need to know how to construct
and execute. I’m new to ORACLE and don’t have it down yet. Any examples
would be great!
__

Author: Simoneau, Roger
Date: Thu Dec 08 11:07:33 PST 2011

Any examples would be great! The simplest example is as James outlined. You
can use that as a starting point. Tip 1: Built the “after” SQL
string exactly as you want it. Tip 2: Then place the SQL string into a variable.
When dealing with the quotes so you can get them the way you want them you can
send the text value through a general sql statement: Select built_text from
dual; If it successfully runs, it’ll produce an output string. You can
visually check the output string – or run it as-is – to see if the
structure is exactly what you want. Final tip: since your new, play with execute
immediate first. See how it works. After you’re comfortable with it, then
apply the more complex queries you want into a logical structure. Roger S.
__

Author: Vic Lenza
Date: Thu Dec 08 11:07:33 PST 2011
Message from: *Vic I’m calling a middle tier stored procedure and will have
all the criteria available via parameters so no need for html. Just need to know
how to construct and execute. I’m new to ORACLE and don’t have it down
yet. Any examples would be great! _______________________________________
Historical Messages Author: Vic Lenza Date: Thu Dec 08 11:07:33 PST 2011 I’m
calling a middle tier stored procedure and will have all the criteria available
via parameters so no need for html. Just need to know how to construct and
execute. I’m new to ORACLE and don’t have it down yet. Any examples
would be great! __ Author: Chris Johnson Date: Thu Dec 08 10:42:59 PST 2011 I
did this exact thing a few years ago. An app I was finishing required about 20
reports, but upon analysis they were mostly the same columns, just sorted and
summarized differently. I wrote an HTML page to let them pick the columns they
wanted to see, the sort order, break levels etc., then I’d string together
which columns they checked, build the dort and group by etc., then used dbms_sql
to execute the string. It’s tedious to set up, but once written, it’s
easy to maintain. Flexible criteria in, flexible reporting out. These days
I’d do it with execute_immediate, but back in the 8i days that was how I did
it. __ Author: Chris Johnson Date: Thu Dec 08 10:42:59 PST 2011 Message from:
whgwannabe I did this exact thing a few years ago. An app I was finishing
required about 20 reports, but upo

Try using Dynamic SQL in your procedure.

Dave

Message from: reedda

Vic: Do you have a copy of Steve Feuerstein’s Oracle PL/SQL Programming 4th
Edition? There is an excellent section on Dynamic SQL. That is the bible for
PL/SQL developers. Dave


Historical Messages

Author: David Reed
Date: Fri Dec 09 08:57:24 PST 2011
Vic: Do you have a copy of Steve Feuerstein’s Oracle PL/SQL Programming 4th
Edition? There is an excellent section on Dynamic SQL. That is the bible for
PL/SQL developers. Dave
__

Author: David Reed
Date: Fri Dec 09 08:57:19 PST 2011
Try using Dynamic SQL in your procedure. Dave
__

Author: Vic Lenza
Date: Thu Dec 08 12:46:34 PST 2011
Have the string built, but I can’t seem to execute it properly. Any syntax
to get this going would be appreciated.
__

Author: Vic Lenza
Date: Thu Dec 08 12:46:34 PST 2011
Message from: *Vic Have the string built, but I can’t seem to execute it
properly. Any syntax to get this going would be appreciated.
_______________________________________ Historical Messages Author: Vic Lenza
Date: Thu Dec 08 12:46:34 PST 2011 Have the string built, but I can’t seem
to execute it properly. Any syntax to get this going would be appreciated. __
Author: Vic Lenza Date: Thu Dec 08 11:45:35 PST 2011 declare l_nam varchar2 (
1000 ) default ‘’ ; begin l_nam := ‘select name from saleable_items
where si_division = ‘‘LH’’’ ; execute immediate l_nam ;
Select built_text from dual ; end; Tried this simple query and its giving an
error on built_text being an invalid identifier. __ Author: christopher.tryon
Date: Thu Dec 08 11:45:35 PST 2011 Vic, Close… Execute immediate l_nam
into build_text; This will only work for single values or a set of values, not
records. For that you will need a cursor (or something like it). Chris __
Author: Vic Lenza Date: Thu Dec 08 11:45:35 PST 2011 Message from: *Vic declare
l_nam varchar2 ( 1000 ) default ‘’ ; begin l_nam := ‘select name
from saleable_items where si_division = ‘‘LH’’’ ; execute
immediate l_nam ; Select built_text from dual ; end; Tried this simple query and
its giving an error on built_text being an invalid identifier.
_______________________________________ Historical Messages Author: Vic Lenza
Date: Thu Dec 08 11:45:35 PST 2011 declare l_nam varchar2 ( 1000 ) default
‘’ ; begin l_nam := ‘select name from saleable_items where
si_division = ‘‘LH’’’ ; execute immediate l_nam ; Select
built_text from dual ; end; Tried this simple query and its giving an error on
built_text being an invalid identifier. __ Author: Vic Lenza Date: Thu Dec 08
11:07:33 PST 2011 I’m calling a middle tier stored procedure and will have
all the criteria available via parameters so no need for html. Just need to know
how to construct and execute. I’m new to ORACLE and don’t have it down
yet. Any examples would be great! __ Author: Simoneau, Roger Date: Thu Dec 08
11:07:33 PST 2011 >> Any examples would be great! The simplest example is as
James outlined. You can use that as a starting point. Tip 1: Built the
“after” SQL string exactly as you want it. Tip 2: Then place the SQL
string into a variable. When dealing with the quotes so you can get them the way
you want them you can send the text value through a general sql statement:
Select built_text from dual; If it successfully runs, it’ll produce an
output string. You can visually check the output string – or run it as-is
– to see if the structure is exactly what you want. Final tip: since your
new, play with execute immediate first. See how it works. After you’re
comfortable with it, then apply the more complex queries you want into a logical
structure. Roger S. __ Author: Vic Lenza Date: Thu Dec 08 11:07:33 PST 2011
Message from: *Vic I’m calling a middle tier stored procedure and will have
all the criteria available via parameters so no need for html. Just need to know
how to construct and execute. I’m new to ORACLE and don’t have it down
yet. Any examples would be great! _______________________________________
Historical Messages Author: Vic Lenza Date: Thu Dec 08 11:07:33 PST 2011 I’m
calling a middle tier stored procedure and will have all the criteria available
via parameters so no need for html. Just need to know how to construct and
execute. I’m new to ORACLE and don’t have it down yet. Any examples
would be great! __ Author: Chris Johnson Date: Thu Dec 08 10:42:59 PST 2011 I
did this exact thing a few years ago. An app I was finishing required about 20
reports, but upon analysis they were mostly the same columns, just sorted and
summarized differently. I wrote an HTML page to let them pick the columns they
wanted to see, the sort order, break levels etc., then I’d string together
which columns they checked, build the dort and group by etc., then used dbms_sql
to execute the string. It’s tedious to set up, but once written, it’s
easy to maintain. Flexible criteria in, flexible reporting out. These days
I’d do it with execute_immediate, but back in the 8i days that was how I did
it. __ Author: Chris Johnson Date: Thu Dec 08 10:42:59 PST 2011 Message from:
whgwannabe I did this exact thing a few years ago. An app I was finishing
required about 20 reports, but upon analysis they were mostly the same columns,
just sorted and summarized differently. I wrote an HTML page to let them pick
the columns they wanted to see, the sort order, break levels etc., then I’d
string together which columns they checked, build the dort and group by etc.,
then used dbms_sql to execute the string. It’s tedious to set up, but once
written, it’s easy to maintain. Flexible criteria in, flexible reporting
out. These days I’d do it with execute_immediate, but back in the 8i days
that was how I did it. _______________________________________ Historical
Messages Author: Chris Johnson Date: Thu Dec 08 10:42:59 PST 2011 I did this
exact thing a few years ago. An app I was finishing required about 20 reports,
but upon analysis they were mostly the same columns, just sorted and summarized
differently. I wrote an HTML page to let them pick the columns they wanted to
see, the sort order, break levels etc., then I’d string together which
columns they checked, build the dort and group by etc., then used dbms_sql to
execute the string. It’s tedious to set up, but once written, it’s easy
to maintain. Flexible criteria in, flexible reporting out. These days I’d do
it with execute_immediate, but back in the 8i days that was how I did it. __
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? __ _______________________________________ __ 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? __ _______________________________________ __ Author:
Chris Johnson Date: Thu Dec 08 10:42:59 PST 2011 I did this exact thing a few
years ago. An app I was finishing required about 20 reports, but upon analysis
they were mostly the same columns, just sorted and summarized differently. I
wrote an HTML page to let them pick the columns they wanted to see, the sort
order, break levels etc., then I’d string together which columns they
checked, build the dort and group by etc., then used dbms_sql to execute the
string. It’s tedious to set up, but once written, it’s easy to maintain.
Flexible criteria in, flexible reporting out. These days I’d do it with
execute_immediate, but back in the 8i days that was how I did it. __ Author:
Chris Johnson Date: Thu Dec 08 10:42:59 PST 2011 Message from: whgwannabe I did
this exact thing a few years ago. An app I was finishing required about 20
reports, but upon analysis they were mostly the same columns, just sorted and
summarized differently. I wrote an HTML page to let them pick the columns they
wanted to see, the sort order, break levels etc., then I’d string together
which columns they checked, build the dort and group by etc., then used dbms_sql
to execute the string. It’s tedious to set up, but once written, it’s
easy to maintain. Flexible criteria in, flexible reporting out. These days
I’d do it with execute_immediate, but back in the 8i days that was how I did
it. _______________________________________ Historical Messages Author: Chris
Johnson Date: Thu Dec 08 10:42:59 PST 2011 I did this exact thing a few years
ago. An app I was finishing required about 20 reports, but upon analysis they
were mostly the same columns, just sorted and summarized differently. I wrote an
HTML page to let them pick the columns they wanted to see, the sort order, break
levels etc., then I’d string together which columns they checked, build the
dort and group by etc., then used dbms_sql to execute the string. It’s
tedious to set up, but once written, it’s easy to maintain. Flexible
criteria in, flexible reporting out. These days I’d do it with
execute_immediate, but back in the 8i days that was how I did it. __ 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? __ _______________________________________ __ 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? __ _______________________________________ __ Author:
Vic Lenza Date: Thu Dec 08 11:07:33 PST 2011 I’m calling a middle tier
stored procedure and will have all the criteria available via parameters so no
need for html. Just need to know how to construct and execute. I’m new to
ORACLE and don’t have it down yet. Any examples would be great! __ Author:
Simoneau, Roger Date: Thu Dec 08 11:07:33 PST 2011 >> Any examples would be
great! The simplest example is as James outlined. You can use that as a starting
point. Tip 1: Built the “after” SQL string exactly as you want it.
Tip 2: Then place the SQL string into a variable. When dealing with the quotes
so you can get them the way you want them you can send the text value through a
general sql statement: Select built_text from dual; If it successfully runs,
it’ll produce an output string. You can visually check the output string
– or run it as-is – to see if the structure is exactly what you
want. Final tip: since your new, play with execute immediate first. See how it
works. After you’re comfortable with it, then apply the more complex
queries you want into a logical structure. Roger S. __ Author: Vic Lenza Date:
Thu Dec 08 11:07:33 PST 2011 Message from: *Vic I’m calling a middle tier
stored procedure and will have all the criteria available via parameters so no
need for html. Just need to know how to construct and execute. I’m new to
ORACLE and don’t have it down yet. Any examples would be great!
_______________________________________ Historical Messages Author: Vic Lenza
Date: Thu Dec 08 11:07:33 PST 2011 I’m calling a middle tier stored
procedure and will have all the criteria available via parameters so no need for
html. Just need to know how to construct and execute. I’m new to ORACLE and
don’t have it down yet. Any examples would be great! __ Author: Chris
Johnson Date: Thu Dec 08 10:42:59 PST 2011 I did this exact thing a few years
ago. An app I was finishing required about 20 reports, but upon analysis they
were mostly the same columns, just sorted and summarized differently. I wrote an
HTML page to let them pick the columns they wanted to see, the sort order, break
levels etc., then I’d string together which columns they checked, build the
dort and group by etc., then used dbms_sql to execute the string. It’s
tedious to set up, but once written, it’s easy to maintain. Flexible
criteria in, flexible reporting out. These days I’d do it with
execute_immediate, but back in the 8i days that was how I did it. __ Author:
Chris Johnson Date: Thu Dec 08 10:42:59 PST 2011 Message from: whgwannabe I did
this exact thing a few years ago. An app I was finishing required about 20
reports, but upon analysis they were mostly the same columns, just sorted and
summarized differently. I wrote an HTML page to let them pick the columns they
wanted to see, the sort order, break levels etc., then I’d string together
which columns they checked, build the dort and group by etc., then used dbms_sql
to execute the string. It’s tedious to set up, but once written, it’s
easy to maintain. Flexible criteria in, flexible reporting out. These days
I’d do it with execute_immediate, but back in the 8i days that was how I did
it. _______________________________________ Historical Messages Author: Chris
Johnson Date: Thu Dec 08 10:42:59 PST 2011 I did this exact thing a few years
ago. An app I was finishing required about 20 reports, but upon analysis they
were mostly the same columns, just sorted and summarized differently. I wrote an
HTML page to let them pick the columns they wanted to see, the sort order, break
levels etc., then I’d string together which columns they checked, build the
dort and group by etc., then used dbms_sql to execute the string. It’s
tedious to set up, but once written, it’s easy to maintain. Flexible
criteria in, flexible reporting out. These days I’d do it with
execute_immediate, but back in the 8i days that was how I did it. __ 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? __ _______________________________________ __ 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? __ _______________________________________ __ Author:
Chris Johnson Date: Thu Dec 08 10:42:59 PST 2011 I did this exact thing a few
years ago. An app I was finishing required about 20 reports, but upon analysis
they were mostly the same columns, just sorted and summarized differently. I
wrote an HTML page to let them pick the columns they wanted to see, the sort
order, break levels etc., then I’d string together which columns they
checked, build the dort and group by etc., then used dbms_sql to execute the
string. It’s tedious to set up, but once written, it’s easy to maintain.
Flexible criteria in, flexible reporting out. These days I’d do it with
execute_immediate, but back in the 8i days that was how I did it. __ Author:
Chris Johnson Date: Thu Dec 08 10:42:59 PST 2011 Message from: whgwannabe I did
this exact thing a few years ago. An app I was finishing required about 20
reports, but upon analysis they were mostly the same columns, just sorted and
summarized differently. I wrote an HTML page to let them pick the columns they
wanted to see, the sort order, break levels etc., then I’d string together
which columns they checked, build the dort and group by etc., then used dbms_sql
to execute the string. It’s tedious to set up, but once written, it’s
easy to maintain. Flexible criteria in, flexible reporting out. These days
I’d do it with execute_immediate, but back in the 8i days that was how I did
it. _______________________________________ Historical Messages Author: Chris
Johnson Date: Thu Dec 08 10:42:59 PST 2011 I did this exact thing a few years
ago. An app I was finishing required about 20 reports, but upon analysis they
were mostly the same columns, just sorted and summarized differently. I wrote an
HTML page to let them pick the columns they wanted to see, the sort order, break
levels etc., then I’d string together which columns they checked, build the
dort and group by etc., then used dbms_sql to execute the string. It’s
tedious to set up, but once written, it’s easy to maintain. Flexible
criteria in, flexible reporting out. These days I’d do it with
execute_immediate, but back in the 8i days that was how I did it. __ 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? __ _______________________________________ __ 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? __ _______________________________________
__

Author: Simoneau, Roger
Date: Thu Dec 08 12:21:44 PST 2011

Tried this simple query and its giving >> an error on built_text being an
invalid >> identifier. In the snippet you provided, you haven’t defined
“built_text” which is why the invalid identifier error. If you wish to see the
text as it would look inside the anonymous block, replace the “built_text” line
with: Dbms_output.put_line('SQL query: ’ || l_nam); Since you’re not loading
anything with your execute immediate statement, the rest of it should work as
is. 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: Vic Lenza
Date: Thu Dec 08 11:45:35 PST 2011

declare

l_nam varchar2 ( 1000 ) default ‘’ ;

begin

l_nam := ‘select name from saleable_items where si_division =
‘‘LH’’’ ;

execute immediate l_nam ;

Select built_text from dual ;

end;

Tried this simple query and its giving an error on built_text being an invalid
identifier.

__

Author: christopher.tryon
Date: Thu Dec 08 11:45:35 PST 2011
Vic, Close… Execute immediate l_nam into build_text; This will only work
for single values or a set of values, not records. For that you will need a
cursor (or something like it). Chris
__

Author: Vic Lenza
Date: Thu Dec 08 11:45:35 PST 2011
Message from: *Vic declare l_nam varchar2 ( 1000 ) default ‘’ ; begin
l_nam := ‘select name from saleable_items where si_division =
‘‘LH’’’ ; execute immediate l_nam ; Select built_text from
dual ; end; Tried this simple query and its giving an error on built_text being
an invalid identifier. _______________________________________ Historical
Messages Author: Vic Lenza Date: Thu Dec 08 11:45:35 PST 2011 declare l_nam
varchar2 ( 1000 ) default ‘’ ; begin l_nam := ‘select name from
saleable_items where si_division = ‘‘LH’’’ ; execute
immediate l_nam ; Select built_text from dual ; end; Tried this simple query and
its giving an error on built_text being an invalid identifier. __ Author: Vic
Lenza Date: Thu Dec 08 11:07:33 PST 2011 I’m calling a middle tier stored
procedure and will have all the criteria available via parameters so no need for
html. Just need to know how to construct and execute. I’m new to ORACLE and
don’t have it down yet. Any examples would be great! __ Author: Simoneau,
Roger Date: Thu Dec 08 11:07:33 PST 2011 >> Any examples would be great! The
simplest example is as James outlined. You can use that as a starting point. Tip
1: Built the “after” SQL string exactly as you want it. Tip 2: Then
place the SQL string into a variable. When dealing with the quotes so you can
get them the way you want them you can send the text value through a general sql
statement: Select built_text from dual; If it successfully runs, it’ll
produce an output string. You can visually check the output string – or
run it as-is – to see if the structure is exactly what you want. Final
tip: since your new, play with execute immediate first. See how it works. After
you’re comfortable with it, then apply the more complex queries you want
into a logical structure. Roger S. __ Author: Vic Lenza Date: Thu Dec 08
11:07:33 PST 2011 Message from: *Vic I’m calling a middle tier stored
procedure and will have all the criteria available via parameters so no need for
html. Just need to know how to construct and execute. I’m new to ORACLE and
don’t have it down yet. Any examples would be great!
_______________________________________ Historical Messages Author: Vic Lenza
Date: Thu Dec 08 11:07:33 PST 2011 I’m calling a middle tier stored
procedure and will have all the criteria available via parameters so no need for
html. Just need to know how to construct and execute. I’m new to ORACLE and
don’t have it down yet. Any examples would be great! __ Author: Chris
Johnson Date: Thu Dec 08 10:42:59 PST 2011 I did this exact thing a few years
ago. An app I was finishing required about 20 reports, but upon analysis they
were mostly the same columns, just sorted and summarized differently. I wrote an
HTML page to let them pick the columns they wanted to see, the sort order, break
levels etc., then I’d string together which columns they checked, build the
dort and group by etc., then used dbms_sql to execute the string. It’s
tedious to set up, but once written, it’s easy to maintain. Flexible
criteria in, flexible reporting out. These days I’d do it with
execute_immediate, but back in the 8i days that was how I did it. __ Author:
Chris Johnson Date: Thu Dec 08 10:42:59 PST 2011 Message from: whgwannabe I did
this exact thing a few years ago. An app I was finishing required about 20
reports, but upon analysis they were mostly the same columns, just sorted and
summarized differently. I wrote an HTML page to let them pick the columns they
wanted to see, the sort order, break levels etc., then I’d string together
which columns they checked, build the dort and group by etc., then used dbms_sql
to execute the string. It’s tedious to set up, but once written, it’s
easy to maintain. Flexible criteria in, flexible reporting out. These days
I’d do it with execute_immediate, but back in the 8i days that was how I did
it. _______________________________________ Historical Messages Author: Chris
Johnson Date: Thu Dec 08 10:42:59 PST 2011 I did this exact thing a few years
ago. An app I was finishing required about 20 reports, but upon analysis they
were mostly the same columns, just sorted and summarized differently. I wrote an
HTML page to let them pick the columns they wanted to see, the sort order, break
levels etc., then I’d string together which columns they checked, build the
dort and group by etc., then used dbms_sql to execute the string. It’s
tedious to set up, but once written, it’s easy to maintain. Flexible
criteria in, flexible reporting out. These days I’d do it with
execute_immediate, but back in the 8i days that was how I did it. __ 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? __ _______________________________________ __ 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? __ _______________________________________ __ Author:
Chris Johnson Date: Thu Dec 08 10:42:59 PST 2011 I did this exact thing a few
years ago. An app I was finishing required about 20 reports, but upon analysis
they were mostly the same columns, just sorted and summarized differently. I
wrote an HTML page to let them pick the columns they wanted to see, the sort
order, break levels etc., then I’d string together which columns they
checked, build the dort and group by etc., then used dbms_sql to execute the
string. It’s tedious to set up, but once written, it’s easy to maintain.
Flexible criteria in, flexible reporting out. These days I’d do it with
execute_immediate, but back in the 8i days that was how I did it. __ Author:
Chris Johnson Date: Thu Dec 08 10:42:59 PST 2011 Message from: whgwannabe I did
this exact thing a few years ago. An app I was finishing required about 20
reports, but upon analysis they were mostly the same columns, just sorted and
summarized differently. I wrote an HTML page to let them pick the columns they
wanted to see, the sort order, break levels etc., then I’d string together
which columns they checked, build the dort and group by etc., then used dbms_sql
to execute the string. It’s tedious to set up, but once written, it’s
easy to maintain. Flexible criteria in, flexible reporting out. These days
I’d do it with execute_immediate, but back in the 8i days that was how I did
it. _______________________________________ Historical Messages Author: Chris
Johnson Date: Thu Dec 08 10:42:59 PST 2011 I did this exact thing a few years
ago. An app I was finishing required about 20 reports, but upon analysis they
were mostly the same columns, just sorted and summarized differently. I wrote an
HTML page to let them pick the columns they wanted to see, the sort order, break
levels etc., then I’d string together which columns they checked, build the
dort and group by etc., then used dbms_sql to execute the string. It’s
tedious to set up, but once written, it’s easy to maintain. Flexible
criteria in, flexible reporting out. These days I’d do it with
execute_immediate, but back in the 8i days that was how I did it. __ 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? __ _______________________________________ __ 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? __ _______________________________________
__

Author: Vic Lenza
Date: Thu Dec 08 11:07:33 PST 2011
I’m calling a middle tier stored procedure and will have all the criteria
available via parameters so no need for html. Just need to know how to construct
and execute. I’m new to ORACLE and don’t have it down yet. Any examples
would be great!
__

Author: Simoneau, Roger
Date: Thu Dec 08 11:07:33 PST 2011

Any examples would be great! The simplest example is as James outlined. You
can use that as a starting point. Tip 1: Built the “after” SQL
string exactly as you want it. Tip 2: Then place the SQL string into a variable.
When dealing with the quotes so you can get them the way you want them you can
send the text value through a general sql statement: Select built_text from
dual; If it successfully runs, it’ll produce an output string. You can
visually check the output string – or run it as-is – to see if the
structure is exactly what you want. Final tip: since your new, play with execute
immediate first. See how it works. After you’re comfortable with it, then
apply the more complex queries you want into a logical structure. Roger S.
__

Author: Vic Lenza
Date: Thu Dec 08 11:07:33 PST 2011
Message from: *Vic I’m calling a middle tier stored procedure and will have
all the criteria available via parameters so no need for html. Just need to know
how to construct and execute. I’m new to ORACLE and don’t have it down
yet. Any examples would be great! _______________________________________
Historical Messages Author: Vic Lenza Date: Thu Dec 08 11:07:33 PST 2011 I’m
calling a middle tier stored procedure and will have all the criteria available
via parameters so no need for html. Just need to know how to construct and
execute. I’m new to ORACLE and don’t have it down yet. Any examples
would be great! __ Author: Chris Johnson Date: Thu Dec 08 10:42:59 PST 2011 I
did this exact thing a few years ago. An app I was finishing required about 20
reports, but upon analysis they were mostly the same columns, just sorted and
summarized differently. I wrote an HTML page to let them pick the columns they
wanted to see, the sort order, break levels etc., then I’d string together
which columns they checked, build the dort and group by etc., then used dbms_sql
to execute the string. It’s tedious to set up, but once written, it’s
easy to maintain. Flexible criteria in, flexible reporting out. These days
I’d do it with execute_immediate, but back in the 8i days that was how I did
it. __ Author: Chris Johnson Date: Thu Dec 08 10:42

Vic:

Do you have a copy of Steve Feuerstein’s Oracle PL/SQL Programming 4th Edition? There is an excellent section on Dynamic SQL. That is the bible for PL/SQL developers.

Dave