Forum Stats

  • 3,734,276 Users
  • 2,246,935 Discussions
  • 7,857,216 Comments

Discussions

Procedure creating dynamic view to be modified to include function returning sql

Vinipanda
Vinipanda Member Posts: 103 Red Ribbon

I had a requirement which was answered in the thread:


Now, there is an additional requirement to modify the procedure such that the way its currently creating view, similarly the select statement would also get generated directly inside the procedure,.i.e. something like function returning sql query.

The end requirement is to call this as source for interactive report in oracle apex, so by directly calling the procedure it would display the data directly.

So basically, the select * from APP_&APP_ID_VW : this would also get dynamically created via proc after view is created.


Current Solution:


CREATE OR REPLACE
  PROCEDURE CREATE_VIEW(
                        P_APP_ID NUMBER
                       )
    IS
        V_STMT VARCHAR2(4000);
    BEGIN
        SELECT  'CREATE OR REPLACE' || CHR(10) ||
                '  VIEW APP_' || APP_ID || '_VW' || CHR(10) ||
                '    AS' || CHR(10) ||
                '      SELECT  *' || CHR(10) ||
                '        FROM  DATA_VALUE' || CHR(10) ||
                '        PIVOT(' || CHR(10) ||
                '              MAX(VAL)' || CHR(10) ||
                '              FOR SEQ IN (' || CHR(10) ||
                '                          ' || LISTAGG(
                                                        SEQ || ' "' || LABEL || '"',
                                                        ',' || CHR(10) ||'                          '
                                                       )
                                                   WITHIN GROUP(ORDER BY SEQ) || CHR(10) ||
                '                         )' || CHR(10) ||
                '             )' || CHR(10) ||
                '        WHERE APP_ID = ' || APP_ID
          INTO  V_STMT
          FROM  DATA_HEADER
          WHERE APP_ID = P_APP_ID
          GROUP BY APP_ID;
          DBMS_OUTPUT.PUT_LINE('Creating view: ' || CHR(10) || V_STMT);
          EXECUTE IMMEDIATE V_STMT;
END;
/

PL/SQL procedure successfully completed.

SQL>

Now:

SQL> EXEC CREATE_VIEW(1)
Creating view:
CREATE OR REPLACE
  VIEW APP_1_VW
    AS
      SELECT  *
        FROM  DATA_VALUE
        PIVOT(
              MAX(VAL)
              FOR SEQ IN (
                          1 "Title",
                          2 "Group",
                          3 "Aspect",
                          4 "EPT",
                          5 "IT",
                          6 "BU",
                          7 "Section",
                          8 "Class",
                          9 "Label Value",
                          10 "Comment"
                         )
             )
        WHERE APP_ID = 1

PL/SQL procedure successfully completed.

SQL> SELECT  *
  2    FROM  APP_1_VW
  3  /

       PID     APP_ID Title    Group    Aspect   EPT      IT       BU       Section  Class    Label Va Comment
---------- ---------- -------- -------- -------- -------- -------- -------- -------- -------- -------- --------
       121          1 Stanley  Deputy   IT       Synopsis Standard Code     Green    Need
       120          1 Robert   Deputy   IT       Synopsis Standard Code     Green    Need

SQL> EXEC CREATE_VIEW(2)
Creating view:
CREATE OR REPLACE
  VIEW APP_2_VW
    AS
      SELECT  *
        FROM  DATA_VALUE
        PIVOT(
              MAX(VAL)
              FOR SEQ IN (
                          1 "Title",
                          2 "Group",
                          3 "Aspect",
                          4 "EPT",
                          5 "IT",
                          6 "BU",
                          7 "Section",
                          8 "Class",
                          9 "Label Value",
                          10 "Comment",
                          11 "Remarks"
                         )
             )
        WHERE APP_ID = 2

PL/SQL procedure successfully completed.

SQL> SELECT  *
  2    FROM  APP_2_VW
  3  /

       PID     APP_ID Title    Group    Aspect   EPT      IT       BU       Section  Class    Label Va Comment  Remarks
---------- ---------- -------- -------- -------- -------- -------- -------- -------- -------- -------- -------- --------
       120          2 Robert   Deputy   IT       Synopsis Standard Code     Green    Need
       121          2 Stanley  Deputy   IT       Synopsis Standard Code     Green    Need

SQL>


Tagged:

