How to Develop Oracle Form 10g

Forms Definition

Forms used for presenting and manipulating data can be developed. It is GUI used for developing client server database application.

.FMB Form Module Binary

.FMT Form Module Text

.FMX Form Module Executable

COMPONENTS OF FORMS

1.Form Builder

It is used to create a form. The design and layout of data entry screens the creations of event driven PL/SQL code used for data validation and navigate can be done via form builder.

2.Form Compiler

It is required to compile the file created in form builder and create a binary file, which can be executable form runtime.

3.Form Runtime

It is used to run the complied code created by forms compiler.

COMPONENTS OF FORM BUILDER

1. Object Navigator

It is hierarchical browsing and editing interface that enables you locate and manipulate application objects quickly and easily.

2.Property Palette

It is used set and modify the properties for all objects in form modules.

3.Layout Editor

It is graphical design facility for creating and arranging interface items and graphical objects in your application.

4.PL / SQL Editor

It is the integrated functionality of oracle procedure builder that exists with in form builder. It provides:

Development of Trigger, Procedures, Functions and Packages

Development of libraries to hold PL/SQL program unit.

FORM MODULE TYPES

1.Form Module

It is a collection of objectives such as block, canvas, items and event based PL/SQL code blocks called trigger .

2.Menu Module

It is a collection of menu items. It can be main menu or sub menu.

3.PL / SQL Libraries

The library module is a collection of PL/SQL function and package stored ion a single library file. This library file is the attached to form / menu modules. All other objects in the form or menu can now access share the collection of PL/SQL functions and procedures.

4.Object Libraries

It is a collection of form objects that you can use in other modules. You can create it to store, maintain and distribute standard objects that can be reuse across the entire development organization.

5. Object Group (Form Builder)

An object group is a container for a group of objects. You define an object group when you want to package related objects so you can copy or subclass them in another module.

OBJECTS OF FORMS

1.Blocks

Block is logical owner of items. It provides a mechanism for grouping related items into a functional unit for storing, displaying and manipulating records.

2.Items

These are interface objects that present data values to the user or enable the user to interact with the form.

3. Canvas

A canvas is the background object upon which interface items appear.

4. Frames

Frames are used to arrange items with in a block.

5. Windows

Windows contains for all visual objects that make up a form builder application.

6. PL/SQL Code Block

It is used for event driven code. That code automatically executes when a specific event occurs.

SET_FORM_PROPERTY

Sets a property of the given form.

Syntax:

SET_FORM_PROPERTY( formmodule_id, property, value);

SET_FORM_PROPERTY( formmodule_name, property, value);

BLOCKS

Block is logical owner of items. It provides a mechanism for grouping related items into a functional unit for storing, displaying and manipulating records.

Types of Blocks

1. Data Blocks

Data blocks are associated with data (table columns) within a database. By default, the association between a data block and the database allows operators to automatically query, update, insert, and delete rows within a database. Data blocks can be based on database tables, views, procedures, or transactional triggers.

2. Control Blocks

A control block is not associated with the database, and the items in a control block do not relate to table columns within a database. All blocks are either single-record or multi-record blocks:

A single-record block displays one record at a time.

A multi-record block displays more than one record at a time.

In addition, a data block can also be a master or detail block:

Master block displays a master record associated with detail records displayed in a detail block.

A detail block displays detail records associated with a master record displayed in master block.

MASTER-DETAIL RELATIONSHIP

A master-detail relationship is an association between two data blocks that reflects a primary foreign key relationship between the database tables on which the two data blocks are based.

The master data block is based on the table with the primary key, and the detail data block is based on the table with the foreign key. A master-detail relationship equates to the one-to-many relationship in the entity relationship diagram. A Detail Block Can Be a Master. You can create block relationships in which the detail of one master-detail link is the master for another link.

What Is a Relation?

A relation is a Form Builder object that handles the relationship between two associated blocks.

