Friday, 9 October 2015

ORA-06502: PL/SQL: numeric or value error: Bulk Bind: Truncated Bind




The example below is raising a ORA-06502: PL/SQL: numeric or value error: Bulk Bind: Truncated Bind
  
DECLARE

TYPE rec1_typ IS RECORD (len_3          VARCHAR2(3) ,
                         len_5          VARCHAR2(5)) ;
                     
TYPE rec1 IS TABLE OF rec1_typ INDEX BY BINARY_INTEGER ;    
rec1_tab  rec1 ;                 
      
CURSOR c1
IS    
SELECT 'xxx' a , 
       'xxxxxxxxxxx' b 
  FROM DUAL ;    

BEGIN

   OPEN c1 ;
   FETCH c1 BULK COLLECT INTO rec1_tab ;
   CLOSE c1 ;

END ;
/
DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: Bulk Bind: Truncated Bind
ORA-06512: at line 18

The issue is being caused because rec1_type.len5 is defined as a VARCHAR2(5) but the cursor is trying to assign an 11 charater string of 'xxxxxxxxxxx'. In this case the fix is to resize the collection.
DECLARE

TYPE rec1_typ IS RECORD (len_3          VARCHAR2(3) ,
                         len_5          VARCHAR2(11)) ;
                     
TYPE rec1 IS TABLE OF rec1_typ INDEX BY BINARY_INTEGER ;    
rec1_tab  rec1 ;                 
                     
CURSOR c1
IS    
SELECT 'xxx' a , 
       'xxxxxxxxxxx' b 
  FROM DUAL ;    

BEGIN

   OPEN c1 ;
   FETCH c1 BULK COLLECT INTO rec1_tab ;
   CLOSE c1 ;

END ;
/



Thursday, 8 October 2015

Oracle, accessing collections in SQL




In Oracle it is possible to access collections within SQL statements.

With the following collection:

CREATE TYPE region_tab AS TABLE OF VARCHAR2(2)
/
And the following table and data

desc regions
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------
 SHORT_CODE                                                     VARCHAR2(2)
 NAME                                                           VARCHAR2(70)


SELECT * from regions ;

SH NAME
-- -----------------------------------------
SE South East
WM West Midlands
SW South West
NE North East

We can write the code below which populates the collection with the short_codes of 'WM' and 'SW' and then use a SQL statement to read the collection and find the corresponding regions.name. This is possible through the use of the collection condition MEMBER OF

DECLARE

CURSOR c1
(pl_region_tab    region_tab)
IS
SELECT name 
  FROM regions
 WHERE short_code MEMBER OF pl_region_tab ;

l_region_tab          region_tab := region_tab ();

BEGIN

   -- add values to collection
   l_region_tab.EXTEND ;
   l_region_tab(1) := 'WM' ;
    
   l_region_tab.EXTEND ;
   l_region_tab(2) := 'SW' ;    
    
      FOR c1_rec IN c1 (l_region_tab)
      LOOP
         dbms_output.put_line ('Region name : ' || c1_rec.name) ;
      END LOOP ;      

END ;
/

Region name : West Midlands
Region name : South West

PL/SQL procedure successfully completed.


For completeness the ddl / dml for the table is:


CREATE TABLE regions
(short_code       VARCHAR2(2),
 name             VARCHAR(70)
)
/

INSERT INTO regions (short_code, name) VALUES ('SE', 'South East') ;
INSERT INTO regions (short_code, name) VALUES ('WM', 'West Midlands') ;
INSERT INTO regions (short_code, name) VALUES ('SW', 'South West') ;
INSERT INTO regions (short_code, name) VALUES ('NE', 'North East') ;
COMMIT ;





Monday, 21 September 2015

ora-00904 xml invalid identifier




If you have a XML column as part of a table, for example:

 DESC tab_with_xml_column
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- -------------
 ID                                                             NUMBER
 XML_DATA                                                       XMLTYPE
 CREATED_DATE                                                   DATE


