Friday 14 December 2012

Oracle Apex, Creating a second Page Example



This blog entry follows on from Apex First Page,Hello World.  It builds on the page built in that blog entry. In this entry instead of displaying Hello on the page where the user has entered thier name the greeting is instead displayed on a seperate page.

The steps involved are:
  1. Create a second page in the application
  2. Create a region on the page
  3. Set up a field to receive the inputted name and set up code to populate the field
  4. Remove the display field from page 1
  5. Create a process on page 1 to branch from page one to page 2 when the submit button is pressed.

 

Step 1, Create a second page in the application 

 

  • Click Create Page
  • Select the Blank Page Radio Group
  • Next
  • Next
  • Enter name of Page 2
  • Next
  • Finish
  • Edit Page


Step 2, Create a region on the page

 

  • Right click on Regions and select  Create
  • Select the HTML radio group
  • Next
  • HTML
  • Next
  • Enter the title as Results Region
  • Create Region 

Step 3, Set up a field to receive the inputted name


  •     Right click region 1 and select Create Page Item
  •     Select Display Only, (not display image)
  •     Next
  •     Set item name to P1_RESULT
  •     Next
  •     Set label to Hello
  •     Next
  •     Next

On this page we need to apply some process logic

  1.     Set Source used to Always, replacing any existing value in Session State.
  2.     Source Type to PL/SQL Expression
  3.     In the Source value or expression enter :

     apex_util.get_session_state ('P1_ENTER_NAME')

      4. Click create Item


Step 4, Remove the original display field on page 1

  • Go back to page 1
  • Right click on p1_result
  • Edit
  • Delete

Step 5, Create a process to branch from page one to page 2 when the submit button is pressed

 

 


  • Under the Page Processing column
  • Expand Processing Node
  • Right Click on Branches
  • Click Create
  • Set the name to BRANCH_TO_PAGE_2
  • Next
  • Click the Popup next to Page and select Page2 (the page we’re branching to)
  • Create Branch

Step 6


Run Page 1

Summary of how the page works

  • A user enters their name into the text box (P1_ENTER_NAME) on page 1.
  • When the submit button on page 1 is pressed the branches (ie redirects ) to page 2
  • The display only field (P2_DISPLAY)  is set to the same value as P1_ENTER_NAME through the call to apex_util.get_session_state ('P1_ENTER_NAME').




Validate XML against an xsd using Notepad++


The application Notepad++ is free to download. A plugin called xml tools is also free to download. One feature of the plugin is the ability to validate XML against an XSD.

As an example, if we have the following xsd saved in a file

<?xml version="1.0" encoding="UTF-8"?> 
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">   
   <xs:element name="userdetails">     
      <xs:complexType>       
         <xs:sequence>             
            <xs:element name="username" type="xs:string"/>         
            <xs:element name="password"  type="xs:string"/> 
         </xs:sequence>     
      </xs:complexType>   
   </xs:element> 
</xs:schema>
and the following xml saved in another file

<userdetails>
   <username>Fred</username>
   <password>pass123</password>
</userdetails>
we can open the xml file in Notepad++. Once opened the XML can be validated against the XSD by clicking on Plugins --> XML Tools --> Validate now




On clicking Validate now the dialogue box below will be displayed. Navigate to where the xsd is stored on your hard drive and click OK.




If the xml validates against the xsd the following dialogue will be displayed.



If the xml is modified by adding an element which is not defined in the xsd

 <userdetails>
 <username>Fred</username>
 <password>pass123</password>
 <newelement>dummy value</newelement>
</userdetails>
then when the validation is run an error is displayed






Referencing the xsd within the xml file

Instead of navigating to the xsd file the location can be specified in the xml document. In the example below the xsd file is called simple_xsd_1.xsd and is located at http://www.onsheld.co.uk/files/xml_files/simple_xsd_1.xsd
 
<userdetails xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
          xsi:noNamespaceSchemaLocation="http://www.onsheld.co.uk/files/xml_files/simple_xsd_1.xsd">
   <username>Fred</username>
   <password>pass123</password>
</userdetails>

Validating xml with a namespace against an xsd

The example below shows the same xml but with a default namespace of http://www.onsheld.co.uk/ns1 added

<?xml version="1.0"?>
<userdetails xmlns="http://www.onsheld.co.uk/ns1">
   <username>Fred</username>
   <password>pass123</password>
