MVC Architecture

•  MODEL
•  VIEW
•  CONTROLLER

How the start with CONNECT BY clause in Oracle works

This page explains the hierarchical query using the (start with) connect by clause in an Oracle SQL Query.

If a table contains hierarchical data, then you can select rows in a hierarchical order using the hierarchical query clause (START WITH condition1) CONNECT BY condition2 

The START WITH clause is optional and specifies the rows athat are the root(s) of the hierarchical query. If you omit this clause, then Oracle uses all rows in the table as root rows. The START WITH condition can contain a subquery, but it cannot contain a scalar subquery expression. 

The CONNECT BY clause specifies the relationship between parent rows and child rows of the hierarchy. The connect_by_condition can be any condition, however, it must use the PRIOR operator to refer to the parent row. Restriction on the CONNECT BY clause: The connect_by_condition cannot contain a regular subquery or a scalar subquery expression. 

The PRIOR operator to refer to the parent row can be used as following: 

Sample query on the employees table:

SELECT employee_id, last_name, manager_id, LEVEL
   FROM employees
   CONNECT BY PRIOR employee_id = manager_id;

shows employee_id, last_name, manager_id and level in the tree for the employee hierarchy. 

In Oracle 9i a new feature for hierarchical queries is added: sys_connect_by_path It returns the path of a column value from root to node, with column values separated by char for each row returned by CONNECT BY condition. Both column and char can be any of the datatypes CHAR, VARCHAR2, NCHAR, or NVARCHAR2. The string returned is of VARCHAR2 datatype and is in the same character set as column. 
Examples
The following example returns the path of employee names from employee Kochhar to all employees of Kochhar (and their employees): 

SELECT LPAD(' ', 2*level-1)|| SYS_CONNECT_BY_PATH(last_name, '/') "Path"
   FROM employees
   CONNECT BY PRIOR employee_id = manager_id;

In Oracle 10 3 new features for hierarchical queries are added:

connect_by_root

CONNECT_BY_ROOT is a unary operator that is valid only in hierarchical queries. When you qualify a column with this operator, Oracle returns the column value using data from the root row. This operator extends the functionality of the CONNECT BY [PRIOR] condition of hierarchical queries. Restriction on CONNECT_BY_ROOT: You cannot specify this operator in the START WITH condition or the CONNECT BY condition. Example query:

SELECT last_name "Employee", CONNECT_BY_ROOT last_name "Manager",
   LEVEL-1 "Pathlen", SYS_CONNECT_BY_PATH(last_name, '/') "Path"
   FROM employees
   WHERE LEVEL > 1 and department_id = 110
   CONNECT BY PRIOR employee_id = manager_id;

connect_by_isleaf

The CONNECT_BY_ISLEAF pseudocolumn returns 1 if the current row is a leaf of the tree defined by the CONNECT BY condition. Otherwise it returns 0. This information indicates whether a given row can be further expanded to show more of the hierarchy. Example

SELECT employee_id, last_name, manager_id, connect_by_isleaf "IsLeaf"
   FROM employees
   START WITH last_name = 'King'
   CONNECT BY PRIOR employee_id = manager_id;

connect_by_iscycle

The CONNECT_BY_ISCYCLE pseudocolumn returns 1 if the current row has a child which is also its ancestor. Otherwise it returns 0. 
You can specify CONNECT_BY_ISCYCLE only if you have specified the NOCYCLE parameter of the CONNECT BY clause. NOCYCLE enables Oracle to return the results of a query that would otherwise fail because of a CONNECT BY loop in the data.

Oracle String Functions

Function	Description
ASCII	Returns the number code that represents the specified character
ASCIISTR	Converts a string in any character set to an ASCII string using the database character set
CHR	Returns the character based on the number code
COMPOSE	Returns a Unicode string
CONCAT	Allows you to concatenate two strings together
Concat with ||	Allows you to concatenate two or more strings together
CONVERT	Converts a string from one character set to another
DECOMPOSE	Accepts a string and returns a Unicode string
DUMP	Returns a varchar2 value that includes the datatype code, the length in bytes, and the internal representation of the expression
INITCAP	Sets the first character in each word to uppercase and the rest to lowercase
INSTR	Returns the location of a substring in a string
INSTR2	Returns the location of a substring in a string, using UCS2 code points
INSTR4	Returns the location of a substring in a string, using UCS4 code points
INSTRB	Returns the location of a substring in a string, using bytes instead of characters
INSTRC	Returns the location of a substring in a string, using Unicode complete characters
LENGTH	Returns the length of the specified string
LENGTH2	Returns the length of the specified string, using UCS2 code points
LENGTH4	Returns the length of the specified string, using UCS4 code points
LENGTHB	Returns the length of the specified string, using bytes instead of characters
LENGTHC	Returns the length of the specified string, using Unicode complete of characters
LOWER	Converts all letters in the specified string to lowercase
LPAD	Pads the left-side of a string with a specific set of characters
LTRIM	Removes all specified characters from the left-hand side of a string
NCHR	Returns the character based on the number code in the national character set
REGEXP_INSTR	Returns the location of a regular expression pattern in a string
REGEXP_REPLACE	Allows you to replace a sequence of characters in a string with another set of characters using regular expression pattern matching
REGEXP_SUBSTR	Allows you to extract a substring from a string using regular expression pattern matching
REPLACE	Replaces a sequence of characters in a string with another set of characters
RPAD	Pads the right-side of a string with a specific set of characters
RTRIM	Removes all specified characters from the right-hand side of a string
SOUNDEX	Returns a phonetic representation (the way it sounds) of a string
SUBSTR	Allows you to extract a substring from a string
TRANSLATE	Replaces a sequence of characters in a string with another set of characters
TRIM	Removes all specified characters either from the beginning or the end of a string
UPPER	Converts all letters in the specified string to uppercase
VSIZE	Returns the number of bytes in the internal representation of an expression

Sequence of triggers firing in oracle forms

Trigger Firing sequence:

Order as follows

First Logon Triggers are fired1.PRE-LOGON
2.ON-LOGON
3.POST-LOGON

After that Pre Triggers4. PRE-FORM
5. PRE-BLOCK
6. PRE-TEXT

After that WHEN-NEW Triggers
7. WHEN-NEW-FORM-INSTANCE
8. WHEN-NEW-BLOCK-INSTANCE
9. WHEN-NEW-ITEM-INSTANCE

After that ITEM Level TriggersAfter this focus is on the first item of the Block. If you type some data and press the tab key following trigger will fire in sequence

10.KEY-NEXT-ITEM (This trigger is present on the item level).

11.POST-CHANGE (This trigger is present on the item level).

12.WHEN-VALIDATE-ITEM (This trigger is present on the item level).

13.POST-TEXT-ITEM (This trigger is present on the item level).

14.WHEN-NEW-ITEM-INSTANCE (Block Level Trigger).

Now focus will go the next item present on the form.
If there are no further items present on the screen them if we enter data and press tab key then only KEY-NEXT-ITEM is fired. Now suppose we close the form then the item level triggers are fired.

POST TRIGGERS

15.POST-BLOCK
16.POST-FORM

What is a Sub Query? Describe its Types?

A sub query is a SELECT statement that is embedded in a clause of another SELECT statement. Sub query can be placed in WHERE, HAVING and FROM clause.

Guidelines for using sub queries:

1. Enclose sub queries within parenthesis
2. Place sub queries on the right side of the comparison condition.
3. Use Single-row operators with single-row sub queries and Multiple-row operators with multiple-row sub queries.

Types of sub queries:

1. Single-Row Sub query: Queries that return only one row from the Inner select statement. Single-row comparison operators are: =, >, >=, <, <=, <>
2. 
   
3. Multiple-Row Sub query: Queries that return more than one row from the inner Select statement. There are also multiple-column sub queries that return more than one column from the inner select statement. Operators includes: IN, ANY, ALL.

PL SQL Vs SQL

	PL Sql vs Sql
Comparison	SQL	PL/SQL
Execution	Single command at a time	Block of code
Application	Source of data to be displayed	Application created by data aquired by SQL
Structures include	DDL and DML based queries and commands	Includes procedures, functions, etc
Recommended while	Performing CRUD operations on data	Creating applications to display data obtained using sql
Compatibility with each other	SQL can be embedded into PL/SQL	PL/SQL cant be embedded in SQL

Materialized Views in Oracle

A materialized view in Oracle is a database object that contains the results of a query. They are local copies of data located remotely, or are used to create summary tables based on aggregations of a table's data. Materialized views, which store data based on remote tables are also, know as snapshots.

A materialized view can query tables, views, and other materialized views. Collectively these are called master tables (a replication term) or detail tables (a data warehouse term).

For replication purposes, materialized views allow you to maintain copies of remote data on your local node. These copies are read-only. If you want to update the local copies, you have to use the Advanced Replication feature. You can select data from a materialized view as you would from a table or view.

For data warehousing purposes, the materialized views commonly created are aggregate views, single-table aggregate views, and join views.

In this article, we shall see how to create a Materialized View in Oracle and discuss Refresh Option of the view.

In replication environments, the materialized views commonly created are primary key, rowid, and subquery materialized views.

Primary Key Materialized Views

The following statement creates the primary-key materialized view on the table emp located on a remote database.

SQL>  CREATE MATERIALIZED VIEW mv_emp_pk
    REFRESH FAST START WITH SYSDATE 
 NEXT  SYSDATE + 1/48
 WITH PRIMARY KEY 
 AS SELECT * FROM emp@remote_db;

Materialized view created.

Note: When you create a materialized view using the FAST option you will need to create a view log on the master tables(s) as shown below:

SQL> CREATE MATERIALIZED VIEW LOG ON emp;
Materialized view log created.

Rowid Materialized Views

The following statement creates the rowid materialized view on table emp located on a remote database:

SQL>  CREATE MATERIALIZED VIEW mv_emp_rowid 
 REFRESH WITH ROWID 
    AS SELECT * FROM emp@remote_db; 

Materialized view log created.

Subquery Materialized Views

The following statement creates a subquery materialized view based on the emp and dept tables located on the remote database:

SQL> CREATE MATERIALIZED VIEW  mv_empdept
AS SELECT * FROM emp@remote_db e
WHERE EXISTS
     (SELECT * FROM dept@remote_db d
     WHERE e.dept_no = d.dept_no)

REFRESH CLAUSE
[refresh [fast|complete|force]
 [on demand | commit]
 [start with date] [next date]
 [with {primary key|rowid}]]


The refresh option specifies:

The refresh method used by Oracle to refresh data in materialized view
Whether the view is primary key based or row-id based
The time and interval at which the view is to be refreshed


Refresh Method - FAST Clause

The FAST refreshes use the materialized view logs (as seen above) to send the rows that have changed from master tables to the materialized view.

You should create a materialized view log for the master tables if you specify the REFRESH FAST clause.
SQL> CREATE MATERIALIZED VIEW LOG ON emp;

Materialized view log created.





Materialized views are not eligible for fast refresh if the defined subquery contains an analytic function.

Refresh Method - COMPLETE Clause

The complete refresh re-creates the entire materialized view. If you request a complete refresh, Oracle performs a complete refresh even if a fast refresh is possible.

Refresh Method - FORCE Clause

When you specify a FORCE clause, Oracle will perform a fast refresh if one is possible or a complete refresh otherwise. If you do not specify a refresh method (FAST, COMPLETE, or FORCE), FORCE is the default.

PRIMARY KEY and ROWID Clause




WITH PRIMARY KEY is used to create a primary key materialized view i.e. the materialized view is based on the primary key of the master table instead of ROWID (for ROWID clause). PRIMARY KEY is the default option. To use the PRIMARY KEY clause you should have defined PRIMARY KEY on the master table or else you should use ROWID based materialized views.

Primary key materialized views allow materialized view master tables to be reorganized without affecting the eligibility of the materialized view for fast refresh.

Rowid materialized views should have a single master table and cannot contain any of the following:

Distinct or aggregate functions
GROUP BY Subqueries , Joins & Set operations


Timing the refresh

The START WITH clause tells the database when to perform the first replication from the master table to the local base table. It should evaluate to a future point in time. The NEXT clause specifies the interval between refreshes
SQL>  CREATE MATERIALIZED VIEW mv_emp_pk
    REFRESH FAST 
 START WITH SYSDATE 
 NEXT  SYSDATE + 2
 WITH PRIMARY KEY 
 AS SELECT * FROM emp@remote_db;

Materialized view created.


In the above example, the first copy of the materialized view is made at SYSDATE and the interval at which the refresh has to be performed is every two days.

Summary

Materialized Views thus offer us flexibility of basing a view on Primary key or ROWID, specifying refresh methods and specifying time of automatic refreshes.

Difference Among Count(column_name), Count(*) and Count(1)

Below are the differences Among Count(column_name), Count(*) and Count(1).


Count(*) and Count(1) will give you total number of records in the table but Count(column_name) will give you NOT NULL records for the particular column selected .


For Example:-

I have a table called Employee, It has 3 columns and the data below.

Emp_name          Emp_id                Dept_name

Ram                         1                          IT
Shyaam                    2                          Sales
Seeta                        3                          IT
Geeta                       4                     


In the above table structure.

Scenario 1:-

Select count(*)
from employee;

select count(1)
from employee;

Select count(emp_id)
from employee;


results :-  4, 4 , 4

All the above queries will give same result.


Scenario 2:-

Select count(*)
from employee;

select count(1)
from employee;

Select count(dept_name)
from employee;


results :-  4, 4, 3

Count(1) and Count(*) will give you same result i.e. 4 but count(dept_name) will give you 3 as it counts only Not NULL values.

Hope this is clear now.