UPDATE QUERY WITH TABLE JOINS IN ORACLE

We can write this query as an example given follows
Update(Select location loc, city city from Employee emp, Address addr where emp.empid = addr.empId and emp.location is null) SET loc = city

Barcode on Oracle Reports

http://www.ozgrid.com/Services/oracle-reports-barcode.htm

Digit only in oracle

FUNCTION DIGIONLY(VAL VARCHAR) RETURN BOOLEAN IS
incre number(10) := 0;BEGIN --loop for checking the every elment in of text field enter by user. LOOP incre := incre + 1; IF substr(trim(VAL),incre,1) not in ('0','1','2','3','4','5','6','7','8','9') then message('Please enter numeric value'); return false; end if; EXIT When LENGTH(trim(VAL)) <= incre; END loop;
return true; EXCEPTION WHEN OTHERS THEN message('Please enter numeric digit only value'); RETURN FALSE; END;

Secure Web.Show_Document() calls to Oracle Reports

http://www.oracle.com/technology/products/forms/pdf/10gr2/frmwebshowdoc_rep_10gr2.pdf

ANY SOME AND ALL in Oracle

ANY SOME AND ALL

ANY or SOME:

Compares a value to each value in a list or returned by a query. Must be preceded by =, !=, >, <, <=, >=. Evaluates to FALSE if the query returns no rows.

Using Any

select ename, sal from emp where sal > any (1600, 2999);
Alternatively

select ename, sal from emp where sal > 1600 or sal > 2999;

ALL:

Compares a value to every value in a list or returned by a query. Must be preceded by =, !=, >, <, <=, >=. Evaluates to TRUE if the query returns no rows.

Using All

select ename, sal from emp where sal > all (1600, 2999);

Alternatively

select ename, sal from emp where sal > 1600 and sal > 2999;

DECODE VS CASE in SQL

DECODE
It used in select state for the comparing values return by select statement.

Case
It is work look like Decode but it brought some power feature.
1. DECODE only work with select statement while CASE work in select and function/procedure for as passing parameters.
2. DECODE is poor in performance while CASE is efficient.
3. DECODE can't include any other select statement in it body! While CASE can do it.

IN VS EXIST IN ORACLE

1.
To determine which clause offers better performance in rule-based optimization, consider how many rows the inner query will return in comparison to the outer query. In many cases, EXISTS is better because it requires you to specify a join condition, which can invoke an INDEX scan. However, IN is often better if the results of the subquery are very small. You usually want to run the query that returns the smaller set of results first.
2.
The IN and EXISTS are logically the same. The IN clause compares values returned by the subquery and filters out rows in the outer query; the EXISTS clause compares values and filters out rows inside the subquery. In the case of NULL values, the resulting set of rows is the same.