You can create a relation either:

• Implicitly with a master-detail form module

• Explicitly in the Object Navigator

Implicit Relations

When you create a master-detail form module, a relation is automatically created. This relation

is named masterblock_detailblock, for example, S_ORD_S_ITEM.

Explicit Relations

If a relation is not established when default blocks are created, you can create your own by setting the properties in the New Relation dialog box. Like implicitly created relations, PL/SQL program units and triggers are created automatically when you explicitly create a relation.

Master Deletes

You can prevent, propagate, or isolate deletion of a record in a master block when corresponding records exist in the detail block by setting the Master Deletes property. For example, you can delete all corresponding line items when an order is deleted.

Property Use

Non-Isolated

Prevents the deletion of the master record when the detail records exist

Cascading

Deletes the detail records when a master record is deleted

Isolated

Deletes only the master record

What Happens When You Modify a Relation?

· Changing the Master Deletes property from the default of Non-Isolated to Cascading replaces the On-Check-Delete-Master trigger with the Pre- Delete trigger.

· Changing the Master Deletes property from the default of Non-Isolated to Isolated results in the removal of the On-Check-Delete-Master trigger

MASTER DELETES PROPERTY RESULTING TRIGGERS

Non-Isolated (the default) On-Check-Delete-Master On-Clear-Details

On-Populate-Details

Cascading On-Clear-Details

On-Populate-Details

Pre-Delete

Isolated On-Clear-Details

On-Populate-Details

Coordination

You can control how the detail records are displayed when a master block is queried by setting the coordination property. For example, you can defer querying the line items for an order until the operator navigates to the item block.

Default [Immediate]

The default setting. When a coordination-causing event occurs, the detail records are fetched immediately. (Deferred False, Auto-Query False)

Deferred with Auto Query

Oracle Forms defers fetching the associated detail records until the operator navigates to the detail data block.

Deferred Without Auto Query

When coordination-causing event occurs, Oracle Forms does not automatically fetch the detail records. To fetch the detail records, the operator must navigate to the detail data block and explicitly execute a query.

Prevent Masterless Operation

Ensures that the detail data block cannot be queried or used to insert records when a master record is not currently displayed.

Join Condition

Use to:

· Create links between blocks using SQL

· Alter links between blocks using SQL Define using:

· Usual SQL equi-join condition syntax

· Block names instead of the base table names

· Item names that exist in the form module instead of base table column names

Master-detail triggers

On-Check-Delete-Master, On-Populate-Details, On-Clear-Details

CANVAS

This object represents a background entity on which you place interface items, such as check boxes, radio groups, and text items. There are four types of canvas objects: Content, Stacked, Horizontal Toolbar, and Vertical Toolbar.

1.Content Canvas

The most common canvas type is the content canvas (the default type). A content canvas is the "base" view that occupies the entire content pane of the window in which it is displayed. You must define at least one content canvas for each window you create.

2.Stacked Canvas

A stacked canvas is displayed atop—or stacked on—the content canvas assigned to the current window. Stacked canvases obscure some part of the underlying content canvas, and often are shown and hidden programmatically. You can display more than one stacked canvas in a window at the same time.

3.Tab Canvas

A tab canvas—made up of one or more tab pages —allows you to group and display a large amount of related information on a single dynamic Form Builder canvas object. Like stacked canvases, tab canvases are displayed on top of a content canvas, partly obscuring it. Tab pages (that collectively comprise the tab canvas) each display a subset of the information displayed on the entire tab canvas.

4.Toolbar Canvas

A toolbar canvas often is used to create toolbars for individual windows. You can create two types of toolbar canvases: horizontal or vertical. Horizontal toolbar canvases are displayed at the top of a window, just under its menu bar, while vertical toolbars are displayed along the far left edge of a window.

Showing and hiding a canvas programmatically

SHOW_VIEW('a_stack'); or SET_VIEW_PROPERTY('a_stack', visible, property_true);

