This discussion is archived
4 Replies Latest reply: Nov 19, 2012 8:03 AM by ups_mike RSS

Synchronize IR columns via plsql API

ups_mike Newbie
Currently Being Moderated
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 Pro
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Yeaaahhhhh!

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

    BR Mike

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points