If you have further questions, please contact me at apexlib@oracleapex.info

• Before you begin
• Installation Requirements

There are two installation options available, depending on your security policy and the Oracle APEX version you are using, you have to select one of it. Read the Feature Restrictions first to find out what's the optimal installation option for you.

1. Install into its own APEXLIB schema (works with all APEX versions)
2. Install into the application schema (works with all APEX versions)
3. Install on apex.oracle.com
4. Upgrade an existing version of ApexLib

Post installation steps:

• Integration into your application
• Integration into the Oracle APEX Builder IDE
• Installation of the Example Application
• Uninstall the ApexLib Framework

Because nobody reads post-installation instructions, the important information at the beginning! :-)

I would really appreciate it if you would inform me if you are using the ApexLib Framework. I'm curious how many are actively using it and how much effort I should put into the framework. If you like it, donate a small amount to support my development efforts. Thanks a lot!

If you have suggestions or problems

Just drop a note to apexlib@oracleapex.info, create a feature/bug report on Sourceforge - Tracker or write a message in the Support Forum.

Thanks for your attention and have fun using the ApexLib Framework!

The ApexLib Framework currently supports the all Oracle APEX versions > 2.0.

Depending on the installation option you select and the Oracle APEX version you have, the ApexLib Framework might not be able to activate all features.

APEX 2.2.x doesn't expose all the meta-data of the application through the repository views, that's why the Framework is unable to determin all settings. If you want to use all features for APEX 2.2.x you have to choose the "Fast direct table access" option of the Install into its own APEXLIB schema installation option or upgrade to APEX 3.x.

If you use APEX 2.2.x and you choose the

• Access through the slower APEX repository views of the "Install into its own APEXLIB schema" option or
• Install into the application schema

the following features will not be available

• Page Flow Diagram Generator.
• Determine "Report Column Heading" for Validation Error Messages if the type is different to "Custom".
• Browser/Server side validation if the Region Title isn't unique on the page.

Just one copy of the views and packages are installed, they will be accessable for all APEX applications.

1. Open a command box.

   Eg on Windows Start/Execute => cmd

2. Change into the directory "Scripts"

   cd Scripts

3. Connect with SYS to create the APEXLIB schema. See ApexLib_create_apexlib_schema.sql why SYS and not an user with DBA privileges is required.

   sqlplus "sys/password@database as sysdba"

4. Execute the following script to create the schema APEXLIB and all the necessary privileges

   @ApexLib_create_apexlib_schema.sql

5. For faster execution, ApexLib can directly access (select only) the APEX repository tables in the FLOWS_xxx schema
   or it can use the APEX repository views if there are security concerns to grant read access to the FLOWS_xxx tables.
   Note: From APEX 3.2 on tables reside in Schema APEX_xxx
   1. Fast direct table access (works for all APEX versions)
      1. Execute the following script:

         @ApexLib_grant_apex_tables.sql

      2. Connect with the APEXLIB schema.

         connect apexlib/password@database

      3. Execute the following script:

         @../DataModel/ApexLib_views_for_apex_tables.sql

   2. Access through the slower APEX repository views (only works with APEX version > 2.2.x)
      1. Connect with the APEXLIB schema.

         connect apexlib/password@database

      2. Execute the following script:
         For 3.x and 4.x:

         @../DataModel/ApexLib_views_for_apex_views_30.sql

         For 2.2.x:

         @../DataModel/ApexLib_views_for_apex_views_22.sql

6. Execute the following installation scripts: (connected as APEXLIB schema)

   1. @ApexLib_install_into_apexlib_schema.sql

   2. @ApexLib_grant_create_synonyms.sql

      When asked enter PUBLIC or your application schema.
7. Lock the APEXLIB schema with a DBA user.

   ALTER USER APEXLIB ACCOUNT LOCK;

The views and packages are installed into each application schema. They have to be installed into the "Parsing Schema" as defined in "Application\Edit Attributes\Definition". This version is probably a little bit slower, because it's using the APEX repository views which are doing a lot of other stuff for security reasons.

1. Open a command box.

   Eg on Windows Start/Execute => cmd

