Amazon

Sunday, November 28, 2010

MQ Manager Configuration Script

1.Create a queue Manager
crtmqm MQIPH

2.Start the Queue Manager
strmqm MQIPH

3.Start the MQ browser to create the queues
runmqsc MQIPH

4. Define Dead letter queue
DEFINE QLOCAL('Q.MQIPH.DLQ') DEFPSIST(YES)
ALTER QMGR DEADQ('Q.MQIPH.DLQ') 

5. Define Server Connection Channel
DEFINE CHANNEL(MQIPH.SRV.CHANNEL) CHLTYPE(SVRCONN) TRPTYPE(TCP)

6. Define q local
DEFINE QLOCAL('Q.MQIPH2_TO_MQIPH') DEFPSIST(YES)

7. Define Xmitq
DEFINE QLOCAL('MQIPH.XMITQ') +
 USAGE(XMITQ)

8. Define q Remote
DEFINE QREMOTE('RQ.MQIPH_TO_MQIPH2') +
    RNAME('Q.MQIPH_TO_MQIPH2') +
    RQMNAME('MQIPH2') XMITQ('MQIPH.XMITQ') DEFPSIST(YES) 

9. Define sender channel
DEFINE CHL ('MQIPH_TO_MQIPH2') +
 CHLTYPE(SDR) TRPTYPE(TCP) +
 CONNAME('172.16.2.24(1414)') +
 XMITQ('MQIPH.XMITQ') +
 DISCINT (0)

10. Define receiver channel
DEFINE CHL ('MQIPH2_TO_MQIPH') +
 CHLTYPE(RCVR) TRPTYPE(TCP)

11.Create a queue Manager
crtmqm MQIPH2

12.Start the Queue Manager
strmqm MQIPH2

13.Start the MQ browser to create the queues
runmqsc MQIPH2

14. Define Server Connection Channel
DEFINE CHANNEL(MQIPH2.SRV.CHANNEL) CHLTYPE(SVRCONN) TRPTYPE(TCP)

15. Define Dead letter queue
DEFINE QLOCAL('Q.MQIPH2.DLQ') DEFPSIST(YES)
ALTER QMGR DEADQ('Q.MQIPH2.DLQ')

16. Define q local
DEFINE QLOCAL('Q.MQIPH_TO_MQIPH2') DEFPSIST(YES)

17. Define xmitq local
DEFINE QLOCAL('MQIPH2.XMITQ') +
USAGE(XMITQ)

18. Define q Remote
DEFINE QREMOTE('RQ.MQIPH2_TO_MQIPH') +
    RNAME('Q.MQIPH2_TO_MQIPH') +
    RQMNAME('MQIPH') XMITQ('MQIPH2.XMITQ') DEFPSIST(YES)


19. Define sender channel
DEFINE CHL ('MQIPH2_TO_MQIPH') +
 CHLTYPE(SDR) TRPTYPE(TCP) +
 CONNAME('172.16.2.24(1415)') +
 XMITQ('MQIPH2.XMITQ') +
 DISCINT (0)

20. Define Receiver channel
DEFINE CHL ('MQIPH_TO_MQIPH2') +
 CHLTYPE(RCVR) TRPTYPE(TCP)

21.Start the Senders Listener Port on 1414
runmqlsr -t tcp -m MQIPH2 -p 1414 &

22.Start the Senders Listener Port on 1415
runmqlsr -t tcp -m MQIPH -p 1415 &

23.Start the Sender Channel
START CHANNEL (MQIPH_TO_MQIPH2)

24.Start the Channel
START CHANNEL (MQIPH2_TO_MQIPH)

Saturday, November 20, 2010

Calling Unix Shell Script from Java Program

Requirement : Calling a Unix Shell Script from Java program and returning result to the Java program from Unix Shell Script.

UNIX Code : 

1 #!/bin/bash
3 function myscript(){
4 set a = 10
5 echo ID from JSP is $id
6 echo 'i m here babe' >> /usr12/SPHI/SIR03174/sanjeev/a.txt
7 ls -lrt
8 return 122
9 }
10
11 myscript

Line 1: Most important Line the shell script. If this is missed Java module won't be able to invoke the Shell Script.
Lin 3: Name of the function, which need to be invoked by shell script at last (see line 11).
Line 4: Create a variable.
Line 5: The env variable id (expressed as $id) will be set in Java module and sent to this function while calling shell script.
Line 11: calling the Unix Function at the end of script, otherwise the code will not be invoked.

JSP Code : This will call the above Unix Shell script, pass a parameter to shell script and read the result from Unix script.


