This discussion is archived
7 Replies Latest reply: Jan 17, 2013 10:59 AM by evrm RSS

Delete / Copy pages through a procedure / api

775360 Newbie
Currently Being Moderated
Hi

Oracle 11gr2
Apex 4.1

I have an application installed multiple times for multiple clients in separate schemas.

Firstly I need to remove a couple of pages from each instance of the application. Is there anyway to do this other than through the web interface?

I also need to copy pages from one application (core) to others. Again is there anyway to do this other than through the web interface?

Any help is much appreciated.

Cheers

Rob
  • 1. Re: Delete / Copy pages through a procedure / api
    evrm Pro
    Currently Being Moderated
    Hi Rob,

    you can use the SQL scripts that Apex generates when you export an application/page.
    These script contain all the API calls you need and can be run in SQL Developer.
    >
    Firstly I need to remove a couple of pages from each instance of the application. Is there anyway to do this other than through the web interface?
    >
    Export the page(s) you want to delete. Then use the next part of the script to delete it:
    set define off
    set verify off
    set serveroutput on size 1000000
    set feedback off
    WHENEVER SQLERROR EXIT SQL.SQLCODE ROLLBACK
    begin wwv_flow.g_import_in_progress := true; end; 
    /
     
     
    prompt  Set Credentials...
     
    begin
     
      -- Assumes you are running the script connected to SQL*Plus as the Oracle user APEX_040000 or as the owner (parsing schema) of the application.
      wwv_flow_api.set_security_group_id(p_security_group_id=>nvl(wwv_flow_application_install.get_workspace_id,<YOUR_WORKSPACE_NUMBER>));
     
    end;
    /
    
    begin wwv_flow.g_import_in_progress := true; end;
    /
    begin 
    
    select value into wwv_flow_api.g_nls_numeric_chars from nls_session_parameters where parameter='NLS_NUMERIC_CHARACTERS';
    
    end;
    
    /
    begin execute immediate 'alter session set nls_numeric_characters=''.,''';
    
    end;
    
    /
    begin wwv_flow.g_browser_language := 'en'; end;
    /
    prompt  Check Compatibility...
     
    begin
     
    -- This date identifies the minimum version required to import this file.
    wwv_flow_api.set_version(p_version_yyyy_mm_dd=>'2010.05.13');
     
    end;
    /
    
    prompt  Set Application ID...
     
    begin
     
       -- SET APPLICATION ID
       wwv_flow.g_flow_id := nvl(wwv_flow_application_install.get_application_id,<YOUR_APP_ID>);
       wwv_flow_api.g_id_offset := nvl(wwv_flow_application_install.get_offset,0);
    null;
     
    end;
    /
    
    PROMPT ...Remove page <YOUR_PAGE_ID>
     
    begin
     
    wwv_flow_api.remove_page (p_flow_id=>wwv_flow.g_flow_id, p_page_id=><YOUR_PAGE_ID>);
     
    end;
    /
    I also need to copy pages from one application (core) to others. Again is there anyway to do this other than through the web interface?
    >
    Use the same method as above but then run the whole script.
    To make the script fit into the workspaces and applications use the procedures in the apex_application_install package.
    Take a look at this blob post http://joelkallman.blogspot.nl/2010/07/apexapplicationinstall.html for some examples.

    regards,
    Erik-jan
  • 2. Re: Delete / Copy pages through a procedure / api
    775360 Newbie
    Currently Being Moderated
    Hi

    Thanks for the response. This is very close to what I want.

    However when I import the page the report region lose the template associated with it.

    It goes from Report Region to 'No Template'

    Is this normal?

    Cheers

    Rob
  • 3. Re: Delete / Copy pages through a procedure / api
    fac586 Guru
    Currently Being Moderated
    RobDemp wrote:
    Hi

    Thanks for the response. This is very close to what I want.

    However when I import the page the report region lose the template associated with it.

    It goes from Report Region to 'No Template'

    Is this normal?
    No. It's indicative of the risks inherent in using an undocumented, unsupported API. Doing so may have corrupted your applications and/or the APEX instance, and voided any support contract you have with Oracle.

    The <tt>apex_application_install</tt> package is documented and its use is fully supported. <tt>wwv_flow_api</tt> is not.
  • 4. Re: Delete / Copy pages through a procedure / api
    775360 Newbie
    Currently Being Moderated
    ???

    I not sure what you are referring to. evrm gave me the idea and I ran in sqlplus

    begin
    apex_application_install.set_application_id( 160);
    apex_application_install.generate_offset;
    apex_application_install.set_application_alias( 'F' || apex_application_install.get_application_id );
    end;
    /

    @f151_page_15.sql

    This produces the report without the template set.
  • 5. Re: Delete / Copy pages through a procedure / api
    VC Guru
    Currently Being Moderated
    RobDemp wrote:
    ???

    I not sure what you are referring to. evrm gave me the idea and I ran in sqlplus

    begin
    apex_application_install.set_application_id( 160);
    apex_application_install.generate_offset;
    apex_application_install.set_application_alias( 'F' || apex_application_install.get_application_id );
    end;
    /

    @f151_page_15.sql

    This produces the report without the template set.
    It seems that you are importing only page 15 and also the report template used in this page doesn't exist in your target instance/system.

    Please note that when you export/import a page it does not include the report template and it has to be separately exported/imported. +(this applies to all shared components)+

    Thanks,
    Vikram
  • 6. Re: Delete / Copy pages through a procedure / api
    775360 Newbie
    Currently Being Moderated
    Hi

    Hi yes that is what I want to do import only page 15 removing the existing page 15 which it does (It is an upgrade to the page. I want to automate this as I have 20 apps to upgrade)

    When I run the code

    begin
    apex_application_install.set_application_id( 160);
    apex_application_install.generate_offset;
    apex_application_install.set_application_alias( 'F' || apex_application_install.get_application_id );
    end;
    /
    @f151_page_15.sql


    After the import for page 15 if I go to Regions->Region Definition->User Interface->Template the template has changed from "Reports Region" to "No Template". If this is the case I will have to go into 20 applications to change the setting from "No Template" back to "Reports Region".

    Is this normal behaviour when importing pages?

    Cheers

    Rob
  • 7. Re: Delete / Copy pages through a procedure / api
    evrm Pro
    Currently Being Moderated
    Hi Rob,

    the problem is with the statement:
    >
    apex_application_install.generate_offset;
    >
    This will generate a new sequence number that will be used in part of the page import like this:
    p_plug_template=> 8931054716739067023+ wwv_flow_api.g_id_offset,
    You can calculate the correct offset based on the export page and the page you want to replace.
    The principle is described in this blog http://joelkallman.blogspot.nl/2010/07/where-did-my-saved-interactive-reports.html

    regards,
    Erik-jan

Legend

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