2. Change into the directory "Scripts"

   cd Scripts

3. Connect to your application schema:

   sqlplus [application schema]/password@database

4. Execute the following installation script:

   @ApexLib_install_into_application_schema.sql

   If your application schema doesn't have access to the SYS.UTL_FILE package, the ApexLib_PageFlowDiagram package will be invalid. Ask your DBA that he grants you the EXECUTE privilege on this package. If he doesn't want to grant it, you can install the Utl_File_Replacement.pks and .pkb files to get the ApexLib_PageFlowDiagram valid. But in that case you will not be able to use the file option which is offered by the interface.
5. That's it! No additional grants or synonyms are necessary.

If you want to install the framework on apex.oracle.com you don't have SQL*Plus access to do a regular installation. That's why I have created an empty application which includes all the views and packages of the framework as supporting objects to make the installation as painless as possible.

You can find the application in the Examples directory.

If you have already installed ApexLib and want to upgrade to a new version, just follow the regular installation steps of the option you used the last time.

@ApexLib_create_apexlib_schema.sql

For the necessary changes in the application, see the change log entries between your current ApexLib version and the new version.

To integrate the ApexLib Framework into your application, follow the steps in the How to integrate document.

For a description of the features check out the ApexLib postings on my blog.

Check out the Oracle APEX Builder Plugin which offers a seamless integration of the ApexLib configuration settings into the Oracle APEX Builder IDE so that you do not have to remember all the ApexLib tags ($APEXLIB_xxx$).

1. Download the ApexLib integration files.
2. Install and configure the Plugin as described on the web page.

In the Examples directory you will find the "Feature Demonstration" application which demonstrates how the different ApexLib Hints are used to configure the ApexLib Framework.

It's a regular Oracle APEX application which can be imported into a workspace. The parsing schema has to be the HR schema. If you don't have installed this schema yet, you will find the necessary installation scripts in the hr.zip which is also contained in the Examples directory.

Are you sure that you want to uninstall the framework?!?! ;-)

• If you have created public synonyms for the ApexLib Framework, connect to the database with the APEXLIB schema and execute the following script.

  @ApexLib_uninstall.sql

• If you have installed the framework into the APEXLIB schema, connect to the database with a DBA user and drop the APEXLIB schema.

  DROP USER APEXLIB CASCADE;

• If you have created synonyms for each application schema or if you have installed the framework into the application schema, connect to the the application schema and execute the following script.

  @ApexLib_uninstall.sql

• Database installation
• Application Server/Oracle XE WebDave installation
• Integration into your application
• Localization
• Documentation
• Problems or Questions

Follow the steps in HowTo Install to install the necessary views and packages in the database.

This step is optional, but highly recommended, because if you put the files onto the Application Server, the browser can cache the files and doesn't have to load them for each page.

If you don't have access to the Application Server, you can also paste the content of the files into a page 0 region as described below.

