Search This Blog

Thursday, December 26, 2013

Trying to get row counts for all tables at a time

You can use any of this
select
      t.owner,
      t.table_name,
      to_number(
        extractvalue(
          xmltype(dbms_xmlgen.getxml('select count(*) c from '||t.owner || '.' || t.table_name))
          ,'/ROWSET/ROW/C')
          )
          count
    from all_tables t,user_tab_privs p
    where t.owner = p.owner
      and t.table_name = p.table_name
      AND privilege = 'SELECT'
      
union all
select
      user,
      t.table_name,
      to_number(
        extractvalue(
          xmltype(dbms_xmlgen.getxml('select count(*) c from '||t.table_name))
          ,'/ROWSET/ROW/C')
          )
          row_count
    FROM user_tables t;
/

SELECT owner,table_name, num_rows 
from ALL_TABLES 
WHERE num_rows>0 
AND owner = 'OWNER_NAME'
ORDER BY num_rows DESC;

Monday, December 23, 2013

Oracle Certification Exam 1Z0-144 Question

Local procedure A calls remote procedure B. Procedure B was compiled at 8 A.M. Procedure A was modified and recompiled at 9 A.M. Remote procedure B was later modified and recompiled at 11 A.M. The dependency mode is set to TI MESTAMP. What happens when procedure A is invoked at 1 P.M?
A. There is no affect on procedure A and it runs successfully.
B. Procedure B is invalidated and recompiles when invoked.
C. Procedure A is invalidated and recompiles for the first time it is invoked.
D. Procedure A is invalidated and recompiles for the second time it is invoked.

Solution:
Answer: D
When the local procedure is invoked, at run time the Oracle server compares the two time stamps of the referenced remote procedure. If the time stamps are equal (indicating that the remote procedure has not recompiled), the Oracle server executes the local procedure. If the time stamps are not equal (indicating that the remote procedure had recompiled), the Oracle server invalidates the local procedure and returns a run-time error. If the local procedure, which is now tagged as invalid, is invoked a second time, the Oracle server re-compiles it before executing, in accordance with the automatic local dependency mechanism. So if a local procedure returns a run-time error the first time that it is invoked, indicating that the remote procedure's time stamp has changed, you should develop a strategy to re-invoke the local procedure.

Thursday, December 19, 2013

Oracle PL Number Evaluation Problem

Give me your feedback for this code mentioned below.
SET SERVEROUTPUT ON;
DECLARE
V_MYAGE NUMBER;
BEGIN
DBMS_OUTPUT.PUT_LINE('V_MYAGE : '||V_MYAGE);
IF V_MYAGE < 10 THEN
DBMS_OUTPUT.PUT_LINE('1-I am a child');
ELSE
DBMS_OUTPUT.PUT_LINE('1-I am NOT a child');
END IF;
IF V_MYAGE > 10 THEN
DBMS_OUTPUT.PUT_LINE('2-I am NOT a child');
ELSE
DBMS_OUTPUT.PUT_LINE('2-I am a child');
END IF;
END;

Monday, November 18, 2013

Swimming Inside Dynamic SQL using Native Dynamic SQL (NDS) & DBMS_SQL Package

In PL/SQL, dynamic SQL executes the following SQL statements where the full text is unknown at compile time such as:
  • A SELECT statement that includes an identifier that is unknown at compile time (such as a table name).
  • A WHERE clause in which the column name is unknown at compile time.
Native Dynamic SQL
Native Dynamic SQL provides the ability to dynamically execute SQL statements whose structure is constructed at execution time.
The EXECUTE IMMEDIATE statement: can be used to execute SQL statements or PL/SQL anonymous blocks.

Method 1:

This method lets your program accept or build a dynamic SQL statement, and then immediately execute it using the EXECUTE IMMEDIATE command. The SQL statement must not be a query (SELECT statement) and must not contain any placeholders for input host variables.
CREATE OR REPLACE PROCEDURE create_table(
  p_table_name VARCHAR2, p_col_specs  VARCHAR2) IS
BEGIN
  EXECUTE IMMEDIATE 
   'CREATE TABLE ' || p_table_name || ' (' || p_col_specs || ')';
END;
/
BEGIN
  create_table('EMPLOYEE_NAMES',
   'id NUMBER(4) PRIMARY KEY, name VARCHAR2(40)');
END;
/
CREATE OR REPLACE PROCEDURE add_col(p_table_name VARCHAR2,
                         p_col_spec VARCHAR2) IS
  v_stmt VARCHAR2(100) := 'ALTER TABLE ' || p_table_name ||
                        ' ADD '|| p_col_spec;
BEGIN
  EXECUTE IMMEDIATE v_stmt;
END;
/

EXECUTE add_col('employee_names', 'salary number(8,2)')

Method 2: 

This method lets your program accept or build a dynamic SQL statement, and then process it using the PREPARE and EXECUTE commands. The SQL statement must not be a query. The number of placeholders for input host variables and the data types of the input host variables must be known at precompile time.
CREATE PROCEDURE add_row(p_table_name VARCHAR2,
   p_id NUMBER, p_name VARCHAR2) IS
BEGIN
  EXECUTE IMMEDIATE 'INSERT INTO '|| p_table_name||
        ' VALUES (:1, :2)' USING p_id, p_name;
END;
/
CREATE FUNCTION del_rows(p_table_name VARCHAR2)
RETURN NUMBER IS
BEGIN
  EXECUTE IMMEDIATE 'DELETE FROM '|| p_table_name;
  RETURN SQL%ROWCOUNT;
END;
/
SET SERVEROUTPUT ON

BEGIN
  DBMS_OUTPUT.PUT_LINE(del_rows('EMPLOYEE_NAMES')|| ' rows deleted.');
END;
/

With Method 2, the SQL statement is parsed just once, but can be executed many times with different values for the host variables. SQL data definition statements such as CREATE and GRANT are executed when they are prepared. 

Method 3:

This method lets your program accept or build a dynamic query, and then process it using the PREPARE command with the DECLARE, OPEN, FETCH, and CLOSE cursor commands. The number of select-list items, the number of placeholders for input host variables, and the data types of the input host variables must be known at precompile time.
CREATE OR REPLACE FUNCTION get_emp(p_emp_id NUMBER)
RETURN employees%ROWTYPE IS
  v_stmt VARCHAR2(200);
  v_emprec employees%ROWTYPE;
BEGIN
  v_stmt := 'SELECT * FROM employees ' ||
          'WHERE employee_id = :id';
  EXECUTE IMMEDIATE v_stmt INTO v_emprec USING p_emp_id;

  RETURN v_emprec;
END;
/
SET SERVEROUTPUT ON
DECLARE
  v_emprec employees%ROWTYPE := get_emp(100);
BEGIN
  DBMS_OUTPUT.PUT_LINE('Emp: '|| v_emprec.last_name);
END;
/

Method 4:

This method lets your program accept or build a dynamic SQL statement, and then process it using descriptors. A descriptor is an area of memory used by your program and Oracle to hold a complete description of the variables in a dynamic SQL statement. The number of select-list items, the number of placeholders for input host variables, and the data types of the input host variables can be unknown until run time. 
CREATE OR REPLACE FUNCTION annual_sal( p_emp_id NUMBER)
RETURN NUMBER IS
  v_plsql varchar2(200) :=
    'DECLARE '||
    ' rec_emp employees%ROWTYPE; '||
    'BEGIN '||
    ' rec_emp := get_emp(:empid); ' ||
    ' :res := rec_emp.salary  * 12; ' ||
    'END;';
  v_result NUMBER;
BEGIN
 EXECUTE IMMEDIATE v_plsql
         USING IN p_emp_id, OUT v_result;
  RETURN v_result;
END; 
/
SET SERVEROUTPUT ON
EXECUTE DBMS_OUTPUT.PUT_LINE(annual_sal(100))
Method 4 is required for dynamic SQL statements that contain an unknown number of select-list items or input host variables. With this method, you use the DBMS_SQL package, which is covered later in this lesson. Situations that require using Method 4 are rare. 

Using the DBMS_SQL Package Subprograms

The DBMS_SQL package provides the following subprograms to execute dynamic SQL:

  • OPEN_CURSOR to open a new cursor and return a cursor ID number
  • PARSE to parse the SQL statement. Every SQL statement must be parsed by calling the PARSE procedures. Parsing the statement checks the statement’s syntax and associates it with the cursor in your program. You can parse any DML or DDL statement. DDL statements are immediately executed when parsed.
  • BIND_VARIABLE to bind a given value to a bind variable identified by its name in the statement being parsed. This is not needed if the statement does not have bind variables.
  • EXECUTE to execute the SQL statement and return the number of rows processed
  • FETCH_ROWS to retrieve the next row for a query (use in a loop for multiple rows)
  • CLOSE_CURSOR to close the specified cursor
CREATE OR REPLACE PROCEDURE insert_row (p_table_name VARCHAR2,
  p_id VARCHAR2, p_name VARCHAR2, p_region NUMBER) IS
  v_cur_id     INTEGER;
  v_stmt       VARCHAR2(200);
  v_rows_added NUMBER;

BEGIN
  v_stmt := 'INSERT INTO '|| p_table_name ||
          ' VALUES (:cid, :cname, :rid)';
  v_cur_id := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(v_cur_id, v_stmt, DBMS_SQL.NATIVE);
  DBMS_SQL.BIND_VARIABLE(v_cur_id, ':cid', p_id);
  DBMS_SQL.BIND_VARIABLE(v_cur_id, ':cname', p_name);
  DBMS_SQL.BIND_VARIABLE(v_cur_id, ':rid', p_region);
  v_rows_added := DBMS_SQL.EXECUTE(v_cur_id);
  DBMS_SQL.CLOSE_CURSOR(v_cur_id);
  DBMS_OUTPUT.PUT_LINE(v_rows_added ||' row added');
END;
/
SET SERVEROUTPUT ON
EXECUTE insert_row('countries', 'LB', 'Lebanon', 4)

Note: Using the DBMS_SQL package to execute DDL statements can result in a deadlock. For example, the most likely reason is that the package is being used to drop a procedure that you are still using.


Wednesday, November 13, 2013

Dancing with Oracle Package with Code only

CREATE OR REPLACE PACKAGE PL_PKG AS 

--Public Variables Declaration
v_tax NUMBER;

--Public Procedures Declaration
PROCEDURE CALCULATOR;

--Public Functions Declaration
FUNCTION TAX (P_VALUE IN NUMBER) RETURN NUMBER;

END PLPU04;
CREATE OR REPLACE 
PACKAGE BODY PL_PKG AS

--Private Variables Declaration
V_COUNTER NUMBER DEFAULT 0;


--Forward Declaration
PROCEDURE P_SP_CALC(NUM IN NUMBER);


--Body Implementation
PROCEDURE CALCULATOR
AS
BEGIN
--Reference Resolved
P_SP_CALC(100);
V_COUNTER := V_COUNTER+1;
DBMS_OUTPUT.PUT_LINE ('V_COUNTER := '||V_COUNTER);
END CALCULATOR;


--The Implementation
PROCEDURE P_SP_CALC(NUM IN NUMBER)
AS
BEGIN
NULL;
END P_SP_CALC;

FUNCTION TAX (P_VALUE IN NUMBER) RETURN NUMBER IS
    V_RATE NUMBER := 0.1;
BEGIN
    RETURN (P_VALUE * V_RATE);
END TAX;


--Initialization Portion(Should be at the end of the package body)
BEGIN
V_COUNTER := 100;

END PLPU04;
DECLARE
  P_VALUE NUMBER;
  v_Return NUMBER;
BEGIN
  PL_PKG .CALCULATOR();
  PL_PKG .CALCULATOR();
  PL_PKG .CALCULATOR();
  P_VALUE := NULL;

  v_Return := PLPU04.TAX(P_VALUE => P_VALUE);
  DBMS_OUTPUT.PUT_LINE('v_Return = ' || v_Return);
END;

Forward Declarations Problem

Problem
In general, PL/SQL is like other block-structured languages and does not allow forward references. You must declare an identifier before using it. For example, a subprogram must be declared before you can call it.

NB
Coding standards often require that subprograms be kept in alphabetical sequence to make them easy to find. In this case, you may encounter problems.

Solution
You can solve the illegal reference problem by reversing the order of the two procedures. However, this easy solution does not work if the coding rules require subprograms to be declared in alphabetical order.

The solution in this case is to use forward declarations provided in PL/SQL. A forward declaration enables you to declare the heading of a subprogram, that is, the subprogram specification terminated by a semicolon.


Forward Declarations

A forward declaration may be required for private subprograms in the package body, and consists of the subprogram specification terminated by a semicolon. Forward declarations help to:
  • Define subprograms in logical or alphabetical order.
  • Define mutually recursive subprograms. Mutually recursive programs are programs that call each other directly or indirectly.
  • Group and logically organize subprograms in a package body.


When creating a forward declaration:
  • The formal parameters must appear in both the forward declaration and the subprogram body.
  • The subprogram body can appear anywhere after the forward declaration, but both must appear in the same program unit.

CREATE OR REPLACE PACKAGE PL_TEST AS 

--Public Procedure Declaration
PROCEDURE CALLER;

END PLPU04;
CREATE OR REPLACE 
PACKAGE BODY PL_TEST AS
--Forward Declaration
PROCEDURE CALC(NUM IN NUMBER);

PROCEDURE CALLER
AS
BEGIN
--Reference Resolved
CALC(100);
END CALLER;

--The Implementation
PROCEDURE CALC(NUM IN NUMBER)
AS
BEGIN
NULL;
END CALC;

END PLPU04;
When to use Forward Declarations with Packages?
Typically, the subprogram specifications go in the package specification, and the subprogram bodies go in the package body. The public subprogram declarations in the package specification do not require forward declarations.

Overloading Subprograms and it's Limitations

The overloading feature in PL/SQL enables you to develop two or more packaged subprograms with the same name. Overloading is useful when you want a subprogram to accept similar sets of parameters that have different data types. For example, the TO_CHAR function has more than one way to be called, enabling you to convert a number or a date to a character string.
PL/SQL allows overloading of package subprogram names and object type methods.
The key rule is that you can use the same name for different subprograms as long as their formal parameters differ in number, order, or data type family.

Consider using overloading when:
  1. Processing rules for two or more subprograms are similar, but the type or number of parameters used varies
  2. Providing alternative ways for finding different data with varying search criteria. For example, you may want to find employees by their employee ID and also provide a way to find employees by their last name. The logic is intrinsically the same, but the parameters or search criteria differ.
  3. Extending functionality when you do not want to replace existing code

You cannot overload(Restrictions):
  1. Two subprograms if their formal parameters differ only in data type and the different data types are in the same family (NUMBER and DECIMAL belong to the same family.)
  2. Two subprograms if their formal parameters differ only in subtype and the different subtypes are based on types in the same family (VARCHAR and STRING are PL/SQL subtypes of VARCHAR2.)
  3. Two functions that differ only in return type, even if the types are in different families
You get a run-time error when you overload subprograms with the preceding features.


Thursday, October 31, 2013

Playing with PL record type

The %ROWTYPE attribute provides a record type that represents a row in a database table. The record can store an entire row of data selected from the table or fetched from a cursor or cursor variable. Variables declared using %ROWTYPE are treated like those declared using a datatype name. You can use the %ROWTYPE attribute in variable declarations as a datatype specifier.

see thoses examples, it will speak to you.

PROCEDURE INSERT_REC
AS
V_EMP_NUMBER NUMBER := 124;
V_EMP_REC EMPLOYEES%ROWTYPE;
BEGIN
DBMS_OUTPUT.PUT_LINE('INSERT_REC');
SELECT * INTO V_EMP_REC FROM EMPLOYEES WHERE EMPLOYEE_ID = V_EMP_NUMBER;

INSERT INTO RETIRED_EMPS
(EMP_NO, FIRST_NAME, LAST_NAME,
EMAIL, PHONE_NUMBER, HIRE_DATE,
JOB, SAL, COMM,
MGR, DEPT_NO)
VALUES
(V_EMP_REC.EMPLOYEE_ID ,V_EMP_REC.FIRST_NAME ,V_EMP_REC.LAST_NAME ,
V_EMP_REC.EMAIL,V_EMP_REC.PHONE_NUMBER ,V_EMP_REC.HIRE_DATE ,
V_EMP_REC.JOB_ID,V_EMP_REC.SALARY ,V_EMP_REC.COMMISSION_PCT ,
V_EMP_REC.MANAGER_ID, V_EMP_REC.DEPARTMENT_ID);

END INSERT_REC;

PROCEDURE INSERT_REC_BULK
AS
V_EMP_NUMBER NUMBER := 124;
V_EMP_REC EMPLOYEES%ROWTYPE;
BEGIN
DBMS_OUTPUT.PUT_LINE('INSERT_REC_BULK');
SELECT * INTO V_EMP_REC FROM EMPLOYEES WHERE EMPLOYEE_ID = V_EMP_NUMBER;
V_EMP_REC.COMMISSION_PCT := 0.1;
V_EMP_REC.EMPLOYEE_ID:= V_EMP_NUMBER*10;
INSERT INTO RETIRED_EMPS VALUES V_EMP_REC;

END INSERT_REC_BULK;

PROCEDURE UPDATE_REC_BULK
AS
V_EMP_NUMBER NUMBER := 1240;
V_EMP_REC RETIRED_EMPS%ROWTYPE;
BEGIN
DBMS_OUTPUT.PUT_LINE('UPDATE_REC_BULK');
SELECT * INTO V_EMP_REC FROM RETIRED_EMPS WHERE EMP_NO = V_EMP_NUMBER;
V_EMP_REC.COMM := 0.9;

UPDATE RETIRED_EMPS SET ROW = V_EMP_REC WHERE  EMP_NO = V_EMP_NUMBER;

END UPDATE_REC_BULK;

PL/SQL Variables Scope

PL/Scope is a compiler-driven tool that collects data about identifiers in PL/SQL source code at program-unit compilation time and makes it available in static data dictionary views. The collected data includes information about identifier types, usages (declaration, definition, reference, call, assigment) and the location of each usage in the source code.

PL/Scope enables the development of powerful and effective PL/Scope source code browsers that increase PL/SQL developer productivity by minimizing time spent browsing and understanding source code. PL/Scope is intended for application developers, and will usually be used in the environment of a development database.

PROCEDURE PROC

AS
V_DESC_SIZE INTEGER(5);
V_PROD_DESCRIPTION VARCHAR2(70) := 'You ';
V_DATE1 DATE DEFAULT SYSDATE;
V_DATE2 V_DATE1%TYPE DEFAULT SYSDATE+360;
V_DATE_DIFF INTEGER;
V_DT_STR DATE := '05-FEB-2013';
V_DT_STR2 DATE := '03-JAN-2013';
V_OUTER VARCHAR2(20) :='Global variable';
BEGIN <<OUTER_LBL>>
DBMS_OUTPUT.PUT_LINE('PROCEDURE PROC');
V_DESC_SIZE := LENGTH(V_PROD_DESCRIPTION);
DBMS_OUTPUT.PUT_LINE('V_DESC_SIZE : '||V_DESC_SIZE);
V_DATE_DIFF := MONTHS_BETWEEN(V_DATE1, V_DATE2);
DBMS_OUTPUT.PUT_LINE('V_DATE_DIFF : '||V_DATE_DIFF );
DBMS_OUTPUT.PUT_LINE('V_DT_STR : '||V_DT_STR );
DBMS_OUTPUT.PUT_LINE('V_DT_STR2 : '||V_DT_STR2 );
    DECLARE 
        V_INNER VARCHAR2(20) := 'Local Var';
        V_OUTER VARCHAR2(20) :='V_OUTER LOCAL var';
    BEGIN <<INNER_LBL>>
        DBMS_OUTPUT.PUT_LINE('V_OUTER : '||V_OUTER );
        DBMS_OUTPUT.PUT_LINE('V_INNER : '||V_INNER );
        DBMS_OUTPUT.PUT_LINE('OUTER_LBL.V_OUTER : '||OUTER_LBL.V_OUTER );
        DBMS_OUTPUT.PUT_LINE('PROC.V_OUTER : '||PROC.V_OUTER );
        V_OUTER :=V_OUTER;
    END;
    DBMS_OUTPUT.PUT_LINE('V_OUTER : '||V_OUTER );
  
    
END PROC;

The out put will be
PROCEDURE PROC
V_DESC_SIZE : 4
V_DATE_DIFF : -12
V_DT_STR : 05-FEB-13
V_DT_STR2 : 03-JAN-13
V_OUTER : V_OUTER LOCAL var
V_INNER : Local Var
OUTER_LBL.V_OUTER : V_OUTER LOCAL var
PROC.V_OUTER : Global variable
V_OUTER : Global variable

Reference for extra reading:
http://docs.oracle.com/cd/B13789_01/appdev.101/b10807/02_funds.htm#i15754

PL/SQL MERGE Statement

The MERGE statement inserts some rows and updates others in a single operation. The decision about whether to update or insert into the target table is based upon a join condition: rows already in the target table that match the join condition are updated; otherwise, a row is inserted using values from a separate subquery.
    
PROCEDURE PROC_MERGE
AS

BEGIN
  DBMS_OUTPUT.PUT_LINE('PROC_MERGE');
  MERGE INTO COPY_EMP C
        USING EMPLOYEES E
        ON(C.EMPLOYEE_ID = C.EMPLOYEE_ID)
    WHEN MATCHED THEN
        UPDATE SET 
            C.FIRST_NAME = E.FIRST_NAME,
            C.LAST_NAME = E.LAST_NAME,
            C.EMAIL = E.EMAIL,
            C.PHONE_NUMBER = E.PHONE_NUMBER,
            C.HIRE_DATE = E.HIRE_DATE,
            C.JOB_ID = E.JOB_ID,
            C.SALARY = E.SALARY,
            C.COMMISSION_PCT = E.COMMISSION_PCT,
            C.MANAGER_ID = E.MANAGER_ID,
            C.DEPARTMENT_ID = E.DEPARTMENT_ID
             
    WHEN NOT MATCHED THEN
        insert values (e.EMPLOYEE_ID,e.FIRST_NAME,e.LAST_NAME,e.EMAIL,e.PHONE_NUMBER,e.HIRE_DATE,e.JOB_ID,e.SALARY,e.COMMISSION_PCT,e.MANAGER_ID,e.DEPARTMENT_ID);
END PROC_MERGE;

Wednesday, October 30, 2013

Associative Arrays, Nested Tables, and Varrays two minutes drill

Associative Arrays


Associative arrays are sets of key-value pairs, where each key is unique and is used to locate a corresponding value in the array. The key can be either integer- or character-based. The array value may be of the scalar data type (single value) or the record data type (multiple values).
Because associative arrays are intended for storing temporary data, you cannot use them with SQL statements such as INSERT and SELECT INTO.

The following methods make associative arrays easier to use:
EXISTS
COUNT
FIRST
LAST
PRIOR
NEXT
DELETE

DECLARE
V_EMP_NUMBER NUMBER := 124;
V_EMP_REC EMPLOYEES%ROWTYPE;
BEGIN
SELECT * INTO V_EMP_REC FROM EMPLOYEES WHERE EMPLOYEE_ID = V_EMP_NUMBER;

INSERT INTO RETIRED_EMPS
(EMP_NO, FIRST_NAME, LAST_NAME,
EMAIL, PHONE_NUMBER, HIRE_DATE,
JOB, SAL, COMM,
MGR, DEPT_NO)
VALUES
(V_EMP_REC.EMPLOYEE_ID ,V_EMP_REC.FIRST_NAME ,V_EMP_REC.LAST_NAME ,
V_EMP_REC.EMAIL,V_EMP_REC.PHONE_NUMBER ,V_EMP_REC.HIRE_DATE ,
V_EMP_REC.JOB_ID,V_EMP_REC.SALARY ,V_EMP_REC.COMMISSION_PCT ,
V_EMP_REC.MANAGER_ID, V_EMP_REC.DEPARTMENT_ID);
END;
/
DECLARE
V_EMP_NUMBER NUMBER := 124;
V_EMP_REC EMPLOYEES%ROWTYPE;
BEGIN
SELECT * INTO V_EMP_REC FROM EMPLOYEES WHERE EMPLOYEE_ID = V_EMP_NUMBER;
V_EMP_REC.COMMISSION_PCT := 0.1;
V_EMP_REC.EMPLOYEE_ID:= V_EMP_NUMBER*10;

INSERT INTO RETIRED_EMPS VALUES V_EMP_REC;
END;
/
DECLARE
V_EMP_NUMBER NUMBER := 1240;
V_EMP_REC RETIRED_EMPS%ROWTYPE;

BEGIN<
SELECT * INTO V_EMP_REC FROM RETIRED_EMPS WHERE EMP_NO = V_EMP_NUMBER;
V_EMP_REC.COMM := 0.9;

UPDATE RETIRED_EMPS SET ROW = V_EMP_REC WHERE  EMP_NO = V_EMP_NUMBER;
END;
/

Nested Tables


A nested table holds a set of values. In other words, it is a table within a table. Nested tables are unbounded; that is, the size of the table can increase dynamically. Nested tables are available in both PL/SQL and the database. Within PL/SQL, nested tables are like one-dimensional arrays whose size can increase dynamically.

Varrays



Variable-size arrays, or varrays, are also collections of homogeneous elements that hold a fixed number of elements (although you can change the number of elements at run time). They use sequential numbers as subscripts. You can define equivalent SQL types, thereby allowing varrays to be stored in database tables.

Setting up SyntaxHighlighter on Google Blogger

  1. First, take backup of your blogger template.
  2. After that open your blogger template (In Edit HTML mode) & copy the all css given in this link before closing of b:skin tag.
    .dp-highlighter
    {
     font-family: "Consolas", "Monaco", "Courier New", Courier, monospace;
     font-size: 12px;
     background-color: #E7E5DC;
     width: 99%;
     overflow: auto;
     margin: 18px 0 18px 0 !important;
     padding-top: 1px; /* adds a little border on top when controls are hidden */
    }
    
    /* clear styles */
    .dp-highlighter ol,
    .dp-highlighter ol li,
    .dp-highlighter ol li span 
    {
     margin: 0;
     padding: 0;
     border: none;
    }
    
    .dp-highlighter a,
    .dp-highlighter a:hover
    {
     background: none;
     border: none;
     padding: 0;
     margin: 0;
    }
    
    .dp-highlighter .bar
    {
     padding-left: 45px;
    }
    
    .dp-highlighter.collapsed .bar,
    .dp-highlighter.nogutter .bar
    {
     padding-left: 0px;
    }
    
    .dp-highlighter ol
    {
     list-style: decimal; /* for ie */
     background-color: #fff;
     margin: 0px 0px 1px 45px !important; /* 1px bottom margin seems to fix occasional Firefox scrolling */
     padding: 0px;
     color: #5C5C5C;
    }
    
    .dp-highlighter.nogutter ol,
    .dp-highlighter.nogutter ol li
    {
     list-style: none !important;
     margin-left: 0px !important;
    }
    
    .dp-highlighter ol li,
    .dp-highlighter .columns div
    {
     list-style: decimal-leading-zero; /* better look for others, override cascade from OL */
     list-style-position: outside !important;
     border-left: 3px solid #6CE26C;
     background-color: #F8F8F8;
     color: #5C5C5C;
     padding: 0 3px 0 10px !important;
     margin: 0 !important;
     line-height: 14px;
    }
    
    .dp-highlighter.nogutter ol li,
    .dp-highlighter.nogutter .columns div
    {
     border: 0;
    }
    
    .dp-highlighter .columns
    {
     background-color: #F8F8F8;
     color: gray;
     overflow: hidden;
     width: 100%;
    }
    
    .dp-highlighter .columns div
    {
     padding-bottom: 5px;
    }
    
    .dp-highlighter ol li.alt
    {
     background-color: #FFF;
     color: inherit;
    }
    
    .dp-highlighter ol li span
    {
     color: black;
     background-color: inherit;
    }
    
    /* Adjust some properties when collapsed */
    
    .dp-highlighter.collapsed ol
    {
     margin: 0px;
    }
    
    .dp-highlighter.collapsed ol li
    {
     display: none;
    }
    
    /* Additional modifications when in print-view */
    
    .dp-highlighter.printing
    {
     border: none;
    }
    
    .dp-highlighter.printing .tools
    {
     display: none !important;
    }
    
    .dp-highlighter.printing li
    {
     display: list-item !important;
    }
    
    /* Styles for the tools */
    
    .dp-highlighter .tools
    {
     padding: 3px 8px 3px 10px;
     font: 9px Verdana, Geneva, Arial, Helvetica, sans-serif;
     color: silver;
     background-color: #f8f8f8;
     padding-bottom: 10px;
     border-left: 3px solid #6CE26C;
    }
    
    .dp-highlighter.nogutter .tools
    {
     border-left: 0;
    }
    
    .dp-highlighter.collapsed .tools
    {
     border-bottom: 0;
    }
    
    .dp-highlighter .tools a
    {
     font-size: 9px;
     color: #a0a0a0;
     background-color: inherit;
     text-decoration: none;
     margin-right: 10px;
    }
    
    .dp-highlighter .tools a:hover
    {
     color: red;
     background-color: inherit;
     text-decoration: underline;
    }
    
    /* About dialog styles */
    
    .dp-about { background-color: #fff; color: #333; margin: 0px; padding: 0px; }
    .dp-about table { width: 100%; height: 100%; font-size: 11px; font-family: Tahoma, Verdana, Arial, sans-serif !important; }
    .dp-about td { padding: 10px; vertical-align: top; }
    .dp-about .copy { border-bottom: 1px solid #ACA899; height: 95%; }
    .dp-about .title { color: red; background-color: inherit; font-weight: bold; }
    .dp-about .para { margin: 0 0 4px 0; }
    .dp-about .footer { background-color: #ECEADB; color: #333; border-top: 1px solid #fff; text-align: right; }
    .dp-about .close { font-size: 11px; font-family: Tahoma, Verdana, Arial, sans-serif !important; background-color: #ECEADB; color: #333; width: 60px; height: 22px; }
    
    /* Language specific styles */
    
    .dp-highlighter .comment, .dp-highlighter .comments { color: #008200; background-color: inherit; }
    .dp-highlighter .string { color: blue; background-color: inherit; }
    .dp-highlighter .keyword { color: #069; font-weight: bold; background-color: inherit; }
    .dp-highlighter .preprocessor { color: gray; background-color: inherit; }
    
  3. Paste the followig code before closing of head tag.
    
    
    
    
    
    
    
    
    
    
    
    
    
    
  4. Paste the following code before closing of body tag.
    <pre class="js" name="code">
    <script language="javascript">
    dp.SyntaxHighlighter.BloggerMode();
    dp.SyntaxHighlighter.HighlightAll('code');
    </script>
    </pre>
    
  5. Save Blogger Template.
  6. Now syntax highlighting is ready to use you can use it as following.
    ...Your html-escaped code goes here...
    
        echo "I like PHP";
    
  7. You can Escape your code here.
  8. Here is list of supported language for class attribute.
LanguageAliases
C++cppcc++
C#c#c-sharpcsharp
CSScss
Delphidelphipascal
Javajava
Java Scriptjsjscriptjavascript
PHPphp
Pythonpypython
Rubyrbrubyrailsror
Sqlsql
VBvbvb.net
XML/HTMLxmlhtmlxhtmlxslt


Monday, September 09, 2013

Deploying ADF Applacition On GlassFish Server

1- Download Glass Fish Server
http://www.oracle.com/technetwork/java/javaee/downloads/index.html
go to "Oracle GlassFish Server" and download it
2- Download ADF Essential
http://www.oracle.com/technetwork/developer-tools/adf/downloads/index.html
got to "Oracle ADF Essentials" and download it ("adf-essentials.zip)
3- Installing Glass Fish. Create Domain.
4- Unzip adf-essentials.zip in domain (unzip.exe –j adf-essentials.zip)
5- Start-domain. http//localhost:4848.
6- server-config. Change JVM setting.
-Doracle.mds.cache=simple
-Duser.timezone=Etc/GMT-2 (write your timezone).
-XX:PermSize=256m (Edit Java momery).
-XX:MaxPermSize=512m (Edit Java momery).
7- Create JDBC Connection Pools. javax.sql.XADataSource.
8- Create JDBC resource. jdbo/hr
9- Edit ADF Application AppModule. Project  properties.  Application properties.
10- Edit Java EE web application.
11- Develop ADF application.
12- run the application
http://localhost:9090/context-root/faces/yourPage.jspx

Wednesday, August 28, 2013

import sun.misc.BASE64Encoder got error in Eclipse

Problem:
while importing sun.misc.BASE64Encoder got error in Eclipse; For this two imports;
import sun.misc.BASE64Encoder;
import sun.misc.BASE64Decoder;

I got this error:
Access restriction: The type BASE64Decoder is not accessible due to restriction on required library xxx.jar

Cause:
That error is caused by your Eclipse configuration. You can reduce it to a warning. Better still, use a Base64 encoder that isn't part of a non-public API. Apache Commons has one.

Solution:
  1. Go to Window-->Preferences-->Java-->Compiler-->Error/Warnings.
  2. Select Deprecated and Restricted API. Change it to warning.
  3. Change forbidden and Discouraged Reference and change it to warning. (or as your need.)

Thursday, August 22, 2013

JPQL/Custom data retrieve from JPQL SELECT Statment

To retrieve specific columns from table and mapped it into new Class not the JPA contains the entire Table columns, to reduce the memory allocation for the entire JPA Entity Class.
The result of this query is a list of AnimalInfo objects that have been instantiated with the new operator and initialized with the animal ID, and Type of the animals.


TABLE CREATION:

CREATE TABLE ANIMAL 
(
  ANIMAL_ID NUMBER NOT NULL 
, TYPE VARCHAR2(45 BYTE) 
, TOTAL_NO NUMBER 
, CATEGORY_ID NUMBER 
, CONSTRAINT ANIMAL_PK PRIMARY KEY 
  (
    ANIMAL_ID 
  )
   ENABLE
);

SEQUENCES AND TRIGGERS CREATION:

CREATE SEQUENCE ANIMAL_SEQ NOCACHE;

create or replace TRIGGER ANIMAL_TRG 
BEFORE INSERT ON ANIMAL 
FOR EACH ROW 
BEGIN
    IF :NEW.ANIMAL_ID IS NULL THEN
      SELECT ANIMAL_SEQ.NEXTVAL INTO :NEW.ANIMAL_ID FROM DUAL;
    END IF;
END;

INSERT TEST DATA:

REM INSERTING into ANIMAL
Insert into ANIMAL (ANIMAL_ID,TYPE,TOTAL_NO,CATEGORY_ID) values (1,'Elephant',4,1);
Insert into ANIMAL (ANIMAL_ID,TYPE,TOTAL_NO,CATEGORY_ID) values (2,'Turtle',33,3);
Insert into ANIMAL (ANIMAL_ID,TYPE,TOTAL_NO,CATEGORY_ID) values (3,'Snake',3,3);
Insert into ANIMAL (ANIMAL_ID,TYPE,TOTAL_NO,CATEGORY_ID) values (4,'Pelican',6,3);
Insert into ANIMAL (ANIMAL_ID,TYPE,TOTAL_NO,CATEGORY_ID) values (5,'Lion',2,1);
Insert into ANIMAL (ANIMAL_ID,TYPE,TOTAL_NO,CATEGORY_ID) values (6,'Zebra',4,1);
Insert into ANIMAL (ANIMAL_ID,TYPE,TOTAL_NO,CATEGORY_ID) values (7,'Owl',2,2);

CLASS CREATION:


Animal JPA Entity Class
@Entity
@Table(name = "ANIMAL")
public class Animal implements Serializable {

    private static final long serialVersionUID = 1L;
    @Id
    @Basic(optional = false)
    @Column(name = "ANIMAL_ID")
    private Integer animalId;
    @Column(name = "TYPE")
    private String type;
    @Column(name = "TOTAL_NO")
    private Integer totalNo;
    @Column(name = "CATEGORY_ID")
    private Integer categoryId;
    //generate getters, setters, toString(), hashCode(),equals()
}

Custom AnimalInfo Class
public class AnimalInfo {

    private Integer animalId;
    private String type;
    private Integer totalNo;

    public AnimalInfo() {
    }

    public AnimalInfo(Integer animalId, String type) {
        this.animalId = animalId;
        this.type = type;
    }

    public AnimalInfo(Integer animalId, String type, Integer totalNo) {
        this.animalId = animalId;
        this.type = type;
        this.totalNo = totalNo;
    }
    //generate getters, setters, toString(), hashCode(),equals()
}

JUNIT TEST CASE:

public class InheritanceJUnit {
    
    static EntityManagerFactory emf;
    static EntityManager em;
    
    @BeforeClass
    public static void initEntityManager() throws Exception {
        emf = Persistence.createEntityManagerFactory("JavaApplicationJPAPU");
        em = emf.createEntityManager();
    }
    
    @AfterClass
    public static void closeEntityManager() throws Exception {
        if (em != null) {
            em.close();
        }
        if (emf != null) {
            emf.close();
        }
    }

@Test
    @Ignore
    public void testTypedQueryReturnInCustomClass() {
        
        TypedQuery typedQuery;
        typedQuery = em.createQuery("select NEW com.jpa.entity.info.AnimalInfo( a.animalId,a.type) from Animal a", AnimalInfo.class);
        List animalInfoList = typedQuery.getResultList();
        for (AnimalInfo animalInfo : animalInfoList) {
            assertNotNull(animalInfo);
            System.out.println(animalInfo);
        }
        
    }
}    

Wednesday, August 21, 2013

JPA @SecondaryTables

Up to now, I have assumed that an entity gets mapped to a single table, also known as a  primary table. But sometimes when you have an existing data model, you need to spread the data across multiple tables, or secondary tables. To do this, you need to use the annotation @SecondaryTable to associate a secondary table to an entity or @SecondaryTables (with an “s”) for several secondary tables. You can distribute the data of an entity across columns in both the primary table and the secondary tables simply by defining the secondary tables with anno-tations and then specifying for each attribute which table it is in (with the @Column annotation, which I’ll describe in the “Attributes” section in more detail).

Example below shows an Address entity mapping its attributes in one primary table and two secondary tables.




TABLE CREATION:


T_ADDRESS Table
CREATE TABLE T_ADDRESS 
(
  ID NUMBER NOT NULL 
, STREET1 VARCHAR2(245) 
, STREET2 VARCHAR2(245) 
, CONSTRAINT T_ADDRESS_PK PRIMARY KEY 
  (
    ID 
  )
  ENABLE 
);

T_CITY Table
CREATE TABLE T_CITY 
(
  ID NUMBER NOT NULL 
, CITY VARCHAR2(45) 
, STATE VARCHAR2(50) 
, ZIPCODE VARCHAR2(10) 
, CONSTRAINT T_CITY_PK PRIMARY KEY 
  (
    ID 
  )
  ENABLE 
);

T_COUNTRY Table
CREATE TABLE T_COUNTRY 
(
  ID NUMBER NOT NULL 
, COUNTRY VARCHAR2(50) 
, CONSTRAINT T_COUNTRY_PK PRIMARY KEY 
  (
    ID 
  )
  ENABLE 
);

SEQUENCES AND TRIGGERS CREATION:

CREATE SEQUENCE T_ADDRESS_SEQ;

CREATE TRIGGER T_ADDRESS_TRG 
BEFORE INSERT ON T_ADDRESS 
FOR EACH ROW 
BEGIN
    IF :NEW.ID IS NULL THEN
      SELECT T_ADDRESS_SEQ.NEXTVAL INTO :NEW.ID FROM DUAL;
    END IF;
END;
/

CONSTRAINT:


T_CITY Table
ALTER TABLE T_CITY
ADD CONSTRAINT T_CITY_FK FOREIGN KEY
(
  ID 
)
REFERENCES T_ADDRESS
(
  ID 
)
ENABLE;

T_COUNTRY Table
ALTER TABLE T_COUNTRY
ADD CONSTRAINT T_COUNTRY_FK FOREIGN KEY
(
  ID 
)
REFERENCES T_ADDRESS
(
  ID 
)
ENABLE;

INSERT TEST DATA:


T_ADDRESS Table
REM INSERTING into T_ADDRESS
Insert into T_ADDRESS (ID,STREET1,STREET2) values (1,'STREET_1 1','STREET_2 1');
Insert into T_ADDRESS (ID,STREET1,STREET2) values (2,'STREET_1 2','STREET_2 2');
Insert into T_ADDRESS (ID,STREET1,STREET2) values (3,'STREET_1 3','STREET_2 3');

T_CITY Table
REM INSERTING into T_CITY
Insert into T_CITY (ID,CITY,STATE,ZIPCODE) values (1,'CITY 1','STATE 1','111');
Insert into T_CITY (ID,CITY,STATE,ZIPCODE) values (2,'CITY 2','STATE 2','222');
Insert into T_CITY (ID,CITY,STATE,ZIPCODE) values (3,'CITY 3','STATE 3','333');

T_COUNTRY Table
REM INSERTING into T_COUNTRY
Insert into T_COUNTRY (ID,COUNTRY) values (1,'COUNTRY 1');
Insert into T_COUNTRY (ID,COUNTRY) values (2,'COUNTRY 2');
Insert into T_COUNTRY (ID,COUNTRY) values (3,'COUNTRY 3');

CLASS CREATION:


TAddress Class
@Entity
@Table(name = "T_ADDRESS")
@SecondaryTables(
        {
    @SecondaryTable(name = "T_COUNTRY"),
    @SecondaryTable(name = "T_CITY")
})
public class TAddress implements Serializable {

    private static final long serialVersionUID = 1L;
    @Id
    @Basic(optional = false)
    @Column(name = "ID")
    private Integer id;
    @Column(name = "STREET1")
    private String street1;
    @Column(name = "STREET2")
    private String street2;

    @Column(table = "T_COUNTRY", name = "COUNTRY")
    private String country;
    
    @Column(table = "T_CITY", name = "CITY")
    private String city;
    @Column(table = "T_CITY", name = "STATE")
    private String state;
    @Column(table = "T_CITY", name = "ZIPCODE")
    private String zipcode;
    
    //generate getters, setters, toString(), hashCode(),equals()
}

JUNIT TEST CASE:

public class InheritanceJUnit {
    
    static EntityManagerFactory emf;
    static EntityManager em;
    static EntityTransaction trx;
    
    @BeforeClass
    public static void initEntityManager() throws Exception {
        emf = Persistence.createEntityManagerFactory("JavaApplicationJPAPU");
        em = emf.createEntityManager();
        trx = em.getTransaction();
    }
    
    @AfterClass
    public static void closeEntityManager() throws Exception {
        if (em != null) {
            em.close();
        }
        if (emf != null) {
            emf.close();
        }
    }
    
    @Before
    public void initTransaction() throws Exception {
        trx.begin();
    }
    
    @After
    public void endTransaction() throws Exception {
        if (!trx.getRollbackOnly()) {
            trx.commit();
        }
    }

    @Test
    @Ignore
    public void testSecondaryTableInsert() {
        
        TAddress tAddress = new TAddress();
        tAddress.setId(10);
        tAddress.setStreet1("Street 1 10");
        tAddress.setStreet2("Street 2 10");
        
        tAddress.setCountry("Country 1 10");
        
        tAddress.setCity("City 10");
        tAddress.setState("State 10");
        tAddress.setZipcode("1010");
        assertNotNull(tAddress);
        em.persist(tAddress);
        System.out.println("TAddress : " + tAddress);
        
        TAddress tAddress2 = new TAddress();
        tAddress2.setId(11);
        tAddress2.setStreet1("Street 1 11");
        tAddress2.setStreet2("Street 2 11");
        
        tAddress2.setCountry("Country 1 11");
        
        
        assertNotNull(tAddress2);
        em.persist(tAddress2);
        System.out.println("TAddress2 : " + tAddress2);
        
    }

    @Test
    @Ignore
    public void testSecondaryTableSelect() {
        
        TAddress tAddress = em.find(TAddress.class, 10);
        assertNotNull(tAddress);
        
        System.out.println("TAddress : " + tAddress);
        
    }
        
    @Test
    @Ignore
    public void testSecondaryTableUpdate() {
        
        TAddress tAddress = em.find(TAddress.class, 10);
        assertNotNull(tAddress);

        tAddress.setStreet1("Street 1 10 edited");
        tAddress.setStreet2("Street 2 10 edited");
        
        tAddress.setCountry("Country 1 10 edited");
        
        tAddress.setCity("City 10 edited");
        tAddress.setState(null);
        tAddress.setZipcode("1010");
        em.merge(tAddress);
        System.out.println("TAddress : " + tAddress);
        
        TAddress tAddress2 = em.find(TAddress.class, 11);
        assertNotNull(tAddress2);
        
        tAddress2.setStreet1("Street 1 11 edited");
        tAddress2.setStreet2("");
        
        tAddress2.setCountry("Country 1 11 edited");
        
        tAddress2.setCity("City 10 edited");
        tAddress2.setState("State 10 edited");
        tAddress2.setZipcode(null);
        
        em.merge(tAddress2);
        System.out.println("TAddress2 : " + tAddress2);
    }
    
    @Test
    @Ignore
    public void testSecondaryTableDelete() {
        
        TAddress tAddress = em.find(TAddress.class, 10);
        assertNotNull(tAddress);
        
        em.detach(tAddress);
        System.out.println("TAddress : " + tAddress);
        
        TAddress tAddress2 = em.find(TAddress.class, 11);
        assertNotNull(tAddress2);
        
        em.detach(tAddress2);
        System.out.println("TAddress2 : " + tAddress2);
        
    }
}

Monday, August 19, 2013

Java Persistence/Embeddables

An embeddable object can be shared between multiple classes. Consider a Address object, that both a Customer and an Orders contain. Both Customer  and Orders have their own tables.


TABLE CREATION:


ORDERS Table
CREATE TABLE ORDERS 
(
  ID NUMBER NOT NULL 
, ORDER_DATE Date
, BENF_NAME VARCHAR2(20) 
, STREET1 VARCHAR2(255) 
, STREET2 VARCHAR2(225) 
, ZIPCODE VARCHAR2(6) 
, STATE VARCHAR2(20) 
, COUNTRY VARCHAR2(20) 
, CITY VARCHAR2(50) 
, CONSTRAINT ORDERS_PK PRIMARY KEY 
  (
    ID 
  )
  ENABLE 
);

CUSTOMER Table
CREATE TABLE CUSTOMER 
(
  ID NUMBER NOT NULL 
, FIRST_NAME VARCHAR2(20) 
, LAST_NAME VARCHAR2(45) 
, PHONE_NUMBER VARCHAR2(15) 
, EMAIL VARCHAR2(50) 
, STREET1 VARCHAR2(255) 
, STREET2 VARCHAR2(255) 
, ZIPCODE VARCHAR2(6) 
, STATE VARCHAR2(20) 
, COUNTRY VARCHAR2(50) 
, CITY VARCHAR2(50) 
, CONSTRAINT CUSTOMER_PK PRIMARY KEY 
  (
    ID 
  )
  ENABLE 
);

SEQUENCES AND TRIGGERS CREATION:


ORDERS Table
CREATE SEQUENCE ORDERS_SEQ NOCACHE;

CREATE TRIGGER ORDERS_TRG 
BEFORE INSERT ON ORDERS 
FOR EACH ROW 
BEGIN
    IF :NEW.ID IS NULL THEN
      SELECT ORDERS_SEQ.NEXTVAL INTO :NEW.ID FROM DUAL;
    END IF;
END;
/

CUSTOMER Table
CREATE SEQUENCE CUSTOMER_SEQ NOCACHE;

CREATE TRIGGER CUSTOMER_TRG 
BEFORE INSERT ON CUSTOMER 
FOR EACH ROW 
BEGIN
    IF :NEW.ID IS NULL THEN
      SELECT CUSTOMER_SEQ.NEXTVAL INTO :NEW.ID FROM DUAL;
    END IF;
END;
/

INSERT TEST DATA:


ORDERS Table
REM INSERTING into ORDERS
Insert into ORDERS (ID,ORDER_DATE,BENF_NAME,STREET1,STREET2,ZIPCADE,STATE,COUNTRY,CITY) values (1,to_timestamp('17-AUG-13','DD-MON-RR HH.MI.SSXFF AM'),'benf 1','street 1','street 2','11','state 11','country 11','city 11');
Insert into ORDERS (ID,ORDER_DATE,BENF_NAME,STREET1,STREET2,ZIPCADE,STATE,COUNTRY,CITY) values (2,to_timestamp('18-AUG-13','DD-MON-RR HH.MI.SSXFF AM'),'benf 2','street 1 -2','street 2 -2','22','state 22','country 22','city 22');

CUSTOMER Table
REM INSERTING into CUSTOMER
Insert into CUSTOMER (ID,FIRST_NAME,LAST_NAME,PHONE_NUMBER,EMAIL,STREET1,STREET2,ZIPCODE,STATE,COUNTRY,CITY) values (1,'cust f name 1','cust l name 1',null,'custfname1@mail.com','cust f name 1 street 1','cust f name 1 street 2','11','state1','cust f name 1 counntry 1','cust f name 1 city 1');
Insert into CUSTOMER (ID,FIRST_NAME,LAST_NAME,PHONE_NUMBER,EMAIL,STREET1,STREET2,ZIPCODE,STATE,COUNTRY,CITY) values (2,'cust f name 2','cust 2 name',null,'custfname2@mail.com','cust f name 2 street 1','cust f name 2 street 2','22','state2','cust f name 2 counntry','cust f name 2 city ');

CLASS CREATION:


Address Class
@Embeddable
public class Address {

    @Column(name = "STREET1")
    private String street1;
    @Column(name = "STREET2")
    private String street2;
    @Column(name = "ZIPCODE")
    private String zipcode;
    @Column(name = "STATE")
    private String state;
    @Column(name = "COUNTRY")
    private String country;
    @Column(name = "CITY")
    private String city;
    //generate getters, setters, toString(), hashCode(),equals()
}


Orders Class
@Entity
@Table(name = "ORDERS")
public class Orders implements Serializable {

    private static final long serialVersionUID = 1L;
    @Id
    @Basic(optional = false)
    @Column(name = "ID")
    private Integer id;
    @Column(name = "ORDER_DATE")
    @Temporal(TemporalType.DATE)
    private Date orderDate;
    @Column(name = "BENF_NAME")
    private String benfName;
    @Embedded
    private Address address;
    //generate getters, setters, toString(), hashCode(),equals()
}

Customer Class
@Entity
@Table(name = "CUSTOMER")
public class Customer implements Serializable {

    private static final long serialVersionUID = 1L;
    @Id
    @Basic(optional = false)
    @Column(name = "ID")
    private Integer id;
    @Column(name = "FIRST_NAME")
    private String firstName;
    @Column(name = "LAST_NAME")
    private String lastName;
    @Column(name = "PHONE_NUMBER")
    private String phoneNumber;
    @Column(name = "EMAIL")
    private String email;
    @Embedded
    private Address address;
    //generate getters, setters, toString(), hashCode(),equals()
}

JUNIT TEST CASE:


public class InheritanceJUnit {

    static EntityManagerFactory emf;
    static EntityManager em;
    static EntityTransaction trx;

    @BeforeClass
    public static void initEntityManager() throws Exception {
        emf = Persistence.createEntityManagerFactory("JavaApplicationJPAPU");
        em = emf.createEntityManager();
        trx = em.getTransaction();
    }

    @AfterClass
    public static void closeEntityManager() throws Exception {
        if (em != null) {
            em.close();
        }
        if (emf != null) {
            emf.close();
        }
    }

    @Before
    public void initTransaction() throws Exception {
        trx.begin();
    }

    @After
    public void endTransaction() throws Exception {
        if (!trx.getRollbackOnly()) {
            trx.commit();
        }
    }
    @Test
    @Ignore
    public void testEmbeddableStrategyInsert() {

        Address address = new Address();
        address.setCity("Cust City");
        address.setCountry("Cust Country");
        address.setState("Cust State");
        address.setStreet1("Cust Street1");
        address.setStreet2("Cust Street2");
        address.setZipcode("Zcode");

        Customer customer = new Customer();
        customer.setId(10);
        customer.setFirstName("Cust FirstName");
        customer.setLastName("Cust LastName");
        customer.setPhoneNumber("Cust Phone");
        customer.setEmail("CustMail@host.com");
        customer.setAddress(address);
        em.persist(customer);
        System.out.println("Customer : " + customer);

        Orders orders = new Orders();
        orders.setId(10);
        orders.setBenfName("Benf Name");
        orders.setOrderDate(new Date());
        //orders.setAddress(null);
        em.persist(orders);
        System.out.println("Orders : " + orders);
    }

    @Test
    @Ignore
    public void testEmbeddableStrategySelect() {

        Customer customer = em.find(Customer.class, 10);
        assertNotNull(customer);
        System.out.println("Customer : " + customer);

        Orders orders = em.find(Orders.class, 10);
        assertNotNull(orders);
        System.out.println("Orders : " + orders);

    }

    @Test
    @Ignore
    public void testEmbeddableStrategyUpdate() {
        Address address = new Address();
        address.setCity("Cust City1");
        address.setCountry("Cust Country");
        address.setState("Cust State");
        address.setStreet1("Cust Street1");
        address.setZipcode("Zcode");

        Customer customer = em.find(Customer.class, 10);
        assertNotNull(customer);
        customer.setEmail("new Mail");
        customer.setAddress(null);
        em.merge(customer);
        System.out.println("Customer : " + customer);

        Orders orders = em.find(Orders.class, 10);
        assertNotNull(orders);
        orders.setBenfName("New Benf Name");
        orders.setAddress(address);
        em.merge(orders);
        System.out.println("Orders : " + orders);


    }

    @Test
    @Ignore
    public void testEmbeddableStrategyDelete() {

        Customer customer = em.find(Customer.class, 10);
        assertNotNull(customer);
        em.remove(customer);

        Orders orders = em.find(Orders.class, 10);
        assertNotNull(orders);
        em.remove(orders);

    }
}

Inheritance Mapping Stategies in JPA

In JPA, an entity class may inherit from another entity class its behavior and state. The behavior and state become shared between entity classes enabling the inheritance of existing mappings. There are several types of inheritance which are  Joined Strategy, Single-Table Strategy, Table-per-Concrete-Class Strategy. Two of these types; Single table inheritance and Joined table inheritance have many similarities:

When it comes to mapping inheritance, JPA supports three different strategies. When an entity hierarchy exists, it always has an entity as its root. The root entity class can define the inheritance strategy by using the @Inheritance annotation. If it doesn’t, the default single-table-per-class strategy will be applied.

Their object model is the same.
  • They can be configured using either annotations or XML.
  • A discriminator column (a single column used for distinguishing to which class type a database row belongs) is required on the database.
  • The underlying database structure of the single table and joined table inheritance is, however, slightly different.
please refer to each type link for further discussion, example and code snippts.

Joined Strategy

In the joined table inheritance, each class shares data from the root table. In addition, each subclass defines its own table that adds its extended state. The following example shows two child tables, EXTERNAT_VET and IN_HOUSE_VET, as well as parent table VET.
Click here for mode details about Joined Strategy

Single-Table Strategy

In the single table inheritance, the entire class hierarchy is represented by a single table. As the following example shows, the Three classes map to the same VET_ALL table.
Click here for mode details about Single Table Strategy


Table-per-Concrete-Class Strategy

In the table-per-concrete class, each Table contains the shared data as its specific data. In addition, each subclass defines its own table that adds its extended state. The following example shows two child tables, VET_IN and VET_OUT.
Click here for mode details about Table-per-Concrete Class Strategy

JPA Table-Per-Concrete Strategy

In the table-per-class (or table-per-concrete-class) strategy, each entity is mapped to its own dedicated table like the joined strategy. The difference is that all attributes of the root entity will also be mapped to columns of the child entity table. From a database point of view, this strategy de-normalizes the model and causes all root entity attributes to be redefined in the tables of all leaf entities that inherit from it. With the table-per-class strategy, there is no shared table, no shared columns, and no discriminator column. The only requirement is that all tables must share a common primary key that matches across all tables in the hierarchy.

In the table-per-concrete class, each Table contains the shared data as its specific data. In addition, each subclass defines its own table that adds its extended state. The following example shows two child tables, VET_IN and VET_OUT

TABLE CREATION:


VET_IN Table
CREATE TABLE VET_IN 
(
  VET_ID NUMBER NOT NULL 
, NAME VARCHAR2(45 BYTE) 
, QUALIFICATION VARCHAR2(45 BYTE) 
, SALARY NUMBER 
, CONSTRAINT VET_IN_PK PRIMARY KEY 
  (
    VET_ID 
  )
   ENABLE
);

VET_OUT Table
CREATE TABLE VET_OUT 
(
  VET_ID NUMBER NOT NULL 
, NAME VARCHAR2(45 BYTE) 
, COUNTRY VARCHAR2(45 BYTE) 
, VISITING_FEES NUMBER 
, QUALIFICATION VARCHAR2(45 BYTE) 
, CONSTRAINT VET_OUT_PK PRIMARY KEY 
  (
    VET_ID 
  )
   ENABLE
);

SEQUENCES AND TRIGGERS CREATION:


Table VET_IN Sequence and Trigger
CREATE SEQUENCE VET_IN_SEQ NOCACHE;

create or replace TRIGGER VET_IN_TRG 
BEFORE INSERT ON VET_IN 
FOR EACH ROW 
BEGIN
    IF :NEW.VET_ID IS NULL THEN
      SELECT VET_IN_SEQ.NEXTVAL INTO :NEW.VET_ID FROM DUAL;
    END IF;
END; 

Table VET_OUT Sequence and Trigger
CREATE SEQUENCE VET_OUT_SEQ NOCACHE;

create or replace TRIGGER VET_OUT_TRG 
BEFORE INSERT ON VET_OUT
FOR EACH ROW 
BEGIN
    IF :NEW.VET_ID IS NULL THEN
      SELECT VET_OUT_SEQ.NEXTVAL INTO :NEW.VET_ID FROM DUAL;
    END IF;
end; 

INSERT TEST DATA:


inserting into VET_IN
REM INSERTING into VET_IN
Insert into VET_IN (VET_ID,NAME,QUALIFICATION,SALARY) values (1,'Ashitraj more','mvsc',35000);
Insert into VET_IN (VET_ID,NAME,QUALIFICATION,SALARY) values (2,'Raj','bvsc',30000);
Insert into VET_IN (VET_ID,NAME,QUALIFICATION,SALARY) values (4,'Rakesh','mvsc',29000);

inserting into VET_OUT
REM INSERTING into VET_OUT
Insert into VET_OUT (VET_ID,NAME,COUNTRY,VISITING_FEES,QUALIFICATION) values (3,'Steven','UK',500,'mvsc');
Insert into VET_OUT (VET_ID,NAME,COUNTRY,VISITING_FEES,QUALIFICATION) values (5,'John','US',450,'mvsc');

CLASS CREATION:


ConcreteVet Class
@Entity
@Inheritance(strategy = InheritanceType.TABLE_PER_CLASS)
public abstract class ConcreteVet implements Serializable {
    private static final long serialVersionUID = 1L;
    
    @Id
    @Basic(optional = false)
    @Column(name = "VET_ID")
    private Integer vetId;
    @Column(name = "NAME")
    private String name;
    @Column(name = "QUALIFICATION")
    private String qualification;
    //generate getters, setters, toString(), hashCode(),equals()
}

ConcreteInVet Class
@Entity
@Table(name = "VET_IN")
public class ConcreteInVet extends ConcreteVet{
 
    @Column(name = "SALARY")
    private Integer salary;
    //generate getters, setters, toString(), hashCode(),equals()
}

ConcreteOutVet Class
@Entity
@Table(name = "VET_OUT")
public class ConcreteOutVet extends ConcreteVet{
    
    @Column(name = "COUNTRY")
    private String country;
    @Column(name = "VISITING_FEES")
    private Integer visitingFees;
    //generate getters, setters, toString(), hashCode(),equals()

}

JUNIT TEST CASE:

public class InheritanceJUnit {

    static EntityManagerFactory emf;
    static EntityManager em;
    static EntityTransaction trx;

    @BeforeClass
    public static void initEntityManager() throws Exception {
        emf = Persistence.createEntityManagerFactory("JavaApplicationJPAPU");
        em = emf.createEntityManager();
        trx = em.getTransaction();
    }

    @AfterClass
    public static void closeEntityManager() throws Exception {
        em.close();
        emf.close();
    }

    @Before
    public void initTransaction() throws Exception {
        trx.begin();
    }

    @After
    public void endTransaction() throws Exception {
        if (!trx.getRollbackOnly()) {
            trx.commit();
        }
    }
    @Test
    @Ignore
    public void testConcreteStrategyInsert() {

        ConcreteInVet inVet = new ConcreteInVet();
        inVet.setName("Invet name 10");
        inVet.setQualification("invet Qualification 10");
        inVet.setSalary(1010);
        inVet.setVetId(10);
        em.persist(inVet);
        System.out.println("InHouseVet inserted");

        ConcreteOutVet extVet = new ConcreteOutVet();
        extVet.setName("extVet name 11");
        extVet.setQualification("extVet Qualification 11");
        extVet.setCountry("xy");
        extVet.setVisitingFees(1111);
        extVet.setVetId(11);
        em.persist(extVet);
        System.out.println("ExternatVet inserted");
    }

    @Test
    @Ignore
    public void testConcreteStrategySelect() {

        ConcreteVet vet = em.find(ConcreteVet.class, 10);
        assertNotNull(vet);

        if (vet instanceof ConcreteInVet) {
            ConcreteInVet concreteInVet = (ConcreteInVet) vet;
            System.out.println(concreteInVet);
        } else if (vet instanceof ConcreteOutVet) {
            ConcreteOutVet concreteOutVet = (ConcreteOutVet) vet;
            System.out.println(concreteOutVet);
        } else {
            System.out.println("ERROR in Type");
        }

        ConcreteVet vet2 = em.find(ConcreteVet.class, 11);
        assertNotNull(vet2);

        if (vet2 instanceof ConcreteInVet) {
            ConcreteInVet concreteInVet = (ConcreteInVet) vet2;
            System.out.println(concreteInVet);
        } else if (vet2 instanceof ConcreteOutVet) {
            ConcreteOutVet concreteOutVet = (ConcreteOutVet) vet2;
            System.out.println(concreteOutVet);
        } else {
            System.out.println("ERROR in Type");
        }

    }

    @Test
    @Ignore
    public void testConcreteStrategyUpdate() {

        ConcreteVet vet = em.find(ConcreteVet.class, 10);
        assertNotNull(vet);

        if (vet instanceof ConcreteOutVet) {
            ConcreteOutVet concreteOutVet = (ConcreteOutVet) vet;
            concreteOutVet.setName("extVet Qualification 10 updated");
            concreteOutVet.setVisitingFees(101010);
            em.merge(concreteOutVet);
            System.out.println(concreteOutVet);
        } else if (vet instanceof ConcreteInVet) {
            ConcreteInVet concreteInVet = (ConcreteInVet) vet;
            concreteInVet.setName("Invet name 10 updated");
            concreteInVet.setSalary(1111);
            em.merge(concreteInVet);
            System.out.println(concreteInVet);
        } else {
            System.out.println("ERROR in Type");
        }

        ConcreteVet vet2 = em.find(ConcreteVet.class, 11);
        assertNotNull(vet2);

        if (vet2 instanceof ConcreteOutVet) {
            ConcreteOutVet concreteOutVet = (ConcreteOutVet) vet2;
            concreteOutVet.setName("extVet Qualification 11 updated");
            concreteOutVet.setVisitingFees(101010);
            em.merge(concreteOutVet);
            System.out.println(concreteOutVet);
        } else if (vet2 instanceof ConcreteInVet) {
            ConcreteInVet concreteInVet = (ConcreteInVet) vet2;
            concreteInVet.setName("extVet name 11 updated");
            concreteInVet.setSalary(1111);
            em.merge(concreteInVet);
            System.out.println(concreteInVet);
        } else {
            System.out.println("ERROR in Type");
        }


    }

    @Test
    @Ignore
    public void testConcreteStrategyDelete() {

        ConcreteVet vet = em.find(ConcreteVet.class, 10);
        assertNotNull(vet);
        em.remove(vet);
        System.out.println("InHouseVet 10 : deleteds");

        ConcreteVet vet2 = em.find(ConcreteVet.class, 11);
        assertNotNull(vet2);

        if (vet2 instanceof ConcreteOutVet) {
            ConcreteOutVet concreteOutVet = (ConcreteOutVet) vet2;
            em.remove(concreteOutVet);
            System.out.println("ExternatVet 11 : deleted");
        } else if (vet2 instanceof ConcreteInVet) {
            ConcreteInVet concreteInVet = (ConcreteInVet) vet2;
            em.remove(concreteInVet);
            System.out.println("InHouseVet 11 : deleteds");
        } else {
            System.out.println("ERROR in Type");
        }
    }
}


Sunday, August 18, 2013

JPA Single Table Strategy

The default inheritance mapping strategy is the single-table strategy, in which all the entities in the hierarchy are mapped to a single table. As it is the default, you can completely omit the @Inheritance annotation on the root entity (thanks to configuration by exception).
In the single table inheritance, the entire class hierarchy is represented by a single table. As the following example shows, the Three classes map to the same VET_ALL table

TABLE CREATION:

CREATE TABLE VET_ALL 
(
  VET_ID NUMBER NOT NULL 
, NAME VARCHAR2(45 BYTE) 
, QUALIFICATION VARCHAR2(45 BYTE) 
, SALARY NUMBER 
, COUNTRY VARCHAR2(45 BYTE) 
, VISITING_FEES NUMBER 
, VET_TYPE VARCHAR2(10 BYTE) 
, CONSTRAINT VET_ALL_PK PRIMARY KEY 
  (
    VET_ID 
  )
   ENABLE
);

SEQUENCES AND TRIGGERS CREATION:

CREATE SEQUENCE VET_ALL_SEQ NOCACHE;

create or replace TRIGGER VET_ALL_TRG 
BEFORE INSERT ON VET_ALL 
FOR EACH ROW 
BEGIN
    IF :NEW.VET_ID IS NULL THEN
      SELECT VET_ALL_SEQ.NEXTVAL INTO :NEW.VET_ID FROM DUAL;
    END IF;
END; 


INSERT TEST DATA:

REM INSERTING into VET_ALL
Insert into VET_ALL (VET_ID,NAME,QUALIFICATION,SALARY,COUNTRY,VISITING_FEES,VET_TYPE) values (1,'Ashitraj more','mvsc',35000,null,null,'IN_VET9');
Insert into VET_ALL (VET_ID,NAME,QUALIFICATION,SALARY,COUNTRY,VISITING_FEES,VET_TYPE) values (2,'Raj','bvsc',30000,null,null,'IN_VET');
Insert into VET_ALL (VET_ID,NAME,QUALIFICATION,SALARY,COUNTRY,VISITING_FEES,VET_TYPE) values (4,'Rakesh','mvsc',29000,null,null,'IN_VET');
Insert into VET_ALL (VET_ID,NAME,QUALIFICATION,SALARY,COUNTRY,VISITING_FEES,VET_TYPE) values (5,'John','mvsc',null,'US',450,'EXT_VET');
Insert into VET_ALL (VET_ID,NAME,QUALIFICATION,SALARY,COUNTRY,VISITING_FEES,VET_TYPE) values (3,'Steven','mvsc',null,'UK',500,'EXT_VET');

CLASS CREATION:


SingleVet Class
@Entity
@Table(name = "VET_ALL")
@Inheritance(strategy = InheritanceType.SINGLE_TABLE)
@DiscriminatorColumn(name = "VET_TYPE")
public abstract class SingleVet implements Serializable {
    
private static final long serialVersionUID = 1L;
    
@Id
@Basic(optional = false)
@Column(name = "VET_ID")
private Integer vetId;
@Column(name = "NAME")
private String name;
@Column(name = "QUALIFICATION")
private String qualification;
//generate getters, setters, toString(), hashCode(),equals()
}

SingleOutVet Class
@Entity
@DiscriminatorValue("EXT_VET")
public class SingleOutVet extends SingleVet{
    
@Column(name = "COUNTRY")
private String country;
@Column(name = "VISITING_FEES")
private Integer visitingFees;
//generate getters, setters, toString(), hashCode(),equals()
}

SingleInVet Class
@Entity
@DiscriminatorValue("IN_VET")
public class SingleInVet extends SingleVet{
 
@Column(name = "SALARY")
private Integer salary;
//generate getters, setters, toString(), hashCode(),equals()
}

JUNIT TEST CASE:

public class InheritanceJUnit {

    static EntityManagerFactory emf;
    static EntityManager em;
    static EntityTransaction trx;

    @BeforeClass
    public static void initEntityManager() throws Exception {
        emf = Persistence.createEntityManagerFactory("JavaApplicationJPAPU");
        em = emf.createEntityManager();
        trx = em.getTransaction();
    }

    @AfterClass
    public static void closeEntityManager() throws Exception {
        em.close();
        emf.close();
    }

    @Before
    public void initTransaction() throws Exception {
        trx.begin();
    }

    @After
    public void endTransaction() throws Exception {
        if (!trx.getRollbackOnly()) {
            trx.commit();
        }
    }
    
    @Test
    @Ignore
    public void testSingleStrategyInsert() {

        SingleInVet inVet = new SingleInVet();
        inVet.setName("Invet name 10");
        inVet.setQualification("invet Qualification 10");
        inVet.setSalary(1010);
        inVet.setVetId(10);
        em.persist(inVet);
        System.out.println("InHouseVet inserted");

        SingleOutVet extVet = new SingleOutVet();
        extVet.setName("extVet name 11");
        extVet.setQualification("extVet Qualification 11");
        extVet.setCountry("xy");
        extVet.setVisitingFees(1111);
        extVet.setVetId(11);
        em.persist(extVet);
        System.out.println("ExternatVet inserted");
    }

    @Test
    @Ignore
    public void testSingleStrategySelect() {
        SingleVet vet = em.find(SingleVet.class, 10);
        assertNotNull(vet);

        if (vet instanceof SingleOutVet) {
            SingleOutVet singleOutVet = (SingleOutVet) vet;
            System.out.println(singleOutVet);
        } else if (vet instanceof SingleInVet) {
            SingleInVet singleInVet = (SingleInVet) vet;
            System.out.println(singleInVet);
        } else {
            System.out.println("ERROR in Type");
        }

        SingleVet vet2 = em.find(SingleVet.class, 11);
        assertNotNull(vet2);

        if (vet2 instanceof SingleOutVet) {
            SingleOutVet singleOutVet = (SingleOutVet) vet2;
            System.out.println(singleOutVet);
        } else if (vet2 instanceof SingleInVet) {
            SingleInVet singleInVet = (SingleInVet) vet2;
            System.out.println(singleInVet);
        } else {
            System.out.println("ERROR in Type");
        }

    }

    @Test
    @Ignore
    public void testSingleStrategyUpdate() {

        SingleVet vet = em.find(SingleVet.class, 10);
        assertNotNull(vet);

        if (vet instanceof SingleOutVet) {
            SingleOutVet SingleOutVet = (SingleOutVet) vet;
            SingleOutVet.setName("extVet Qualification 11 updated");
            SingleOutVet.setVisitingFees(101010);
            em.merge(SingleOutVet);
            System.out.println(SingleOutVet);
        } else if (vet instanceof SingleInVet) {
            SingleInVet SingleInVet = (SingleInVet) vet;
            SingleInVet.setName("Invet name 10 updated");
            SingleInVet.setSalary(1010);
            em.merge(SingleInVet);
            System.out.println(SingleInVet);
        } else {
            System.out.println("ERROR in Type");
        }

        SingleVet vet2 = em.find(SingleVet.class, 11);
        assertNotNull(vet2);

        if (vet2 instanceof SingleOutVet) {
            SingleOutVet SingleOutVet = (SingleOutVet) vet2;
            SingleOutVet.setName("extVet Qualification 11 updated");
            SingleOutVet.setVisitingFees(111111);
            em.merge(SingleOutVet);
            System.out.println(SingleOutVet);
        } else if (vet2 instanceof SingleInVet) {
            SingleInVet SingleInVet = (SingleInVet) vet2;
            SingleInVet.setName("Invet name 11 updated");
            SingleInVet.setSalary(1111);
            em.merge(SingleInVet);
            System.out.println(SingleInVet);
        } else {
            System.out.println("ERROR in Type");
        }
    }

    @Test
    @Ignore
    public void testSingleStrategyDelete() {

        SingleVet vet = em.find(SingleVet.class, 10);
        assertNotNull(vet);
        em.remove(vet);
        System.out.println("InHouseVet 10 : deleteds");

        SingleVet vet2 = em.find(SingleVet.class, 11);
        assertNotNull(vet2);
        if (vet2 instanceof SingleOutVet) {
            SingleOutVet singleOutVet = (SingleOutVet) vet2;
            em.remove(singleOutVet);
            System.out.println("ExternatVet 11 : deleted");
        } else if (vet2 instanceof SingleInVet) {
            SingleInVet singleInVet = (SingleInVet) vet2;
            em.remove(singleInVet);
            System.out.println("InHouseVet 11 : deleteds");
        } else {
            System.out.println("ERROR in Type");
        }
    }

}

JPA Joined Strategy

In the joined strategy, each entity in the hierarchy is mapped to its own table. The root entity maps to a table that defines the primary key to be used by all tables in the hierarchy, as well as the discriminator column. Each subclass is represented by a separate table that contains its own attributes (not inherited from the root class) and a primary key that refers to the root table’s primary key. The non-root tables do not hold a discriminator column.

In the joined table inheritance, each class shares data from the root table. In addition, each subclass defines its own table that adds its extended state. The following example shows two child tables, EXTERNAT_VET and IN_HOUSE_VET, as well as parent table VET:


Table Creation:


VET table
CREATE TABLE VET 
(
  VET_ID NUMBER NOT NULL 
, NAME VARCHAR2(45 BYTE) 
, QUALIFICATION VARCHAR2(45 BYTE) 
, VET_TYPE VARCHAR2(10 BYTE) 
, CONSTRAINT VET_PK PRIMARY KEY 
  (
    VET_ID 
  )
   ENABLE
);

EXTERNAT_VET  table
CREATE TABLE EXTERNAT_VET 
(
  VET_ID NUMBER NOT NULL 
, COUNTRY VARCHAR2(45 BYTE) 
, VISITING_FEES NUMBER 
, CONSTRAINT EXTERNAT_VET_PK PRIMARY KEY 
  (
    VET_ID 
  )
  ENABLE
);

IN_HOUSE_VET table
CREATE TABLE IN_HOUSE_VET 
(
  VET_ID NUMBER NOT NULL 
, SALARY NUMBER 
, CONSTRAINT IN_HOUSE_VET_PK PRIMARY KEY 
  (
    VET_ID 
  )
  ENABLE
);

Sequences and Triggers Creation:

CREATE SEQUENCE VET_SEQ NOCACHE;

create or replace TRIGGER VET_TRG 
BEFORE INSERT ON VET 
FOR EACH ROW 
BEGIN
    IF :NEW.VET_ID IS NULL THEN
      SELECT VET_SEQ.NEXTVAL INTO :NEW.VET_ID FROM DUAL;
    END IF;
END;
/

Insert Test Data:

VET table
REM INSERTING into VET
Insert into VET (VET_ID,NAME,QUALIFICATION,VET_TYPE) values (1,'Ashitraj more','mvsc','IN_VET');
Insert into VET (VET_ID,NAME,QUALIFICATION,VET_TYPE) values (2,'Raj','bvsc','IN_VET');
Insert into VET (VET_ID,NAME,QUALIFICATION,VET_TYPE) values (3,'Steven','mvsc','EXT_VET');
Insert into VET (VET_ID,NAME,QUALIFICATION,VET_TYPE) values (4,'Rakesh','mvsc','IN_VET');
Insert into VET (VET_ID,NAME,QUALIFICATION,VET_TYPE) values (5,'John','mvsc','EXT_VET');
Insert into VET (VET_ID,NAME,QUALIFICATION,VET_TYPE) values (6,'vet','vet qualification','VET');

EXTERNAT_VET  table
REM INSERTING into EXTERNAT_VET
Insert into EXTERNAT_VET (VET_ID,COUNTRY,VISITING_FEES) values (3,'UK',500);
Insert into EXTERNAT_VET (VET_ID,COUNTRY,VISITING_FEES) values (5,'US',450);

IN_HOUSE_VET table
REM INSERTING into IN_HOUSE_VET
Insert into IN_HOUSE_VET (VET_ID,SALARY) values (1,35000);
Insert into IN_HOUSE_VET (VET_ID,SALARY) values (2,30000);
Insert into IN_HOUSE_VET (VET_ID,SALARY) values (3,29000);

Class Creation:


VET Class
@Entity
@Table(name = "VET")
@Inheritance(strategy = InheritanceType.JOINED)
@DiscriminatorColumn(name = "VET_TYPE")
@DiscriminatorValue("VET")
public class Vet implements Serializable {
    private static final long serialVersionUID = 1L;
    
    @Id
    @Basic(optional = false)
    @Column(name = "VET_ID")
    private Integer vetId;
    @Column(name = "NAME")
    private String name;
    @Column(name = "QUALIFICATION")
    private String qualification;
    //generate getters, setters, toString(), hashCode(),equals()
}

EXTERNAT_VET Class
@Entity
@Table(name = "EXTERNAT_VET")
@DiscriminatorValue("EXT_VIT")
public class ExternatVet extends Vet{
    
    @Column(name = "COUNTRY")
    private String country;
    @Column(name = "VISITING_FEES")
    private Integer visitingFees;
    //generate getters, setters, toString(), hashCode(),equals()
}

IN_HOUSE_VET Class
@Entity
@Table(name = "IN_HOUSE_VET")
@DiscriminatorValue("IN_VET")
public class InHouseVet extends Vet{
 
    @Column(name = "SALARY")
    private Integer salary;
    //generate getters, setters, toString(), hashCode(),equals()
}

JUnit Test Case:

public class InheritanceJUnit {

    static EntityManagerFactory emf;
    static EntityManager em;
    static EntityTransaction trx;

    @BeforeClass
    public static void initEntityManager() throws Exception {
        emf = Persistence.createEntityManagerFactory("JavaApplicationJPAPU");
        em = emf.createEntityManager();
        trx = em.getTransaction();
    }

    @AfterClass
    public static void closeEntityManager() throws Exception {
        em.close();
        emf.close();
    }

    @Before
    public void initTransaction() throws Exception {
        trx.begin();
    }

    @After
    public void endTransaction() throws Exception {
        if (!trx.getRollbackOnly()) {
            trx.commit();
        }
    }

    @Test
    @Ignore
    public void testJoinedStrategyInsert() {

        InHouseVet inVet = new InHouseVet();
        inVet.setName("Invet name 10");
        inVet.setQualification("invet Qualification 10");
        inVet.setSalary(1010);
        inVet.setVetId(10);
        em.persist(inVet);
        System.out.println("InHouseVet inserted");

        ExternatVet extVet = new ExternatVet();
        extVet.setName("extVet name 11");
        extVet.setQualification("extVet Qualification 11");
        extVet.setCountry("xy");
        extVet.setVisitingFees(1111);
        extVet.setVetId(11);
        em.persist(extVet);
        System.out.println("ExternatVet inserted");
    }

    @Test
    @Ignore
    public void testJoinedStrategySelect() {

        Vet vet = em.find(Vet.class, 10);
        assertNotNull(vet);

        if (vet instanceof ExternatVet) {
            ExternatVet externatVet = (ExternatVet) vet;
            System.out.println(externatVet);
        } else if (vet instanceof InHouseVet) {
            InHouseVet inHouseVet = (InHouseVet) vet;
            System.out.println(inHouseVet);
        } else {
            System.out.println("ERROR in Type");
        }

        Vet vet2 = em.find(Vet.class, 11);
        assertNotNull(vet2);

        if (vet2 instanceof ExternatVet) {
            ExternatVet externatVet = (ExternatVet) vet2;
            System.out.println(externatVet);
        } else if (vet2 instanceof InHouseVet) {
            InHouseVet inHouseVet = (InHouseVet) vet2;
            System.out.println(inHouseVet);
        } else {
            System.out.println("ERROR in Type");
        }

        Vet vet = em.find(Vet.class, 6);
        assertNotNull(vet);

        if (vet instanceof ExternatVet) {
            ExternatVet externatVet = (ExternatVet) vet;
            System.out.println(externatVet);
        } else if (vet instanceof InHouseVet) {
            InHouseVet inHouseVet = (InHouseVet) vet;
            System.out.println(inHouseVet);
        } else if (vet instanceof Vet) {
            System.out.println(vet);
        } else {
            System.out.println("ERROR in Type");
        }
    }

    @Test
    @Ignore
    public void testJoinedStrategyUpdate() {

        Vet vet = em.find(Vet.class, 10);
        assertNotNull(vet);

        if (vet instanceof ExternatVet) {
            ExternatVet externatVet = (ExternatVet) vet;
            externatVet.setName("extVet Qualification 11 updated");
            externatVet.setVisitingFees(101010);
            em.merge(externatVet);
            System.out.println(externatVet);
        } else if (vet instanceof InHouseVet) {
            InHouseVet inHouseVet = (InHouseVet) vet;
            inHouseVet.setName("Invet name 10 updated");
            inHouseVet.setSalary(1010);
            em.merge(inHouseVet);
            System.out.println(inHouseVet);
        } else {
            System.out.println("ERROR in Type");
        }

        Vet vet2 = em.find(Vet.class, 11);
        assertNotNull(vet2);

        if (vet2 instanceof ExternatVet) {
            ExternatVet externatVet = (ExternatVet) vet2;
            externatVet.setName("extVet Qualification 11 updated");
            externatVet.setVisitingFees(111111);
            em.merge(externatVet);
            System.out.println(externatVet);
        } else if (vet2 instanceof InHouseVet) {
            InHouseVet inHouseVet = (InHouseVet) vet2;
            inHouseVet.setName("Invet name 11 updated");
            inHouseVet.setSalary(1111);
            em.merge(inHouseVet);
            System.out.println(inHouseVet);
        } else {
            System.out.println("ERROR in Type");
        }
    }

    @Test
    @Ignore
    public void testJoinedStrategyDelete() {

        Vet vet = em.find(Vet.class, 10);
        assertNotNull(vet);
        em.remove(vet);
        System.out.println("InHouseVet 10 : deleteds");

        Vet vet2 = em.find(Vet.class, 11);
        assertNotNull(vet2);

        if (vet2 instanceof ExternatVet) {
            ExternatVet externatVet = (ExternatVet) vet2;
            em.remove(externatVet);
            System.out.println("ExternatVet 11 : deleted");
        } else if (vet2 instanceof InHouseVet) {
            InHouseVet inHouseVet = (InHouseVet) vet2;
            em.remove(inHouseVet);
            System.out.println("InHouseVet 11 : deleteds");
        } else {
            System.out.println("ERROR in Type");
        }
    }

}

Tuesday, August 13, 2013

UML Use Case Diagram

Purpose:

The purpose of use case diagram is to capture the dynamic aspect of a system. But this definition is too generic to describe the purpose. Use case diagrams are used to gather the requirements of a system including internal and external influences. These requirements are mostly design requirements. So when a system is analyzed to gather its functionalities use cases are prepared and actors are identified.

The purposes of use case diagrams can be as follows:
  • Used to gather requirements of a system.
  • Used to get an outside view of a system.
  • Identify external and internal factors influencing the system.
  • Show the interacting among the requirements are actors.
How to draw Use Case Diagram?
Use case diagrams are considered for high level requirement analysis of a system. So when the requirements of a system are analyzed the functionalities are captured in use cases. So we can say that uses cases are nothing but the system functionalities written in an organized manner. Now the second things which are relevant to the use cases are the actors. Actors can be defined as something that interacts with the system.

The actors can be human user, some internal applications or may be some external applications. So in a brief when we are planning to draw an use case diagram we should have the following items identified:
  • Functionalities to be represented as an use case
  • Actors
  • Relationships among the use cases and actors.

Use case diagrams are drawn to capture the functional requirements of a system. So after identifying the above items we have to follow the following guidelines to draw an efficient use case diagram.

The name of a use case is very important. So the name should be chosen in such a way so that it can identify the functionality performed.
  • Give a suitable name for actors.
  • Show relationships and dependencies clearly in the diagram.
  • Do not try to include all types of relationships. Because the main purpose of the diagram is to identify requirements.
  • Use note when ever required to clarify some important points.

These diagrams are used at a very high level of design. Then this high level design is refined again and again to get a complete and practical picture of the system. A well structured use case also describes the pre condition, post condition, exceptions. And these extra elements are used to make test cases when performing the testing.

The following are the places where use case diagrams are used:

  • Requirement analysis and high level design.
  • Model the context of a system.
  • Reverse engineering.
  • Forward engineering.



For example an online reservation system use case diagram had been introduced with the system boundaries separated by rectangular box, including two actors: the primary actor which is Customer can make all those use cases (Search flight use case, Make a reservation use case, Purchase a ticket use case, Check flight status use case, Cancel flight use case), other actor which is system actor Payment Process use a Validate credit card use case. This diagram show an extend relationship between Reschedule flight use case which extends Cancel flight use case and the Select seat use case which extends Purchase a ticket use case. A dependency relationship between Purchase a ticket Invoking use case include a Validate credit card Included use case

The following topics describe the relationships that you can use in use case diagrams:

Association relationships
In UML models, an association is a relationship between two classifiers, such as classes or use cases, that describes the reasons for the relationship and the rules that govern the relationship.
Generalization relationships
In UML modeling, a generalization relationship is a relationship in which one model element (the child) is based on another model element (the parent). Generalization relationships are used in class, component, deployment, and use case diagrams.
Include relationships
In UML modeling, an include relationship is a relationship in which one use case (the base use case) includes the functionality of another use case (the inclusion use case). The include relationship supports the reuse of functionality in a use case model.
Extend relationships
In UML modeling, you can use an extend relationship to specify that one use case (extension) extends the behavior of another use case (base). This type of relationship reveals details about a system or application that are typically hidden in a use case.

a good diagram i find while googling