1   <%--
2       Document   : proc.jsp
3       Created on : Nov 1, 2010, 2:48:14 PM
4       Author     : sanjeev pandey
5   --%>
6
7   <%@page contentType="text/html" pageEncoding="UTF-8"%>
8
9   <%@page import="java.io.BufferedReader"%>
10  <%@page import="java.io.File"%>
11  <%@page import="java.io.InputStream"%>
12  <%@page import="java.io.InputStreamReader"%>
13
14
15  <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
16     "http://www.w3.org/TR/html4/loose.dtd">
17
18  <html>
19      <head>
20          <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
21          <title>JSP Page</title>
22      </head>
23      <body>
24          <h1>Hello World!</h1>
25           <%
26   String [] env = {"id=pheonix1"};
27              Runtime rt = Runtime.getRuntime();
28              out.println("Runtime : "+rt);
29
30              Process p = rt.exec("/usr/scpt.sh", env);
31              out.println("<br><br>Process : "+p);                                        
32
33              InputStream is =  p.getInputStream();
34              out.println("<br><br>InputStream : "+is);
35
36              BufferedReader br = new BufferedReader(new InputStreamReader(is));
37              out.println("<br><br>BufferedReader : "+br);
38                                                                                            
39              String line = "";
40              out.println("<br><br>line is space : "+line);
41                                                                                            
42              while(line != null) {
43                  line = br.readLine();
44                  out.println("<br><br>Reading inputstream : "+ line);
45              }
46          %>
47      </body>
48  </html>                                                                                          

Line 26: Creating String Variable to be passed to shell script. In shell script its treated as environment variable.
Line 30: Passing the Variable embedded in 'env' to shell script before executing it from java.
Line 33: Getting the inputstream, created after execution of the Unix script. This input stream contains all the things which are printed on unix when the shell script is executed.
Line 42-45: Printing the unix output on JSP.


Sunday, May 30, 2010

Oracle - Using NULL statement

The NULL statement does nothing, and passes control to the next statement.

EXCEPTION
WHEN ZERO_DIVIDE THEN
ROLLBACK;
WHEN VALUE_ERROR THEN
INSERT INTO errors VALUES ...
COMMIT;
WHEN OTHERS THEN
NULL;
END;

The NULL statement is a handy way to create placeholders and stub procedures. In the following example, the NULL statement lets you compile this procedure, then fill in the real body later:

PROCEDURE debit_account (acct_id INTEGER, amount REAL) IS
BEGIN
NULL;
END debit_account;

Saturday, May 29, 2010

Oracle - Using Lables

<<main>>
DECLARE
ctr constant INTEGER := 11;
BEGIN
FOR ctr IN 1..25 LOOP -- ctr is declared locally in FOR loop
IF main.ctr > 10 THEN -- Using main label it refers to global variable
dbms_output.put_line('hello');
END IF;
END LOOP;
END main;

Transient - Can they be used for optimizaton?

Control serialization in remote EJBs

When you decide to write your code for distributed /remote object you need to carefully choose what method parameters you want to send over the network,for example when you pass an object like this :

remoteObject.setPersonInfo(person); // call remote object by passing object

here, not only the PersonInfo object will be serialized and sent over network but all the total PersonInfo object graph (variables and it's super class variables except transient variables) will also be sent through network because of default behavior. You might want to send only PersonInfo object only but not total object graph.

To avoid this problem, use 'transient' key word for the attributes that need not be sent over the network.

Monday, May 17, 2010

Oracle - Using Ref Cursors

Ref Cursors are variables which can point to any cursor dynamically, or can be passed as parameter in some procedure, function or java program, etc.

The PROC below takes a REF CURSOR as a parameter and iterates to print the data contained in the cursor.

create or replace procedure emp_proc_ref_cur (c_emp SYS_REFCURSOR ) is
type rec_emp is record
(
name varchar2(20),
sal number(6)
);
er rec_emp;
begin
open c_emp;
loop
fetch c_emp into er;
exit when c_emp%notfound;
dbms_output.put_line(er.name || ' - ' || er.sal);
end loop;
end;
/


The PROC below creates a REF CURSOR and OPENS it on EMP table, and passes that REF CURSOR as parameter to the PROC above.

create or replace procedure emp_rec_pro is
type emp_ref is REF CURSOR;
emp_cur emp_ref;
BEGIN
open emp_cur for select ename name, sal from emp where sal>250;
emp_proc_ref_cur (emp_cur);
end;
/

Sunday, May 16, 2010

Oracle PL/SQL Notes - 3

===================
Exception Handling
===================
PL/SQL Exception message consists of three parts.
1) Type of Exception
2) An Error Code
3) A message

By Handling the exceptions we can ensure a PL/SQL block does not exit abruptly.

The General Syntax for coding the exception section

DECLARE
Declaration section
BEGIN
Exception section
EXCEPTION
WHEN ex_name1 THEN
-Error handling statements
WHEN ex_name2 THEN
-Error handling statements
WHEN Others THEN
-Error handling statements
END;

General PL/SQL statments can be used in the Exception Block.

If there are nested PL/SQL blocks like this.

DELCARE
Declaration section
BEGIN
DECLARE
Declaration section
BEGIN
Execution section
EXCEPTION
Exception section
END;
EXCEPTION
Exception section
END;

In the above case, if the exception is raised in the inner block it should be handled in the exception block of the inner PL/SQL block else the control moves to the Exception block of the next upper PL/SQL Block.