1. On the Application Server go into the parent directory (that's the images directory) of the directory where the htmldb_get.js and htmldb_html_elements.js are located (images/javascript).
   See APEX installation guide for details. For Oracle XE see Dietmar Aust's blog posting about file storage on XE.
2. Create a new directory called apexlib_2_1 in the images directory.
The version number has been added to the directory name, because the files are cached by the browser and just updating the files isn't always recognized by the browser. A different directory name helps to avoid this problem, so that the end users do not have to clear their browser cache.
3. Copy the following files from the JavaScript directory into this new directory:
   • ApexLib_Full-min.js
   • ApexLib_Lov-min.js
   • ApexLib.css
   • size_grip.png
4. After that you can integrate the files into your page templates.

   For details see Including JavaScript in a .js File Referenced by the Page Template.
   The order has to be ApexLib_Full-min.js and then ApexLib.css

   OR

   If you don't want to touch all your page templates, you can also include the necessary commands into a page 0 region as described below.

1. Integrate automatically using script ApexLib_integrate_into_application.sql
2. Do the integration manually with a 14-step Instruction

1. you don't have access to the Application Server
2. you already modified the page error template
3. you use subscription algorithm for your templates

• connect to the database using your Application Schema
• and execute @ApexLib_integrate_into_application.sql
• Enter the id of the application you want to integrate ApexLib at.
• That's all. Now you can use all the features of the ApexLib Framework!

• Name: APEXLIB_ITEM_REQUIRED
• Text:

  %label is required.

• Name: APEXLIB_ITEM_REQUIRED_INLINE
• Text:

  Is required

• Name: APEXLIB_ITEM_INVALID_FORMAT

• %label doesn't match to the format "%0" (eg %1).

• Name: APEXLIB_ITEM_INVALID_FORMAT_INLINE

• Format "%0"<br>Eg %1

• Name: APEXLIB_ITEM_BETWEEN

• %label has to be between %0 and %1.

• Name: APEXLIB_ITEM_BETWEEN_INLINE

• Has to be between<br>%0 and %1

• Name: APEXLIB_ITEM_GREATER_OR_EQUAL

• %label has to be greater or equal to %0.

• Name: APEXLIB_ITEM_GREATER_OR_EQUAL_INLINE

• Has to be greater or<br>equal to %0

• Name: APEXLIB_ITEM_LESS_OR_EQUAL

• %label has to be less or equal to %0.

• Name: APEXLIB_ITEM_LESS_OR_EQUAL_INLINE

• Has to be less or<br>equal to %0

• Name: APEXLIB_TABFORM_COLUMN_REQUIRED
• Text:

  %label is required.

• Name: APEXLIB_TABFORM_INVALID_FORMAT
• Text:

  %label doesn't match to the format "%0" (eg %1).

• Name: APEXLIB_TABFORM_BETWEEN
• Text:

  %label has to be between %0 and %1.

• Name: APEXLIB_TABFORM_GREATER_OR_EQUAL
• Text:

  %label has to be greater or equal to %0.

• Name: APEXLIB_TABFORM_LESS_OR_EQUAL
• Text:

  %label has to be less or equal to %0.

• Name: APEXLIB_TABFORM_ROW_PREFIX
• Text:

  Row %0:

To unleash the full power of ApexLib we highly recommend using the Oracle APEX Build Plugin!
Using the Builder Plugin makes the stuff down here obsolete, you just don't need to care, the Builder Plugin does all the work for you.

Check out the related ApexLib postings for an explanation and examples of the different features.

Also take a look at the package specifications (*.pks) of the different packages. This will show you all the APIs of the framework and they are very well documented (or at least I hope so :-) ).

The behavior of the framework can be changed by adding hints into the comment text or link attributes (for report columns) of the different items. The following hints do exist so far, for an explanation see the postings.

• $APEXLIB_IGNORE_LOV$
• $APEXLIB_IGNORE_LOV_NULL$
• $APEXLIB_ALWAYS_REFRESH$
• $APEXLIB_IGNORE_VALIDATION$
• $APEXLIB_REQUIRED$
• $APEXLIB_UPPERCASE$
• $APEXLIB_LOWERCASE$
• $APEXLIB_CAPITALIZE$
• $APEXLIB_V_RESIZE$
• $APEXLIB_MAX_LENGTH=length$
  • $APEXLIB_MAX_LENGTH=40$
• $APEXLIB_MIN_VALUE=fixed value or column/item reference$
  • $APEXLIB_MIN_VALUE=0$ => has to comply to the format mask of the column/item!
  • $APEXLIB_MIN_VALUE=#HIRE_DATE#$ for a Tabular Form Column reference
  • $APEXLIB_MIN_VALUE=&P4_HIRE_DATE.$ for a Page Item or Application Item reference
• $APEXLIB_MAX_VALUE=fixed value or column/item reference$
  • $APEXLIB_MAX_VALUE=5,000$ => has to comply to the format mask of the column/item!
  • $APEXLIB_MAX_VALUE=#MAX_SALARY#$ for a Tabular Form Column reference
  • $APEXLIB_MAX_VALUE=&P4_MAX_SALARY.$ for a Page Item or Application Item reference
• $APEXLIB_HIDE_SELECT_ALL$

My page item isn't validated by your library!

• Have you set a format mask for the numeric item?
• Have you set a date picker?
• Does the item/region have a condition/authorization?
• Enable debug mode, there you see why the library isn't validating your item as soon as you press submit

