Oracle XMLELEMENT Example 1

The following example produces an Emp element for a series of employees, with nested elements that provide the employee's name and hire date:

SELECT XMLELEMENT("Emp", XMLELEMENT("Name",
   e.job_id||' '||e.last_name),
   XMLELEMENT("Hiredate", e.hire_date)) as "Result"
   FROM employees e WHERE employee_id > 200;

Result
-------------------------------------------------------------------

  MK_MAN Hartstein
  17-FEB-96


  MK_REP Fay
  17-AUG-97


  HR_REP Mavris
  07-JUN-94


  PR_REP Baer
  07-JUN-94


  AC_MGR Higgins
  07-JUN-94


  AC_ACCOUNT Gietz
  07-JUN-94

6 rows selected.

The following similar example uses the XMLElement function with the XML_attributes_clause to create nested XML elements with attribute values for the top-level element:

SELECT XMLELEMENT("Emp",
      XMLATTRIBUTES(e.employee_id AS "ID", e.last_name),
      XMLELEMENT("Dept", e.department_id),
      XMLELEMENT("Salary", e.salary)) AS "Emp Element"
   FROM employees e
   WHERE e.employee_id = 206;

Oracle XMLELEMENT Example

Purpose

XMLElement takes an element name for identifier, an optional collection of attributes for the element, and arguments that make up the content of the element. It returns an instance of type XMLType. XMLElement is similar to SYS_XMLGen except that XMLElement can include attributes in the XML returned, but it does not accept formatting using the XMLFormat object.

The XMLElement function is typically nested to produce an XML document with a nested structure, as in the example in the following section.

You must specify a value for identifier, which Oracle Database uses as the enclosing tag. The identifier can be up to 4000 characters and does not have to be a column name or column reference. It cannot be an expression or null.

The objects that make up the element content follow the XMLATTRIBUTES keyword. In the XML_attributes_clause, if the value_expr is null, then no attribute is created for that value expression. The type of value_expr cannot be an object type or collection. If you specify an alias for value_expr using the AS clause, the c_alias can be up to 4000 characters.

For the optional value_expr that follows the XML_attributes_clause in the diagram:

If value_expr is a scalar expression, then you can omit the AS clause, and Oracle uses the column name as the element name.

If value_expr is an object type or collection, then the AS clause is mandatory, and Oracle uses the specified c_alias as the enclosing tag.

If value_expr is null, then no element is created for that value expression.

Oracle SQL SELECT Statement

The most commonly used SQL command is SELECT statement. The SQL SELECT statement is used to query or retrieve data from a table in the database. A query may retrieve information from specified columns or from all of the columns in the table. To create a simple SQL SELECT Statement, you must specify the column(s) name and the table name. The whole query is called SQL SELECT Statement.

Syntax of SQL SELECT Statement:
SELECT column_list FROM table-name
[WHERE Clause]
[GROUP BY clause]
[HAVING clause]
[ORDER BY clause];

table-name is the name of the table from which the information is retrieved.
column_list includes one or more columns from which data is retrieved.
The code within the brackets is optional.

Example:

SELECT EMPLOYEE_NAME,EMPLOYEE_ID,DESIGNATION FROM EMPLOYEE_DETAILS;

Oracle PL/SQL Tutorial

SQL commands are instructions used to communicate with the database to perform specific task that work with data. SQL commands can be used not only for searching the database but also to perform various other functions like, for example, you can create tables, add data to tables, or modify data, drop the table, set permissions for users. SQL commands are grouped into four major categories depending on their functionality:

Data Definition Language (DDL) - These SQL commands are used for creating, modifying, and dropping the structure of database objects. The commands are CREATE, ALTER, DROP, RENAME, and TRUNCATE.
Data Manipulation Language (DML) - These SQL commands are used for storing, retrieving, modifying, and deleting data. These commands are SELECT, INSERT, UPDATE, and DELETE.
Transaction Control Language (TCL) - These SQL commands are used for managing changes affecting the data. These commands are COMMIT, ROLLBACK, and SAVEPOINT.
Data Control Language (DCL) - These SQL commands are used for providing security to database objects. These commands are GRANT and REVOKE

Oracle CEIL or CEILING Function

In Oracle/PLSQL, the ceil function returns the smallest integer value that is greater than or equal to a number.
The CEIL and CEILING functions round the specified number up, and return the smallest number that is greater than or equal to the specified number

The specified number must be a DOUBLE PRECISION number.
If the specified number is NULL, the result of these functions is NULL.
If the specified number is equal to a mathematical integer, the result of these functions is the same as the specified number.
If the specified number is zero (0), the result of these functions is zero.
If the specified number is less than zero but greater than -1.0, then the result of these functions is zero.

Syntax:
CEIL ( number )
CEILING ( number )
number is the value used to find the smallest integer value.

Example :
SELECT CEIL(12345.67) FROM dual;     Output: 1236
SELECT CEIL(-45.44) FROM dual;           Output: -45

What is not possible using java class Inheritance?

1. Private members of the superclass are not inherited by the subclass and can only be indirectly accessed.
2. Members that have default accessibility in the superclass are also not inherited by subclasses in other packages, as these members are only accessible by their simple names in subclasses within the same package as the superclass.
3. Since constructors and initializer blocks are not members of a class, they are not inherited by a subclass.
4. A subclass can extend only one superclass

Compiling and Running an Application in Java

To compile and run the program you need the JDK distributed by Sun Microsystems. The JDK contains documentation, examples, installation instructions, class libraries and packages, and tools. Download an editor like Textpad/EditPlus to type your code. You must save your source code with a .java extension. The name of the file must be the name of the public class contained in the file.

Steps for Saving, compiling and Running a Java

Step 1:Save the program With .java Extension.
Step 2:Compile the file from DOS prompt by typing javac .
Step 3:Successful Compilation, results in creation of .class containing byte code
Step 4:Execute the file by typing java

Java source code

A Java program is a collection of one or more java classes. A Java source file can contain more than one class definition and has a .java extension. Each class definition in a source file is compiled into a separate class file. The name of this compiled file is comprised of the name of the class with .class as an extension. Before we proceed further in this section, I would recommend you to go through the ‘Basic Language Elements’.

Below is a java sample code for the traditional Hello World program. Basically, the idea behind this Hello World program is to learn how to create a program, compile and run it. To create your java source code you can use any editor( Text pad/Edit plus are my favorites) or you can use an IDE like Eclipse.

public class HelloWorld {
    public static void main(String[] args) {
        System.out.println("Hello World");
    }//End of main
}//End of HelloWorld Class


Output
Hello World

ABOUT THE PROGRAM

I created a class named “HelloWorld” containing a simple main function within it. The keyword class specifies that we are defining a class. The name of a public class is spelled exactly as the name of the file (Case Sensitive). All java programs begin execution with the method named main(). main method that gets executed has the following signature : public static void main(String args[]).Declaring this method as public means that it is accessible from outside the class so that the JVM can find it when it looks for the program to start it. It is necessary that the method is declared with return type void (i.e. no arguments are returned from the method). The main method contains a String argument array that can contain the command line arguments. The brackets { and } mark the beginning and ending of the class. The program contains a line ‘System.out.println(”Hello World”);’ that tells the computer to print out on one line of text namely ‘Hello World’. The semi-colon ‘;’ ends the line of code. The double slashes ‘//’ are used for comments that can be used to describe what a source code is doing. Everything to the right of the slashes on the same line does not get compiled, as they are simply the comments in a program.

Java Main method Declarations

class MainExample1 {public static void main(String[] args) {}}
class MainExample2 {public static void main(String []args) {}}
class MainExample3 {public static void main(String args[]) {}}

All the 3 valid main method’s shown above accepts a single String array argument.

Java Architecture

The Java environment is composed of a number of system components. You use these components at compile time to create the Java program and at run time to execute the program. Java achieves its independence by creating programs designed to run on the Java Virtual Machine rather than any specific computer system.

After you write a Java program, you use a compiler that reads the statements in the program and translates them into a machine independent format called bytecode.


Bytecode files, which are very compact, are easily transported through a distributed system like the Internet.
The compiled Java code (resulting byte code) will be executed at run time.
Java programs can be written and executed in two ways:

Stand-alone application (A Java Swing Application)
Applet which runs on a web browser (Example: Internet Explorer)