Results 1 to 9 of 9
  1. #1
    Join Date
    May 2004
    Posts
    184

    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...

    Code:
    declare 
            view_name varchar2(20) :='audience_1';
            view_sql varchar2(200) :='select * from mytable';
    begin
         create or replace view view_name as view_sql;
    end;
    Thanks for any suggestions.

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    You will need to use EXECUTE IMMEDIATE to create your view. Details can be bound in the Oracle manual:

    http://download-west.oracle.com/docs...4/11_dynam.htm

  3. #3
    Join Date
    May 2004
    Posts
    184
    shammat,

    Thank you! I'll check it out.

    Robert

  4. #4
    Join Date
    May 2004
    Posts
    184
    shammat,

    You're aweseome. That did exactly what I needed to do. Thank you again.

    Robert

  5. #5
    Join Date
    Dec 2003
    Posts
    1,074
    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.
    -cf

  6. #6
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    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.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  7. #7
    Join Date
    May 2004
    Posts
    184
    Quote Originally Posted by chuck_forbes
    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.
    -cf
    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.

  8. #8
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    You might want to try a pipelined function instead.

    The advantages are that you can do complex logic which you cant necessarily do in a view or select. Also you can change the table or procedure without hopefully doing an application code change.

    As for your large IN clause if you can avoid it then better, if you could populate a global temp table instead and join to it then it might be a better solution.

    Alan

  9. #9
    Join Date
    May 2004
    Posts
    184
    Alan,

    Thank you. I'll take a look at the PIPELINED function option you mentioned.

    Thanks,

    Robert

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •