4 Replies Latest reply: Nov 19, 2012 10:03 AM by ups_mike RSS

    Synchronize IR columns via plsql API

    ups_mike
      Hi!

      I would like to use the package wwv_flow_worksheet_standard to synchronize the IR columns in my application out of plsql.
      Reason: My IR are based on views (simply: Select * from view_name) I have lots of them and they change frequently via plsql execute immediate.

      What I have tried is:

      DECLARE
      l_flow_id NUMBER (20);
      l_region_id NUMBER (20);
      l_query VARCHAR2 (4000);
      l_result VARCHAR2 (4000);

      BEGIN
      SELECT application_id, region_id, sql_query
      INTO l_flow_id, l_region_id, l_query
      FROM apex_application_page_ir
      WHERE page_id = 9 AND application_id = 133;

      APEX_040100.wwv_flow_worksheet_standard.synch_report_columns (p_flow_id => l_flow_id,
      p_region_id => l_region_id,
      p_query => l_query,
      p_add_new_cols_to_default_rpt => 'Y');
      COMMIT;
      END;

      Unfortunately it doesn't work. When executing the synch_report_columns procedure the following error raises.

      ORA-01003: no statement parsed
      ORA-06512: at "SYS.DBMS_SYS_SQL", line 1685
      ORA-06512: at "SYS.DBMS_SQL", line 629
      ORA-06512: at "APEX_040100.WWV_FLOW_WIZARD_API", line 840
      ORA-06512: at "APEX_040100.WWV_FLOW_WORKSHEET_STANDARD", line 2735
      ORA-06512: at line 23

      Can anyone help?
      Best regards,
      Mike
        • 1. Re: Synchronize IR columns via plsql API
          Joni Vandenberghe
          I wouldn't use an undocumented function. You can use DBMS_SQL to retrieve colum names.

          Example:
          DECLARE
          cur PLS_INTEGER := DBMS_SQL.open_cursor;
          cols DBMS_SQL.desc_tab;
          ncols PLS_INTEGER;
          BEGIN
          DBMS_SQL.parse (cur, 'SELECT * FROM EMP', DBMS_SQL.native);
          DBMS_SQL.describe_columns (cur, ncols, cols);

          FOR i IN 1 .. ncols
          LOOP
          DBMS_OUTPUT.put_line (cols (i).col_name);
          END LOOP;
          END;

          Taken from:
          http://www.toadworld.com/KNOWLEDGE/KnowledgeXpertforOracle/tabid/648/TopicID/PLS5K17/Default.aspx

          Official documentation at:
          http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_sql.htm
          • 2. Re: Synchronize IR columns via plsql API
            ups_mike
            Hello Joni!

            Thank you for your reply! Unfortunately this is not what I am searching for.

            I want to update the interactive report definition on my apex page out of plsql.
            E.g. the query of the IR on my page is: Select * from view_name

            When i add/remove columns from the view, the IR is broken. In order to repair the IR I need to logon to apex, edit every IR and save it again. Afterwards the new columns will appear in the report.
            Because my application has about 30 IR's and columns will change frequently I would like to script this step.

            The function APEX_040100.wwv_flow_worksheet_standard.synch_report_columns sounds like it does exactly what I need.
            Unfortunately what I tried doesn't work. (Look above)

            BR Michael
            • 3. Re: Synchronize IR columns via plsql API
              evrm
              Hi Mike,

              you have to set the workspace you are synchronize the IR columns for:
              DECLARE
              l_flow_id NUMBER (20);
              l_region_id NUMBER (20);
              l_query VARCHAR2 (4000);
              l_result VARCHAR2 (4000);
              
              BEGIN
              wwv_flow_api.set_security_group_id(apex_util.find_security_group_id('<WORKSPACE_NAME>'));
              
              SELECT application_id, region_id, sql_query
              INTO l_flow_id, l_region_id, l_query
              FROM apex_application_page_ir
              WHERE page_id = 9 AND application_id = 133;
              
              APEX_040100.wwv_flow_worksheet_standard.synch_report_columns (p_flow_id => l_flow_id,
              p_region_id => l_region_id,
              p_query => l_query,
              p_add_new_cols_to_default_rpt => 'Y');
              COMMIT;
              END;
              regards,
              Erik-jan
              • 4. Re: Synchronize IR columns via plsql API
                ups_mike
                Yeaaahhhhh!

                That was the missing jigsaw piece!! Thank you very much!!

                BR Mike