I have tested the library with FireFox 2.0 and IE 6.0, if you have another browser where it doesn't work, let me know.

1. Edit page 0, if it doesn't exist yet create a new page with type "blank page", set as page number "0", enter any name, no breadcrumbs, no tabs.
2. If you have not integrated the ApexLib_Full-min.js and ApexLib.css into the page template, create a new region
   • Type: "HTML" and as subtype also HTML
   • Title: ApexLib - Javascript/CSS
   • Region template: no template
   • Display point: After Header
   • HTML Content

     <script type="text/javascript" src="#IMAGE_PREFIX#apexlib_2_1/ApexLib_Full-min.js">
     </script>
     <link rel="stylesheet" href="#IMAGE_PREFIX#apexlib_2_1/ApexLib.css" type="text/css" />
     

     Note: If you do not have access to the Application Server you can also create 3 regions for the different files and paste the content of the file into the regions. eg:

     <script type="text/javascript">
     Paste the ApexLib_Full-min.js content here
     </script>
     

     <style type="text/css">
     Paste the ApexLib.css content here
     </style>
     

3. Create a region on page 0
   • Type: "PL/SQL Dynamic Content"
   • Title: ApexLib - Before footer
   • Region template: no template
   • Display point: Before footer
   • PL/SQL source

     ---------------------------------------------
     -- Get metadata of page
     ---------------------------------------------
     ApexLib_Page.generateBrowserData;
     ---------------------------------------------
     -- Get metadata of items and tabular forms.
     ---------------------------------------------
     ApexLib_Item.generateBrowserData;
     ApexLib_TabForm.generateBrowserData;
     
     ---------------------------------------------
     -- Send data for cascading lovs
     ---------------------------------------------
     ApexLib_Lov.generateBrowserData;
     
     ---------------------------------------------
     -- Send data for AJAX computations
     ---------------------------------------------
     ApexLib_Computation.generateBrowserData;
     
     ---------------------------------------------
     -- Send data for AJAX Page Process
     ---------------------------------------------
     ApexLib_Process.generateBrowserData;
     
     ---------------------------------------------
     -- Send data for button hotkeys
     ---------------------------------------------
     ApexLib_Button.generateBrowserData;
     
     ---------------------------------------------
     -- Send NLS data for date formats and numeric
     -- format masks and translated error messages
     ---------------------------------------------
     ApexLib_Browser.setNlsData;
     ApexLib_Browser.setApexLibMessages;
     
     ---------------------------------------------
     -- Active different navigation improvements
     ---------------------------------------------
     ApexLib_Browser.setLovIconsNonNavigable;
     ApexLib_Browser.setLRButtonIconsNonNavigable
       ( pLeftClassName  => 'L'
       , pRightClassName => 'R'
       );
     ApexLib_Browser.checkForLovKey;
     ApexLib_Browser.checkForUpDownKey;
     
     ---------------------------------------------
     -- Active different UI improvements
     ---------------------------------------------
     /* optional
     ApexLib_Browser.setTextareaProperty
       ( pProperty => ApexLib_Browser.VERTICAL_RESIZEABLE
       );
     */
     ---------------------------------------------
     -- Init browser validation
     ---------------------------------------------
     ApexLib_Browser.initValidation
       ( pShowRequiredWarning => TRUE
       );
     ---------------------------------------------
     -- Has to be the last statement!
     ---------------------------------------------
     ApexLib_Browser.flushJsBuffer;
     

     For pLeftClassName and pRightClassName the class names of the surrounding button image TD has to be specified. Eg L and R or t1L and t1R, ... Use the Inspect function of Firebug or look into the HTML output to get the class names. If you don't use 3-part image buttons, you can remove that call.