HIDE_VIEW('a_stack'); or SET_VIEW_PROPERTY('a_stack', visible, property_false);

WINDOW

A window is a container for all visual objects that make up a Form Builder application, including canvases. A single form can include any number of windows. While every new form automatically includes a default window named WINDOW1, you can create additional windows as needed by inserting them under the Windows node in the Object Navigator.

There are two window styles:

Document

Document Windows Document windows typically display the main canvases and work areas of your application where most data entry, and data retrieval is performed.

Dialog

Dialog Windows Dialog windows are free-floating, containers typically used for modal dialogs that require immediate user interaction.

Window Modality

1.Modal Windows

Modal windows are usually used as dialogs, and have restricted functionality compared to modeless windows. On some platforms, for example, end users cannot resize, scroll, or iconify a modal window. Modal windows are often displayed with a platform-specific border unique to modal windows. On some platforms, modal windows are "always-ontop" windows that cannot be layered behind modeless windows.

2. Modeless Windows

You can display multiple modeless windows at the same time, and end users can navigate freely among them (provided your application logic allows it). On most GUI platforms, you can layer modeless windows so that they appear either in front of or behind other windows.

Hide on Exit property

For a modeless window, determines whether Form Builder hides the window automatically when the end user navigates to an item in another window.

MDI and SDI windows

1. Multiple Document Interface

MDI applications display a default parent window, called the application window. All other windows in the application are either document windows or dialog windows. Document windows always are displayed within the MDI application window frame.

2. Single Document Interface

Although MDI is the default system of window management during Forms Runtime, Form Builder also provides support for an SDI root window on Microsoft Windows.REPLACE_CONTENT_VIEW built-in Replaces the content canvas currently displayed in the indicated window with a different content canvas.

REPLACE_CONTENT_VIEW (window_name VARCHAR2, view_name VARCHAR2);

** Built-in: REPLACE_CONTENT_VIEW

** Example: Replace the 'salary' view with the 'history'

** view in the 'employee_status' window. */

BEGIN

Replace_Content_View('employee_status','history');

END;

Trigger - Windows

When-Window-Activated , When-Window-Deactivated , When-Window-Closed , When- Window-Resized

ALERT

An alert is a modal window that displays a message notifying the operator of some application condition.

Use alerts to advise operators of unusual situations or to warn operators who are about to perform an action that might have undesirable or unexpected consequences.

There are three styles of alerts: Stop, Caution, and Note. Each style denotes a different level of message severity. Message severity is represented visually by a unique icon that displays in the alert window.

 OBJECT GROUPS

An object group is a container for a group of objects. You define an object group when you want to package related objects so you can copy or subclass them in another module. Object groups provide a way to bundle objects into higher-level building blocks that can be used in other parts of an application and in subsequent development projects.

You define an object group when you want to package related objects for copying or sub classing in another module. You can use object groups to bundle numerous objects into higher-level building blocks that you can use again in another application.

Using Object Groups

·        Blocks include:

Items

Item-level triggers

Block-level triggers

Relations

·        Object groups cannot include other object groups

·        Deleting:

An object group does not affect the objects

An object affects the object group

Copying an Object

Copying an object creates a separate, unique version of that object in the target module. Any objects owned by the copied object are also copied.

Use copying to export the definition of an object to another module.

·        Changes made to a copied object in the source module do not affect the copied object in the target module.

Subclassing

Subclassing is an object-oriented term that refers to the following capabilities:

·        Inheriting the characteristics of a base class (Inheritance)

·        Overriding properties of the base class (Specialization)

OBJECT LIBRARY

This object provides an easy method of reusing objects and enforcing standards across the entire development organization.

You can use the Object Library to create, store, maintain, and distribute standard and reusable objects.

In addition, by using Object Libraries, you can rapidly create applications by dragging and dropping predefined objects to your form.

