2 Replies Latest reply: Jul 3, 2012 1:00 AM by Nico Martens RSS

    Installing supporting objects with sqlplus

    Nico Martens
      Hey,

      For our development cycle we use jenkins with liquibase to automatically build our plsql backend code.

      We want to do the same thing for our APEX applications.

      After some searching I found that I needed to set the workspace, application id and schema with the apex_application_install api. I also generated an offset.

      After doing this setup (without liquibase cause all of this is not supported by liquibase) the installation of the application is without any errors.

      Now I exported my supporting objects as a single file and I'm also executing this with jenking (in the end I just open an sqlplus session and execute these files).

      But when I do something like this:
      wwv_flow_api.g_varchar2_table := wwv_flow_api.empty_varchar2_table;
      wwv_flow_api.g_varchar2_table(1) := 'some code for images';
      
      declare
          l_name   varchar2(255);
      begin
          l_name := 'uncheck.png';
       
        wwv_flow_api.create_or_remove_file(
           p_name=> l_name,
           p_varchar2_table=> wwv_flow_api.g_varchar2_table,
           p_mimetype=> 'image/png',
           p_location=> 'APPLICATION',
           p_nlang=> '0',
           p_mode=> 'CREATE_OR_REPLACE',
           p_type=> 'IMAGE');
       
      end;
      /
      My image would not be added to the application.

      When I do the exact same thing but change the p_location parameter to WORKSPACE instead of APPLICATION this code would work.

      I wonder if I need to set some other stuff in the wwv_flow_api for the p_location=>'APPLICATION' to work.

      It's not that it would stop our development but it's just inconvenient that we can not link images to applications anymore if we want to be able to use automatic building tools.

      Any help is appreciated.

      Br,

      Nico
        • 1. Re: Installing supporting objects with sqlplus
          Roel Hartman
          When you look at an export file you see the code you posted + some more:
          wwv_flow_api.create_install_script(
            p_id => 65734586813251463819 + wwv_flow_api.g_id_offset,
            p_flow_id => wwv_flow.g_flow_id,
            p_install_id=> 65734584003963461721 + wwv_flow_api.g_id_offset,
            p_name => 'AFINSRW.GIF',
            p_sequence=> 20,
            p_script_clob=> s);
          (where 's' is a clob containing your sort of code).
          So it seems you have to add a little more to your script to automate it...
          • 2. Re: Installing supporting objects with sqlplus
            Nico Martens
            Hey Roel,

            It's not an export of the APEX application. It's an export of the installation script of the supporting objects.

            I haven't figured out how to execute supporting objects with the APEX application script. For now I copied the installation scripts with the 'display as single script' link under supporting objects, installation scripts.

            This single script is the script I'm trying to execute trough sqlplus.

            I execute it this way
            declare
              lco_app_id constant apex_applications.application_id%type := 111;
              lco_workspace apex_applications.owner%type := 'SOME_WORKSPACE';
              lco_schema apex_applications.owner%type := 'SOME_SCHEMA';
              
              l_workspace_id apex_workspaces.workspace_id%type;
            begin
              select workspace_id 
              into l_workspace_id
              from apex_workspaces 
              where workspace = lco_workspace;
            
              apex_application_install.set_workspace_id(l_workspace_id);
              apex_application_install.set_application_id(lco_app_id);
              apex_application_install.set_schema(lco_schema);
              apex_application_install.generate_offset;
            end;
            /
            
            @"f111.sql"; -- My APEX application export
            @"f111_so.sql"; -- Supporting objects display single script
            
            commit;
            Everything works but when I add an image on application level with my supporting objects script then this image is not added to my APEX images.

            When I use workspace then everything works but this means that I can not bind images to my application anymore.

            Thnx for the help,

            Regards

            Nico