I have a package where I need either to do same (or similar) unions many times or not at all depending on parameters. The code below is simplified. But the real one would be a bit more complex. As I wish to avoid dynamic statements I though I create a temp view and do operations on this view.

PROCEDURE CREATE_VIEW( id IN BINARY_INTEGER, id2 IN BINARY_INTEGER, id3 IN BINARY_INTEGER) IS
sqlCommand VARCHAR2(32000);

BEGIN
    sqlCommand :=
    'CREATE OR REPLACE VIEW TMP_HELPER_VIEW AS 
    SELECT ID, IMPORT1_ID, IMPORT2_ID, PROD_ID
    FROM 
    (
        SELECT ID,
               IMPORT1_ID,
               -1 AS IMPORT2_ID,
               PROD_ID                
          FROM TABLE1
          WHERE IMPORT1> '|| id1;

       
    IF id := 123 THEN    
        sqlCommand := sqlCommand||
        'UNION
        SELECT ID,
               -1 AS IMPORT1_ID,
               IMPORT2_ID,
               PROD_ID               
          FROM TABLE2
          WHERE IMPORT2> '|| id2;
    END IF;
    sqlCommand := sqlCommand||')' ;
    EXECUTE IMMEDIATE  sqlCommand;                
        

END CREATE_VIEW;

With view I can do:

SELECT ID, IMPORT1_ID, IMPORT2_ID FROM TMP_HELPER_VIEW; 

Instead of

 sqlCommand := 
          'SELECT ID,
           IMPORT1_ID,
           -1 AS IMPORT2_ID,
           PROD_ID                
      FROM TABLE1
      WHERE IMPORT1> '|| id1;
       
    IF id := 123 THEN    
        sqlCommand := sqlCommand||
        'UNION
        SELECT ID,
               -1 AS IMPORT1_ID,
               IMPORT2_ID,
               PROD_ID               
          FROM TABLE2
          WHERE IMPORT2> '|| id2;
    END IF;

So the code would be much better readable as I have many functions which uses similar statements. Statements are always bit different, so I need to write them every time (I can't write one in function and call evey time from there). But I wonder if there is a better solution for this problem. Maybe someone can give me any clue. Many thanks!

CodePudding user response：

You're having a dynamic statement anyway, but in addition, you're storing that in a view. The use case is not very clear to me. If this is some sort of one time configuration to set up the view for a customer/installation, I think this could be fine.

If however, you're using this to generate different queries on the fly, I think it's risky. Two clients can be fighting to each generate their own version of the view, leading to unwanted, unpredictable results. The first client can create their version of the view, but by the time they want to use it, another client may have replaced it.

So for that, there are various better solutions, for example

1. Have the 'big' union of all tables in the view. Filter out unneeded rows from the results. This looks inefficient, but can be surprisingly fast, especially if you're using UNION ALL instead of UNION. The latter filters out duplicate rows, and it looks like your won't need that.
2. Create a pipelined table function. Open the needed queries one by one, and pipe their results to the output. You can use the function as a sort of parameterized view.
3. Create a function that generates the desired query, but instead of storing it as a view, return a cursor (sys_refcursor) based on the query. The caller can read the results of that cursor. Other clients can make consecutive calls and get their own cursor, without interfering with each other.
4. A mix between the two above would be to write a function that generates the query, bulk collects the results a table type and returns it. Very simple to implement, but may be less memory efficient, so less suitable for larger result sets.
5. If you're on one of the recent innovation versions, you can use SQL Macros to generate the SQL snippet you need.

It's hard to tell which one of this is best in your specific situation, but I've used each of these successfully in our application.

Example for 4.

create type IMPORT_R as object (
  ID int,
  IMPORT1_ID int,
  IMPORT2_ID int,
  PROD_ID int
);
create type IMPORT_T as table of IMPORT_R;

CREATE FUNCTION GetImport(
  id IN BINARY_INTEGER, 
  id2 IN BINARY_INTEGER, 
  id3 IN BINARY_INTEGER) 
return IMPORT_T
IS
  sqlCommand VARCHAR2(32000);
  v_Result IMPORT_T;
begin
    sqlCommand :=
        'SELECT ID, IMPORT1_ID, IMPORT2_ID, PROD_ID
        FROM 
        (
            SELECT ID,
                   IMPORT1_ID,
                   -1 AS IMPORT2_ID,
                   PROD_ID                
              FROM TABLE1
              WHERE IMPORT1> '|| id1;

       
    IF id := 123 THEN    
        sqlCommand := sqlCommand||
        'UNION
        SELECT ID,
               -1 AS IMPORT1_ID,
               IMPORT2_ID,
               PROD_ID               
          FROM TABLE2
          WHERE IMPORT2> '|| id2;
    END IF;
    
    sqlCommand := sqlCommand||')' ;
    
    EXECUTE IMMEDIATE sqlCommand BULK COLLECT INTO v_Result;
    
    return v_Result;
end;
/