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!
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.
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.
Have the string built, but I can’t seem to execute it properly. Any syntax to get this going would be appreciated.
Try using Dynamic SQL in your procedure.
Dave
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