·        Is a convenient container of objects for reuse

·        Simplifies reuse in complex environments

·        Supports corporate, project, and personal standards

·        Simplifies the sharing of reusable components

Object libraries are convenient containers of objects for reuse. They simplify reuse in complex environments, and they support corporate, project, and personal standards.

An object library can contain simple objects, property classes, object groups, and program units, but they are protected against change in the library. Objects can be used as standards (classes) for other objects.

Object libraries simplify the sharing of reusable components. Reusing components enables you to:

·        Apply standards to simple objects, such as buttons and items, for a consistent look and feel

·        Reuse complex objects such as a Navigator

Benefits of the Object Library

·        Simplifies the sharing and reuse of objects

·        Provides control and enforcement of standards

·        Eliminates the need to maintain multiple referenced forms

SMARTCLASS

A SmartClass is a special member of an Object Library. Unlike other Object Library members, it can be used to subclass existing objects in a form using the SmartClass option from the right mouse button popup menu. Object Library members which are not SmartClasses can only be used to create new objects in form modules into which they are added.

 If you frequently use certain objects as standards, such as standard buttons, date items, and alerts, you can mark them as SmartClasses by selecting each object in the object library and choosing Object—>SmartClass.

You can mark many different objects that are spread across multiple object libraries as SmartClasses.

·        Is an object in an object library that is frequently used as a class

·        Can be applied easily and rapidly to existing objects

·        Can be defined in many object libraries

You can have many SmartClasses of a given object

PL/SQL Libraries

A library is a collection of PL/SQL program units, including procedures, functions, and packages. A single library can contain many program units that can be shared among the Oracle Developer modules and applications that need to use them.

A library:

·        Is produced as a separate module and stored in either a file or the database

·        Provides a convenient means of storing client-side code and sharing it among applications

·        Means that a single copy of program units can be used by many form,menu, report, or graphic modules

·        Supports dynamic loading of program units

 FUNCTION locate_emp(bind_value IN NUMBER) RETURN VARCHAR2 IS

v_ename VARCHAR2(15);

BEGIN

SELECT ename INTO v_ename FROM emp WHERE empno = bind_value;

RETURN(v_ename);

END;

Reasons to share objects and code:

·        Increased productivity

·        Increased modularity

·        Decreased maintenance

·        Maintaining standards

.PLL PL/SQL Library Module Binary

.PLD PL/SQL Library Module Text

.PLX PL/SQL Library Module Executable

.MMB Menu Module Binary

.MMT Menu Module Text

.MMX Menu Module Executable

Form Builder Built-in Package

EXEC_SQL Provides built-ins for executing dynamic SQL within PL/SQL procedures

VBX

Provides built-ins for controlling and interacting with VBX controls; this package works only in a 16-bit environment and is provided for backward compatibility

WEB

Provides built-ins for the Web environment

OLE2

Provides a PL/SQL API for creating, manipulating, and accessing attributes of OLE2 automation objects

SUBPROGRAM

A subprogram can be either a procedure or a function. Built-in subprograms are therefore called in two distinct ways:

·        Built-in procedures:

Called as a complete statement in a trigger or program unit with mandatory arguments.

·        Built-in functions:

Called as part of a statement, in a trigger or program unit, at a position where the function’s return value will be used. Again, the function call must include any mandatory arguments.

TRIGGER

Triggers are blocks of PL/SQL code that are written to perform tasks when a specific event occurs within an application. In effect, a Form Builder trigger is an event-handler written in PL/SQL to augment (or occasionally replace) the default processing behavior. Every trigger has a name, and contains one or more PL/SQL statements. A trigger encapsulates PL/SQL code so that it can be associated with an event and executed and maintained as a distinct object.

Trigger Scope

1.Form Level

The trigger belongs to the form and can fire due to events across the entire form.

2.Block Level

The trigger belongs to a block and can only fire when this block is the current block.

3.Item Level