There are 3 types of Exceptions.
a) Named System Exceptions - System exceptions are automatically raised by Oracle, when a program violates a RDBMS rule. There are some system exceptions which are raised frequently, so they are pre-defined and given a name in Oracle which are known as Named System Exceptions. Named system exceptions are:
1) Not Declared explicitly,
2) Raised implicitly when a predefined Oracle error occurs,
3) caught by referencing the standard name within an exception-handling routine.
CURSOR_ALREADY_OPEN (ORA-06511) When you open a cursor that is already open.
INVALID_CURSOR (ORA-01001) When you perform an invalid operation on a cursor like closing a cursor, fetch data from a cursor that is not opened.
NO_DATA_FOUND (ORA-01403) When a SELECT...INTO clause does not return any row from a table.
TOO_MANY_ROWS (ORA-01422) When you SELECT or fetch more than one row into a record or variable.
ZERO_DIVIDE (ORA-01476) When you attempt to divide a number by zero.
BEGIN
Execution section
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line ('A SELECT...INTO did not return any row.');
END;

b) Unnamed System Exceptions - Those system exception for which oracle does not provide a name is known as unamed system exception. These exception do not occur frequently. These Exceptions have a code and an associated message.

There are two ways to handle unnamed sysyem exceptions:
1. By using the WHEN OTHERS exception handler, or
2. By associating the exception code to a name and using it as a named exception. We can assign a name to unnamed system exceptions using a Pragma called EXCEPTION_INIT. EXCEPTION_INIT will associate a predefined Oracle error number to a programmer_defined exception name.

CREATE OR REPLACE PROCEDURE EXCEPTION_PROC
IS
UN_NAMED_EXCEPTION EXCEPTION;
PRAGMA
EXCEPTION_INIT (UN_NAMED_EXCEPTION, -00022);
BEGIN
RAISE UN_NAMED_EXCEPTION;
EXCEPTION
WHEN UN_NAMED_EXCEPTION THEN
dbms_output.put_line( '' || 'UN_NAMED_EXCEPTION');
END;

c) User-defined Exceptions
Apart from sytem exceptions we can explicity define exceptions based on business rules. These are known as user-defined exceptions.

Steps to be followed to use user-defined exceptions:
A)They should be explicitly declared in the declaration section.
B)They should be explicitly raised in the Execution Section.
C)They should be handled by referencing the user-defined exception name in the exception section.

RAISE_APPLICATION_ERROR ( ) - a built-in procedure in oracle which is used to display the user-defined error messages along with the error number whose range is in between -20000 and -20999. Whenever a message is displayed using RAISE_APPLICATION_ERROR, all previous transactions which are not committed within the PL/SQL Block are rolled back automatically. RAISE_APPLICATION_ERROR raises an exception but does not handle it.

RAISE_APPLICATION_ERROR is used for the following reasons,
a) to create a unique id for an user-defined exception.
b) to make the user-defined exception look like an Oracle error.

RAISE_APPLICATION_ERROR (error_number, error_message);

• The Error number must be between -20000 and -20999
• The Error_message is the message you want to display when the error occurs.

Steps to be folowed to use RAISE_APPLICATION_ERROR procedure:
1. Declare a user-defined exception in the declaration section.
2. Raise the user-defined exception based on a specific business rule in the execution section.
3. Finally, catch the exception and link the exception to a user-defined error number in RAISE_APPLICATION_ERROR.

CREATE OR REPLACE PROCEDURE EXCEPTION_PROC_RAE
IS
UN_NAMED_EXCEPTION EXCEPTION;
BEGIN
RAISE UN_NAMED_EXCEPTION;
EXCEPTION
WHEN UN_NAMED_EXCEPTION THEN
raise_application_error(-20000, 'Raising un named exception');
END;

Output :-
SQL> exec EXCEPTION_PROC_RAE
BEGIN EXCEPTION_PROC_RAE; END;

*
ERROR at line 1:
ORA-20000: Raising un named exception
ORA-06512: at "IPHSIT.EXCEPTION_PROC_RAE", line 11
ORA-06512: at line 1

==========
Trigger
==========
A trigger is triggered automatically when an associated DML statement is executed.

CREATE [OR REPLACE ] TRIGGER trigger_name
{BEFORE | AFTER | INSTEAD OF }
{INSERT [OR] | UPDATE [OR] | DELETE}
[OF col_name]
ON table_name
[REFERENCING OLD AS o NEW AS n]
[FOR EACH ROW]
WHEN (condition
BEGIN
--- sql statements
END;

a) {BEFORE | AFTER | INSTEAD OF } - This clause indicates at what time should the trigger get fired. i.e for example: before or after updating a table. INSTEAD OF is used to create a trigger on a view. before and after cannot be used to create a trigger on a view.
b) {INSERT [OR] | UPDATE [OR] | DELETE} - This clause determines the triggering event. More than one triggering events can be used together separated by OR keyword. The trigger gets fired at all the specified triggering event.
c) [OF col_name] - This clause is used with update triggers. This clause is used when you want to trigger an event only when a specific column is updated.
d) [ON table_name] - This clause identifies the name of the table or view to which the trigger is associated.
e) REFERENCING OLD AS o NEW AS n] - This clause is used to reference the old and new values of the data being changed. By default, you reference the values as :old.column_name or :new.column_name. The reference names can also be changed from old (or new) to any other user-defined name. You cannot reference old values when inserting a record, or new values when deleting a record, because they do not exist.
f) [FOR EACH ROW] - This clause is used to determine whether a trigger must fire when each row gets affected ( i.e. a Row Level Trigger) or just once when the entire sql statement is executed(i.e.statement level Trigger).
g) WHEN (condition) - This clause is valid only for row level triggers. The trigger is fired only for rows that satisfy the condition specified.

