Use of NOCOPY with Oracle Function and Procedures.

• NOCOPY will instruct Oracle to pass the argument as fast as possible. This can significantly enhance performance when passing a large value.

• Ussually IN paramter is pass by value and OUT/IN OUT are pass by refererence....

If one wants to send the IN paramter too as pass by reference he could add NOCOPY
parameter....

advantage is always less memory usage

disadvantage is when there is a change happened to the Reffered value and a rollback occurs in the procedure the change of value would be retained.... it could not be rolled back....

so I beleive that it is always safe to work without the NOCOPY parameter.....

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.