The trigger belongs to an individual item and can only fore when this item is the current item.

Trigger Properties

Execution Style

Execution Hierarchy property

Specifies how the current trigger code should execute if there is a trigger with the same name defined at a higher level in the object hierarchy.

The following settings are valid for this property:

Override

Specifies that the current trigger fire instead of any trigger by the same name at any higher scope. This is known as "override parent" behavior.

Before

Specifies that the current trigger fire before firing the same trigger at the next-higher scope. This is known as "fire before parent" behavior.

After

Specifies that the current trigger fire after firing the same trigger at the next-higher scope. This is known as "fire after parent" behavior.

What are triggers used for?

·         Validate data entry

·         Protect the database from operator errors

·         Limit operator access to specified forms

·         Display related field data by performing table lookups

·        Compare values between fields in the form

·        Calculate field values and display the results of those calculations

·        Perform complex transactions, such as verifying totals

·        Display customized error and information messages to the operator

·        Alter default navigation

·        Display alert boxes

·        Create, initialize, and increment timers

 Groups of triggers

GROUP

FUNCTION

When-triggers

Execute in addition to default processing

On-triggers

Replace default processing

Pre- and Post-triggers

Add processing before or after an event

Key-trigger

Change default processing assigned to a specific key

 Open Oracle Form 10g:

Navigation : Start Menu -> All Programs -> Oracle Developer Suite – 10g -> Form Developer -> Form Builder

How to Hide the local Drive

How to Hide a Local Drive using command prompt?

1. Click on Start - Run

2. In Run box type cmd and hit Enter. Now Command Prompt window will appear.

3. In command prompt, type diskpart and hit Enter.

4. Then type list volume and hit Enter.

Now it will display the list of all your computer drives details.

5. Then type the Drive Letter which you want to hide.
If you want to hide volume E, type select volume E and hit Enter.

Now follow mentioned massage will appear to you.
Volume 4 is the selected volume.

6. After that, type remove letter E and hit an Enter.

That's all, you have done. Now restart your computer once. When you start your PC again, you want be able to see the particular drive in My Computer.


How to get back Hidden Drive again in My Computer.

Do the procedure which i have mentioned in step 1,2,3,4 and 5 again.

Click Start --> Run --> cmd -->

Then type diskpart - list volume - select volume E

After that type assign letter E and hit Enter.

That's all !! You have got back the hidden local drive. Once again restart your PC, now you will be able to see hidden local drive in My Computer.

Q Operators

This is 10g Release new feature

Here are some Q operators Examples

select q'(amar's web blog. It's personal.)' str from dual;

select q'[amar's web blog. It's personal.]' str from dual;

select q'Aamar's web blog. It's personal.A' str from dual;

select q'/amar's web blog. It's personal./' str from dual;

select q'Zamar's web blog. It's personal.Z' str from dual;

select q'|amar's web blog. It's personal.|' str from dual;

select q'+amar's web blog. It's personal.+' str from dual;

How to Export the Results from Oracle SQL *Plus

Spool c:\Employees_Data.txt;


Select * From Employees ;

Open/Call and Close/Exit From a Canvas

Open/Call a canvas

Open/Call a canvas from other canvas.

Use this below code in Base canvas trigger to call a new canvas

Close/Exit From a canvas

Close/Exit a canvas and go to other canvas.

Use this below code in Child canvas trigger to call a Base canvas

Simple Decode Function

Simple For Loop

Complex SQl Quries

To fetch ALTERNATE records from a table. (EVEN NUMBERED)
select * from emp where rowid in (select decode(mod(rownum,2),0,rowid, null) from emp);

To select ALTERNATE records from a table. (ODD NUMBERED)
select * from emp where rowid in (select decode(mod(rownum,2),0,null ,rowid) from emp);

Find the 3rd MAX salary in the emp table.
select distinct sal from emp e1 where 3 = (select count(distinct sal) from emp e2 where e1.sal <= e2.sal);