Answers

  • Hub Tijhuis
    Hub Tijhuis Member Posts: 92 Silver Badge

    create a sql-file create_view_sql.sql

    define stmt=&1
    EXEC CREATE_VIEW(&stmt)
    SELECT  * FROM  APP_&stmt._VW
    /
    
    

    execute it :

    @create_view_sql 1

    @create_view_sql 2

    Maybe that enough for you.

    It is possible to modify your procedure into a function returning the view_name , but I don't now if its possible to execute the resulting variable.

    I don't have sqlplus available : could you check if the following gives the result 777?

    define x=select 777 from dual

    &&x

    /

  • Vinipanda
    Vinipanda Member Posts: 103 Red Ribbon

    The end application is apex, how can we call this from there?

  • Hub Tijhuis
    Hub Tijhuis Member Posts: 92 Silver Badge

    I don't know APEX wel enough. And I don't know what you want to achieve.

    I know the coding is like PL/SQL so you can call the create_view procedure or function.

    The function can be made to return the viewname or a refcursor. 


    From there it depends where the data of the select (from viewname) or fetch (refcursor) should go, with a dynamic select the problem probably is in the definition of the recieving datastructure if the selected columns are not of the same type.


    If you would have just 1 viewname how would your code look like ?


    And:

    If your only goal is to execute a dynamic query and the output structure is the same for all queries en you don't need the views afterwards you could build a packge with a table-function.

    In that case the select would look like

    SELECT * FROM TABLE(package.create_select(p_app_id))

    ( p_app_id is an available variable in pl/sql)

  • Vinipanda
    Vinipanda Member Posts: 103 Red Ribbon

    Output structure would vary because of dynamic columns.

    I simply wish this procedure to return the select sql also, so that when executed, it would directly return the data as per app id instead of just creating the view which it is doing curently.


    I know the requirement is getting perplexing but this is what has been asked for.

  • Hub Tijhuis
    Hub Tijhuis Member Posts: 92 Silver Badge

    Then make the procedure a function returning the sql. This is an example how it works

    create or replace function CREATE_VIEW(

                           P_APP_ID NUMBER

                          ) return varchar2 

                          is 

               v_view_name varchar2(255);

               v_stmt varchar2 (32767);

    begin 

    v_view_name := 'APP_' || P_APP_ID || '_VW';

    v_stmt := 'CREATE OR REPLACE' || CHR(10) ||

                   ' VIEW ' || v_view_name || ' AS ' || CHR(10) ||

                   'select ' || P_APP_ID || ' id, o.* from all_objects o where rownum <= ' || p_app_id;

    dbms_output.put_line(v_stmt);

    EXECUTE IMMEDIATE v_STMT;

    return 'select * from ' || v_view_name;

    end;

    /


    declare

     your_sql varchar2(32767);

    begin

     your_sql := create_view(1);

     dbms_output.put_line(your_sql);

     your_sql := create_view(2);

     dbms_output.put_line(your_sql);

    end;

    /

  • Vinipanda
    Vinipanda Member Posts: 103 Red Ribbon

    Hi,

    I tried creating function and it got compiled. however execution is throwing error.

    CREATE OR REPLACE
     FUNCTION CREATE_VIEW_FUNC(
                P_APP_ID NUMBER
                )
      return varchar2 
               is 
          v_view_name varchar2(255);
          v_stmt varchar2 (32767);
      BEGIN
    v_view_name := 'APP_' || P_APP_ID || '_VW';
        SELECT 'CREATE OR REPLACE' || CHR(10) ||
           ' VIEW ' || v_view_name || ' AS ' || CHR(10) ||
            '  AS' || CHR(10) ||
            '   SELECT *' || CHR(10) ||
            '    FROM DATA_VALUE' || CHR(10) ||
            '    PIVOT(' || CHR(10) ||
            '       MAX(VAL)' || CHR(10) ||
            '       FOR SEQ IN (' || CHR(10) ||
            '             ' || LISTAGG(
                                SEQ || ' "' || LABEL || '"',
                                ',' || CHR(10) ||'             '
                                )
                              WITHIN GROUP(ORDER BY SEQ) || CHR(10) ||
            '             )' || CHR(10) ||
            '       )' || CHR(10) ||
            '    WHERE APP_ID = ' || APP_ID
         INTO V_STMT
         FROM DATA_HEADER
         WHERE APP_ID = P_APP_ID
         GROUP BY APP_ID;
         DBMS_OUTPUT.PUT_LINE('Creating view: ' || CHR(10) || V_STMT);
       dbms_output.put_line(v_stmt);
    EXECUTE IMMEDIATE v_STMT;
    return 'select * from ' || v_view_name;
    end;
    /
    


    This got compiled.

    But how do i call this function?

    This is throwing error saying missing select keyword:

    declare

     your_sql varchar2(32767);

    begin

     your_sql := create_view_func(1);

     dbms_output.put_line(your_sql);

     your_sql := create_view_func(2);

     dbms_output.put_line(your_sql);

    end;

    /

  • Hub Tijhuis
    Hub Tijhuis Member Posts: 92 Silver Badge

    My example call is mend to be used in pl/sql -coding that is also used in APEX, you are using it in a place where APEX expects a select statement. As I said earlier I don't know APEX enough. Does the error appear at build or at runtime?

    I checked the APEX documentation for dynamic query region. 

    In the example there an sql-string is returned within a begin end block like a function.

    In that case you could try :


    begin

     return create_view_func(1);

    end;

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,398 Black Diamond

    The end requirement is to call this as source for interactive report in oracle apex, so by directly calling the procedure it would display the data directly.

    I don't know if this will work in APEX (I have no APEX experience):

    CREATE OR REPLACE
      PROCEDURE GET_APP_DATA(
                             P_APP_ID NUMBER
                            )
        IS
            PROCEDURE PREPARE_APP_DATA(
                                       P_APP_ID NUMBER
                                      )
              IS
                  V_STMT VARCHAR2(4000);
                  V_CUR  SYS_REFCURSOR;
              BEGIN
                  SELECT  'SELECT  *' || CHR(10) ||
                          '  FROM  DATA_VALUE' || CHR(10) ||
                          '  PIVOT(' || CHR(10) ||
                          '        MAX(VAL)' || CHR(10) ||
                          '        FOR SEQ IN (' || CHR(10) ||
                          '                    ' || LISTAGG(
                                                            SEQ || ' "' || LABEL || '"',
                                                            ',' || CHR(10) ||'                    '
                                                           )
                                                      WITHIN GROUP(ORDER BY SEQ) || CHR(10) ||
                          '                   )' || CHR(10) ||
                          '       )' || CHR(10) ||
                          '  WHERE APP_ID = ' || APP_ID
                    INTO  V_STMT
                    FROM  DATA_HEADER
                    WHERE APP_ID = P_APP_ID
                    GROUP BY APP_ID;
                    OPEN V_CUR FOR V_STMT;
                    DBMS_SQL.RETURN_RESULT(V_CUR);
            END;
        BEGIN
            PREPARE_APP_DATA(
                             P_APP_ID
                            );
    END;
    /
    
    
    Procedure created.
    
    
    SQL> exec get_app_data(1)
    
    
    PL/SQL procedure successfully completed.
    
    
    ResultSet #1
    
    
           PID     APP_ID Title    Group    Aspect   EPT      IT       BU       Section  Class    Label Va Comment
    ---------- ---------- -------- -------- -------- -------- -------- -------- -------- -------- -------- --------
           121          1 Stanley  Deputy   IT       Synopsis Standard Code     Green    Need
           120          1 Robert   Deputy   IT       Synopsis Standard Code     Green    Need
    
    
    SQL> exec get_app_data(2)
    
    
    PL/SQL procedure successfully completed.
    
    
    ResultSet #1
    
    
           PID     APP_ID Title    Group    Aspect   EPT      IT       BU       Section  Class    Label Va Comment  Remarks
    ---------- ---------- -------- -------- -------- -------- -------- -------- -------- -------- -------- -------- --------
           120          2 Robert   Deputy   IT       Synopsis Standard Code     Green    Need
           121          2 Stanley  Deputy   IT       Synopsis Standard Code     Green    Need
    
    
    SQL>
    
    

    SY.

  • Vinipanda
    Vinipanda Member Posts: 103 Red Ribbon
    edited Dec 9, 2020 5:06AM

    Hi Solomon,


    If i try executing this in sql developer, the result i am getting is not the format as you showed.

    I am getting format like:

    PID APP_ID

    Title

    Group

    And so on...

    In pivoted format.

    Is there another way to view proper output in sql developer?

    P.S. Sysrefcursors wont work in apex - checked this.

    Can we try something like pipelined function?

  • Hub Tijhuis
    Hub Tijhuis Member Posts: 92 Silver Badge

    A pipelined function has a fixed output structure, and you stated your output structures will not be the same.

  • Vinipanda
    Vinipanda Member Posts: 103 Red Ribbon

    I am using oracle 12.1. Could that be the reason?

Sign In or Register to comment.