Unanswered: view name and tsql needs to be variables
I have a web application that stores numerous SQL select statements in a table and then later needs to join these selects statements together dynamically with AND/OR logic in a web application before executing the final SQL statement. To optimize this process, I thought if I could create a trigger in the table that created/replaced a view dynamically based on the values being stored in the table, I would improve the performance of the app.
However, in other environments, you usually cannot create objects where the name is actually a parameter or a variable. Is this also the case with Oracle 9i? I'd like to do something like, but of course I'd update it to work with standard trigger objects...
view_name varchar2(20) :='audience_1';
view_sql varchar2(200) :='select * from mytable';
create or replace view view_name as view_sql;
A view will NOT run any faster then issuing a select. A view is a stored select that is merged with any where clause that you specify when you reference a view and then the combined select is issued. Building up a select or using a pre build view makes no difference. The other problem with your idea is that there will be a LOT of objects in your database. What happens when a user makes a view called dual or all_objects, or any other existing oracle object. If you want to store a select for a user in a table, fire it using execute immediate. Another thing to look out for is sql injection If the select is built using a tool or free formed, a user could actually imbedded other DML or DDL commands.
You do not need a parachute to skydive. You only need a parachute to skydive twice.
You won't be able to use EXECUTE IMMEDIATE in a FOR EACH ROW trigger unless you use an AUTONOMUS TRANSACTION. DDL contains an implicit commit, and commits are not allowed in FOR EACH ROW triggers.
Chuck, thanks. I just ran into this issue and was coming back to search the forum for solutions. Thank you.
beilstwh - in this particular case I get to control the name. I didn't realize that the views in Oracle were not compiled at all, so I can see your point about no performance gain. However, this will still help because in my web application I can simply do a 'select * from mystoredview_01 union select * from mystoredview_02' (columns are always the same between the views) which will help because now the parameter string I have to pass via SQLnet won't have to be so long.
One of my concerns is this application could have 10-15 different views to pull from simultaneously and some of the criteria can be quite long (see next paragraph) if I were to try and build the select statement in my web app and pass them to Oracle. If I can set up views for the 10-15 different criterias, then my web app only has to know the view name, not the columns or the criteria of the underlying views.
On a similar issue: One of the possibilities of this app is that a user could create an IN clause where the in list could contain 300 plus items, ie., 'id in (1,2,3,etc 300+ times)'. I haven't tested this, but I'm assuming that this would be a big performance hit. Am I correct? If I need to I'll limit this via the web application I will, but I don't want to make any false assumptions here.
Thanks. I open to doing this another way if anyone has a better solution.