Find the 3rd MIN salary in the emp table.
select distinct sal from emp e1 where 3 = (select count(distinct sal) from emp e2where e1.sal >= e2.sal);

Select FIRST n records from a table.
select * from emp where rownum <= &n;

Select LAST n records from a table
select * from emp minus select * from emp where rownum <= (select count(*) - &n from emp);

List dept no., Dept name for all the departments in which there are no employees in the department.
select * from dept where deptno not in (select deptno from emp);
alternate solution: select * from dept a where not exists (select * from emp b where a.deptno = b.deptno);
alternate solution: select empno,ename,b.deptno,dname from emp a, dept b where a.deptno(+) = b.deptno and empno is null;

How to get 3 Max salaries ?
select distinct sal from emp a where 3 >= (select count(distinct sal) from emp b where a.sal <= b.sal) order by a.sal desc;

How to get 3 Min salaries ?
select distinct sal from emp a where 3 >= (select count(distinct sal) from emp b where a.sal >= b.sal);

How to get nth max salaries ?
select distinct hiredate from emp a where &n = (select count(distinct sal) from emp b where a.sal >= b.sal);

Select DISTINCT RECORDS from emp table.
select * from emp a where rowid = (select max(rowid) from emp b where a.empno=b.empno);

How to delete duplicate rows in a table?
delete from emp a where rowid != (select max(rowid) from emp b where a.empno=b.empno);

Count of number of employees in department wise.
select count(EMPNO), b.deptno, dname from emp a, dept b where a.deptno(+)=b.deptno group by b.deptno,dname;

Suppose there is annual salary information provided by emp table. How to fetch monthly salary of each and every employee?
select ename,sal/12 as monthlysal from emp;

Select all record from emp table where deptno =10 or 40.
select * from emp where deptno=40 or deptno=10;

Select all record from emp table where deptno=30 and sal>1500.
select * from emp where deptno=30 and sal>1500;

Select all record from emp where job not in SALESMAN or CLERK.
select * from emp where job not in ('SALESMAN','CLERK');

Select all record from emp where ename in 'BLAKE','SCOTT','KING' and 'FORD'.
select * from emp where ename in('JONES','BLAKE','SCOTT','KING','FORD');

Select all records where ename starts with ‘S’ and its lenth is 6 char.
select * from emp where ename like'S____';

Select all records where ename may be any no of character but it should end with ‘R’.
select * from emp where ename like'%R';

Count MGR and their salary in emp table.
select count(MGR),count(sal) from emp;

In emp table add comm+sal as total sal .
select ename,(sal+nvl(comm,0)) as totalsal from emp;

Select any salary <3000 from emp table.
select * from emp where sal> any(select sal from emp where sal<3000);

Select all salary <3000 from emp table.
select * from emp where sal> all(select sal from emp where sal<3000);

Select all the employee group by deptno and sal in descending order.
select ename,deptno,sal from emp order by deptno,sal desc;

How can I create an empty table emp1 with same structure as emp?
Create table emp1 as select * from emp where 1=2;

How to retrive record where sal between 1000 to 2000?
Select * from emp where sal>=1000 And sal<2000

Select all records where dept no of both emp and dept table matches.
select * from emp where exists(select * from dept where emp.deptno=dept.deptno)

If there are two tables emp1 and emp2, and both have common record. How can I fetch all the recods but common records only once?
(Select * from emp) Union (Select * from emp1)

How to fetch only common records from two tables emp and emp1?
(Select * from emp) Intersect (Select * from emp1)

How can I retrive all records of emp1 those should not present in emp2?
(Select * from emp) Minus (Select * from emp1)

Count the totalsal deptno wise where more than 2 employees exist.
SELECT deptno, sum(sal) As totalsal
FROM emp
GROUP BY deptno
HAVING COUNT(empno) > 2


To see current user name
Sql> show user;

Change SQL prompt name
SQL> set sqlprompt “Manimara > “

Switch to DOS prompt
SQL> host

How do I eliminate the duplicate rows ?
SQL> delete from table_name where rowid not in (select max(rowid) from table group by duplicate_values_field_name);
or
SQL> delete duplicate_values_field_name dv from table_name ta where rowid <(select min(rowid) from table_name tb where ta.dv=tb.dv);

Example.
Table Emp
Empno Ename
101 Scott
102 Jiyo
103 Millor
104 Jiyo
105 Smith

delete ename from emp a where rowid < ( select min(rowid) from emp b where a.ename = b.ename);
The output like,
Empno Ename
101 Scott
102 Millor
103 Jiyo
104 Smith

How do I display row number with records?
To achive this use rownum pseudocolumn with query, like SQL> SQL> select rownum, ename from emp;
Output:
1 Scott
2 Millor
3 Jiyo
4 Smith

Display the records between two range
select rownum, empno, ename from emp where rowid in (select rowid from emp where rownum <=&upto minus select rowid from emp where rownum<&Start);
Enter value for upto: 10
Enter value for Start: 7
ROWNUM EMPNO ENAME
--------- --------- ----------
1 7782 CLARK
2 7788 SCOTT
3 7839 KING
4 7844 TURNER


I know the nvl function only allows the same data type(ie. number or char or date
Nvl(comm, 0)), if commission is null then the text “Not Applicable” want to display, instead of
blank space. How do I write the query?
SQL> select nvl(to_char(comm.),'NA') from emp;
Output :
NVL(TO_CHAR(COMM),'NA')
-----------------------
NA
300
500
NA
1400
NA
NA


Oracle cursor : Implicit & Explicit cursors
Oracle uses work areas called private SQL areas to create SQL statements.
PL/SQL construct to identify each and every work are used, is called as Cursor.
For SQL queries returning a single row, PL/SQL declares all implicit cursors.
For queries that returning more than one row, the cursor needs to be explicitly declared.


Explicit Cursor attributes
There are four cursor attributes used in Oracle
cursor_name%Found, cursor_name%NOTFOUND, cursor_name%ROWCOUNT, cursor_name%ISOPEN


Implicit Cursor attributes
Same as explicit cursor but prefixed by the word SQL
SQL%Found, SQL%NOTFOUND, SQL%ROWCOUNT, SQL%ISOPEN
Tips : 1. Here SQL%ISOPEN is false, because oracle automatically closed the implicit cursor after
executing SQL statements.
: 2. All are Boolean attributes.


Find out nth highest salary from emp table
SELECT DISTINCT (a.sal) FROM EMP A WHERE &N = (SELECT COUNT (DISTINCT (b.sal)) FROM EMP B
WHERE a.sal<=b.sal);
Enter value for n: 2
SAL
---------
3700


To view installed Oracle version information
SQL> select banner from v$version;


Display the number value in Words
SQL> select sal, (to_char(to_date(sal,'j'), 'jsp'))
from emp;
the output like,
SAL (TO_CHAR(TO_DATE(SAL,'J'),'JSP'))
--------- -----------------------------------------------------
800 eight hundred
1600 one thousand six hundred
1250 one thousand two hundred fifty
If you want to add some text like,
Rs. Three Thousand only.
SQL> select sal "Salary ",
(' Rs. '|| (to_char(to_date(sal,'j'), 'Jsp'))|| ' only.'))
"Sal in Words" from emp
/
Salary Sal in Words
------- ------------------------------------------------------
800 Rs. Eight Hundred only.
1600 Rs. One Thousand Six Hundred only.
1250 Rs. One Thousand Two Hundred Fifty only.


Display Odd/ Even number of records
Odd number of records:
select * from emp where (rowid,1) in (select rowid, mod(rownum,2) from emp);
1
3
5
Even number of records:
select * from emp where (rowid,0) in (select rowid, mod(rownum,2) from emp)
2
4
6


Which date function returns number value?
months_between


Any three PL/SQL Exceptions?
Too_many_rows, No_Data_Found, Value_Error, Zero_Error, Others


What are PL/SQL Cursor Exceptions?
Cursor_Already_Open, Invalid_Cursor


Other way to replace query result null value with a text
SQL> Set NULL ‘N/A’
to reset SQL> Set NULL ‘’


What are the more common pseudo-columns?
SYSDATE, USER , UID, CURVAL, NEXTVAL, ROWID, ROWNUM


What is the output of SIGN function?
1 for positive value,
0 for Zero,
-1 for Negative value.

Aggregate functions return a single result row based on a group of rows. This differentiates them 

Aggregate functions return a single result row based on a 

from Single-Row functions which act on each row. These functions are extensively used with the GROUP BY clause in SQL statements. AVG (), COUNT (), SUM () … are few aggregate functions which are quite commonly used. Today, one of my colleague asked me if there is some aggregation function forMultiplication. I thought about it for a while and found myself surprised that I have never thought about doing such a thing 

So, How do we do the multiplication then? I tried it but just couldn’t do it in SQL. So, I asked this question on our internal self help channel and I got a pretty impressive reply:

“Using a mathematical approach…”

After understanding the solution, I was surprisingly happy with the simplicity of the approach and found it worth sharing. Let’s assume that we have a table “tbl” with one column “num”. This table has three rows having values 2, 3 & 4 for column “num”.

WITH tbl AS      (SELECT 2 num         FROM DUAL       UNION       SELECT 3 num         FROM DUAL       UNION       SELECT 4 num         FROM DUAL) SELECT num   FROM tbl;

We need the multiplication of row’s data for this column. So essentially, we are looking for an aggregate function MUL (num).

There is no such function as MUL () in Oracle (I actually tried using it). Here comes the computational part of the puzzle. A multiplication operation can be mathematically expressed as:

MUL (num) = EXP (SUM (LN (num)))

Not very clear at first, I agree. Lets review the maths behind it:

x = (2 * 3 * 4)
ln(x) = ln(2 * 3 * 4)
ln(x) = ln(2) + ln(3) + ln(4) => SUM(LN(num))
ln(x) = .693 + 1.098 + 1.386
ln(x) = 3.178
x = e (3.178) => EXP(SUM(LN(num)))
x = 24

And that’s it. We just created our own multiplication function and now the result can be calculated as:

WITH tbl AS      (SELECT 2 num         FROM DUAL       UNION       SELECT 3 num         FROM DUAL       UNION       SELECT 4 num         FROM DUAL) SELECT EXP (SUM (LN (num))) MUL   FROM tbl;

Result: 24

Everything looks perfect. But hey, I have got negative values. The moment you put a negative value in the dataset, you are bound to get the following Oracle error:

“ORA-01428: argument ‘x’ is out of range”

This is because the range for LN () argument is > 0. But this is now easy to handle, here is how:

WITH tbl AS      (SELECT -2 num         FROM DUAL       UNION       SELECT -3 num         FROM DUAL       UNION       SELECT -4 num         FROM DUAL),      sign_val AS      (SELECT CASE MOD (COUNT (*), 2)                 WHEN 0 THEN 1                 ELSE -1              END val         FROM tbl        WHERE num < 0) SELECT   EXP (SUM (LN (ABS (num)))) * val     FROM tbl, sign_val GROUP BY val

Result: -24

So, we first counted the negative records in the table. If the count is odd, the final result should be negative and vice versa. We then multiplied this signed value with the multiplication of the absolute values. A subquery can also be used instead of GROUP BY but that’s trivial. Now the solution is complete and we are able to handle the negative values too.

I was so impressed by this approach that I haven’t given a thought about any other solution. But I am sure there would be. If you find a different approach, please share.