Then when trying to apply XML functionality to that column errors can occur, eg:


SELECT xml_data.EXTRACT('/userdetails/username') FROM tab_with_xml_column
       *
ERROR at line 1:
ORA-00904: "XML_DATA"."EXTRACT": invalid identifier


The solution is to simply enclose the column name within brackets:


SELECT (xml_data).EXTRACT('/userdetails/username') FROM tab_with_xml_column ;

(XML_DATA).EXTRACT('/USERDETAILS/USERNAME')
----------------------------------------------------------------------------------------------------
Fred

Example 2:

SQL > SELECT *
        FROM tab_with_xml_column
       WHERE (xml_data).EXTRACT('/userdetails/username/text()').GETSTRINGVAL()  = 'Fred';

                ID
------------------
XML_DATA
----------------------------------------------------------------------------------------------------
CREATED_D
---------
                 1

   Fred
   pass123

20-SEP-15

This was tested on Oracle 10 and Oracle 11. The table and data used was:

CREATE TABLE tab_with_xml_column
(id               NUMBER ,
 xml_data         XMLTYPE ,
 created_date     DATE
)
/

INSERT INTO tab_with_xml_column
(id ,
 xml_data ,
 created_date)
VALUES 
(1,
 XMLTYPE('
   Fred
   pass123
') ,
SYSDATE) 
/

COMMIT
/



Tuesday, 22 January 2013

Validate XML against an XSD using JDeveloper




Validating xml against an xsd can be achieved thorough JDeveloper, (which is available as a free download).

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 both the files in JDeveloper. Once opened a reference to the XSD needs to be made in the xml file. Modify the xml file and add the following entry to the initial node

    
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"  
  xsi:noNamespaceSchemaLocation="a.xsd">

so the file looks like

<userdetails
 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"  
    xsi:noNamespaceSchemaLocation="a.xsd">
   <username>Fred</username>
   <password>pass123</password>
</userdetails>

This adds a reference to the xsd file (a.xsd) which resides in the same file location as the xml.

The xml can be validated by right clicking the xml and choosing Validate XML.


If the xml validates against the xsd the following will be displayed in the messages log.


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


Validating xml with a namespace against an xsd

In the following example the xml is modified to have a default namespace of http://www.onsheld.co.uk/ns1

<?xml version="1.0"?>
<userdetails xmlns="http://www.onsheld.co.uk/ns1">
   <username>Fred</username>
   <password>pass123</password>
</userdetails>

The xsd to validate the xml is show below.  Note the xsd has a reference to the 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>


Further resources


Monday, 7 January 2013

VB .Net, Creating Hello World Example



In this blog entry we are going to create a form in VB .Net.  The user will be able to run the form and enter their name in a text box.  On pressing the submit button the form will display the words Hello and the name entered in the text box.

To create this application we will complete the following steps:
    1. Open up Visual Studio 2012
    2. Create a new project
    3. Add a text box to the form
    4. Set the properties of the text box
    5. Add a button
    6. Set the properties of the button
    7. Add a label to the form
    8. Set the properties of the label
    9. Create the code behind the form
    10. Run the form

      Step 1, Open up Microsoft Visual Studio 2012.

      • Click the windows button
      • Click on Visual Studio 2012

      Step 2, Create a new project

      • Click File
      • New Project




      • In the name field enter a name
      • Click OK.

      Step 3, Add a text box to the form


      A page will appear with a new blank form.
      At the left hand side of the form there is a menu called Toolbox.



      Click on the Toolbox Menu


      • Click on TextBox
      • Hold down the left mouse button
      • Drage th Text Box off the Menu
      • When the mernu disappears drop the text box onto the form.
       The form should now look like this:


      Step 4, Set the properties of the text box


      If we click on the text box on the bottom right of the screen we can see the properties of the text box.  The properties are all the differnt settings of the text box.



      Click on the properites and find name.  Make sure its set to textBox1


      Step 5, Add a button

      • Click on the Toolbox on the left of the form
      • Click on Button
      • Hold down the left mouse button
      • Drag the Button off the Menu
      • When the mernu disappears drop the button onto the form.

      Step 6, Set the properties of the button

      • Click on the button on the form
      • Go over to the properties (bottom right of the screen)
      • Make sure the name is button1
      • Change text to Submit.  When this is changed you'll see the label of the button change from button1 to Submit 

      Step 7, Add a label to the form


      Click on the Toolbox on the left of the form
      Click on Label
      Hold down the left mouse button
      Drag the Label off the Menu
      When the menu disappears drop the label onto the form.
      Put the lavel on the form underneath the button

      Step 8, Set the properties of the label

      • Make sure the name is label1
      • Set the text to nothing
      • Set Minimum Size to 50,0


      Step 9, Create the code behind the form

      • Double click on the button
      • Enter the following code
        • label2.Text = "Hello " +  textBox1.Text
      • between the text Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click and  End Sub
      Public Class Form1
      
          Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
      
             label2.Text = "Hello " +  textBox1.Text
      
          End Sub
      End Class
      



      Step 10, run the form


      Click the start button on the toolbar to run the form

      How it works

       

      • The users enters their name in textbox1.
      • The user then presses the button button1.
      • Pressing button1 causes the code behind button1to run.
      • The code behind button1 sets the label (labl1) to the text Hello + whatever text has been entered in textbox1.



      Saturday, 5 January 2013

      Validate XML in Oracle against an XSD using DBMS_XMLSCHEMA and XMLTYPE.SCHEMAVALIDATE

      The xml below

      <userdetails>
         <username>Fred</username>
         <password>pass123</password>
      </userdetails>
      can be represented by the following XSD

      <?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>
      The xsd can be registered with the oracle database by making a call to dbms_xmlschema.registerSchema.
      In the example below we have named this xsd as xsd1.xsd

      BEGIN    
      
         dbms_xmlschema.registerSchema(schemaURL   => 'xsd1.xsd', 
                                       schemaDoc   => '<?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>'); 
      END; 
      / 
      The registered schema can be viewed in the database by accessing the data dictionary all_xml_schemas

      SELECT schema 
        FROM all_xml_schemas 
       WHERE schema_url = 'xsd1.xsd'
      /
      
      SCHEMA
      ----------------------------------------------------------------------------------------------------
      <?xml version="1.0" encoding="WINDOWS-1252"?>
      <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:oraxdb="http://xmlns.oracle.com/xdb" oraxdb:flags="295" oraxdb:schemaURL="xsd1.xsd" oraxdb:schemaOwner="MYSCHEMA" oraxdb:numProps="3">
         <xs:element name="userdetails" oraxdb:propNumber="2515" oraxdb:global="true" oraxdb:SQLName="userdetails" 
        oraxdb:SQLType="userdetails193_T" oraxdb:SQLSchema="MYSCHEMA" oraxdb:memType="258" oraxdb:defaultTable="userdetails194_TAB" oraxdb:defaultTableSchema="MYSCHEMA">
            <xs:complexType oraxdb:SQLType="userdetails193_T" oraxdb:SQLSchema="MYSCHEMA">
               <xs:sequence>
                  <xs:element name="username" type="xs:string" oraxdb:propNumber="2513" oraxdb:global="false"
      oraxdb:SQLName="username" oraxdb:SQLType="VARCHAR2" oraxdb:memType="1" oraxdb:SQLInline="true" oraxdb:MemInline="true" oraxdb:JavaInline="true"/>
                  <xs:element name="password" type="xs:string" oraxdb:propNumber="2514" oraxdb:global="false"
      oraxdb:SQLName="password" oraxdb:SQLType="VARCHAR2" oraxdb:memType="1" oraxdb:SQLInline="true" oraxdb:MemInline="true" oraxdb:JavaInline="true"/>
               </xs:sequence>
            </xs:complexType>
         </xs:element>
      </xs:schema>
      To validate the xml shown below

      <userdetails>
         <username>Fred</username>
         <password>pass123</password>
      </userdetails>
      we need to add a reference to the xsd we registered earlier and called xsd1.xsd. To the outer node add the reference xsi:noNamespaceSchemaLocation="xsd1.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

      <userdetails xsi:noNamespaceSchemaLocation="xsd1.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
      

      We can then make a call to XMLTYPE.schemaValidate to validate the xml against the schema
      DECLARE
      
      l_xml         XMLTYPE ;
      
      BEGIN    
      
         l_xml := XMLTYPE('<userdetails xsi:noNamespaceSchemaLocation="xsd1.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
                             <username>Fred</username>
                             <password>pass123</password>
                           </userdetails>');     
                           
         XMLTYPE.schemaValidate(l_xml); 
      
      END; 
      /
      
      
      PL/SQL procedure successfully completed.
      The successful execution of the pl/sql indicates the xml was valid. Adding an element that is not defined in the xsd (element newelement) will fail schema validation resulting in an exception being raised

      DECLARE
      
      l_xml         XMLTYPE ;
      
      BEGIN    
      
         l_xml := XMLTYPE('<?xml version="1.0" encoding="UTF-8"?>
                           <userdetails xsi:noNamespaceSchemaLocation="xsd1.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
                             <username>Fred</username>
                             <password>pass123</password>
                             <newelement>dummy value</newelement>
                           </userdetails>');     
                           
         XMLTYPE.schemaValidate(l_xml); 
      
      END; 
      /
      
      DECLARE
      *
      ERROR at line 1:
      ORA-30937: No schema definition for 'newelement' (namespace '##local') in parent '/userdetails'
      ORA-06512: at "SYS.XMLTYPE", line 345
      ORA-06512: at line 14
      If we don't have an XSD but already have a type representation in the database Oracle allows us to generate an XSD based upon that type

      Our original XML

      <userdetails>
         <username>Fred</username>
         <password>pass123</password>
      </userdetails>
      can be represented in the database by the following type

      CREATE TYPE user_details_typ AS OBJECT
      (
       "username"     VARCHAR2(20) ,
       "password"     VARCHAR2(30)
      )
      /
      
      Type created.
      Note, the attributes username and password have been enclosed in double quotes to preserve their case. To generate the XSD a call to dbms_xmlschema.generateschema is required.

      SELECT DBMS_XMLSCHEMA.generateschema('MYSCHEMA', 'USER_DETAILS_TYP', 'userdetails') FROM DUAL ;
      

      This will result in the following XSD being created
      <?xml version="1.0"?>                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
      <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" 
      xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
      xmlns:xdb="http://xmlns.oracle.com/xdb" 
      xsi:schemaLocation="http://xmlns.oracle.com/xdb http://xmlns.oracle.com/xdb/XDBSchema.xsd">                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
       <xsd:element name="userdetails" type="USER_DETAILS_TYPType" xdb:SQLType="USER_DETAILS_TYP" xdb:SQLSchema="MYSCHEMA"/>                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
       <xsd:complexType name="USER_DETAILS_TYPType" xdb:SQLType="USER_DETAILS_TYP" xdb:SQLSchema="MYSCHEMA" xdb:maintainDOM="false">                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
        <xsd:sequence>                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
         <xsd:element name="username" xdb:SQLName="username" xdb:SQLType="VARCHAR2">                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
          <xsd:simpleType>                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
           <xsd:restriction base="xsd:string">                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
            <xsd:maxLength value="20"/>                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
           </xsd:restriction>                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
          </xsd:simpleType>                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
         </xsd:element>                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
         <xsd:element name="password" xdb:SQLName="password" xdb:SQLType="VARCHAR2">                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
          <xsd:simpleType>                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
           <xsd:restriction base="xsd:string">                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
            <xsd:maxLength value="30"/>                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
           </xsd:restriction>                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
          </xsd:simpleType>                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
         </xsd:element>                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
        </xsd:sequence>                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
       </xsd:complexType>                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
      </xsd:schema> 
       
      The parameters passed into dbms_xmlschema.generateschema were:
      • 'MYSCHEMA' - the name of the schema the object (user_details_typ) resides
      • 'USER_DETAILS_TYP' - the name of the object
      • 'userdetails' - the name of the top node
      An issue with creating types where the attributes are enclosed in double quotes such as

      CREATE TYPE user_details_typ AS OBJECT
      (
       "username"     VARCHAR2(20) ,
       "password"     VARCHAR2(30)
      )
      /
      
      
      is that in order to reference the type in pl/sql the attributes have to be enclosed in double quotes to preserve their case

      DECLARE
      
      l_user_details     user_details_typ ;
      
      BEGIN
      
         l_user_details := user_details_typ (NULL, NULL) ;
         l_user_details."username" := 'Fred' ;
      
      END ;
      /
      
      PL/SQL procedure successfully completed.
      
      
      Failing to use double quotes will result in an error

      DECLARE
      
      l_user_details     user_details_typ ;
      
      BEGIN
      
         l_user_details := user_details_typ (NULL, NULL) ;
         l_user_details.username := 'Fred' ;
      
      END ;
      /
      
      ERROR at line 8:
      ORA-06550: line 8, column 19:
      PLS-00302: component 'USERNAME' must be declared
      ORA-06550: line 8, column 4:
      PL/SQL: Statement ignored
      
      
      Alternatively the object could be created without double quotes and the resulting xml could be tranformed using a style sheet.
      The code below transforms xml with uppercase attribute names to xml with lower case attribute names using the xml method transform.

      DECLARE
      
      l_xml              XMLTYPE ;
      l_xslt             XMLTYPE ;
      l_transformed_xml  XMLTYPE ;
      
      BEGIN 
      
         -- the original xml
         l_xml := XMLTYPE ('<USERDETAILS>
                              <USERNAME>Fred</USERNAME>
                              <PASSWORD>pass123</PASSWORD>
                           </USERDETAILS>') ;
                           
         dbms_output.put_line ('Original xml' || CHR(10)||'---------------------------') ;                  
         dbms_output.put_line (l_xml.getclobval) ;   
         
         
         -- stylesheet to transform all the element names to lower case
         
         l_xslt := XMLTYPE ('<?xml version="1.0" encoding="UTF-8"?>
                              <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
                              <xsl:output method="xml" encoding="UTF-8" version="1.0" />  
                                <xsl:template match="*">
                                  <xsl:variable name="vElement"
                                    select="translate(name(), ''ABCDEFGHIJKLMNOPQRSTUVWXYZ'',''abcdefghijklmnopqrstuvwxyz'')" />
                                  <xsl:element name="{$vElement}">
                                    <xsl:apply-templates />
                                  </xsl:element>
                                </xsl:template>
                              </xsl:stylesheet>') ;      
      
         -- apply the xslt to the original xml
         
         l_transformed_xml := l_xml.transform (l_xslt) ;          
         
         dbms_output.put_line ('Transformed xml'|| CHR(10)||'---------------------------') ;      
         dbms_output.put_line (l_transformed_xml.getclobval) ;
      
      
      END ;
      /
      
      Original xml
      ---------------------------
      <USERDETAILS>
                              <USERNAME>Fred</USERNAME>
      
      <PASSWORD>pass123</PASSWORD>
                           </USERDETAILS>
      Transformed xml
      ---------------------------
      <userdetails>
      <username>Fred</username>
      <password>pass123</password>
      </userdetails>
      
      
      PL/SQL procedure successfully completed.
      

      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').