Thursday, May 13, 2010

Oracle PL/SQL Notes - 2

=============
Procedure
=============
General Syntax to create a procedure is:
CREATE [OR REPLACE] PROCEDURE proc_name [list of parameters]
IS
Declaration section
BEGIN
Execution section
EXCEPTION
Exception section
END;

IS - marks the beginning of the body of the procedure and is similar to DECLARE in anonymous PL/SQL Blocks. The code between IS and BEGIN forms the Declaration section.

create or replace procedure emp_proc
is
cursor emp_cur is
select empno, ename from emp where sal>1500;
emp_rec emp_cur%rowtype;
begin
open emp_cur;
loop
fetch emp_cur into emp_rec;
exit when emp_cur%notfound;
dbms_output.put_line(emp_rec.empno|| ' :: ' ||emp_rec.ename);
end loop;
end;
There are two ways to execute a procedure.

1) From the SQL prompt.
EXECUTE [or EXEC] procedure_name;

2) Within another procedure – simply use the procedure name.
procedure_name;
=================
PL/SQL Functions
=================
A function is a named PL/SQL Block which is similar to a procedure. The major difference between a procedure and a function is, a function must always return a value, but a procedure may or may not return a value.

The General Syntax to create a function is:
CREATE [OR REPLACE] FUNCTION function_name [parameters]
RETURN return_datatype;
IS
Declaration_section
BEGIN
Execution_section
Return return_variable;
EXCEPTION
exception section
Return return_variable;
END;
1) Return Type: The header section defines the return type of the function. The return datatype can be any of the oracle datatype like varchar, number etc.
2) The execution and exception section both should return a value which is of the datatype defined in the header section.
A function can be executed in the following ways.
1) Since a function returns a value we can assign it to a variable.
employee_name := employer_details_func;
If ‘employee_name’ is of datatype varchar we can store the name of the employee by assigning the return type of the function to it.

2) As a part of a SELECT statement
SELECT employer_details_func FROM dual;

3) In a PL/SQL Statements like,
dbms_output.put_line(employer_details_func);
This line displays the value returned by the function.


create or replace function myfunction
return varchar2 is
divide_by_zero_error exception;
begin
raise divide_by_zero_error;
return 'hello world';
exception
when divide_by_zero_error then
return 'zero error';
end;


SQL> select myfunction from emp;

MYFUNCTION
-------------------------------------
zero error
zero error
zero error

======================================
Parameters in Procedure and Functions
======================================
IN - CREATE [OR REPLACE] PROCEDURE proc_name ( param1 IN datatype)

This type of parameter is a read only parameter. We can assign the value of IN type parameter to a variable or use it in a query, but we cannot change its value inside the procedure.
OUT - CREATE [OR REPLACE] PROCEDURE proc2 (param_name OUT datatype)

This is a write-only parameter i.e, we cannot pass values to OUT paramters while executing the stored procedure, but we can assign values to OUT parameter inside the stored procedure and the calling program can recieve this output value.
IN OUT - CREATE [OR REPLACE] PROCEDURE proc3 (param_name IN OUT datatype)

By using IN OUT parameter we can pass values into a parameter and return a value to the calling program using the same parameter. But this is possible only if the value passed to the procedure and output value have a same datatype. This parameter is used if the value of the parameter will be changed in the procedure.

If a parameter is not explicitly defined a parameter type, then by default it is an IN type parameter.

Oracle PL/SQL Notes - 1

Reference Website : http://plsql-tutorial.com/plsql-constants.htm

PL/SQL Syntax

DECLARE
Variable declaration
BEGIN
Program Execution
EXCEPTION
Exception handling
END;

Placeholders : Number (n,m) , Char (n) , Varchar2 (n) , Date , Long , Long raw,
Raw, Blob, Clob, Nclob, Bfile

The General Syntax to declare a variable is:

variable_name datatype [NOT NULL := value ];

For example, if you want to store the current salary of an employee, you can use a variable.
DECLARE
salary number (6);
dept varchar2(10) NOT NULL := “HR Dept”;

1) We can directly assign values to variables.
The General Syntax is:

variable_name:= value;

2) We can assign values to variables directly from the database columns by using a SELECT..
INTO statement. The General Syntax is:

SELECT column_name
INTO variable_name
FROM table_name
[WHERE condition];

Example: The below program will get the salary of an employee with id '1116' and display it on the screen.

DECLARE
var_salary number(6);
var_emp_id number(6) = 1116;
BEGIN
SELECT salary
INTO var_salary
FROM employee
WHERE emp_id = var_emp_id;

dbms_output.put_line(var_salary);
dbms_output.put_line('The employee ' || var_emp_id || ' has salary ' || var_salary);
END;
/


declare
salary number(6,2);
name varchar2(40);
begin
select e.sal, e.ename into salary, name from emp e where e.empno='7934';
dbms_output.put_line('salary is '|| salary || ' name is ' || name);
end;

====================
Scope of Variables
====================

PL/SQL allows the nesting of Blocks within Blocks i.e, the Execution section of an outer block can contain inner blocks. Therefore, a variable which is accessible to an outer Block is also accessible to all nested inner Blocks.

1 declare
2 salary number(6,2);
3 name varchar2(40);
4 deptname varchar (40);
5 begin
6 select e.sal, e.ename into salary, name from emp e where e.empno='7934';
7 dbms_output.put_line('salary is '|| salary || ' name is ' || name);
8 begin
9 select dname into deptname from dept where deptno='10';
10 dbms_output.put_line('dept is '|| deptname);
11 end;
12* end;
SQL> /
salary is 1300 name is MILLER
dept is ACCOUNTING

PL/SQL procedure successfully completed.

==========
Constant
==========
The General Syntax to declare a constant is:
constant_name CONSTANT datatype := VALUE;

DECLARE
salary_increase CONSTANT number (3) := 10;

You must assign a value to a constant at the time you declare it. If you do not assign a value to a constant while declaring it and try to assign a value in the execution section, you will get a error.

==============
PL/SQL Records
==============

Records are composite datatypes, which means it is a combination of different scalar datatypes like char, varchar, number etc. Each scalar data types in the record holds a value. A record can be visualized as a row of data. It can contain all the contents of a row.

TYPE record_type_name IS RECORD
(first_col_name column_datatype, second_col_name column_datatype);

col_name table_name.column_name%type;

The datatype of the column is dynamically applied to the field. This method is useful when you are altering the column specification of the table, because you do not need to change the code again.

record_name record_type_name;

can use also %type to declare variables and constants.

Declaring user defined type - employee_type is user defined type

DECLARE
TYPE employee_type IS RECORD
(employee_id number(5),
employee_first_name varchar2(25),
employee_last_name employee.last_name%type,
employee_dept employee.dept%type);
employee_salary employee.salary%type;
employee_rec employee_type;

If all the fields of a record are based on the columns of a table, we can declare the record as follows:

record_name table_name%ROWTYPE;

DECLARE
employee_rec employee%ROWTYPE;

declare
emp_rec emp%rowtype;
begin
select * into emp_rec from emp where empno='7369';
dbms_output.put_line(emp_rec.empno);
end;


================================
Conditional Statements in PL/SQL
================================

1)
IF condition THEN
statement 1;
ELSE
statement 2;
END IF;
2)
IF condition 1 THEN
statement 1;
statement 2;
ELSIF condtion2 THEN
statement 3;
ELSE
statement 4;
END IF

3)
IF condition 1 THEN
statement 1;
statement 2;
ELSIF condtion2 THEN
statement 3;
ELSE
statement 4;
END IF;
4)
IF condition1 THEN
ELSE
IF condition2 THEN
statement1;
END IF;
ELSIF condition3 THEN
statement2;
END IF;

==============================
Iterative Statements in PL/SQL
==============================

1)Simple Loop - A Simple Loop is used when a set of statements is to be executed at least once before the loop terminates

LOOP
statements;
EXIT; //only exit cause the loop to execute only once
{or EXIT WHEN condition;} //loop will end when the condtion will meet
END LOOP;


2)While Loop - The condition is evaluated at the beginning of each iteration. The iteration continues until the condition becomes false.

WHILE
LOOP statements;
END LOOP;

3)For Loop - Iteration occurs between the start and end integer values given. The counter is always incremented by 1. The loop exits when the counter reaches the value of the end integer.

declare
begin
for counter in 1..10 loop
dbms_output.put_line(counter);
end loop;
end;

declare
begin
for counter in reverse 1..10 loop
dbms_output.put_line(counter);
end loop;
end;
a)val1 - Start integer value.
b)val2 - End integer value.
c)The counter variable is implicitly declared in the declaration section, so it's not necessary to declare it explicity.
d)The counter variable is incremented by 1 and does not need to be incremented explicitly.
e)EXIT WHEN statement and EXIT statements can be used in FOR loops but it's not done oftenly.

===========
CURSORS
===========

Cursor is a temporary work area is used to store the data retrieved from the database, and manipulate this data. A cursor can hold more than one row, but can process only one row at a time. The set of rows the cursor holds is called the active set.

Types of cursors in PL/SQL:
a)Implicit cursors - They are created when a SELECT statement that returns just one row is executed. They are also created when you execute DML statements like DELETE, INSERT, UPDATE and SELECT statements, implicit statements are created to process these statements. Attribute to find status of cursor :
%FOUND
%NOTFOUND
%ROWCOUNT and
%ISOPEN

DECLARE
var_rows number(5);
BEGIN
UPDATE employee SET sal = sal + 1000;

IF SQL%NOTFOUND THEN
dbms_output.put_line('None of the salaries where updated');
ELSIF SQL%FOUND THEN
var_rows := SQL%ROWCOUNT;
dbms_output.put_line('Salaries for ' || var_rows || 'employees are updated');
END IF;
END;

b)Explicit cursors - They must be created when you are executing a SELECT statement that returns more than one row. Even though the cursor stores multiple records, only one record can be processed at a time, which is called as current row. When you fetch a row the current row position moves to next row.

Both implicit and explicit cursors have the same functionality, but they differ in the way they are accessed.

An explicit cursor is defined in the declaration section of the PL/SQL Block. We can provide a suitable name for the cursor.

There are four steps in using an Explicit Cursor.
1)DECLARE the cursor in the declaration section.
2)OPEN the cursor in the Execution Section.
3)FETCH the data from cursor into PL/SQL variables or records in the Execution Section.
4)CLOSE the cursor in the Execution Section before you end the PL/SQL Block.

1 DECLARE
2 emp_rec employee%rowtype;
3 CURSOR emp_cur IS
4 SELECT *
5 FROM emp
6 WHERE sal > 2500;
7 empname varchar2 (10);
8 BEGIN
9 OPEN emp_cur;
10 loop
11 fetch emp_cur into emp_rec;
12 dbms_output.put_line('emp name is ' || emp_rec.ename);
13 exit when emp_cur%notfound;
14 end loop;
15 close emp_cur;
16* end;
SQL> /
emp name is JONES
emp name is BLAKE
emp name is SCOTT
emp name is KING
emp name is FORD
emp name is FORD

PL/SQL procedure successfully completed.
General Syntax for using FOR LOOP:

FOR record_name IN cusror_name
LOOP
process the row...
END LOOP;


==================
Stored Procedures
==================
A named PL/SQL block which performs one or more specific task. A procedure has a header and a body. The header consists of the name of the procedure and the parameters or variables passed to the procedure. The body consists or declaration section, execution section and
exception section similar to a general PL/SQL Block. A procedure is similar to an anonymous PL/SQL Block but it is named for repeated usage.

CREATE OR REPLACE PROCEDURE employer_details
IS
CURSOR emp_cur IS
SELECT ename, job, sal FROM emp;
emp_rec emp_cur%rowtype;
BEGIN
dbms_output.put_line('foring');
FOR emp_rec in emp_cur
LOOP
dbms_output.put_line(emp_rec.ename || ' ' ||emp_rec.job || ' ' ||emp_rec.sal);
END LOOP;
END;


Executing :: EXECUTE [or EXEC] procedure_name;

Begin
procedure_name;
end;

Wednesday, May 12, 2010

EJB 2.0 Chapter 02 The Client View

Home
javax.ejb.EJBHome
javax.ejb.EJBLocalHome

Local home has fewer methods than Remote Home.

Client View
javax.ejb.EJBObject
javax.ejb.EJBLocalObject

Remote Session Bean Client can remove a bean using the bean's home, but a local client cannot.

JNDI - Java naming and directory Interface.
API for accessing naming and directory services.
Organizes things in virtual directory tree. Each level of a tree is either another virtual directory (called context) or an object.

javax.naming.Context
^
|
javax.naming.InitialContext - is entry point in JNDI Tree.

Doing Lookup:
Context ic = new InitialContext();
Object o = ic.lookup("Advisor");

If the deployer assigned additional context to the bean, by naming if at deploy time "bar/Advisor", then the lookup code will change to

Object o = ic.lookup("bar/Advisor");
AdviceHome home = (AdviceHome)o;

Doing NARROW:
Naming forces the object returned from the JNDI lookup to be absolutely positively something that implements the home interface i.e. in place of above lookup code we should use

AdviceHome home = (AdviceHome)PortableRemoteObject.narrow(o, AdviceHome.class);

Narrow is used only when the home Interface is remote. The reason is connected to CORBA.

For EJB, the communication between the server and the client is based on RMI (both remote and local interfaces, in fact, do implements thejava.rmi.Remote interface).
The underlying protocol that it is used for the communication is IIOP (I think 1.2), that is part of CORBA standards. It is normally used to describe this communication system using the Java RMI over IIOP.

IIOP has not been designed for Java, but for generic languages, and this means that there are some limitations. Some languages, in fact, do not have the concept of casting.
Java RMI-IIOP provides a mechanism to narrow the the Object you have received from from your lookup, to the appropriate type. This is done through the
javax.rmi.PortableRemoteObject class and, more specifically, using the narrow() method.

Just a note: when you are using the new EJB 2.0 Local Client API, you should be able to do a direct/explicit cast from the looked up Object, to the interface you need.

javax.naming.InitialContext
java.rmi.RemoteException
javax.rmi.PortableRemoteObject
javax.ejb.CreateException

Casting Vs Narrowing
Casting is object polimorphism with a cast, the object doen't change, but the way you refer the object class changes. With narrowing, you must get actually a different object.

IIOP
  1. aWire protocol of CORBA.
  2. Can propagate both Transaction and Security information, and can't be sent with a non-IIOP remote method call, so IIOP lets container inter-operate with other servers, including one that isn't Java-based.
  3. All EJBs must be IIOP Compliant.
Home create() method must throw CreateException, RemoteException

Stateless create(): stateless bean can have only one no arg create() method, but stateful canhae many create methods (overloaded). The name of create method in stateful bean must begin with create(i.e. createAccount() etc).

Remote: This interface does not have any method.

Home interface

public interface EJBHome {
EJBMetaData getEJBMetaData() HomeHandle getHomeHandle() void remove(Handle handle) void remove(Object primaryKey)
}
public interface AdviceHome exetends EJBHome{
public void create();
}

Container implements the Home interface and matching sub-class.
AdviceHomeImpl - Class of actual remote object and
AdviceHomeImpl_stub - Class of Home object stub.

SessionBean object - getPrimaryKey()
RemoteException - If Remote
EJBException - If Local

EJBObject interface
Object getPrimaryKey()
getEJBHome() -> No need to do JNDI lookup if you have EJBObject.
getHandle()-> Serialize and use again.
remove()->Finished with bean.
isIdentical(Object o) -> To know if two references are of same bean.

Handle Interface
getEJBObject() -> Handle gives the EJBObject but its not the same object of the bean. It has to narrow to the RemoteObject class.

Handle h = this.restoreTheHandle();
Object o = h.getEJBObject();
Shopping cart = (Shopping)PortableRemoteObject.narrow(o, Shopping.class);

Client should have authorization to call methods on the bean. So if a handle is given to someone else, its not a security issue.

IsIdentical()
Stateless -> True if two Remote EJB objects from same home.
Stateful -> False for any two unique stubs even if from same bean.
Entity -> True for Stubs referring to two entities with same primary key.

equals() vs isIdentical()
equals() method compares two objects on the same Heap, whereas isIdentical() compares two Remote objects on the Server.

getEJBHome()
JNDI lookups are expensive. Some overhead is served while using this method.

Compiler Law and EJB Spec Law
No Stubs -> While local client view.
Handle -> Remote only.
Local -> No stub no handle. both related to Remote. Local can simply use Java reflection methods and getEJBMetadata.only required for Remote Client.

remove()->
Two remove in Remote Home
One Remove in RemoteObject
Local clients dont have Handle so local home dont have method remove(handle).

Only create() and remove () must declare exception in Local Client Interface.

RemoteException -> All remote methods can throw (Checked)
EJBException -> All Local Client interface methods can throw(Unchecked). Also used in local interface methods in place of RemoteException (which is used in Remote).
CreateException->Checked.
RemoveException->Checked.




EJB 2.0 Chapter 01 EJB Architecture

Client <-> Stub(Local to client, knows network communication) <-> Skeleton (knows network communication)<->Remote Object

Stub -> Remote object proxy at client side.
Skeleton -> Its optional, but its work must be done by something on server.

RMI promotes network transparency.

Skeleton
Accepts network (socket) connection from stub.
Figures out which method to call on which object.
Calls method on Remote Object.

java.rmi.RemoteException
Checked Exception.
Client has to handle or declare the exception.

Network Shippable Values must be one of these
Primitives.
Serializable objects.
An array or collection of primitives or serializable objects.
A Remote Object.

Method Call
Local means same heap/jvm. Java passes an object reference by value in case of local method call. And for Remote method call serializable copy of the actual object is passed.

When a Remote object is passed to/from a remote method, Java actually sends Remote object's stub.

Being Serializable
Collection implementation in J2Se are Serializable. Its a good practice to explicitly declare a class serializable.

Business Interface has business methods the client wants to call. Business method of a Remote object must follow three Rules
Must extend java.rmi.Remote.
Each method must declare a java.rmi.RemoteException.
Arguments and return values must be shippable on network.

Client -> Remote Business Interface -> Stub.

The Stub and the Remote Object, both implement Remote Business Interface.

EjbObject
The Remote object is the bean's bodyguard.
Implements the Remote Interface and takes Remote Calls.
When calls get to EjbObject the server jumps in with all the services like
Security - is client authorized to call the method
Transactions - is the call part of existing txn or new txn.
Persistence - does bean need to load info from DB before running the client's method.

RMI interface extends java.rmi.Remote.
EJB business interface extends javax.ejb.EJBObject.

What happens when EJB is deployed

Remote Interface
Bean does not implement the Remote Interface because the bean is never supposed to be a Remote Object i.e. we don't want stub of an actual bean i.e. client can't talk directly to EJB so that server will come between client and EJB to apply services.

Component Interface
Container writes the class which implement component interface. It also extends EjbObject. This can pretend as Ejb and respond to client calls i.e. calls from Stub. Its only job is to capture the request from client to bean. Its implementation is totally vendor dependent.

Bean
When a Bean is deployed, the container looks at DD, it generates classes implementing the Remote Component (EJBObject) and Remote Home Interface. And because they are Remote the container also creates Stubs, that know how to communicate back to the Remote Objects.

RMI - IIOP
Stubs should be RMI-IIOP compliant. Remote objects also follow same. Plain old RMI uses JRMP as its wire protocol. But IIOP lets Remote Objects interoperate through CORBA, this gives objects a chance to be accessed, for example by non-java client.

MDBs don't have homes because they don't have client view i.e. clients can't get reference to a MDB.

Home's main job is to hand out references to a bean's component interface. Each deployed bean has its own home, and that home is responsible for all bean instances of that type. e.g. if 2000 clients each want their own shopping cart bean references the one and only shopping cart Home will hand out all 2000 references.

Clients share the same home, may share the bean. If all the clients trying to access 'Fred Smith #420', they will each have their own stub, but all stubs will communicate with the same Remote Ejb Object. EJBObject is the body guard of actual bean.

Stateless Session beans are more scalable, because unlike stateful session beans every client get their own EJBObject, i.e. they don't share EJBObject i.e. same bean can serve multiple EJBObjects, just not at the same time. The bean comes out of the pool only when a client invokes a business method on the EJBObject stub.
But different pool for different stateless session beans.

Tuesday, May 11, 2010

Oracle Views

Views with force option

Views can be created with error and later it can be compiled. For example creating a view on table which does not exist.

CREATE VIEW TEST_VIEW AS SELECT C1, C2 FROM TEST_TABLE;

As test_table does not exist, it will not create view and will give error also.

CREATE FORCE VIEW TEST_VIEW AS SELECT C1, C2 FROM TEST_TABLE;

This will create view with warning.

Later create test_table and compile the view. Now data can be selected from the view.

Read Only

CREATE VIEW ALL_LOCATIONS AS SELECT COUNTRY_ID, COUNTRY_NAME FROM LOCATIONS NATURAL JOIN COUNTRIES WITH READ ONLY;

WITH CHECK OPTION

CREATE OR REPLACE VIEW DEPT_ABOVE_250 AS
SELECT DEPT_ID DID, DEPT_NAME FROM DEPT WHERE DEPT_ID>250;

Now insert a row in view

INSERT INTO DEPT_ABOVE_250 VALUES(199, 'Temporary Dept');

This will insert in DEPT table.

SELECT * FORM DEPT WHERE DEPT_ID = 199;

this will give one row. Although the view is defined with a WHERE clause to verify DEPT_ID > 250, oracle did not enforce the condition when a new row was inserted. This can be inforced by using WITH CHECK OPTION clause.

CREATE OR REPLACE VIEW DEPT_ABOVE_250 AS
SELECT DEPT_ID DID, DEPT_NAME FROM DEPT WHERE DEPT_ID>250
WITH CHECK OPTION;

Now the insert statement for view will not work.


Join Views
View with more than one base table in the top-level from clause. An updatable join view can be used to update the base tables through the view.
A table in the join-view is key preserved, if the primary and unique keys of the table are unique in the view's resultset. A key-preserved table can be updated by the join view.

CREATE OR REPLACE VIEW COUNTRY_REGION AS
SELECT a.country_id, a.country_name, a.region_id, b.region_name from countries a, regions b
where a.region_id = b.region_id;

If the view is defined with the WITH CHECK OPTION, the columns joining the tables in join view cannot be updated.

Inline Views
Inline views are subqueries used in the FROM clause. These subqueries can have an ORDER BY clause.

Oracle Synonyms

A synonym is an alias for another database object (table, view, sequence, procedure, function or package in local database).
Public Synonym - Available to all users.
CREATE PUBLIC SYNONYM employees FOR hr.employees;
Used to identify well known objects, e.g. data dictionary views (USER_OBJECTS etc)

Private Synonym - Available to the owner or the account to whom owner gives privileges.
CREATE SYNONYM employees FOR hr.employees;
Are useful when the table is renamed and old and new names are needed.


Synonym don't get invalid if the object to they point is dropped. Synonym can be created for any object which does not exist or the owner does not have privileges.

Removing Synonym :
Public: DROP PUBLIC SYNONYM employees;
Private: DROP SYNONYM employees;

References to an object is looked up in following order:
  • An object owned by current user.
  • A private synonym owned by current user.
  • A public synonym.

Oracle Indexes

Indexes (B-Tree and Bitmap) can offer improved performance in obtaining specific rows over the default full table scan. Oracle retrieves rows in one of the following ways :
  • By RowID - Mapping column data to ROWIDs for the columns of interest.
  • By Full Table Scan
Indexes may improve performance of SELECT, UPDATE or DELETE operations. Indexes may degrade performance of data change (DML), because indexes must be modified in addition to the table.

B-Tree indexes
  1. Default and most common index type.
  2. Can be unique or non-unique and either simple (one column) or concatenated (multi cols).
  3. Provides best performance on high cardinality (many distinct values) columns.
  4. Offer methods to retrieve small number of interesting rows.
  5. Can be used if any combination of the leading columns of the index are used in the SQL. For ex - The OE.INVENTORIES table has the index INVENTORY_PK on the PRODUCT_ID (leading) and WAREHOUSE_ID columns. We can use this INVENTORY_PK index with the following query:
SELECT SUM(quantity_on_hand) FROM oe.inventories WHERE
PRODUCT_ID = 3191 and warehouse_id = 3;
Following will also use index as PRODUCT_ID is leading column in INDEX.

SELECT SUM(quantity_on_hand) FROM oe.inventories WHERE
PRODUCT_ID = 3191

But below will not use index as WAREHOUS_ID is not leading column
SELECT SUM(quantity_on_hand) FROM oe.inventories WHERE
warehouse_id = 3;

Bitmap Indexes
  1. Primarily used for decision-support systems or static data, because they do not support row level locking.
  2. Like B-Tree indexes they can also be simple or concatenated.
  3. Best used for low or medium cardinality columns.
  4. Index is constructed by storing the bit-maps in the leaf nodes of a B-Tree structure. The B-Tree makes it easy to find the bitmaps of interest quickly.
  5. Bitmaps are stored in compressed format, so takes less disk space comparison to B-Tree index.

Amazon Best Sellors

TOGAF 9.2 - STUDY [ The Open Group Architecture Framework ] - Chap 01 - Introduction

100 Feet View of TOGAF  What is Enterprise? Collection of Organization that has common set of Goals. Enterprise has People - organized by co...