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)

PATH and CLASSPATH in Java

PATH and CLASSPATH
The following are the general programming errors, which I think every beginning java programmer would come across. Here is a solution on how to solve the problems when running on a Microsoft Windows Machine.

1. ‘javac’ is not recognized as an internal or external command, operable program or batch file

When you get this error, you should conclude that your operating system cannot find the compiler (javac). To solve this error you need to set the PATH variable.

How to set the PATH Variable?

Firstly the PATH variable is set so that we can compile and execute programs from any directory without having to type the full path of the command. To set the PATH of jdk on your system (Windows XP), add the full path of the jdk\bin directory to the PATH variable. Set the PATH as follows on a Windows machine:

a. Click Start > Right Click “My Computer” and click on “Properties”
b. Click Advanced > Environment Variables.
c. Add the location of bin folder of JDK installation for PATH in User Variables and System Variables. A typical value for PATH is:

C:\jdk\bin (jdk

If there are already some entries in the PATH variable then you must add a semicolon and then add the above value (Version being replaced with the version of JDK). The new path takes effect in each new command prompt window you open after setting the PATH variable.

Getting Started with Java :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)

Method Overriding in Java with Example

Description :
Method overriding in java means a subclass method overriding a super class method. Superclass method should be non-static. Subclass uses extends keyword to extend the super class. In the example class B is is the sub class and class A is the super class. In overriding methods of both subclass and superclass possess same signatures. Overriding is used in modifying  the methods of the super class. In overriding  return types and constructor parameters of methods should match .

Another way of explanation:
In a class hierarchy, when a method in a subclass has the same name and type signature as a method in its superclass, then the method in the subclass is said to override the method in the superclass. When an overridden method is called from within a subclass, it will always refer to the version of that method defined by the subclass. The version of the method defined by the superclass will be hidden

Method Overriding is achieved when a subclass overrides non-static methods defined in the superclass, following which the new method implementation in the subclass that is executed.

The new method definition must have the same method signature (i.e., method name and parameters) and return type. Only parameter types and return type are chosen as criteria for matching method signature. So if a subclass has its method parameters as final it doesn’t really matter for method overriding scenarios as it still holds true. The new method definition cannot narrow the accessibility of the method, but it can widen it. The new method definition can only specify all or none, or a subset of the exception classes (including their subclasses) specified in the throws clause of the overridden method in the super class


// Method overriding.
class A {
int i, j;
A(int a, int b) {
i = a;
j = b;
}
// display i and j
void test() {
System.out.println("i and j: " + i + " " + j);
}
}

class B extends A {
int k;
B(int a, int b, int c) {
super(a, b);
k = c;
}
// display k – this overrides test() in A
void test() {
System.out.println("k: " + k);
}
}

class Override {
public static void main(String args[]) {
B subOb = new B(1, 2, 3);
subOb.test(); // this calls test() in B
}
}
The output produced by this program is testn here:

k: 3

When test( ) is invoked on an object of type B, the version of test( ) defined within B is used. That is, the versio n of test( ) inside B overrides the version declared in A. If you wish to access the superclass version of an overridden function, you can do so by using super. For example, in this version of B, the superclass version of test( ) is invoked within the subclass' version. This allows all instance variables to be displayed.

Function or Method Overloading in Java with Example

In java the concept of Polymorphism is achieved in two ways:

1. Method Overloading
2. Method Overriding 

In Java it is possible to define two or more methods within the same class that share the same name, as long as their parameter declarations are different.

Here we will discuss only the concept of Method Overloading:
// Demonstrate method overloading.
class MethodOverloadExample {
void overloadsample() {
System.out.println("overloadsample with void";
}
// Overload overloadsample for one integer parameter.
void overloadsample(int a) {
System.out.println("overloadsample with Integer value:  " + a);
}
// Overload overloadsample for two integer parameters.
void overloadsample(int a, int b) {
System.out.println("overloadsample with two  Integer  values" + a + " " + b);
}
// overload overloadsample for a double parameter
double overloadsample(double a) {
System.out.println("overloadsample with double value " + a);
return a*a;
}
}
class Overload {
public static void main(String args[]) {
MethodOverloadExample ob = new MethodOverloadExample();
double result;
// call all versions of overloadsample()
ob.overloadsample();
ob.overloadsample(10);
ob.overloadsample(10, 20);
result = ob.overloadsample(123.2);
System.out.println("Result of ob.overloadsample(123.2): " + result);

}
}

As you can see, overloadsample( ) is overloaded four times. The first version takes no parameters, the second takes one integer parameter, the third takes two integer parameters, and the fourth takes one double parameter. The fact that the fourth version of overloadsample( ) also returns a value is of no consequence relative to overloading, since return types do not play a role in overload resolution.

When an overloaded method is called, Java looks for a match between the arguments used to call the method and the method's parameters. However, this match need not always be exact. In some cases Java's automatic type conversions can play a role in overload resolution. For example, consider the following program:

Forward declaration in Oracle Functions with Example

Example of forward declaration in package :

CREATE OR REPLACE PACKAGE Functions_exmple_package
AS
PROCEDURE create_drop_seq
( p_CountryCode IN country.country_code TYPE
p_state IN p_type.p_type TYPE);

END Functions_exmple_package;
/

CREATE OR REPLACE PACKAGE BODY Functions_exmple_package
AS
/*Forward declaration for procedure as this is private to package and defined later*/
PROCEDURE create_drop_seq
( p_CountryCode IN country.country_code TYPE
p_state IN p_type.p_type TYPE
);

PROCEDURE create_drop_seq
( p_CountryCode IN country.country_code TYPE
p_state IN p_type.p_type TYPE)
AS
BEGIN
create_drop_seq(p_CountryCode cur_LegVeh);
/*procedure is called here only because it is forward declared (not defined till now)*/
END create_drop_seq;

PROCEDURE create_drop_seq ( p_CountryCode IN
country.country_code TYPE
p_state IN p_type.p_type TYPE)
AS
BEGIN
............................/*Procedure defined here onwards....*/
END create_drop_seq;

Cursors in Oracle Explanation with Example

A cursor is a handle (pointer) in memory for a DML operation (Select Update).

There are mainly 2 types of cursors .
1) Implicit Cursor.
2) Explicit Cursor.

Implicit cursor: Oracle will implicitly creates an area for the DML operations. Programmer will not have control on implicit cursors. The only useful attribute on this implicit cursor is SQL ROWCOUNT it will give the number of rows affected by the recent DML operation.

The only Implicit cursor is SQL.

Explicit Cursor:
Explicit cursors are created by the programmer and programmer have control on it
Programmer can

1) Open
2) Fetch
3) Close

and do some manipulations on the values

Explicit Cursors are classified into

1) Normal cursor

2) Parameterized cursor

3) Cursor For Loops and

4) REF cursors

REF Cursors:

Normally when we create a normal cursor we cant change the select query associated to that query (the query which is given at the time of definition)

But using REF cursors we can change the cursor statement also.

These REF cursors are useful when we are sending data from one environment to another environment.

Oracle Cursors and Types of Cursors

The Cursor is a handle (name or a pointer) for the memory associated with a specific statement.  A cursor is basically an Area alocated by Oracle for executing the Sql Statements.  Oracle Uses an Implicit Cursor statement for a single row query and Explicit Cursor for a multi row query.

Types of Cursor :

I) Implicit
2) Expicit

Flow in Explicit Cursor
------------------------------
 I. Declaring a cursor :- Involves assign a name to cursor and associating a query with it..
 II. Open the cursor   :-  Executes the query and identify the result set.
 III. Fetch the cursor :-  gets the result set and Loops through to process them
 IV. Close the cursor :- Releases the cursor

Types of Cursors in Oracle

Cursors are of two types
1. Implicit Cursors: -  Whenever we execute sql statements oracle server assigns a work area called private sql area to store precessed infomation. The most recently used work are can be accessed using SQL%. In implicit cursors open, fetch , close operations are automatically performed by the server implicitly.

2.Explicit cursors:-   In explicit cursor  you can explicitly assign a name to process information stored in private sql areas.  This  process involves four steps

II) Explicit (Explicit cursor has three sub-types)

1)  Simple Cursor

2)  Parameterised Cursor

3) Ref Cursor

PRAGMA EXECPTION_INIT In Oracle

Pragma exception_init  Allow you to handle the Oracle predefined message by you'r own message. means you can instruct compiler toassociatethe specific message to oracle predefined message at compile time.This way you Improve the Readbility of your program,and handle it accoding to your own way.

It should be declare at the DECLARE section.

example

declare

salary number;

FOUND_NOTHING exception;

Pragma exception_init(FOUND_NOTHING ,100);

begin

select sal in to salaryfrom emp where ename ='ANURAG';

dbms_output.put_line(salary);

exception

WHEN FOUND_NOTHING THEN

dbms_output.put_line(SQLERRM);

end;

Difference between Union and Minus in Oracle

Let's consider the difference between Minus and Union using
following examples.

1.create TABLE A AND B With similar structure
2.insert records in Table A and B.
3.Keep some records identical(here 2 rows).
4.find out the difference betwn 2 looking into the output.


CREATE TABLE A(NAME VARCHAR2(30));
  INSERT INTO A VALUES('A');
   INSERT INTO A VALUES('B');
   INSERT INTO A VALUES('C');
   INSERT INTO A VALUES('D');
COMMIT;
   
CREATE TABLE B(NAME VARCHAR2(30));
INSERT INTO b VALUES('A')
INSERT INTO b VALUES('B')
INSERT INTO b VALUES('Y')
INSERT INTO b VALUES('X')
COMMIT;



1) SELECT * FROM A
 MINUS
 SELECT * FROM B

NAME                         
------------------------------
C                            
D                            
2 rows selected



 2)SELECT * FROM A
 UNION
 SELECT * FROM B


NAME                         
------------------------------
A                            
B                            
C                            
D                            
Y                            
x                            
6 rows selected

Shell script for oracle export/imp

#!/bin/ksh
# I assume that no other environment variables
# depend on ORACLE_SID
# I also assume that ORACLE_SID is used
# instead of TWO_TASKS
#
# Example:

ORACLE_SID=$1
PARFILE=$2

exp / ... parfile=$PARFILE

Sql script for exporting schema of an instances in Oracle

set serveroutput on
set trimspool on
set line 500
set head off
set feed off

spool exp_test.sql

declare
username varchar2(30);
dba_users varchar2(30);
user_id varchar2(30);
begin

select username,user_id
from dba_users;

dbms_output.put_line('spool exp.'||username||'.'||
to_char(sysdate, 'ddMonyy')||'.log');


-- Loop through schemas
for c1 in (select username us
from sys.dba_users)
loop
dbms_output.put_line(exp 'sys/||sys||'owner='||c1.us||' file=testdump.dmp log='||username||'.log');

end loop;
dbms_output.put_line('spool off');
end;
/

Unix Shell Script for Importing data using Oracle

Step 2 IMPORTING
---------------------

again create a Parameter file called c:\Import\parfile.txt

in the parfile give following

userid=raj/@
fromuser=scott
touser=raj
file=c:\Export\exportfile.dmp
--this is the export file to be imported
log=c:\Import\import.log
buffer=10000
statistics=none

then in command prompt give

imp parfile=c:\import\parfile.txt

this will start
Import: Release 9.2.0.1.0 - Production on Wed Feb 27 17:20:51 2002
(c) Copyright 2002 Oracle Corporation. All rights reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
Export file created byblah blah

Unix Shell Script for Exporting data using Oracle

step 1 :EXPORTING

Get an export dump of your scott schema
I usually perform an export using a parameter file

for that you need to create a parameter file save it in any location

Before you perform an export make sure you have calculated size of your scott schema and see if you have enogh space left in you local hard disk for the dump file


eg i created a paramter file c:\Export\Parfile.txt

in the Parfile.txt give the following

userid=scott/password@databasename
owner=scott
file=c:\Export\exportfile.dmp
log=c:\Export\exportlog.log
buffer=10000

then in command prompt give

exp parfile=c:\Export\Parfile.txt

this will start a series on operation
you will see various messages like
Export: Release 9.2.0.1.0 - Production on Wed Feb 27 16:52:15 2002
(c) Copyright 2002 Oracle Corporation. All rights reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
exporting table abc..
exporting table someother etc

once it is over an export file has been created in c:\Export\ by name exportfile.dmp

now you are ready for importing

Oracle/PLSQL: To_Char Function | Oracle Functions Tutorial

In Oracle/PLSQL, the to_char function converts a number or date to a string.

The syntax for the to_char function is:

to_char( value, [ format_mask ], [ nls_language ] )

value can either be a number or date that will be converted to a string.

format_mask is optional. This is the format that will be used to convert value to a string.

nls_language is optional. This is the nls language used to convert value to a string.



Applies To:

Oracle 8i, Oracle 9i, Oracle 10g, Oracle 11g


Examples - Numbers

The following are number examples for the to_char function.

to_char(1210.73, '9999.9') would return '1210.7'
to_char(1210.73, '9,999.99') would return '1,210.73'
to_char(1210.73, '$9,999.00') would return '$1,210.73'
to_char(21, '000099') would return '000021'

JVM crash on a Red Hat machine ( Java 1.4.2 applicationwith ) with a SIGSEGV error. -Xms is 64M and -Xmx is 128M

I'm running a Java 1.4.2 application on a Red Hat machine that causes the JVM to crash with a SIGSEGV error. -Xms is 64M and -Xmx is 128M. I first thought that the perm gen size was too low since it always has a high percentage (98/99%) when the JVM crashes. After reading more about the perm gen, though, I'm not so sure. Am I looking down the right path by thinking it may be the perm gen size being too low or is the percentage so high just because the JVM will resize it dynamically? When it crashes the perm gen's total size is around 10/11 MB and its usually 99% full.

#
# An unexpected error has been detected by HotSpot Virtual Machine:
#
#  SIGSEGV (0xb) at pc=0xb7c70b4c, pid=5342, tid=2810452880
#
# Java VM: Java HotSpot(TM) Client VM (1.4.2_16-b05 mixed mode)
# Problematic frame:
# V  [libjvm.so+0x196b4c]
#


Heap
 def new generation   total 4608K, used 3372K [0xa7900000, 0xa7e00000, 0xa82d0000)
  eden space 4096K,  78% used [0xa7900000, 0xa7c27780, 0xa7d00000)
  from space 512K,  27% used [0xa7d80000, 0xa7da3ad8, 0xa7e00000)
  to   space 512K,   0% used [0xa7d00000, 0xa7d00000, 0xa7d80000)
 tenured generation   total 60544K, used 21376K [0xa82d0000, 0xabdf0000, 0xaf900000)
   the space 60544K,  35% used [0xa82d0000, 0xa97b0210, 0xa97b0400, 0xabdf0000)
 compacting perm gen  total 11264K, used 11159K [0xaf900000, 0xb0400000, 0xb3900000)
   the space 11264K,  99% used [0xaf900000, 0xb03e5c30, 0xb03e5e00, 0xb0400000)
  
  
   ---------------  S Y S T E M  ---------------

OS:Red Hat Enterprise Linux Client release 5 (Tikanga)

uname:Linux 2.6.18-8.1.8.el5 #1 SMP Mon Jun 25 17:06:19 EDT 2007 i686
libc:glibc 2.5 NPTL 2.5
rlimit: STACK 10240k, CORE 0k, NPROC 16370, NOFILE 1024, AS infinity
load average:0.00 503...

CPU:total 2 family 15, cmov, cx8, fxsr, mmx, sse, sse2

Memory: 4k page, physical 1034572k(314260k free), swap 2096472k(2096472k free)

vm_info: Java HotSpot(TM) Client VM (1.4.2_16-b05) for linux-x86, built on Sep 17 2007 00:34:43 by unknown with unknown compiler