4. Create a region on page 0
   • Type: "HTML" and as subtype also HTML
   • Name: ApexLib - Handle Error Page
   • Region Template: no template
   • Display point: Before footer
   • HTML Content:

     <script type="text/javascript">
     apexlib.error.init
       ( "t7Messages"
       , "t7Notification"
       , "#6699cc"
       , "#000000"
       , apexlib.error.DISPLAY_LOCATION.FIELD_AND_NOTIFICATION
       );
     apexlib.error.handleErrorPage();
     </script>
     

     t7 has to be replaced by your theme number! The class name is case sensitive and has to match with the class name Oracle APEX is using to render the error notification.
     
     #6699cc is the background color and #000000 is the foreground color for cell highlighting.
     
     The last parameter is used to specify where error messages for page items are displayed.
     • FIELD_AND_NOTIFICATION
     • FIELD
     • NOTIFICATION
     
5. Create two new Application Items (Shared Components\Application Item) named
   • APEXLIB_REFERENCE_TYPE and
   • APEXLIB_REFERENCE_ID
   • APEXLIB_ROUTINE_TYPE
6. Create an Application Process (Shared Components/Application Process)
   • Name: ApexLib_getGenericResult
   • Process point: On Demand
   • Process text

     ApexLib_Generic.generateResult
       ( :APEXLIB_ROUTINE_TYPE
       , :APEXLIB_REFERENCE_TYPE
       , :APEXLIB_REFERENCE_ID
       );
     

     
7. Create an Application Process (Shared Components\Application Process)
   • Name: ApexLib-Before Computation/Validation
   • Sequence: 1 (should be a number before any of your page level processes)
   • On submit: After Page Submission - Before Computations and Validations
   • Process Text:

     BEGIN
         ApexLib_Item.setBoolDefault;
         ApexLib_Item.trimItems;
         ApexLib_TabForm.trimColumns;
         ApexLib_Lov.clearLovNullValues;
         ApexLib_Item.validateItems;
         ApexLib_TabForm.validateColumns;
         --
         ApexLib_Error.showErrorStack;
     END;
     

   • Process Error Message: #SQLERRM#
   
8. Create a static LOV (Shared Components\List of Values)
   • Name: APEXLIB_BOOLEAN_1_0
   • We need only on value - aka one sequence
   • Display Value: < put a whitespace in here >
   • Return Value: 1
9. Create a static LOV (Shared Components\List of Values)
   • Name: APEXLIB_BOOLEAN_Y_N
   • We need only on value
   • Display Value: < put a whitespace in here >
   • Return Value: Y
10. If you want to change or translate the text used by the framework, create the following Text Messages (Shared Components/Text Message)
    • Name: APEXLIB_ITEM_REQUIRED
    • Text:

      %label is required.

    • Name: APEXLIB_ITEM_REQUIRED_INLINE
    • Text:

      Is required

    • Name: APEXLIB_ITEM_INVALID_FORMAT

    • %label doesn't match to the format "%0" (eg %1).

    • Name: APEXLIB_ITEM_INVALID_FORMAT_INLINE

    • Format "%0"<br>Eg %1

    • Name: APEXLIB_ITEM_BETWEEN

    • %label has to be between %0 and %1.

    • Name: APEXLIB_ITEM_BETWEEN_INLINE

    • Has to be between<br>%0 and %1

    • Name: APEXLIB_ITEM_GREATER_OR_EQUAL

    • %label has to be greater or equal to %0.

    • Name: APEXLIB_ITEM_GREATER_OR_EQUAL_INLINE

    • Has to be greater or<br>equal to %0

    • Name: APEXLIB_ITEM_LESS_OR_EQUAL

    • %label has to be less or equal to %0.

    • Name: APEXLIB_ITEM_LESS_OR_EQUAL_INLINE

    • Has to be less or<br>equal to %0

    • Name: APEXLIB_TABFORM_COLUMN_REQUIRED
    • Text:

      %label is required.

    • Name: APEXLIB_TABFORM_INVALID_FORMAT
    • Text:

      %label doesn't match to the format "%0" (eg %1).

    • Name: APEXLIB_TABFORM_BETWEEN
    • Text:

      %label has to be between %0 and %1.

    • Name: APEXLIB_TABFORM_GREATER_OR_EQUAL
    • Text:

      %label has to be greater or equal to %0.

    • Name: APEXLIB_TABFORM_LESS_OR_EQUAL
    • Text:

      %label has to be less or equal to %0.

    • Name: APEXLIB_TABFORM_ROW_PREFIX
    • Text:

      Row %0:

    You can enter these Text Messages manually or use the File Scripts\ApexLib_load_apexlib_messages_de.sql (containing german translations) as a template to do your own translation and add them to all applications you want to.
    Please remember to connect to your applications parsing schema (e.g. SQLPLUS your_application_user/pwd) and to set a AL32UTF8 characterset (e.g. AMERICAN_AMERICA.AL32UTF8) before applying this scripts.
    
    Windows example:
    
    C:\ApexLib\Scripts> SET NLS_LANG=AMERICAN_AMERICA.AL32UTF8