</userdetails>
The xsd is amended to include the new namespace

<xs:schema elementFormDefault="qualified" 
           targetNamespace="http://www.onsheld.co.uk/ns1" 
           xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:element name="userdetails">
    <xs:complexType>
      <xs:sequence>
        <xs:element type="xs:string" name="username"/>
        <xs:element type="xs:string" name="password"/>
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>
The xml would require the following two tags to point the xml to the xsd and indicate validation was required
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
This indicates xml validation
xsi:schemaLocation="http://www.onsheld.co.uk/ns1 http://www.onsheld.co.uk/files/xml_files/a.xsd"
This has two parts, first the namespace followed by a space followed by the name and location of the xsd file (in this case the file is called a.xsd which resides on the web at location http://www.onsheld.co.uk/files/xml_files/a.xsd )

The revised xml would be:
<?xml version="1.0"?>
<userdetails xmlns="http://www.onsheld.co.uk/ns1"
             xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
             xsi:schemaLocation="http://www.onsheld.co.uk/ns1 http://www.onsheld.co.uk/files/xml_files/a.xsd">
   <username>Fred</username>
   <password>pass123</password>
</userdetails>
If the schema (xsd file) is not accessible then the unable to parse schema file error is returned





Oracle Apex first page, Hello World Example




In this blog entry I am going to create a Hello World page in Apex.  The page will be a little more sophisticated in that a user will be able to enter their name and submit it.  The page will then say Hello to the user name.

To achieve this I'm going to complete the following steps:

  1. Create an application in Apex complete with a Page
  2. Create a region on the page
  3. Create a text entry field
  4. Create a submit button
  5. Create a display region to say Hello to the entered user name
This page will look like:




Step 1, Create a new application in Apex complete with a page


In Apex 4.2, from the Home Screen click

  • Application Builder
  • Create 
  • Check the Radio Group Database
  • Next
  • Next
  • Create Application
  • Create Application
The application is now created complete with a Page 1. 

Step 2 create a region to hold the page items.

 

Click on the home page

In tree view, right click on regions and then click

 





  • Create
  • Select the HTML radio group
  • Next
  • Select the HTML radio group
  • NEXT
  • Enter a title of Region 1 
  • Click Create Region.

Now the region is created the edit screen is displayed.

Step 3, Create a text entry field


  • Right Click on Region 1 and select Create Page Item
  • Select the radio group Text Field and click Next
  • Enter an item name of P1_ENTER_NAME then click Next
  • In the label enter the text Enter Name, then click
  • Next
  • Next
  • Create Item

     

    Step 4, Create a submit button


    • Right Click on Region 1
    • Click Create Region Button
    • Enter a name of P1_SUBMIT
    • Set the label value to Submit. 
    • Click Create Item



      Step 5, Create a display region to say Hello to the entered user name


      • Right click region 1 and select Create Page Item
      • Select Display Only, (not display image)
      • Next
      • Set item name to P1_RESULT
      • Next
      • Set label to Hello
      • Next
      • Next
      On this page we need to apply some process logic

      1. Set Source used to Always, replacing any existing value in Session State.
      2. Source Type to PL/SQL Expression
      3. In the Source value or expression enter :
           apex_util.get_session_state ('P1_ENTER_NAME')
            4. Click create Item
       
       These settings mean
      1. Always change the value of the field
      2. The source for the value of the filed will be derived from PL/SQL
      3. Set the value to the value of P1_ENTER_NAME as held in session state.

      Step 6, Run the page


      Click run on the page.  Enter a name and press the submit button.

      Summary of how the page works


      • A user enters their name into the text box (P1_ENTER_NAME).
      • When Submit is pressed the page is submitted, the values of all fields are placed in session state and the page is startes to re-render.
      • The display only field is set to the same value as P1_ENTER_NAME through the call to apex_util.get_session_state ('P1_ENTER_NAME').



      Wednesday 12 December 2012

      Customising an Apex url Example

       


      When providing users with a link to your Apex application the url will typically look something like:

      http://apex.oracle.com/pls/apex/f?p=44054:1:

      where

      • 44054 represents the application and 
      • 1 represents the page number.

      Apex allows the customising of url's so they can appear more descriptive, such as:

      http://apex.oracle.com/pls/apex/f?p=MY_FIRST_APPLICATION:MY_FIRST_PAGE:

      where

      • MY_FIRST_APPLICATION is the name of the application and
      • MY_FIRST_PAGE is the name of the page.

      Naming the application

       

      To give an application a name:

      Application Builder -> Edit the Application -> Shared Componments

      On the right side of the page, in a box titled Application click Edit Definition.

      On the resulting page change the Application Alias to the name you want.




      Click Apply Changes

      Now, the url

      http://apex.oracle.com/pls/apex/f?p=44054:1:

      can be represented as

      http://apex.oracle.com/pls/apex/f?p=MY_FIRST_APPLICATION:1:


      Changing the Page Name

       

      Click on the page.

      In tree mode, double click on Page name



      In the Page Alias text box enter a page alias and then click Apply Changes.



      Now the url can be specified as

      http://apex.oracle.com/pls/apex/f?p=MY_FIRST_APPLICATION:MY_FIRST_PAGE:



      Monday 10 December 2012

      Introduction to Oracle XML



      XML in Oracle can be stored in the database type XMLTYPE.
      To convert string text into an xml document a call to XMLTYPE needs to be made
      /* Convert a string into xml */
      DECLARE
      
      l_xml      XMLTYPE ;
      
      BEGIN
      
         l_xml := XMLTYPE ('<Message>Hello World</Message>') ;
      
      END ;
      /
      
      XML data can be converted into a clob by the use of the method getclobval

      /*Convert a string into xml and then into a clob */
      DECLARE
      
      l_xml      XMLTYPE ;
      l_clob     CLOB ;
      
      BEGIN
      
         l_xml := XMLTYPE ('<Message>Hello World</Message>') ;
         
         l_clob := l_xml.getclobval ;
      
      END ;
      /
      
      Trying to convert a null xmltype to a clob will result in an error

      DECLARE
      
      l_xml      XMLTYPE ;
      l_clob     CLOB ;
      
      BEGIN
      
         l_clob := l_xml.getclobval ;
      
      END ;
      /
      
      DECLARE
      *
      ERROR at line 1:
      ORA-30625: method dispatch on NULL SELF argument is disallowed
      ORA-06512: at line 10
      
      XML data can be stored in tables.
      /* create a table to store xml in */
      CREATE TABLE my_xml OF XMLType
      /
      
      /* store xml in the table */
      
      DECLARE
      
      l_xml      XMLTYPE ;
      l_clob     CLOB ;
      
      BEGIN
      
         l_xml := XMLTYPE ('<Message>Hello World</Message>') ;
         
         INSERT INTO my_xml VALUES (l_xml) ;
         
         COMMIT ;
      
      END ;
      /
      
      PL/SQL procedure successfully completed.
      
      select * from my_xml ;
      
      SYS_NC_ROWINFO$
      ------------------------------------------------------------------------------
      <Message>Hello World</Message>
      
      
      When selecting large xml documents from a table in SQLPLUS the details may be truncated. To overcome this increase the size of long using the command SET LONG 999999999

      Extracting data from xml using xpath

      Using the sample xml document

      <userdetails>
         <username>Fred</username>
         <password>pass123</password>
      </userdetails>
      
      Insert the xml into a database table

      DELETE FROM my_xml ;
      
      INSERT INTO my_xml VALUES (XMLTYPE ('<userdetails>
                                             <username>Fred</username>
                                             <password>pass123</password>
                                           </userdetails>')) ;
                                           
      COMMIT ;
      
      
      /* To extract the username */
      
      SELECT extractValue(value(x),
                          '/userdetails/username')
        FROM my_xml x ;
      
      EXTRACTVALUE(VALUE(X),'/USERDETAILS/USERNAME')
      ----------------------------------------------------------------
      Fred
      
      /* To extract the password */
      
      SELECT extractValue(value(x),
                          '/userdetails/password')
        FROM my_xml x ;
      
      EXTRACTVALUE(VALUE(X),'/USERDETAILS/PASSWORD')
      -----------------------------------------------------------
      pass123
      
      Note, the xpath case must match the case in the xml document. If we pass in password in uppercase then no data will be located

      SELECT extractValue(value(x),
                          '/userdetails/PASSWORD')
        FROM my_xml x ;
      
      EXTRACTVALUE(VALUE(X),'/USERDETAILS/PASSWORD')
      -------------------------------------------------------
      
      
      Extracting XML details in pl/sql

      DECLARE
      
      l_xml                XMLTYPE ;
      l_username           XMLTYPE ;
      l_username_varchar   VARCHAR2(20) ;
      
      BEGIN
      
         l_xml := XMLTYPE ('<userdetails>
                               <username>Fred</username>
                               <password>pass123</password>
                            </userdetails>') ;
             
         -- extract value to clob               
         l_username := l_xml.extract ('/userdetails/password/text()') ;          
      
         dbms_output.put_line (l_username.getclobval) ;   
      
         -- extract value to string 
         l_username_varchar := l_xml.extract ('/userdetails/password/text()').getstringval ;
      
      END ;
      /
      
      pass123
      
      PL/SQL procedure successfully completed.
      
      
      The /text() was required to extract the value, without it we would get the node as shown below

      DECLARE
      
      l_xml        XMLTYPE ;
      l_username   XMLTYPE ;
      
      BEGIN
      
         l_xml := XMLTYPE ('<userdetails>
                               <username>Fred</username>
                               <password>pass123</password>
                            </userdetails>') ;
                            
         l_username := l_xml.extract ('/userdetails/password') ;          
      
         dbms_output.put_line (l_username.getclobval) ;   
      
      END ;
      /
      
      <password>pass123</password>
      
      
      PL/SQL procedure successfully completed.
      
      If an XML document has one or more namespaces then these need to be passed into the xpath

      DELETE FROM my_xml ;
      
      INSERT INTO my_xml VALUES (XMLTYPE('<userdetails xmlns="http://abc.com/namespaceabc/v1">
                                            <username>Fred</username>
                                            <password>pass123</password>
                                          </userdetails>')) ;
      
      COMMIT ;
      
      The previous xpath will no longer work as this xml has a namespace, (the namespace being http://abc.com/namespaceabc/v1

      SELECT extractValue(value(x),
                          '/userdetails/password')
        FROM my_xml x ;
        
      EXTRACTVALUE(VALUE(X),'/USERDETAILS/PASSWORD')
      -----------------------------------------------------
         
      
      The namespace needs to be passed into the xpath
      SELECT extractValue(value(x),
                          '/a:userdetails/a:password' ,
                          'xmlns:a="http://abc.com/namespaceabc/v1"')
        FROM my_xml x ;
        
      EXTRACTVALUE(VALUE(X),'/A:USERDETAILS/A:PASSWORD','XMLNS:A="HTTP://ABC.COM/NAMESPACEABC/V1"')
      --------------------------------------------------------------------------------------------------
      pass123
      
      The use of the letter a for the alias is not relevant. In this example the word fred is used instead of the letter a
      SELECT extractValue(value(x),
                          '/fred:userdetails/fred:password' ,
                          'xmlns:fred="http://abc.com/namespaceabc/v1"')
        FROM my_xml x ;
      
      EXTRACTVALUE(VALUE(X),'/FRED:USERDETAILS/FRED:PASSWORD','XMLNS:FRED="HT
      -----------------------------------------------------------------------
      pass123  
      
      Sometimes values are stored as attributes. In the example below the usertype of ADMIN is held in the attribute called usertype. References can be made to attributes by using the @ symbol.
      DECLARE
       
      l_xml        XMLTYPE ;
      l_usertype   VARCHAR2(10) ;
       
      BEGIN
       
         l_xml := XMLTYPE ('<userdetails>
                               <username usertype="ADMIN">Fred</username>
                               <password>pass123</password>
                            </userdetails>') ;
       
         l_usertype := l_xml.extract ('/userdetails/username/@usertype').getstringval ;          
       
         dbms_output.put_line (l_usertype) ;   
       
      END ;
      /
       
      ADMIN
       
      PL/SQL procedure successfully completed.
      



      Saturday 8 December 2012

      Oracle APEX, Allowing users to switch Applications without having to log back in



      When developing different applications in the same workspace sometimes you want to call one application from another.  
      If the applications are using the same authentication schema the user will still be prompted to log on each time they move from one application to another.

      It is possible to configure your applications so a user only has to log in once. This is done by setting cookie attributes.
      • In Apex, click on the first application, then shared componments
      • Click the edit link next to the authentication schemes.
      • On the next page select the Session Cookie tab. 
      • In the Cookie Name file and enter a cookie name.



      • Repeat this for all your applications making sure you enter the same cookie name.
      Now once the user has logged in once they are no longer prompted to log in each time they move from one application to another.