Parent Child Report - SQL Developer

1. To create a parent child report in SQL Developer Click on the View Menu and then Reports.


Once reports is shown right click on User Defined Reports and select New








2. Give the report a name and in the SQL box enter the sql for the parent report.







3. Click Apply



4. Go Back to reports and edit the report.











5. Click on child reports and enter a name for the child report.









6. On the tree on the left hand side, expand child reports and click on SQL Query.

Enter the query for the child report.
In this report the child report ties to the parent report via the deptno number.
The deptno number of the parent report is referenced by :DEPTNO. The bind variable must be in upper case and must be prefixed with a colon.






7. Save the report.



8. Double click the report in the reports tree to run it.  When an entry in the parent report is selected the appropriate children in the child report are shown.

Drill Down Report - SQL Developer

In SQL Developer you can link one oracle report to another creating a drill down from the first report to the second. To do this we create two reports, the master report and the drill down report.

1. First I'm going to create a master report based on the dept table.

From the VIEW menu choose Reports.

Once reports is displayed right click on User Defined Reports and select New Report








2. The Create Report dialogue is displayed.

Enter the query to select the details of the master report in the SQL section, in this case



  
SELECT *
  FROM dept


and click apply






The newly created Dept report now appears under User Defined Reports.


3. Right click on the report and run it.









4. Next I'll create the drill down report.  This report is based on the emp table which details employees.  Each employee in this table belongs to a department shown in the Dept report above.

As before I'll click on User Defined Reports and choose new report.

I'll give it a name of emp and under SQL Query I'll enter



  
SELECT *
  FROM emp
 WHERE deptno = :DEPTNO


The where clause ties the drill down to the master report.  It says select records from emp where the deptno, (department number), equals the :DEPTNO bind value passed in.  When referencing a bind value it is always in upper case and always prefixed with a colon.








5. Next I need to go back to the master report (dept).

Edit the report and choose Drill Down.
Click Add Report.

Give the report a name and then under report click the drop down box and select the emp report.






6. Save the report and then run it again.

Right click on any department.  The employess report is shown in the menu.







7. Click on employess and the employees report will be run for the selected deptartment

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 ;
/

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 ;

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
/

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

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.