C:\ApexLib\Scripts> SQLPLUS my_application_user/xxxyyyyxxx@somewhere
SQL> @ApexLib_load_apexlib_messages_de
    
    Then you will be asked for the application id where you want to add the Text Messages. Thats it!
11. Go to Shared Components\Application Definition\Template Defaults\Error Page Template and get the template name of the Error Page Template
12. Open the Shared Components/Templates/Page/[your error page template] and go to the Error Page Template Control section. Paste the following HTML code

    <div id="ApexLibErrorMessage" class="ApexLibErrorMessage" style="display:none">#MESSAGE#
    </div>
    <a href="javascript:ApexLib_extractErrorPage()">#OK#</a>
    
    <script language="javascript">
    function ApexLib_extractErrorPage()
    {
     var vElementList = null;
     var vErrorStack  = null;
     //----------------------------------------------------------------------------
     // Find our ErrorPageMessage and ApexLibErrorMessage div elements.
     // Extract and store them in a cookie
     //----------------------------------------------------------------------------
     vElementList = window.document.getElementsByTagName("div");
     for (i=0; i < vElementList.length; i++)
     {
       if ((vElementList[i].className == "ErrorPageMessage") ||
           (vElementList[i].className == "ApexLibErrorMessage"))
       {
         vErrorStack=(vErrorStack==null?"":vErrorStack+"<br />")+vElementList[i].innerHTML;
       }
     }
     // store the error message in a cookie
     var vCookieCount   = 0;
     var vCookieContent = null;
     while (vErrorStack != "")
     {
       vCookieCount++;
       vCookieContent = vErrorStack.substr(0, 4000);
       vErrorStack    = vErrorStack.substr(3999);
     document.cookie="ApexLibErrorStack"+vCookieCount+"="+encodeURIComponent(vCookieContent);
     }
     //----------------------------------------------------------------------------
     // now go back the the previous page, on that page an onload event will check
     // if the cookie exists and paste it into the page.
     //----------------------------------------------------------------------------
     window.history.go(-1);
    } // ApexLib_extractErrorPage
    
    // If debug mode is enabled, don't do a redirect immediatly so that
    // the debug info isn't lost
    if (!html_GetElement('pdebug'))
    {
      ApexLib_extractErrorPage();
    }
    else
    {
      document.getElementById("ApexLibErrorMessage").style.display="block";
    }
    </script>
    

13. Depending on the theme you are using in your application, you also have to check the other page templates. There you have to search for

    #SUCCESS_MESSAGE# #NOTIFICATION_MESSAGE#

    in the Definition/Body section. If the text is not surrounded by

    <div class="t7Messages">
    #SUCCESS_MESSAGE# #NOTIFICATION_MESSAGE#
    </div>
    

    you have to replace it with the above text.
    t7 has to be replaced by your theme number! It's also ok if the class name is called t13MessageHolder for example. In that case you have to adapt the apexlib.error.init call to use this class name. The class name is case sensitive!
14. Only for APEX Versions before 4.0:
    Open the Shared Components/Templates/Popup List of Values/Popup LOV and go to the Page Attributes/Page Heading Text section. Paste the following HTML code

    <script type="text/javascript" src="#IMAGE_PREFIX#apexlib_2_1/ApexLib_Lov-min.js"></script>
    

    in front of the existing code. If you do not have access to the Application Server you can also paste the code directly into the section.

    <script type="text/javascript">
    Paste the ApexLib_Lov-min.js content here
    </script>
    

    

That's it! Now you can use all the features of the ApexLib Framework!