ORACLE - COMPLETE FREE GUIDE LINE

Oracle Complete Learning Guide Line

2009-08-30T10:00:00.000-07:00

Salim Mahmud----------------------------------------(Click) Bangladesh information

Introduction: --------------------------------

In this guide you will find all the common things, sql, pl-sql, procedure, function, package, trigger, cursor like many items are described simply, how to install, connection & problem with connection and so on as required for learners.

In addition you will find here a complete project with developer tools. This is a simple project. You can tell it supply chain or small inventory. Actually this project to maintain a shop for ladies mainly garments items.

I have a shop here. I am using this software to maintain this shop. The name of this shop is "Ladies Corner". We are purchasing / buying Ladies / Womens / Girls / kids items from supplier and serving / selling it to our valuable customers. The items are like: Skirt, Tops, Under Garments, Bra, Swimwear Bikinis, Panty, Petticoat, Sleeping gown, Shoes, Beauty items, Cosmetics, Women Fashion Wear, Ladies Shorts, Trousers, Blouses, Beach-wear, Cardigans and so many items for modern Women, teenager girls and kids.

We shall make database for this project and develop a software using developer tools. We shall create Forms, Master details forms, window, canvas, trigger, LOV alert etc step by step which we need to make the complete software.

Some Link items you can find here.

Oracle(PL/SQL) Hand Book (PDF file)

Oracle (PL/SQL) in Bengoli Language (PDF file)

INTRODUCTION
ABOUT ORACLE CORPORATION
DBMS & RDBMS DATABASE TIME LINE

BEFORE INSTALLATION
ORACLE – 8i
ORACLE – 9i
ORACLE – 10g

INSTALLATION
Simple Installation ORACLE – 8i

Installation ORACLE – 9i

Installation ORACLE – 10g

CHECKING INSTALLATION

DOWNLOAD SOFTWARE
COPY TO HARD DISK

3 ways to connect database

SOME PROBLEM HAPPENS DURING CONNECTON
HOW TO MANAGE PASSWORD

Removing Oracle Database

Summary of PL/SQL
SQL

DDL-Data Definition Language

Constraint & Comments

DCL-Data Control Language

DML-Data manipulation Language

TCL-Transaction Control Language

Select Statement

Sub-Query (Select)

All Type of Join

Database Object(View)

Database Object(Sequence)

Synonym & Index

PL/SQL
Simple Variable
PL/SQL BLOCK
Cursor
Procedure
Function
Package
Trigger

FORMS (DEVELOPER)
REAL ESTATE SOFTWARE DEVELOPE (COMPLETE)
Creating Software Class # 1

REPORTS

During learning period I tried to collect data from internet, but for me It was difficult, though Oracle Corporation’s build many site for the beneficiary such as http://www.oracle.com/index.html is enough healthy. There are many sites you can find as bellows: http://otn.oracle.com/, http://metalink.oracle.com/, www.db.cs.ucdavis.edu/dbs, http://www.oracle.com/support/index.html http://www.oracle.com/us/community/index.htm http://www.oracle.com/products/index.html

http://www.oracle.com/industries/index.html

Further, you may find hundreds, thousands or more Oracle related sites in the internet. But? Here, in this site I tried to provide all Oracle related Data, Demo, Tutorial, Tricks and Tips & Techniques. Even you can find all types of files such and PDF, WORD, HTML formated file and so on for Oracle 6i, 8i, 9i and 10g. I collect many of them from internet, some are personally collected and some of from me. I hope, all the documents (together) here will be helpful for New and Advanced for DBA and Developer both equally. I hope and believe it is enough organized and capable to fulfill the requirement of oracle hunter / Oracle users, manily for new; if not then fill free to mail me with your requirement/question: Mahmud15d@gmail.com

Your Success is my goal in the filed of ORACLE

REAL ESTATE SOFTWARE DEVELOPE

2009-08-25T23:46:00.000-07:00

develop a complete real estate software

Now there is a work in my hand. A customize software for real estate (in Arabic it is called Akara) business. Real estate is equivalent to Akaria (Arabic word). We like to make / create software for real estate. To do that job; we will create user, create table and will create form one by one that will create report and so on as per the requirement. All of my reader; please follow me to do that one and learn how to make software for real estate company /business.

As of Client requirement it will very simple. We will create only 4 tables. They are asking very simple software for his user. There is nothing; Only Clint /Owner details and Customer details. The real estate is one kind of broker or middle man and his work is cooperating with Owner and Customer. So we have keep record for Owner & Customer Details. To do that will create table as bellows:

Note: Before start Install Oracle-8i or Oracle-9i or Oracle-10g and Developer.

1. Owner Detail
2. Villa Detail
3. Customer Detail
4. Payment Detail.

Starting software develop

At first we shall log on to SQL Plus. I hope all of you know how to log on- SEE BELOW FOR LOG ON.

Click Start (Menu)
Click Programs (Menu)
Click Oracle Home (Menu) -as Oracle-OracleDb10gHome1
Then you will find SQL Plus, Click on that. A window appears. To be connected as SYSDBA. The command syntax will be:

Sys/password@database_name as sysdba;

Message appears- connected

First step we shall create user for that software. Type the command as below-

SQL> create user akaria identified by a; (Simple password is easy to use during creation)
User created message appears.
After creating it you have grant some privilege for that user. We will issue DBA privilege to the user

SQL>Grant DBA to akaria;
Grant succeed message will appear.

Then connect to the database as below
SQL>connect akaria/a@dababase_name;
Connected message will appear.

Now we have to create tables. To create table write the command as below.

Posted- 27-07-2009

SQL>Create table Owner_detail
(Owner_id number (4),
O_National_ID varchar2(20) not null,
Owner_name varchar2(100),
Owner_address varchar2(200),
O_city varchar2(100),
O_telphone number(15),
O_fax number (15),
O_mobile varchar2(15),
Agree_date date default sysdate,
Villa_type varchar2(100),
O_remark varchar2(1000),
Constraint PK_O_ID primary key(Owner_id));

Table created.

*************

We now like to create Sequence to generate unique number for Owner_detail table on Owner_id column. So, unique number will be created/generated as of below command in the SQL prompt.

SQL>Create sequence O_seq
Start with 1
Increment by 1
Nocache;

Sequence created.

Date posted-28-07-2009

Today we will create a form. To do that follow as below:

Click – Start Menu
Click – Programs
Click – Oracle Developer Suite – DevSuiteHome1
Click – Forms Developer
Click – Forms Builder

Oracle forms developers will start / run and appear a window – Object Navigator.
See the Picture below:

Click – Data Blocks ( Nagivation Window) Click – + (Plus Sign) left side of Object Navigation window

Or,
From menu Click – edit and then Click – create.

New data block window appear with two options
---- Wizard (Selected default)
---- Manually

Click – Ok

Welcome data block wizard window appear

Click – Next
Another window appear
Click – Browse
Type your User name, password and data base name.
Then Click – Connect
A window appear (Table list which table you created before)
Select a table (Owner_detail)
Click – Ok
A window appear
Click – >> to select all column of table to from left side to right side.
Click – Next
Write data block name (No action)
Click – Next
Congratulation window appear (Two options)
------Create data block, then call the layout wizard (default)
------Just crate data block

Click – Finish
Another window appear
Click – Next
One more window appear with two options for select as below:
----- New canvas
----- Content (Canvas Type)

Click – Next
Another window appear
Click – >> to select all column of table to from left side to right side.

Click – Next
Next window appear with 2 options.
Forms (Default)
Tabular

Click – Next

[On the appear window you may write Frame title, Record (how many record show?), distance between records, display scrollbar or not etc] These all are optional. You can write or not)

Click – Next

Then Click – Finish

(A window appears and we have to do many things here such as Canvas, Window, Items respectively their size, position etc. But later on we will do it one by one as required. No action required now.)

Just run the Forms. To run it Click run button from toolbar or Click program menu and Click Run Forms, but before run it please start OC4J instance for 10g.

You may see what will be at run time for this form. After view Close the form, Click Save for saving it.
Write a name for form/module, then Click save.Exit developer suite and shutdown your computer for today.

Date -01-08-2009

We would like to start customized the form which we crated before. We will do step by step and please follow below:

Open form, go to Object Navigator
Keep cursor on the data block (Owner_detail)
Press F4 (key) for properties
If you want to change name you can; but keep it as it is.
Change Navigation Style ‘Same record’ to ‘Change Record’
Change other properties if you need (Before change try to understand the matter)
Keep cursor on Trigger (Just below data block ‘Owner_detail’)
Click + (Plus Sign – Left side tool bar of Object Navigator)
Select Pre-insert trigger form the list
A window named PL/Sql Editor appear
Type the command as below:
Select O_seq.nextval
Into :Owner_detail.Owner_id
From dual;
Click left upper corner button for compiling and finding errors. If there is no error then close the window or to compiling select program the compile)

Click item (Just below the trigger)
Keep cursor on ‘Owner_id’
Click F4 (key) for properties

Change the properties as below:
Required = Yes to No
Visible = Yes to No
Close properties.

Again

Keep cursor on Canvas (‘Owner_detail’)
Click F4 (key) for properties
Change as below:
Cavas type = Content (As we created, keep it as it is)
Width =734
Height =366

Keep cursor on window (window1)
Click F4 (key) for properties
Change the name of window ‘window1’ to ‘O_window’
Change as below:
Width = 729
Heght =366
Close the properties.

Go Owner_detail (Canvas)
Select/ keep cursor on frame just below the canvas
Click F4 (key) for properties
Change properties
Update layout = ‘Automatically’ to ‘Manually’

(You can change others as of size of item, position of item as well as others things if you can understand and if you required, and place items as you choice)
After finish close the properties window:

(We like to place a picture on this canvas/form. To do this please follows as below:

Go to the Object Navigator again
Double Click on canvas (Owner_detail)
Click the rectangle button (From left side tool bar of the object navigator)
Place a box possible right side of the canvas
Click F4 (key) for properties
Change as below:
Name = villa
X position = 434
Y position = 35
Width =282
Height =192
Line width = .05
Dash Style = Solid
Join Style = Bevel
Bevel =Outset
Close the properties

Click the image button (From left side tool bar of the object navigator)
Place a box (for image) possible right side – inside the rectangle of the canvas which we created before.
Click F4 (key) for properties

Make change as below:
Name = v_sal
Item type = Image
Enable = Yes
Image format = BMP
Display Quality = high
Sizing Style = Adjust
Canvas = Owner_detail
X position = 486
Y position = 73
Width = 190
Height = 131
Bevel = Lowered
Close the properties to come back to object navigator

Keep cursor on trigger (Top of the form/module named (‘Akaria’)
Right Click (mouse)
Select Smart Trigger
Click WHEN-NEW-FORM-INSTANCE
Type as below:
Read_image_file (‘D:\mahdi\sal.bmp’, ‘.bmp’, ‘v_sal’);
[Note- Before do it you have to save picture named sal.bmp in a folder]
{Right the appropriate path for with drive:\folder\file_name.bmp}

Compile it
(Click left upper corner button for compiling and finding errors. If there is no error then close the or select program the compile all)
If there is no error then Close Pl/Sql editor and run the form (Go program and click run)

We would like to make some button for navigation like next, last, first, previous etc.
Go Object Navigator
Keep cursor on ‘Data Block’
Click + (Plus Sign- from left tool bar of the object navigator window.)
Select ‘build a new data block manually’
Change the name of data block button_palette

Again
Keep cursor on canvas
Click + (Plus Sign- from left tool bar of the object navigator window.)
Click F4 (key) for properties
Make change as below:
Name = button_Can
Canvas Type = Stacked
View port width = 517
View port height = 23
Visible = Yes
Window =O_window
Width =513
Height =24

Close the properties
Double click button_can (Canvas)
Click Button tool (Form left side tool bar)
Place a button inside the canvas and place total 10 button as same way or you can copy and paste also
Select all button (Shift+Click or by mouse drug)
Click F4 (key) for properties
Make change as below:
Canvas = Button_can
Y position =3
Width =45
Height =17
Then Close properties.

Place the buttons as below picture.

Select button one by one and press F4 and make change their properties as below:
Name =View/Add etc.(One by One)
Item Type = Push button
Enable =Yes
Canvas =button_Can
X position = (if you place manually then no need to change, keep default)
Y position =3 (We set before)
Width =45 (We set before)
Height =17 (We set before)

Compile all the work (Go program from menu, select compile then select compile all)
Close properties
Run the form

See what is going on. Next will right code and other procedure for button
Best luck for all today

08-08-09

We will write code for above created button.
Open object navigator

Double click button_can
You will see the all button which we created before
If you don’t change name yet;
Please select one button
Click F4 (key) for properties
Change the name as above picture

Select view button
Right Click (Mouse)
Select Smart triggers
Click WHEN-BUTTON-PRESS
Write code as below:
begin
go_block('Owner_detail');
execute_query;
end;

Compile it
Close it.

Again
Select add button
Right Click (Mouse)
Select Smart triggers
Click WHEN-BUTTON-PRESS
Write code as below:

begin
go_block('owner_detail');
create_record;
end;

Again
Select next button
Right Click (Mouse)
Select Smart triggers
Click WHEN-BUTTON-PRESS
Write code as below:
begin
go_block('owner_detail');
Next_record;
end;

Again
Select last button
Right Click (Mouse)
Select Smart triggers
Click WHEN-BUTTON-PRESS
Write code as below:

begin
go_block('Owner_detail');
Last_record;
end;

Again
Select first button
Right Click (Mouse)
Select Smart triggers
Click WHEN-BUTTON-PRESS
Write code as below:

begin
go_block('Owner_detail');
First_record;
end;

Again
Select previous button
Right Click (Mouse)
Select Smart triggers
Click WHEN-BUTTON-PRESS
Write code as below:

begin
go_block('Owner_detail');
previous_record;
end;

Again
Select modify button
Right Click (Mouse)
Select Smart triggers
Click WHEN-BUTTON-PRESS
Write code as below:

begin
go_block('Owner_detail');
execute_query;
end;

Again
Select delete button
Right Click (Mouse)
Select Smart triggers
Click WHEN-BUTTON-PRESS
Write code as below:

declare
chk_button number;
begin
go_block('Owner_detail');
set_alert_property('confirm_alert',title, 'Delete Record');
set_alert_property('confirm_alert', alert_message_text,
'Do you really want to delete the record ?');
chk_button:=show_alert('alert_delete');
if chk_button=alert_button1 then
delete_record;
end if;
end;

Again
Select save button
Right Click (Mouse)
Select Smart triggers
Click WHEN-BUTTON-PRESS
Write code as below:

begin
go_block('Owner_detail');
commit_form;
end;

Again
Select save button
Right Click (Mouse)
Select Smart triggers
Click WHEN-BUTTON-PRESS
Write code as below:

declare
answer number;
begin
if :system.form_status='CHANGED' THEN
SET_ALERT_PROPERTY('CONFIRM_ALERT',TITLE, 'Save Changes');
SET_ALERT_PROPERTY('CONFIRM_ALERT',ALERT_MESSAGE_TEXT,
'Would you like to make changes permanent?');
answer:=show_alert('confirm_alert');

if answer=alert_button1 then
commit_form;
end if;
end if;
clear_block;
exit_form;
end;

Now we will create Procedure. There is some condition in side the procedure. It will make enable or disable the button when condition will true/false. As you know procedure will not work until you call it to perform appropriate task. We will create ant save for the future wherever it require.

To create a procedure:
Click Progam units (Object Navagator)
Type a name “Scroll_control”
Click Ok

A window appear as below (Blue Color)

PROCEDURE s IS
BEGIN

END;

In between BEGIN and END type code as below: Completely with code will be as below:

PROCEDURE SCROL_CONTROLL IS
BEGIN
IF :SYSTEM.last_record='TRUE' AND :SYSTEM.cursor_record='1' THEN
set_item_property('button_palette.next', enabled, property_off);
set_item_property('button_palette.last', enabled, property_off);
set_item_property('button_palette.first', enabled, property_off);
set_item_property('button_palette.previous', enabled, property_off);

ELSIF :SYSTEM.last_record='TRUE' THEN
set_item_property('button_palette.next', enabled, property_off);
set_item_property('button_palette.last', enabled, property_off);
set_item_property('button_palette.first', enabled, property_on);
set_item_property('button_palette.previous', enabled, property_on);

ELSIF :system.cursor_record='1' THEN
set_item_property('button_palette.next', enabled, property_on);
set_item_property('button_palette.last', enabled, property_on);
set_item_property('button_palette.first', enabled, property_off);
set_item_property('button_palette.previous', enabled, property_off);

ELSE
set_item_property('button_palette.next', enabled, property_on);
set_item_property('button_palette.last', enabled, property_on);
set_item_property('button_palette.first', enabled, property_on);
set_item_property('button_palette.previous', enabled, property_on);

END IF;
END;

Try to understand the code, compile the code and close it.

Now we will call the procedure to performing enable /disable our button.

We will call the procedure beginning of the form.
Keep cursor on trigger (Just below data block Owner_detail)
Right Click
Select Smart triggers
Click WEHN-NEW-RECORD-INSTACNE
Type the command as below:

BEGIN
SCROLL_CONTROL;
END;

Compile all the module/form
Run the form.

Enter/Add 2/3 record to data block. (Owner_detail)

Click next, last, first, previous and observe the result.

(Now you can read the code again and try to understand the total subject)

14/08/2009

List of Value (LOV)

We will try to create a LOV and record group. This will perform retrieve data from table to your form. LOV will be connected with respective item. When cursor will be in the respective item then click CTRL+L; LOV window will appear. So we can select appropriate data from the list which we required.

To create a LOV follow command as below:
Run developer suite.
Open Akaria form in the object navigator.
Connect to database
(Click File menu then click Connect and type you user name, password and database name)
Keep cursor on LOVs
Click + (Plus Sign) from left top corner of navigation bar.
A window appear with two options
1. Use the LOV wizard (Default Selected)
2. Build a new LOV manually.
Click OK.
A window appear with two options
New record group based on the query (Default Selected)
Click NEXT
Another window appear with two options

Click Build SQL query
A window appear with data base list (You can see all database which you created before)
Select Owner_detail
Click INCLUDE (Button)
Then Click Close (Button)
A window appears with database (table) column.
Select All field by clicking (Or select as you require)
Then Press OK (button)
Another window appear with “SQL Query Statement”
Click NEXT (If you want you can change SQL query from here)
Click >> (sign)
Record group columns to LOV columns
(All column from left side to right side)
Click Next
Another window appear
Keep cursor on Owner_id (Return Value)
Click “Look up return item” (button)
Another window appear
Select Owner_detail.Owner_id
Click OK
Follow same procedure for others column and after finish
Click NEXT
Another window appear
Type title here
(Others keep as it is default)
Click NEXT
Another window appear
Type 10 in the text field
Click NEXT
Another window appear
Select Owner_detail.Owner_name (Where LOV will assign but you can select all field you if want)

Click NEXT
Click FINISH.
You will come back Object Navigator (Automatically)
Change the name LOV9 to Owner_lov
Change the name LOV9 to Owner_lov_record (Record Group)

Keep cursor on LOV9 to Owner_lov (LOV)
Click F4 (for properties)
(Change width, height, X position, Y position respectively other properties of the LOV window if you need)
{You can set Width=350, Height=250
Compile the form
Run the form

Click Owner Name
Click CTRL+L
You can see the result

Call LOV/Show LOV
For the end user to make it user friendly we like to make button for the above created LOV. So, user can click the Button and cab simply open.

To do that follow
Go to the Object Navigator
Double click Owner_detail (CANVAS)
Click Button tool from button tool palette.
Place a button just right side “owner name” (text item )
Change the name of the button as you like. Write “NAME”
Right Click on the Name button
Select Smart Triggers
Click WHEN-BUTTON-PRESS.
Write code as below:

DECLARE
O_Lov_Open BOOLEAN;
BEGIN
O_Lov_Open := Show_Lov('Owner_lov');
IF NOT O_Lov_Open THEN Message('You have not selected a value.');
Bell; RAISE Form_Trigger_Failure;
END IF;
END;

Compile the form
Run the form
Click that button and see your result.

22/08/2009

TEXT EDITOR
Run the from (‘Akaria’)
Keep cursor on any text box
Click VIEW (button)
Click CTRL+E
You will see a built-in text editor. This is Oracle built-in. You can add, edit and delete data from there.
(Normally for long field like remarks this we need this type of Ediror)
We can create it manually. Built-in TEXT EDITOR some times not perfect for all software. So somewhere we need to create it manually.

To create TEXT EDITOR follow as below:

Go to the Object Navigator
Keep Cursor on Editor
Click + (Plus Sign) left side bar of the navigator bar.
Editor will be created as named EDITOR23 (With 2 digit number)

Change the name of the editor
Type REMARK_EDIT
Keep cursor on REMARK_EDIT
Click F4 (key) for properties
Type Title – Remarks (As you required)
Type Bottom Tile – Edit your remarks (As you required)
(If you want change the colour of Foreground and Background as well as font name, font size etc)

Change as below:
X position = 90 (As you required)
Y position= 150 (As you required)
Width = 400 (As you required)
Height = 200 (As you required)
Close properties.

Next Step
Click Owner_detail (Data block)
Click items
Click O_remark (Data block item or select where you want to assign this EDITOR)
Click F4 (key) for properties
In the properties you will find editor item
Click right side of the properties.
Select REMARK_EDIT
Close properties.

Compile the form
Run form

Keep cursor on Remarks field
Press CTRL+E
See you editor
You can change it at any time as you required

Formatting Items

Click CANVAS on the Object Navigator
Double Click Owner_detail (Canvas)
Canvas will open
Click Owner_detail item
Click F4 (key) for properties
Click Prompt.
Type “Owner National ID”
(To making it understandable for end users. Normally it was as we created field of table. End user or user can be confused. So we will change name as possible as meaning full)
Please select one by one item and change the name as you required.

Now, we will like to change the format masks to displaying as we required.

Please see the reference below for all type of format mask (Collected from ebook)

Type Example Description
FM FMXX99 Fill mode: accept string as typed, do not right justify. Allows end
user input string to be shorter than the format mask.

X XXXX Any alphabetic, numeric, or special character. End user input
string must be exact length specified by format mask.

9 9999 Numeric characters only. End user input string must be exact length
specified by format mask.

A AAAA Alphabetic characters only. End user input string
must be exact length specified by format mask

Number Format

9 9999 Number of nines determines display width. Any leading zeros will
be displayed as blanks.
0 0999 Display leading zeros.
0 9990 Display zero value as zero, not blank.
$ $9999 Prefix value with dollar sign.
B B9999 Display zero value as blank, not "0".
MI 9999MI Display "-" after a negative value.
PR 9999PR Display a negative value in .
comma 9,999 Display a comma in this position. For correct behaviour in
multilingual applications, substitute G to return the appropriate
group (thousands) separator.

period 99.99 Display a decimal point in this position. For correct behaviour in
multilingual applications, substitute D to return the appropriate
decimal separator.

Date Format

YYYY or SYYYY 4-digit year;
"S" prefixes "BC" date with "-".
YYY or YY or Y Last 3, 2, or 1 digits of year.
Y,YYY Year with comma in this position.
BC or AD BC/AD indicator.
B.C. or A.D. BC/AD indicator with periods.
MM Month (01-12; JAN = 01).
MONTH Name of month, padded with blanks to length of 9 characters. MON Name

of month, 3-letter abbreviation.

DDD Day of year (1-366).

DD Day of month (1-31).
D Day of week (1-7; Sunday=1).
DAY Name of day, padded with blanks to length of 9 characters.
DY Name of day, 3-letter abbreviation.
AM or PM Meridian indicator.
A.M. or P.M. Meridian indicator with periods.
HH or HH12 Hour of day (1-12). HH24 Hour of day (0-23).
MI Minute (0-59).
SS Second (0-59).
SSSSS Seconds past midnight (0-86399).

DD-MONTH-YYYY Displays as 12-JANUARY-1994.
DY-DDD-YYYY Displays as WED-012-1994.
DY-DD-MON-YY Displays as WED-12-JAN-94. Be sure to include month. Avoid
masks such as DY-DD-YY, which could generate an error.

Your can change your format mask as you want see your data.

We will change properties for date item.
Click Agree_date (Agreement date)
Click F4 (key) for properties
Click Initial Value
Type $$dbdate$$
Click Format mask
Type dd, Month yyyy

We will change Initial value properties for O_City item.
Click O_City (Owner City)
Click F4 (key) for properties
Click Format mask
Type Dhaka

Again
Click Villa_type
Click F4 (key) for properties
Click Format mask
Type Resident

Change Case Restriction as you like.
Upper = All capital letter
Lower= All small letter
Mixed= Mixed (Capital & Small)

There are many properties for items. Read that entire item and change the properties as you like.
Close properties

Compile the Form
Run the Form
And see the result

This is enough for today for us. Next we will learn new task.

02-10-2009

I just come back after a long days. Due to holly Ramadan I was not able to write. I am sorry for that delay. I hope every week even weekly 2/3 posts you can find. Thank you for your patience. Today we will make another Table.

We have to keep/save record for villa details for the respective Owner. When will enter data for Villa Owner then at the same time we have to keep/save record for villa/house details.

We will make table named Villa_detail. To do that follow below task:

At first we shall log on to SQL Plus. I hope all of you know how to log on-

Click Start (Menu)
Click Programs (Menu)
Click Oracle Home (Menu)
Then you will find SQL Plus, Click and write as user and password (User/Password@Database_name)

Akaria/a@Salim (Database name was ‘salim’)

Message appears- connected

Now we have to create tables. To create table write the command as below:

Create table Villa_detail
(villa_id number(4),
O_National_ID varchar2(20) not null,
Villa_No varchar2(20),
Villa_detail varchar2(100),
Yearly_rent number (10,2),
V_remarks Varchar2(200),
Constraint PK_V_ID PRIMARY KEY(Villa_ID));

We like to create Sequence to generate unique number for villa_detail table on villa_id column. So, unique number will be created./generated for said column.

SQL>Create sequence V_seq
Start with 1
Increment by 1
Nocache
Nocycle;

Sequence created.

Exit SQL Plus.

After create table and sequence we will start making another data block for that table (villa_detail)

We will create a new form
Run Oracle developer suite
Save the form as Villa

We will create a data block and to do that follow as below:

Go to – Object Navigator.
Click – Data Blocks
Click – + (Plus Sign) left side of Object Navigation window

New data block window appear with two options
Use the data block wizard (Default select)
Build a data block Manually
Click – Ok

Welcome data block wizard window appear with two option
Click Next
Another Window appear
Table or View (Default select)
Stored Procedure
Click – Next

Another window appear
Click – Browse
Another window appear
Type your User name, password and data base name.
Then Click – Connect

A window appear (Table list which table you created before)
Select the table (Villa_detail)
Click – Ok

A window appear
Click – >> to select all column of table to from left side to right side. Available items to Display items
Click – Next

Another window appear
Click delete relationship button
Remove check box by clicking.
Click Next

data block name (No action) (A new window appear)
Click – Next
Congratulation window appear (Two options)
Create data block, then call the layout wizard (default)
Just crate data block
Click – Finish

Another window appear
Click – Next
One more window appear with two options
Canvas
Content (Canvas Type)

In the canvas field
Select canvas = new Canvas
Select Canvas type = Stacked
Click – Next

Another window appear
Click – >> to select all column of table to from left side to right side. (Available column to database items)

Click – Next

Another window appear
Click – >> to select all column of table to from left side to right side. (Available items to display items)

Another window appear to customize Prompt, width, height
Click Next

Next window appear with 2 options.
Forms (Default)
Tabular

Click – Next

[On the appear window you may write Frame title, Record (how many record show?), distance between records, display scrollbar or not etc] These all are optional. You can write or not)
Click – Next
Then Click – Finish
(A window (Owner_detail data block) appears and we have to do many things here such as Canvas, Window, Items respectively their size, position etc. But later on we will do it one by one as required. No action required now.)

Just run the Forms. To run it Click run button from toolbar or Click program menu and Click Run Forms, but before run it please start OC4J instance for 10g.

Configure the new form (Canvas, window, items)

Open form, go to Object Navigator
Keep cursor on the data block (villa_detail)
Press F4 (key) for properties
If you want to change name you can; but keep it as it is.
Change Navigation Style ‘Same record’ to ‘Change Record’
Change other properties if you need (Before change try to understand the matter)
Keep cursor on Trigger (Just below data block ‘villa_detail’)
Click + (Plus Sign – Left side tool bar of Object Navigator)
Select Pre-insert trigger form the list
A window named PL/Sql Editor appear
Type the command as below:
Select v_seq.nextval
Into :villa_detail.villa_id
From dual;
Click left upper corner button for compiling and finding errors. If there is no error then close the window or to compiling select program the compile)

Click item (Just below the trigger)
Keep cursor on ‘villa_id’
Click F4 (key) for properties

Change the properties as below:
Required = Yes to No
Visible = Yes to No
Close properties.

Keep cursor on window
Click – + (Plus Sign) left side of Object Navigation window
Keep cursor on the new widow
Click F4 (key) for properties
Change the name of window to ‘v_window’
Change as below:
X Position = 440
Y Position = 107
Width = 270
Heght = 165
Bevel = Lowered.

Click the new Canvas
Change the name of the canvas as ‘villa_detail’
Go villa_detail (Canvas)
Select/ keep cursor on frame just below the canvas
Click F4 (key) for properties
Change
Update layout = ‘Automatically’ to ‘Manually’

Keep cursor on Canvas (‘villa_detail’)
Click F4 (key) for properties
Change as below

Window = v_window
Viewport width =270
Viewport height =149

Width =272
Height =151
Double Click on villa_detail Canvas
Place the entire item inside the canvas

(You can change others as of size of item, position of item as well as others things if you can understand and if you required, and place items as you choice)
After finish close the properties window:
Compile it and run it

Open form akaria
Double Click Owner_details Canvas
Place Create a button top of the picture frame.
Change the Name button as “Villa_Detail”

Right Click the Button
Select Smart Triggers
Then Select WHEN-BUTTON-PRESSED
Type the code as below:

BEGIN
OPEN_FORM ('h:\Mahdi\villa'); (Drive name, Folder name and form name)
END;

Compile the form and run it
Check all the things as you ability.

If you click Villa_detail button, The Villa_detail form will appear. But when you are checking and clicking any key on the Owner_details Canvas. For this solve the open the villa_detail form/module. Go object navigator.

Keep cursor on window.
Click F4 (Properties)
Make Window type = Document to Dialog.

Compile all the form; run the form and check again.
I hope everything is ok now. So we can enter data for Owner and respectively about the villa information.
Make essential button for villa detail block (Save, Exit, Delete etc as you like)

10/10/2009

Today we will make the main form for issuing villa to the customers. To do that follow me please follow as below:

Now we will create two more tables as below:

Customer Details table for Customer
Payment Details for table for payment calculation.

Connect SQL Plus (As we connected before)
Make two tables as describe below:

Create table Customer_detail(
Cust_id number(4) not null,
O_National_ID varchar2(20) not null,
Iqama_no varchar2(20) not null,
Cust_name varchar2(20),
Cust_nationality varchar2(30),
Cust_company varchar2(50),
Cust_Position varchar2(30),
Cust_tele varchar2(15),
Cust_fax varchar2(15),
Cust_mobile varchar2(15),
Cont_S_date date,
Cont_end_date date,
C_remarks varchar2(200),
Constraint PK_C_ID primary key(Cust_id));

Create table Payment_detail(
Payment_id number(4),
O_National_ID varchar2(20) not null,
Iqama_ID varchar2(20) not null,
Agree_Money number(10,2),
Agree_date date,
P_Payment number(10,2),
Due_pay number(10,2),
Due_pay_date date,
P_remark varchar2(200),
Constraint PK_P_ID primary key(Payment_id));

Sequence for this two table.
Sequence for Customer Detail table

Create sequence C_seq
Start with 1
Increment by 1
Nocache;

Sequence for Payment Detail table
Create sequence P_seq
Start with 1
Increment by 1
Nocache;

Create New Form
Before we created two forms modules. So, I don’t want to go thoroughly. Just follow the command. No need detail description.
Before we created two data block, (now we will create data block #3).

Run Oracle developer suite
Open the form akaria.
Go to object Navigator
Keep Cursor on Data Block
Click – + (Plus Sign) left side of Object Navigation window (Create)
Then
Click OK
Click Next
Click Next
Click Browse button
Connect Database (Type your user, password and database name)
If you connect before
Select a table from the list (Owner_detail)
Select Owner_id, O_national_id & Owner_name
Click Next
Click Next
Click Next
Click finish

Then
Click next
Select New CANVAS
Select CANVAS type Stacked.
Click Next

Select >> all field (Left to Right)
Click Next
Click Next
Click Next
Click Next
Click finish

Close CANVAS
Go to object Navigator
Change the new created Data Block Name (Make it Issue_Rent)
Change the new CANVAS name (Issue_rent)

Keep cursor on Canvas (Issue_Rent)
Click F4 (for properties)
Change Window (O_window) if necessary.

Again
We will create another data block (Data Block # 4)

Keep Cursor on Data Block
Click – + (Plus Sign) left side of Object Navigation window (Create)
Click OK
Click Next
Click Next
Click Browse Button
Select a table from the list (Customer_detail)
Select Cust_id, Iqama_no, Cust_name & O_national_id (Left to Right)
Click Next
Click Delete Relationship Button
Remove Check from Check box
Click Next
Click Next
Click Finish
Click Next
Select CANVAS (Issue_rent) [from drop-down list]
Click Next
Select >> all field (Left to Right)

Click Next
Click Next
Click Next
Click Next
Click Finish.

Again
We will create another data block (Data Block # 5)

Keep Cursor on Data Block
Click – + (Plus Sign) left side of Object Navigation window (Create)
Click OK
Click Next
Click Next
Click Browse Button
Select a table from the list (Villa_detail)
Select villa_id, O_national_id & Yearly_Rent (Left to Right)
Click Next
Click Delete Relationship Button
Remove Check from Check box
Click Next
Click Next
Click Finish
Click Next
Select CANVAS (Issue_rent) [from drop-down list]
Click Next
Select >> all field (Left to Right)

Click Next
Click Next
Click Next
Click Next
Click Finish.

Again

We will create another data block (Data Block # 6)

Keep Cursor on Data Block
Click – + (Plus Sign) left side of Object Navigation window (Create)
Click OK
Click Next
Click Next
Click Browse Button
Select a table from the list (Payment_detail)
Select >> all field (Left to Right)
Click Next
Click Delete Relationship Button
Remove Check from Check box
Click Next
Click Next
Click Finish
Click Next
Select CANVAS (Issue_rent) [from drop-down list]
Click Next
Select >> all field (Left to Right)

Click Next
Click Next
Select Radio Button (Tabular)
Click Next
Type Frame Title (Payment)
Make Record Display = 5
Click Next
Click Next
Click Finish.

All the data block already been created

We will format that CANVAS as our requirement. Give make-up & get-up as your like
As we formatted the CANVAS; see the picture below:

(Failed to upload; soon i will add picture)

Change all 4 frames properties
Set Update Layout property = Manual
Then drag and drop CANVAS & Items as you like.

See as picture below:

Go to Object Navigator,
Keep Cursor on Button_can
Click F3 (Properties)
Change CANVAS TYPE= Horizontal toolbar
Set window =O_window

I like to bring the menu which we created before on the Owner_detail CANVAS. (Button Blcok / button CANVAS)

To do that you have to follow as below:

After creating & formatting the form we have change the button code. So, it can be work for every block respectively for every CANVAS and items.

Code for button (Trigger- When-button-pressed)

To do that one you have to do following 3 steps.

Step No# 1
Keep cursor on procedure (object navigator)
Click + (Plus Sign)
A window appear.
Click Package_Spec (Radio Button)
Write a name in the name field as I wrote (P_Form). Another window appear and You will see as like below.
PACKAGE P_FORM IS

END

In between this two line you have to write = x number;
So full code will like below:

PACKAGE P_FORM IS
X number;
END

Compile it and close it.

Step No# 2
Every block should be a unique number, to do that we will create WHEN-NEW-BLOCK-INSTANCE trigger. Please find the code as below:

Go to the master block (Owner_Details)
Create a trigger – WHEN-NEW-BLOCK-INSTANCE
Write the code below:
P_FORM.x :=1;

Again
Go to the Villa_detail Block
Create a trigger – WHEN-NEW-BLOCK-INSTANCE
Write the code below:
P_FORM.x :=2;

Again
Go to the (issue_rent) Block
Create a trigger – WHEN-NEW-BLOCK-INSTANCE
Write the code below:
P_FORM.x :=3;

Again
Go to the (VILLA_DETAIL1) Block
Create a trigger – WHEN-NEW-BLOCK-INSTANCE
Write the code below:
P_FORM.x :=4;

Again
Go to the (Customer_detail) Block
Create a trigger – WHEN-NEW-BLOCK-INSTANCE
Write the code below:
P_FORM.x :=5;

Again
Go to the (PAYMENT_DETAIL1) Block
Create a trigger – WHEN-NEW-BLOCK-INSTANCE
Write the code below:
P_FORM.x :=6;

Step No# 3
Finally we have to change every button code. One by one change the code as below
View button (View record)-Execute Query
Select view button
Right Click (Mouse)
Select Smart triggers
Click WHEN-BUTTON-PRESSED
Change the code as below:

Declare
Block_Data varchar2(80) := :system.cursor_block;
Begin
Go_block (‘Owner_Detail’);
Execute_query;

Go_block (‘villa_Detail’);
Execute_query;

Go_block (‘issue_rent’);
Execute_query;

Go_block (‘villa_detail1’);
Execute_query;

Go_block (‘Customer_detail’);
Execute_query;

Go_block (‘payment_detail’);
Execute_query;

End;

Cont'd.

Close all and shut down your computer and sleep upto next posting

If there is any error or any problem dont hesitate to mail me to mahmud15d@gmail.com

Synonym & Index

2009-06-30T23:36:00.000-07:00

Synonym & Index

Type of Synonym

..........1. Public Synonym

..........2. Private Synonym

(DBA CAN CREATE PUBLIC SYNONYM )

CREATE SYNONYM E FOR EMP;

CREATE SYNONYM d_sum FOR dept_sum_vu;

DROP SEQUENCE

DROP SYNONYM DROP SYNONYM D_SUM;

DROP PUBLIC SYNONYM DEPT_SYN

Create Index

CREATE INDEX IDX_EMP_ENAME ON EMP(ENAME);

CREATE INDEX emp_last_name_idx ON employees(last_name);

CREATE INDEX upper_dept_name_idx ON dept2(UPPER(department_name));

Drop /Delete Indexed File

drop index emp_nm_idx;

drop index index_em_hi_idx

---------------end----------------

Database Objects (Sequence)

2009-06-30T23:16:00.001-07:00

SEQUENCE

Command Syntax (with parameter)

CREATE SEQUENCE sequence_name
[INCREMENT BY n]
[START WITH n]
[{MAXVALUE n NOMAXVALUE}]
[{MINVALUE n NOMINVALUE}]
[{CYCLE NOCYCLE}]
[{CACHE n NOCACHE}];

Example

CREATE SEQUENCE SEQ_EMP1
START WITH 1
INCREMENT BY 1
NOCACHE;

CREATE SEQJUENCE SEQ_EMP2
START WITH 100
INCREMENT BY 1
MINVALUE 90
MAXVALUE 150
CYCLE;

To view the sequence value from dual;

Note: (Dual is dummay table)
SELECT SEQ_EMP1.NEXTVAL FROM DUAL;

GENERATE SEQUENCE

Insert into student_data

values (student_d_seq.nextval, 'Nabil Mahmud', 'Noor Mohmmad', 11, 17,'15-dec-07', 150, '17-Jun-07',0507282848);

Next Value=Nextval

(Return the next available sequence value Return the unique value every time, even for differenct users.)

Insert into student_data
values (student_d_seq.nextval, 'NITI Mahmud', 'Shaik Mohmmad', 12, 17,'15-dec-07', 150, '17-Jun-07', 150, '17-Jun-07',0507282848);

select student_d_seq.nextval from dual;

select student_d_seq.nextval from dual;

Current Value=currval

(Currval obtain the current sequence value. Nextval must be used for that sequence before currval contains a value )

select student_d_seq.currval from dual;

Altering sequence

ALTER SEQUENCE EMP_D_SEQ
MAXVALUE 2000;

ALTER SEQUENCE STUDENT_D_SEQ
MAXVALUE 1000

Drop sequence?

DROP SEQUENCE EMP_D_SEQ;

DROP SEQUENCE STUDENT_D_SEQ;

--------------end--------------------------

Supply Chain Development-Class # 1

2009-06-29T02:51:00.000-07:00

Introduction:
As I said before I have a shop named “Ladies Corner” and I import that garments items from Bangladesh, China and other items form Italy. Some are form others Country and some of them from Local market. Specially, garments from Bangladesh, Leather from Pakistan and Italy.

Before start to creating software; we have to make table to keeping record for selling and buying list. Table may many; it’s depends on requirement. Here, we shall create 13 (Thirteen) table. See short description for that table:

Table # 1
This is a product table. All the items/product will be recorded here. When we shall purchase new item then, we shall add that item to this table. Total 10 columns as describe below their column type and width. All the items will be entered for one time. No duplicate record here. That means this is your complete product list and their description.

create table product
(product_id number(10) constraint pk_product_id primary key,
product_name varchar2(100),
P_type varchar2(1),
made_by varchar2(50),
P_unit varchar2(50),
P_Size varchar2(100)
p_qty number (10),
P_price number(12,2),
S_price number(12,2),
Re_order_lavel number(10));

Table # 2
This is a supplier table. All suppliers will be recorded here. Supplier means from where / whom we are purchasing above said items. We shall add that supplier to this table. Total 9 columns as describe below their columns type and width. All the suppliers will be entered for one time. No duplicate record here. So it is the complete supplier list.

create table supplier
(supplier_id number(10) constraint pk_supplier_id primary key,
supplier_name varchar2(100),
Contact_person varchar2(50),
address varchar2(200),
phone varchar2(15),
fax varchar2(15),
mobail varchar2(15),
email varchar2(50),
balance number(12,2));

Table # 3
This is a customer table. All customers will be recorded here. Customer means to where / whom we are selling above said items, products or goods. We shall add that customer to this table. Total 9 columns as describe below their columns type and width. All the customers will be entered for one time. No duplicate record here. So it is the complete customer list.

create table customer
(customer_id number(10) constraint pk_customer_id primary key,
customer_name varchar2(100),
Contact_person varchar2(50),
address varchar2(200),
phone varchar2(15),
fax varchar2(15),
mobail varchar2(15),
email varchar2(50),
balance number(12,2));

Table # 4
This is a small table named purchase_master to keeping the purchasing record. Total 3 columns as describe below their columns type and width. Actually here the relation is important.

create table purchase_master
(Purchase_id number(10) constraint pk_purchase_id primary key,
purchase_Date date,
supplier_Id number(10),
constraint fk_suppler_idn foreign key(supplier_id) references supplier(supplier_id));

Table # 5
This table named purchase_detail to keeping detail record for the purchasing items. Total 5 columns as describe below their columns type and width. Relation is also important here.

create table purchase_detail
(Purchase_id number(10),
product_id number(10),
qty number(10),
price number(12,2),
total number(12,2),
constraint fk_detail_id foreign key (purchase_id) references purchase_master(purchase_id),
constraint fk_produc_id foreign key(product_id) references product(product_id));

Table # 6
Purchase Return Master
This table named pr_master to keeping record for the return/reject items to supplier from us. Total 3 columns as describe below their columns type and width. Relation is also important here.

create table pr_master
(pr_id number(10) constraint pk_pr_id primary key,
pr_Date date,
Purchase_id number(10),
constraint fk_purchase_id foreign key(purchase_id) references purchase_master(purchase_id));

Table # 7
Purchase Return Details to Supplier
This table named pr_detail to keeping detail record for the return/reject items from us to supplier. Total 5 columns as describe below their columns type and width. Relation is also important here.

create table pr_detail
(pr_id number(10),
product_id number(10),
qty number(10),
price number(12,2),
total number(12,2),
constraint fk_prtn_id foreign key (pr_id) references pr_master(pr_id),
constraint fk_produc_rtn_id foreign key(product_id) references product(product_id));

Table # 8
Payment Details
This table named payment to keeping payment details. When we are paying to our supplier then we will keep record here. Total 5 columns as describe below their columns type and width. Relation is also important here.

create table payment
(payment_id number(10) constraint pk_payment_id primary key,
supplier_Id number(10),
payment_Date date,
payment_mode varchar2(15),
ammount number(12,2),
constraint fk_supplerP_id foreign key(supplier_id) references supplier(supplier_id));

Table # 9
Sale Master
This is a small table named sale_master to keeping the selling record. Total 3 columns as describe below their columns type and width. Relation is important.

create table sale_master
(sale_id number(10) constraint pk_sale_id primary key,
sale_date date,
customer_id number(10),
constraint fk_custs_id foreign key (customer_id) references customer(customer_id));

Table # 10
Sale Detail
This table named sale_detail to keeping detail record for the selling items. Total 5 columns as describe below their columns type and width. Relation is also important here.

create table sale_detail
(sale_id number(10),
product_id number (10),
qty number(10),
price number(12,2),
total number(12,2),
constraint fk_sales_id foreign key(sale_id) references sale_master(sale_id),
constraint fk_saleP_id foreign key(product_id) references product(product_id));

Table # 11
Sale Return Master
This table named sr_master to keeping record for the return/reject items from the customer. Total 3 columns as describe below their columns type and width. Relation is also important here.

create table sr_master
(sr_id number(10) constraint pk_sale_rtn_id primary key,
sr_date date,
sale_id number(10),
constraint fk_salePrtn_id foreign key(sale_id) references sale_master(sale_id));

Table # 12
Sale Return Detail
This table named sr_detail to keeping detail record for the return/reject items from customer to us. Total 5 columns as describe below their columns type and width. Relation is also important here.

create table sr_detail
(sale_id number(10),
product_id number(10),
qty number(10),
price number(12,2),
total number(12,2),
constraint fk_sale_rt_id foreign key (sale_id) references sale_master(sale_id),
constraint fk_salertn_id foreign key(product_id) references product(product_id));

Table # 13
This table named Recovery to keeping detail record for the recovery / dues from customer. Total 5 columns as describe below their columns type and width. Relation is also important here.

create table recovary
(recovary_id number(10) constraint pk_rec_id primary key,
customer_id number(10),
recovary_date date,
recovary_mode varchar2(15),
ammount number(12,2),
constraint fk_cust_id foreign key (customer_id) references customer(customer_id));

------------------------------------------------end----------------------------------------------------

Trrigers

2009-06-26T22:29:00.001-07:00

create or replace trigger at_trig
before update
on emp
begin
if (to_char(sysdate,'24HH') not between '08' and '18') then
raise_application_trigger(-20001,'Date canot be same');
end if;
end;

SQL> /
Warning: Trigger created with compilation errors.
SQL> show error
Errors for TRIGGER AT_TRIG:
LINE/COL ERROR
-------- -----------------------------------------------------------------
3/1 PLS-00201: identifier 'RAISE_APPLICATION_TRIGGER' must be
declared
3/1 PL/SQL: Statement ignored

SQL> ed
Wrote file afiedt.buf
create or replace trigger at_trig
before update
on emp
begin
if (to_char(sysdate,'24HH') not between '08' and '18') then
raise_application_error(-20001,'Date canot be same');
end if;
end;

create or replace trigger at_trig
before update
on emp
begin
if (to_char(sysdate,'24HH') not between '08' and '18') or
to_char(sysdate,'dy') in ('sat','sun') then
raise_application_error(-20001,'Date canot be same');
end if;
end;

create or replace trigger at_trig
before update
on emp
begin
if (to_char(sysdate,'24HH') not between '08' and '18') or
to_char(sysdate,'dy') in ('sat','sun') then
raise_application_error(-20001,'Date canot be same');
end if;
end;

Trigger created.

create or replace trigger at_trig
before update
on emp
begin
if (to_char(sysdate,'24HH') not between '08' and '18') or
to_char(sysdate,'dy') in ('sat','sun') then
raise_application_error(-20001,'Date canot be same');
end if;
end;
10 .
SQL> /
Trigger created.

create or replace trigger at_trig
before update
on emp
begin
if (to_char(sysdate,'HH24') not between '08' and '18') or
to_char(sysdate,'dy') in ('sat','sun') then
raise_application_error(-20001,'Date canot be same');
end if;
end;
SQL> /
Trigger created.
SQL> /

1 row updated.

create or replace trigger at_trig
before update or delete or insert
on emp
begin
if (to_char(sysdate,'HH24') not between '08' and '18') or
to_char(sysdate,'dy') in ('sat','sun') then
raise_application_error(-20001,'Date canot be same');
end if;
end;
SQL> /
Trigger created.

create or replace trigger at_trig
after update or delete or insert
on emp
begin
if (to_char(sysdate,'HH24') not between '08' and '18') or
to_char(sysdate,'dy') in ('sat','sun') then
raise_application_error(-20001,'Date canot be same');
end if;
end;
SQL> /
Trigger created.

create or replace trigger at_trig
after update or delete or insert
on emp
begin
if (to_char(sysdate,'HH24') not between '08' and '18') or
to_char(sysdate,'dy') in ('sat','sun') then
raise_application_error(-20001,'Date canot be same');
end if;
end;
SQL> /
Trigger created.

create or replace trigger at_trig
after update or delete or insert
on emp
for each row
begin
if :new.sal > 6000 then
raise_application_error(-20001,'salary more than');
end if;
end;

SQL> /
Trigger created.
SQL> udpate emp set sal = 4000 where empno = 48;
SP2-0734: unknown command beginning "udpate emp..." - rest of line ignored.
SQL> update emp set sal = 4000 where empno = 48;
1 row updated.

create or replace trigger at_trig
after update or delete or insert
on emp
for each row
begin
insert into chor values(user,sysdate,:old.sal,:new.sal);
end;
Trigger created.
SQL> select * from chor;
no rows selected

SQL> upate emp set sal = 3200 where empno = 48;
SP2-0734: unknown command beginning "upate emp ..." - rest of line ignored.
SQL> update emp set sal = 3400 where empno = 48;
1 row updated.

SQL> select * from chor;
SQL> update emp set sal=2300 where empno = 48;
1 row updated.

SQL> insert into emp(empno,sal) values(200,4500);
1 row created.

CREATE OR REPLACE TRIGGER INS_EMP
AFTER INSERT OR DELETE OR UPDATE OF SAL ON EMP
REFERENCES NEW AS N OLD AS O
FOR EACH ROW
BEGIN
IF INSERTING THEN
INSERT INTO EMP1(EMPNO,ENAME,JOB)
VALUES (:
ELSIF UPDATING THEN
UPDATE EMP1
SET SAL = :N.SAL
WHERE EMPNO = :O.EMPNO;
ELSIF DELETING THEN
DELETE FROM EMP
WHERE EMPNO = :O.EMPNO;
END IF;
END;

CREATE OR REPLACE TRIGGER secure_emp
BEFORE INSERT ON employees BEGIN
IF (TO_CHAR(SYSDATE,'DY') IN ('SAT','SUN')) OR
(TO_CHAR(SYSDATE,'HH24:MI') NOT BETWEEN '08:00' AND '18:00') THEN
RAISE_APPLICATION_ERROR(-20500, 'You may insert' ' into EMPLOYEES table only during ' ' business hours.');
END IF;
END;

CREATE OR REPLACE TRIGGER restrict_salary
BEFORE INSERT OR UPDATE OF salary ON employees
FOR EACH ROW
BEGIN
IF NOT (:NEW.job_id IN ('AD_PRES', 'AD_VP'))
AND :NEW.salary > 15000 THEN
RAISE_APPLICATION_ERROR (-20202,
'Employee cannot earn more than $15,000.');
END IF;
END;

INSERT INTO employees
(employee_id, last_name, job_id, salary, ...)
VALUES (999, 'Temp emp', 'SA_REP', 6000,...);
UPDATE employees
SET salary = 7000, last_name = 'Smith'
WHERE employee_id = 999;

CREATE OR REPLACE TRIGGER logon_trig
AFTER LOGON ON SCHEMA
BEGIN
INSERT INTO log_trig_table(user_id,log_date,action)
VALUES (USER, SYSDATE, 'Logging on');
END;
/

CREATE OR REPLACE TRIGGER logoff_trig
BEFORE LOGOFF ON SCHEMA
BEGIN
INSERT INTO log_trig_table(user_id,log_date,action)
VALUES (USER, SYSDATE, 'Logging off');
END;
----------------end------------------

Packages

2009-06-26T22:28:00.001-07:00

CREATE OR REPLACE PACKAGE comm_pkg IS
std_comm NUMBER := 0.10; --initialized to 0.10
PROCEDURE reset_comm(new_comm NUMBER);
END comm_pkg;

CREATE OR REPLACE PACKAGE BODY comm_pkg IS
FUNCTION validate(comm NUMBER) RETURN BOOLEAN IS
max_comm employees.commission_pct%type;
BEGIN
SELECT MAX(commission_pct) INTO max_comm
FROM employees;
RETURN (comm BETWEEN 0.0 AND max_comm);
END validate;

PROCEDURE reset_comm (new_comm NUMBER) IS BEGIN
IF validate(new_comm) THEN
std_comm := new_comm; -- reset public var
ELSE RAISE_APPLICATION_ERROR(-20210, 'Bad Commission');
END IF;
END reset_comm;
END comm_pkg;

CREATE OR REPLACE PACKAGE BODY comm_pkg IS ...
PROCEDURE reset_comm(new_comm NUMBER) IS
BEGIN
IF validate(new_comm) THEN
std_comm := new_comm;
ELSE ...
END IF;
END reset_comm;
END comm_pkg;

CREATE OR REPLACE PACKAGE dept_pkg IS
PROCEDURE add_department(deptno NUMBER,name VARCHAR2 := 'unknown', loc NUMBER := 1700);
PROCEDURE add_department(
name VARCHAR2 := 'unknown', loc NUMBER := 1700);
END dept_pkg;

CREATE OR REPLACE PACKAGE taxes_pkg IS
FUNCTION tax (value IN NUMBER) RETURN NUMBER;
END taxes_pkg;

CREATE OR REPLACE PACKAGE BODY taxes_pkg IS
FUNCTION tax (value IN NUMBER) RETURN NUMBER IS
rate NUMBER := 0.08;
BEGIN
RETURN (value * rate);
END tax;
END taxes_pkg;

SOME OF BUILTIN PACKAGE
DBMS_ALERT
DBMS_LOCK
DBMS_SESSION
DBMS_OUTPUT
HTP
UTL_FILE
UTL_MAIL
DBMS_SCHEDULER
---------end-----------

Functions

2009-06-26T22:27:00.001-07:00

create or replace function at_function
return number
is
begin
return 20;
end;

Function created.

variable g_f number;
execute :g_f := at_function;
procedure successfully completed.
print :g_f;

G_F
----------
20

create or replace function at_function
return number
is
x number;
begin
x := 20;
return x*20;
end;

Function created.
SQL> execute :g_f := at_function;
PL/SQL procedure successfully completed.
SQL> print :g_f;

G_F
----------
400

create or replace function at_function(x number)
return number
is
begin
return x*20;
end;
Function created.
SQL> execute :g_f := at_function(40);
PL/SQL procedure successfully completed.
SQL> print :g_f;
G_F
----------
800

create or replace function at_function(x in number)
return number
is
begin
return x*20;
end;

Function created.
SQL> execute :g_f := at_function(12);
PL/SQL procedure successfully completed.
SQL> print :g_f;
G_F
----------
240

create or replace function at_function(x in number,y out number)
return number
is
begin
y := x*20;
return y;
end;
SQL> /
Function created.
SQL> variable g_y number;
SQL> execute :g_f := at_function(12,:g_y);
PL/SQL procedure successfully completed.
SQL> print :g_f;

G_F
----------
240

SQL> print :g_y;

G_Y
----------
240
create or replace function at_function(x in number,y out number)
return number
is
x number;
begin
y := x*20;
z := y*x;
return z;
end;
SQL> /
Warning: Function created with compilation errors.
SQL> show error
Errors for FUNCTION AT_FUNCTION:
LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0 PL/SQL: Compilation unit analysis terminated
1/1 PLS-00410: duplicate fields in RECORD,TABLE or argument list are
not permitted
SQL> ed
Wrote file afiedt.buf

create or replace function at_function(x in number,y out number)
return number
is
z number;
begin
y := x*20;
z := y*x;
return z;
end;
SQL> /
Function created.
SQL> execute :g_f := at_function(13,:g_y);
PL/SQL procedure successfully completed.
SQL> print :g_y;
G_Y
----------
260

SQL> print :g_f;
G_F
----------
3380

create or replace function at_function(x in out number)
return number
is
z number;
begin
x := x*20;
z := x*10;
return z;
end;
SQL> /
Function created.
SQL> begin :g_f := 5; end;
PL/SQL procedure successfully completed.

create or replace function at_function(x in out number)
return number
is
y number;
begin
x := x*20;
y := x*10;
return y;
end;
SQL> /
Function created.
SQL> begin :g_f := 5; end;
PL/SQL procedure successfully completed.
SQL> execute :g_f := at_function(:g_f);
PL/SQL procedure successfully completed.
SQL> print :g_f;
G_F
----------
1000

SQL> execute :g_y := at_function(:g_f);
PL/SQL procedure successfully completed.
SQL> print :g_y;
G_Y
----------
200000

SQL> print :g_f;
G_F
----------
20000

CREATE OR REPLACE FUNCTION F_EMP(P_EMPNO IN NUMBER)
RETURN VARCHAR2
IS
V_EMP EMP%ROWTYPE;
BEGIN
SELECT ENAME
INTO P_ENAME
FROM EMP
WHERE EMPNO = P_EMPNO;
END;

CREATE OR REPLACE FUNCTION tax(value IN NUMBER)
RETURN NUMBER IS
BEGIN
RETURN (value * 0.08);
END tax;

SELECT employee_id, last_name, salary, tax(salary)
FROM employees
WHERE department_id = 100;

CALL FUNCTION SELECT F_EMP(7788) FROM DUAL;

-----------------------end-------------------------------------

Procedures

2009-06-26T22:25:00.000-07:00

declare
x number;
begin
x := 10;
end;

declare
x number;
begin
x := 10;
end;

declare
x number;
begin
x := 10;
dbms_output.put_line(x);
end;

declare
y number;
begin
y := 10;
dbms_output.put_line(y);
end;

declare
y number;
x varchar2(20);
begin
y := 10;
x := 'hafeez';
dbms_output.put_line(y);
dbms_output.put_line(x);
end;

declare
x number;
y varchar2(20);
begin
x := 10;
y := 'hafeez';
dbms_output.put_line(x);
dbms_output.put_line(y);
end;

declare
x number;
y varchar2(20);
begin
x := 10;
y := 'hafeez';
dbms_output.put_line(y);
dbms_output.put_line(x);
end;

declare
x number := 30;
y varchar2(20) := 'nadeem';
begin
dbms_output.put_line(y);
dbms_output.put_line(x);
end;

declare
x number := 30;
y varchar2(20) := 'nadeem';
begin
x := 10;
y := 'hafeez';
dbms_output.put_line(y);
dbms_output.put_line(x);
end;

declare
x number := 30;
y varchar2(20) := 'nadeem';
begin
dbms_output.put_line(y);
dbms_output.put_line(x);
x := 10;
y := 'hafeez';
dbms_output.put_line(y);
dbms_output.put_line(x);
end;

declare
x number := 30;
y varchar2(20) := 'nadeem';
begin
dbms_output.put_line(y);
dbms_output.put_line(x);
dbms_output.put_line(y);
dbms_output.put_line(x);
end;

declare
x constant number := 30;
y varchar2(20) := 'nadeem';
begin
dbms_output.put_line(y);
dbms_output.put_line(x);
dbms_output.put_line(y);
dbms_output.put_line(x);
end;

declare
x constant number := 30;
y varchar2(20) := 'nadeem';
begin
dbms_output.put_line(y);
dbms_output.put_line(x);
x := 10;
y := 'hafeez';
dbms_output.put_line(y);
dbms_output.put_line(x);
end;

declare
x constant number := 30;
y varchar2(20) := 'nadeem';
begin
dbms_output.put_line(y);
dbms_output.put_line(x);
y := 'hafeez';
dbms_output.put_line(y);
dbms_output.put_line(x);
end;

declare
x number;
begin
select sal
into x
from emp where empno = '&no';
dbms_output.put_line(x);
end;

declare
x number;
begin
select sal
into x
from emp where empno = '&no';
dbms_output.put_line(x);
end;
declare
x number;
begin
x := 10/2;
dbms_output.put_line(x);
end;

declare
x number;
begin
x := 10/0;
dbms_output.put_line(x);
exception
when zero_divide then
dbms_output.put_line('dividing by zero');
end;

declare
x number;
begin
select sal
into x
from emp
where empno = 10;
dbms_output.put_line(x);
exception
when zero_divide then
dbms_output.put_line('dividing by zero');
when no_data_found then
dbms_output.put_line('No data available');
end;

declare
x number;
begin
select sal
into x
from emp;
dbms_output.put_line(x);
exception
when zero_divide then
dbms_output.put_line('dividing by zero');
when no_data_found then
dbms_output.put_line('No data available');
when too_many_rows then
dbms_output.put_line('many rows');
end;

declare
x number;
y number;
z varchar2(1000);
begin
select sal
into x
from emp;
dbms_output.put_line(x);
y := sqlcode;
exception
when others then
dbms_output.put_line(y);
end;

declare
x number;
y number;
z varchar2(1000);
begin
select sal
into x
from emp;
dbms_output.put_line(x);
y := sqlcode;
z := sqlerrm;
exception
when others then
dbms_output.put_line(y);
dbms_output.put_line(z);
end;

declare
x number;
y number;
z varchar2(1000);
begin
x := 10/0;
y := sqlcode;
z := sqlerrm;
exception
when others then
dbms_output.put_line(y);
dbms_output.put_line(z);
end;

declare
x number;
y number;
z varchar2(1000);
begin
x := 10/0;
y := sqlcode;
z := sqlerrm;
dbms_output.put_line(y);
dbms_output.put_line(z);
exception
when others then
dbms_output.put_line('hafeez');
end;

declare
x number;
y number;
z varchar2(1000);
begin
x := 10/0;
y := sqlcode;
z := sqlerrm;
dbms_output.put_line(y);
dbms_output.put_line(z);
exception
when others then
dbms_output.put_line('hafeez');
dbms_output.put_line(y);
dbms_output.put_line(z);
end;

declare
x number;
y number;
z varchar2(1000);
begin
x := 10/0;
exception
when others then
y := sqlcode;
z := sqlerrm;
dbms_output.put_line('hafeez');
dbms_output.put_line(y);
dbms_output.put_line(z);
end;

NAMED PROCEDURE / STORED PROCEDURE

create or replace procedure at_procedure
x number;
begin
x := 10/2;
dbms_output.put_line(x);
end;

Warning: Procedure created with compilation errors.

SQL> show error
Errors for PROCEDURE AT_PROCEDURE:

LINE/COL ERROR
-------- -----------------------------------------------------------------
2/1 PLS-00103: Encountered the symbol "X" when expecting one of the
following:
( ; is with authid as cluster compress order using compiled
wrapped external deterministic parallel_enable pipelined
The symbol "is" was substituted for "X" to continue.

SQL> ed
Wrote file afiedt.buf

create or replace procedure at_procedure
is
x number;
begin
x := 10/2;
dbms_output.put_line(x);
end;
SQL> /

Procedure created.

SQL> execute at_procedure;
5

PL/SQL procedure successfully completed.

create or replace procedure at_procedure
is
x number;
begin
select sal
into x
from emp
where empno = 7902;
dbms_output.put_line(x);
end;

Procedure created.

SQL> execute at_procedure;
3000

PL/SQL procedure successfully completed.

create or replace procedure at_procedure
is
x number(10);
y varchar2(20);
begin
select sal,ename
into x,y
from emp
where empno = 7902;
dbms_output.put_line(x);
dbms_output.put_line(y);
end;

SQL> /
Procedure created.
SQL> execute at_procedure;
3000
FORD
PL/SQL procedure successfully completed.

create or replace procedure at_procedure(emp_no number)
is
x number(10);
y varchar2(20);
begin
select sal,ename
into x,y
from emp
where empno = '&emp_no';
dbms_output.put_line(x);
dbms_output.put_line(y);
end;

SQL> /
Enter value for emp_no: 7900
old 9: where empno = '&emp_no';
new 9: where empno = '7900';

Procedure created.

SQL> execute at_procedure(7902);
950
JAMES
PL/SQL procedure successfully completed.

create or replace procedure at_procedure(emp_no in number)
is
x number(10);
y varchar2(20);
begin
select sal,ename
into x,y
from emp
where empno = emp_no;
dbms_output.put_line(x);
dbms_output.put_line(y);
end;
SQL> /
Procedure created.
SQL> execute at_procedure(7902);
3000,
FORD

create or replace procedure at_procedure(emp_no in number,e_name out varchar2)
is
begin
select ename
into e_name
from emp
where empno = emp_no;
end;
SQL> /
Procedure created.
SQL> variable v_e_name varchar2(20);
SQL> execute at_procedure(7902,:v_e_name);

PL/SQL procedure successfully completed.

SQL> print v_e_name

V_E_NAME
--------------------------------
FORD

create or replace procedure at_procedure(emp_no in number,e_name out varchar2)
is
begin
select ename
into e_name
from emp
where empno = emp_no;
dbms_output.put_line(e_name);
end;
SQL> /
Procedure created.
SQL> execute at_procedure(7902,:v_e_name);
FORD
PL/SQL procedure successfully completed.
SQL> print v_e_name;

V_E_NAME
--------------------------------
FORD

create or replace procedure at_procedure(e_no out varchar2)
is
begin
e_no := '('substr(1,3)
')'substr(4,3)
'-'substr(7);
end;
SQL> /
Warning: Procedure created with compilation errors.
SQL> show error;
Errors for PROCEDURE AT_PROCEDURE:

LINE/COL ERROR
-------- -----------------------------------------------------------------
4/1 PL/SQL: Statement ignored
6/7 PLS-00306: wrong number or types of arguments in call to 'SUBSTR'

SQL> ed
Wrote file afiedt.buf
create or replace procedure at_procedure(e_no out varchar2)
is
begin
e_no := '('substr(1,3)
')'substr(4,3)
'-'substr(7);
end;
SQL> /
Warning: Procedure created with compilation errors.
SQL> show error
Errors for PROCEDURE AT_PROCEDURE:

LINE/COL ERROR
-------- -----------------------------------------------------------------
4/1 PL/SQL: Statement ignored
6/7 PLS-00306: wrong number or types of arguments in call to 'SUBSTR'

SQL> ed
Wrote file afiedt.buf
create or replace procedure at_procedure(e_no out varchar2)
is
begin
e_no := '('substr(1,3)
')'substr(4,3)
'-'substr(7,10);
end;
SQL> /
Procedure created.
begin :v_e_no := '8001248888'; end;
SQL> /
PL/SQL procedure successfully completed.

create or replace procedure at_procedure(e_no out varchar2)
is
begin
e_no := '('substr(1,3)
')'substr(4,3)
'-'substr(7,10);
end;
SQL> /
Procedure created.
SQL> variable v_e_no varchar2(20);
SQL> begin :v_e_no := '8001248888'; end;
PL/SQL procedure successfully completed.
SQL> execute at_procedure(:v_e_no);
PL/SQL procedure successfully completed.

SQL> print :v_e_no;

V_E_NO
--------------------------------
()-

create or replace procedure at_procedure(e_no in out varchar2)
is
begin
e_no := '('substr(1,3)
')'substr(4,3)
'-'substr(7,10);
end;
SQL> /
Procedure created.
SQL> variable v_e_no varchar2(20);
SQL> begin :v_e_no := '8001248888'; end;
PL/SQL procedure successfully completed.

SQL> execute at_procedure(:v_e_no);

PL/SQL procedure successfully completed.

SQL> print :v_e_no;

V_E_NO
--------------------------------
()-

create or replace procedure at_procedure(e_no in out varchar2)
is
begin
e_no := '('substr(1,3)
')'substr(4,3)
'-'substr(7,10);
dbms_output.put_line(e_no);
end;
SQL> /
Procedure created.
SQL> variable v_e_no varchar2(20);
SQL> begin :v_e_no := '8001248888'; end;
PL/SQL procedure successfully completed.
SQL> execute at_procedure(:v_e_no);
()-

PL/SQL procedure successfully completed.

SQL> print :v_e_no;

V_E_NO
--------------------------------
()-

create or replace procedure at_procedure(e_no in out varchar2)
is
begin
e_no := '('substr(e_no,1,3)
')'substr(e_no,4,3)
'-'substr(e_no,7);
end;
SQL> /
Procedure created.
SQL> variable v_e_no varchar2(20);
SQL> begin :v_e_no := '8001248888'; end;
PL/SQL procedure successfully completed.
SQL> execute at_procedure(:v_e_no);
PL/SQL procedure successfully completed.

SQL> print :v_e_no;

V_E_NO
--------------------------------
(800)124-8888

IN PARAMETER

CREATE OR REPLACE PROCEDURE P_EMP(P_EMPNO NUMBER)
IS
V_ENAME EMP.ENAME%TYPE;
BEGIN
SELECT ENAME
INTO V_ENAME
FROM EMP
WHERE EMPNO = P_EMPNO;
DBMS_OUTPUT.PUT_LINE(V_ENAME);
END;

CREATE OR REPLACE PROCEDURE raise_salary
(id IN employees.employee_id%TYPE,
percent IN NUMBER)
IS
BEGIN
UPDATE employees
SET salary = salary * (1 + percent/100)
WHERE employee_id = id;
END raise_salary;

OUT PARAMETER

CREATE OR REPLACE PROCEDURE P_EMP(P_EMPNO IN NUMBER, P_ENAME OUT
VARCHAR2) IS

BEGIN
SELECT ENAME
INTO P_ENAME
FROM EMP
WHERE EMPNO = P_EMPNO;
END;

IN OUT PARAMETER

CREATE OR REPLACE PROCEDURE P_EMP(P_EMPNO_SAL IN OUT NUMBER)
IS
BEGIN
SELECT SAL
INTO P_EMPNO_SAL
FROM EMP
WHERE EMPNO = P_EMPNO_SAL;
END;

CREATE OR REPLACE PROCEDURE format_phone
(phone_no IN OUT VARCHAR2) IS
BEGIN
phone_no := '(' SUBSTR(phone_no,1,3)
')' SUBSTR(phone_no,4,3)
'-' SUBSTR(phone_no,7);
END format_phone;

Call Procedure

DECLARE
X NUMBER;
BEGIN
P_EMP(100,X);
END;

BEGIN
P_EMP(100);
END;

DECLARE
X NUMBER := 100;
BEGIN
P_EMP(X);
END;

------------end----------

Cursor

2009-06-26T22:23:00.000-07:00

Cursor

set serveroutput on

declare
cursor c_emp is select * from emp;
v_emp emp%rowtype;
begin
open c_emp;
loop
fetch c_emp into v_emp;
dbms_output.put_line(v_emp.sal);
dbms_output.put_line(v_emp.job);
exit when c_emp%notfound;
end loop;
close c_emp;
end;

PL/SQL procedure successfully completed.

SQL> /
9000
CLERK
1600
SALESMAN

PL/SQL procedure successfully completed.

declare
cursor c_emp is select * from emp;
v_emp emp%rowtype;
begin
for v_emp in c_emp loop
dbms_output.put_line(v_emp.sal);
dbms_output.put_line(v_emp.job);
end loop;
end;

declare
cursor c is select * from emp;
v_emp emp%rowtype;
begin
for v_emp in c loop
dbms_output.put_line(v_emp.sal);
dbms_output.put_line(v_emp.job);
end loop;
end;

declare
cursor c is select * from emp;
r emp%rowtype;
begin
for r in c loop
dbms_output.put_line(r.sal);
dbms_output.put_line(r.job);
end loop;
end;

declare
cursor c_emp is select * from emp;
v_emp c_emp%rowtype;
begin
open c_emp;
loop
fetch c_emp into v_emp;
dbms_output.put_line(v_emp.ename);
dbms_output.put_line(v_emp.sal);
dbms_output.put_line(v_emp.job);
exit when c_emp%notfound;
end loop;
close c_emp;
end;

declare
cursor c_emp is select * from emp;
v_emp c_emp%rowtype;
begin
for v_emp in c_emp loop
dbms_output.put_line(v_emp.ename);
dbms_output.put_line(v_emp.sal);
dbms_output.put_line(v_emp.job);
end loop;
end;

DECLARE
V_EMP EMP%ROWTYPE;
CURSOR C_EMP IS
SELECT *
INTO V_EMP
FROM EMP
WHERE DEPTNO = 10;
BEGIN
OPEN C_EMP;
LOOP
FETCH C_EMP INTO V_EMP;
EXIT WHEN C_EMP%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(V_EMP.ENAME);
END LOOP;
END;

Cursor for Loop
BEGIN
FOR I IN (SELECT * FROM EMP) LOOP
DBMS_OUTPUT.PUT_LINE(I.ENAME);
END LOOP;
END;

------------------- end------------------------

PL-SQL Block

2009-06-26T22:22:00.001-07:00

SUMMARY OF SQL & PL/SQL

PART-2 (PL/SQL)

Block
Declaration Section (Declare all variable, constants, cursors, exceptions)
Executable Section (All executable PL/SQL Commands, call other procedures,DML)
Exception Section (Tran Run time errors and give your own messages)

Variable for Block
PL/SQL Variable
Scalar variable
Composite variable

Example for Block

Declare
X number(10);
V_EMPNO EMP.EMPNO%TYPE;
V_EMP EMP%ROWTYPE;
BEGIN
SELECT *
INTO V_EMP
FROM EMP
WHERE EMPNO = 7788;
DBMS_OUTPUT.PUT_LINE(V_EMP.ENAME);
END;

DML in PL/SQL
SELECT
INSERT
UPDATE
DELETE

Declare
X number(10);
V_EMPNO EMP.EMPNO%TYPE;
V_EMP EMP%ROWTYPE;
BEGIN
SELECT *
INTO V_EMP
FROM EMP
WHERE EMPNO = 7788;
DBMS_OUTPUT.PUT_LINE(V_EMP.ENAME);

INSERT INTO EMP1 (EMPNO,ENAME,JOB,SAL)
VALUES (V_EMP.EMPNO,V_EMP.ENAME,V_EMP.JOB,V_EMP.SAL);

UPDATE EMP1
SET SAL = V_EMP.SAL
WHERE EMPNO = V_EMP.EMPNO;

DELETE FROM EMP1
WHERE DEPTNO = V_EMP.DEPTNO;
END;

Flow Control (Program)
If condition (Example)

declare
x number;
begin
select sal
into x
from emp where empno = '&no';
dbms_output.put_line(x);
if x > 3000 then
dbms_output.put_line('first grade');
elsif x > 2000 then
dbms_output.put_line('second grade');
else
dbms_output.put_line('junior grade');
end if;
end;

declare
x number;
begin
select sal
into x
from emp where empno = '&no';
dbms_output.put_line(x);
if x >= 3000 then
dbms_output.put_line('first grade');
elsif x > 2000 then
dbms_output.put_line('second grade');
else
dbms_output.put_line('junior grade');
end if;
end;

declare
x number;
begin
select sal
into x
from emp where empno = '&no';
dbms_output.put_line(x);
if x >= 3000 then
dbms_output.put_line('first grade');
elsif x > 2000 then
dbms_output.put_line('second grade');
end if;
end;

declare
x number;
begin
select sal
into x
from emp where empno = '&no';
dbms_output.put_line(x);
if x >= 3000 then
dbms_output.put_line('first grade');
else
dbms_output.put_line('junior grade');
end if;
end;

declare
x number;
begin
select sal
into x
from emp where empno = '&no';
dbms_output.put_line(x);
if x >= 3000 then
dbms_output.put_line('first grade');
end if;
end;

declare
x varchar2(20);
begin
select ename
into x
from emp where empno = '&no';
dbms_output.put_line(x);
dbms_output.put_line(x);
dbms_output.put_line(x);
dbms_output.put_line(x);
dbms_output.put_line(x);
dbms_output.put_line(x);
dbms_output.put_line(x);
end;

DECLARE
V_EMP EMP%ROWTYPE;
BEGIN
SELECT *
INTO V_EMP
FROM EMP
WHERE ENAME = ‘SCOTT’;

IF V_EMP.SAL <>

DBMS_OUTPUT.PUT_LINE(‘A’);

ELSIF V_EMP.SAL <>

DBMS_OUTPUT.PUT_LINE(‘C’);

ELSE

DBMS_OUTPUT.PUT_LINE(‘D’); END IF;

END;

Basic loop

declare

x varchar2(20);

y number := 1;

begin

select ename into x from emp where empno = '&no';

loop

dbms_output.put_line(x); 10 y := y + 1; exit when Y > 10;
end loop;
end;

declare
x varchar2(20);
y number := 1;
begin
select ename
into x
from emp where empno = '&no';
loop
dbms_output.put_line(x);
dbms_output.put_line(y);
11 y := y + 2;
exit when Y > 10;
end loop;
end;

DECLARE
X NUMBER := 1;
BEGIN
LOOP
X := X +1 ;
DBMS_OUTPUT.PUT_LINE(X);
EXIT WHEN X = 10;
END LOOP;
END;

While loop

declare
x varchar2(20);
y number := 1;
begin
select ename
into x
from emp where empno = '&no';
while y <>

dbms_output.put_line(x);

dbms_output.put_line(y); 11 y := y + 2;

end loop;

end;

declare

x varchar2(20);

y number := 1;

begin

select ename into x from emp where empno = '&no';

while y <>

dbms_output.put_line(x);

dbms_output.put_line(y);

y := y + 1;

end loop;

end;

DECLARE

X NUMBER := 1;

BEGIN

WHILE X <>

X := X +1;

DBMS_OUTPUT.PUT_LINE(X);

END LOOP;

END;

For loop

declare

x varchar2(20);

begin

select ename into x from emp where empno = '&no';

for i in 1..10 loop

dbms_output.put_line(x);

dbms_output.put_line(i);

end loop;

end;

begin

for i in 1..10 loop

dbms_output.put_line('hafeez');

dbms_output.put_line(i);

end loop;

end;

BEGIN

FOR I IN 1..10 LOOP

DBMS_OUTPUT.PUT_LINE(I);

END LOOP;

END;

Exceptions

DECLARE

V_EMP EMP%ROWTYPE;

BEGIN

SELECT * INTO V_EMP FROM EMP WHERE DEPTNO = 1; DBMS_OUTPUT.PUT_LINE(V_EMP.ENAME); EXCEPTION WHEN

NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE(‘SORRY, NO DATA FOUND’);

WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE(‘SORRY, MANY RECORDS FOUND’);

END;

--------------------end----------------------

Simple Variable

2009-06-26T22:18:00.000-07:00

VARIABLE

1. Bind Variable (For Individual Value Store )
2. Substitution Variable (For Command text store )

SINGEL AMPERSAND SUBSTITION VARIABLE

SELECT EMPNO, ENAME, SAL
FROM EMP
WHERE DEPTNO=&DEPT_NO;
Enter value for dept_no: 10
old 3: WHERE DEPTNO=&DEPT_NO
new 3: WHERE DEPTNO=10

SQL> /
Enter value for dept_no: 20
old 3: WHERE DEPTNO=&DEPT_NO
new 3: WHERE DEPTNO=20

SELECT EMPNO, ENAME, SAL, JOB
FROM EMP
WHERE JOB=&JOB_TITLE
Enter value for job_title: 'MANAGER'
old 3: WHERE JOB=&JOB_TITLE
new 3: WHERE JOB='MANAGER'

SELECT EMPNO, ENAME, SAL, JOB
FROM EMP
WHERE JOB='&JOB_TITLE'
Enter value for job_title: MANAGER
old 3: WHERE JOB='&JOB_TITLE'
new 3: WHERE JOB='MANAGER'

EXPRESSION WITH VARIABLE

SELECT DEPTNO, &ARI_EXP
FROM EMP
Enter value for ari_exp: SAL*12
old 1: SELECT DEPTNO, &ARI_EXP
new 1: SELECT DEPTNO, SAL*12

SQL> /
Enter value for ari_exp: SAL*12/100
old 1: SELECT DEPTNO, &ARI_EXP
new 1: SELECT DEPTNO, SAL*12/100

SELECT * FROM SALGRADE
WHERE LOSAL &CONDITION
Enter value for condition: >900
old 2: WHERE LOSAL &CONDITION
new 2: WHERE LOSAL >900

Enter value for condition: BETWEEN 2000 AND 3000
old 2: WHERE LOSAL &CONDITION
new 2: WHERE LOSAL BETWEEN 2000 AND 3000

DOUBLE AMPERSAND SUBSTITION VARIABL

SELECT * FROM EMP
WHERE DEPTNO=&&DDEPTNO;
Enter value for ddeptno: 10
old 2: WHERE DEPTNO=&&DDEPTNO
new 2: WHERE DEPTNO=10

DEFINE Variable

DEFINE REM=SAL*12 +NVL(COMM, 0)

SELECT ENAME, JOB, &REM
FROM EMP
ORDER BY &REM;
old 1: SELECT ENAME, JOB, &REM
new 1: SELECT ENAME, JOB, SAL*12
old 3: ORDER BY &REM
new 3: ORDER BY SAL*12

SQL> define x=100
SQL> define y=25
SQL> SELECT &X+&Y FROM DUAL;
old 1: SELECT &X+&Y FROM DUAL
new 1: SELECT 100+25 FROM DUAL

SQL> SELECT &X*&Y FROM DUAL
old 1: SELECT &X*&Y FROM DUAL
new 1: SELECT 100*25 FROM DUAL

Create script file
SQL FILE /SPOOL FILE/SCRIPT FILE

COMMAND SYNTAX

SQL>SPOOL DRIVE:\FOLDER\FILE NAME.SQL
SQL>SPOOL D:\MAHMUD\ORA.SQL

---------------- end--------------------

Other Database Objects- VIEW

2009-06-26T22:12:00.001-07:00

Other Database Objects

Views

(View based on a table or another view and acts as a window through which data on a table can be viewed or changes. A view does not contain data and store data as select statement. )

1. SIMPLE VIEW
........a. CREATE FROM A SINGLE TABLE
2. COMPLEX VIEW
........a. CREATE FROM MANY TABLE

FEATURE/ DISCRIPTION ...............SIMPLE VIEW ...................COMPLEX VIEW

NUMBER OF TABLE ...................................ONE ......................................MANY
CONTAIN FUNCTION ................................NO ..........................................YES
CONTAIN GROUP OF DATA .......................NO ..........................................YES
ALLOW DML .............................................YES .........................................NOT ALWAYS

(VIEW CAN NOT CONTAIN ORDER BY CLAUSE )

CREATE VIEW (REPLACE = FORCE )

CREATE OR REPLACE VIEW EMP_VIEW1
AS
SELECT EMPNO, ENAME, JOB
FROM EMP
WHERE DEPTNO=10;

CREATE OR REPLACE VIEW EMP_VIEW2
AS
SELECT EMPNO, ENAME, JOB
FROM EMP
WHERE JOB='MANAGER'

COLUMN ALIAS (To Change the Column Name during creation of view)

CREATE VIEW EMP_SAL5
AS
SELECT EMPNO EPLOYEE_NUMBER, ENAME NAME, SAL SALARY
FROM EMP
WHERE DEPTNO=10
OR DEPTNO=20

CREATE VIEW DEPT_SUM6
(NAME, MINSAL, MAXSAL, AVGSAL)
AS
SELECT DNAME, MIN(SAL), MAX(SAL), AVG(SAL)
FROM EMP, DEPT
WHERE EMP.DEPTNO=DEPT.DEPTNO
GROUP BY DNAME

CREATE VIEW D_EMP7
AS
SELECT EMPNO, ENAME, DEPTNO
FROM EMP
WHERE DEPTNO=10
WITH CHECK OPTION;

CREATE VIEW ORG_EMP_V AS
SELECT A.ENAME MANAGER,A.EMPNO MANAGERID,
B.ENAME SUBORDINATE,B.EMPNO SUBORDINATEID
FROM EMP A,EMP B
WHERE A.EMPNO = B.MGR;

CREATE VIEW empvu80
AS SELECT employee_id, last_name, salary
FROM employees
WHERE department_id = 80;

READ ONLY VIEW.

CREATE OR REPLACE VIEW EMP_READ9
(EMPLOYEE_NUMBER, EMPLOYEE_NAME, DESIGNATION)
AS
SELECT EMPNO, ENAME, JOB
FROM EMP
WHERE DEPTNO=30
WITH READ ONLY;

DROP VIEW

DROP VIEW EMP_VIEW1

------------end--------------

Sub-Query

2009-06-26T22:10:00.001-07:00

Sub-Query

SINGLE ROW SUB-QUERY

(Sub query also a select statement that nested within another select statement. )

SELECT ENAME, JOB, SAL
FROM EMP
WHERE SAL=(LOWEST SALARY WHICH IS UNKNOWN)

SELECT MIN(SAL) FROM EMP;

SELECT ENAME, JOB, SAL
FROM EMP
WHERE SAL=800;

COMBINE THIS TWO COMMAND BELOW (Nested Query)

SELECT ENAME, JOB, SAL
FROM EMP
WHERE SAL=(SELECT MIN(SAL) FROM EMP)

SELECT * FROM EMP
WHERE SAL < (SELECT SAL FROM EMP WHERE ENAME = ‘SMITH’);

SELECT employee_id, manager_id, department_id

FROM employees

WHERE (manager_id, department_id)

IN (SELECT manager_id, department_id

FROM employees

WHERE employee_id

IN (199,174)) AND employee_id NOT IN (199,174);

SELECT employee_id, manager_id, department_id

FROM employees WHERE manager_id

IN (SELECT manager_id

FROM employees

WHERE employee_id IN (174,199))

AND department_id IN (SELECT department_id

FROM employees WHERE employee_id

IN (174,199)) AND employee_id NOT IN (174,199);

SELECT employee_id, last_name, (CASE WHEN department_id = (SELECT department_id FROM departments WHERE location_id = 1800) THEN 'Canada' ELSE 'USA' END) location FROM employees;

SELECT select_list FROM table WHERE expr operator (SELECT select_list FROM table); SELECT last_name FROM employees WHERE salary > (SELECT salary
FROM employees
WHERE last_name = 'Montel');

SELECT last_name, job_id, salary
FROM employees
WHERE job_id =
(SELECT job_id
FROM employees
WHERE employee_id = 142)
AND salary >
(SELECT salary
FROM employees
WHERE employee_id = 101);

HAVING CALUSE WITH NESTED SUBQUERY

SELECT DEPTNO, AVG(SAL) FROM EMP
HAVING AVG(SAL) > (SELECT AVG(SAL)
FROM EMP
WHERE DEPTNO=30) GROUP BY DEPTNO;

SELECT JOB, AVG(SAL) FROM EMP
GROUP BY JOB
HAVING AVG(SAL)=(SELECT MAX(AVG(SAL))
FROM EMP
GROUP BY JOB)

( There is a limit on the levels of nested for a sub-query is 255)

MORE NESTED SUB-QUERY

MAIN QUERY
INNER QUERY

SELECT ENAME, JOB
FROM EMP
WHERE JOB=(SELECT JOB FROM EMP
WHERE ENAME='BLAKE')

SELECT ENAME, JOB FROM EMP
WHERE ENAME='BLAKE'

SELECT ENAME, JOB FROM EMP
WHERE job='MANAGER'

SELECT ENAME, JOB, HIREDATE, SAL
FROM EMP
WHERE SAL> (SELECT MAX(SAL)
FROM EMP
WHERE DEPTNO=(SELECT DEPTNO FROM DEPT
WHERE DNAME='SALES'))

CORELATED QUERY

SELECT EMPNO, ENAME, SAL, DEPTNO FROM EMP E
WHERE SAL>(SELECT AVG(SAL)
FROM EMP
WHERE DEPTNO=E.DEPTNO)
ORDER BY DEPTNO;

SUB QUERY THAT RETURN MORE THAN ONE ROW

SELECT ENAME, SAL, DEPTNO FROM EMP
WHERE SAL IN (SELECT MIN(SAL)
FROM EMP
GROUP BY DEPTNO);

SELECT ENAME, SAL, DEPTNO
FROM EMP
WHERE (SAL,DEPTNO) IN (SELECT MIN(SAL), DEPTNO
FROM EMP
GROUP BY DEPTNO);

--------- END-----------

All Type of Join

2009-06-26T22:08:00.002-07:00

Joins

In the data query we need to query data from many tables. From the related table we can query same type of data. To do that some time simple join or complex join may reqire. There is many type of join. In the Oracle we may find 8 types of join and in SQL there is 4 types of join also can be implemented in Oracle. You can query data by using set operator. That is also one kind of join to make query. You will find all kind of join here in details.

General Rule

SELECT STATEMENT MUST BE APPLIED SAME NUMBER OF COLUMN. CORRESPONDING COLUMN DATA TYPE MUST BE SAME. DISTINCT IS NOT APPLICABLE. COLUMN NAME/HEADING ROW APPEAR FROM THE FIRST QUERY OF THE RESULT ORDER BY CLAUSE CAN BE USED AT THE END OF STATEMENT. IN THE ORDER BY CLAUSE COLUMN NUMBER POSITION CAN BE USED.

Oracle joins are equal SQL is described below. You can use any one to query data.

Similarity between Oracle & SQL Joins

ORACLE .............................................SQL:1999 ..........................................REMARKS

------------..............................................--------------..........................................---------------

1. EQUIJOIN ........................................1. NATURAL / INNER / SIMPLE

2. NONEQUI JOIN .............................. 2. JOIN USING

3. OUTER JOIN................................... 3. LEFT OUTER JOIN
........a. LEFT OUTER JOIN
........b. RIGHT OUTER JOIN
........c. FULL OUTER JOIN

4. SELFJOIN..................................... 4. JOIN ON

5. CARTESIAN PRODUCT...............5. CROSS JOIN

SET OPERATOR (Special / Another join)
......UNION / UNION ALL
......INTERSECT
......MINUS
......ANY SOME, ALL

ORACLE JOIN DETAILS

EQUIJOIN

If in the two or more tables there is a common field/column, then we can use EQIOJOIN to query common data from two or more tables. Usually Equal sing is using for this type of joins. It is equal to simple or inner join. We need to use Arithmetic Operator, Sql Operator to query data. ... contd.

SELECT DNAME,ENAME,JOB,SAL
FROM EMP,DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO;

NON EQUIJON
SELECT e.last_name, e.salary, j.grade_level
FROM employees e JOIN job_grades j
ON e.salary BETWEEN j.lowest_sal AND j.highest_sal;

OUTER JOIN
In oracle 9i a new ANSI/ISO systax for joins was introduced. Here are some types of this new syntax. Please avoid confusions here these are no new things except FULL OUTER JOIN, rest of all are just the new systax and ways to do the old things. Means they also performs EQUI, NON EQUI, SELF and OUTER joins but in a different way with a different syntax.

SELECT DNAME,ENAME,JOB,SAL
FROM EMP,DEPT
WHERE EMP.DEPTNO(+) = DEPT.DEPTNO;

LEFT OUTER JOIN

First of all try to understand what an Outer Join is. A left outer join is an outer join where you need to put the (+) sign on the left side of the where condition. Lets consider the following Outer Join Query.

SQL> select ename , dname
from dept d , emp e
where e.deptno(+) = d.deptno;

Equivalent syntax for such query in ANSI/ISO syntax would be:

select ename, dname
from dept d LEFT OUTER JOIN emp e;

SELECT e.last_name, e.department_id, d.department_name
FROM employees e LEFT OUTER JOIN departments d
ON (e.department_id = d.department_id) ;

RIGHT OUTER JOIN
This is an outer join where you put the (+) sign on the right side of the where condition. You will just replace the LEFT OUTER JOIN from the above query to RIGHT OUTER JOIN to perform a Right outer Join.

SELECT e.last_name, e.department_id, d.department_name
FROM employees e RIGHT OUTER JOIN departments d
ON (e.department_id = d.department_id) ;

FULL OUTER JOIN

This is an outer join when there are values deficient in both the tables. You will just replace the LEFT OUTER JOIN from the above query to FULL OUTER JOIN to perform a Full outer Join.

SELECT e.last_name, d.department_id, d.department_name
FROM employees e FULL OUTER JOIN departments d
ON (e.department_id = d.department_id) ;

SELF JOIN
Self join means joining a table to itself. Joining two columns of a single table to each other for a certain output. e.g. we want to see who works under who? or who is the manager of whom. Here is the full data for EMP table.

The MGR column denotes that who is the manager of the employee in the row. It actually is having the EMPNO of the manager for that employee. For instance the MGR for SMITH is 7902. And 7902 is the EMPNO of FORD so FORD is the manager of SMITH. Now to retrieve this kind of information out we will have to join the MGR and EMPNO column of the EMP table.
Here we have accessed the EMP table twice in the from clause but with a different alias, so we got two different views of the emp table named W and M. W for workers and M for Managers.The join condition states to retrive only those rwos where mgr number is equal to the employee number. The MGR for SMITH is 7902 which is the EMPNO of FORD the Condition is true so FORD has been displayed against SMITH.
The MGR for ALLEN is 7698 which is the EMPNO for BLAKE so BLAKE is displayed as ALLEN's Manager and so on....
First of all try to understand what an Outer Join is. A left outer join is an outer join where you need to put the (+) sign on the left side of the where condition. Lets consider the following Outer Join Query.

SELECT ENAME , DNAME
FROM DEPT D , EMP E
WHERE E.DEPTNO(+) = D.DEPTNO;

EQUIVALENT SYNTAX FOR SUCH QUERY IN ANSI/ISO SYNTAX WOULD BE:

SELECT ENAME, DNAME
FROM DEPT D LEFT OUTER JOIN EMP E;

SELECT A.ENAME MANAGER,A.EMPNO MANAGERID,
B.ENAME SUBORDINATE,B.EMPNO SUBORDINATEID
FROM EMP A,EMP B
WHERE A.EMPNO = B.MGR;

CARTESIAN PRODUCT

CROSS JOIN / CATESIAN JOIN

SELECT last_name, department_name
FROM employees
CROSS JOIN departments ;
SQL JOIN

NATURAL JOIN/ INNER JOIN / SIMPLE JOIN
A simple Natural Join or Natural Join Using Clause always perform an Equi Join on the tables. Say there are two tables which does not have any column with same name and same data, now the Natural Join On Clause is a help. Consider the following tables and Sample Data.

select * from salgrade;

select * from emp;

Please see how to perform Non Equi Join in such a situation. Here we will discuss how to perform a Non Equi Join using the by Natural Join Syntax.

select ename , sal , grade
from salgrade s JOIN emp e
ON (e.sal between s.losal and s.hisal);

(In the ON Clause you can put any consition to join bothe tables. )

SELECT department_id, department_name,
location_id, city
FROM departments
NATURAL JOIN locations ;

JOIN USING
SELECT employees.employee_id, employees.last_name,
departments.location_id, department_id
FROM employees JOIN departments
USING (department_id) ;

LEFT OUTER
SELECT e.last_name, e.department_id, d.department_name
FROM employees e LEFT OUTER JOIN departments d
ON (e.department_id = d.department_id) ;

JOIN ON
SELECT e.employee_id, e.last_name, e.department_id,
d.department_id, d.location_id
FROM employees e JOIN departments d
ON (e.department_id = d.department_id);

SELECT e.employee_id, e.last_name, e.department_id,
d.department_id, d.location_id
FROM employees e JOIN departments d
ON (e.department_id = d.department_id)
AND e.manager_id = 149 ;

CROSS JOIN
The question arises now is why is it necessary to join those tables from which we want to retrieve data by a single select statement. The answer is if we don't join these tables they generates a cartesian product or a cross join output i.e. all rows in one table are multiplied with all rows in the other table. Seeing the above data if a Cartesian Product is generated the result will be 28 rows i.e. 7 x 4. 7 rows of EMP and 4 rows of DEPT. Have a look what will happen if we don't put a join condition in a query which is retrieving data from more than one tables.

select ename , dname
from dept , emp;

To avoid a cartesian product you must provide a join condition for all the tables in the query. The formula for that is if n is the number of tables in the query then there should be n-1 number of join conditions in the query.There are many ways by tables can be joined depending upon the situations. Here are a few types of joins available.

SET OPERATOR

UNION
SELECT employee_id, job_id
FROM employees
UNION
SELECT employee_id, job_id
FROM job_history;

UNION ALL
SELECT employee_id, job_id, department_id
FROM employees
UNION ALL
SELECT employee_id, job_id, department_id
FROM job_history
ORDER BY employee_id;

INTERSECT
SELECT employee_id, job_id
FROM employees
INTERSECT
SELECT employee_id, job_id
FROM job_history;

MINUS
SELECT employee_id,job_id
FROM employees
MINUS
SELECT employee_id,job_id
FROM job_history;

SOME/ANY/ALL OPERATOR

SOME

SELECT ENAME, SAL, JOB, DEPTNO
FROM EMP
WHERE SAL>SOME (SELECT DISTINCT SAL
FROM EMP
WHERE DEPTNO=10)
ORDER BY SAL DESC

ANY

ANY OR SOME COMPARES A VALUE TO EACH VALUE RETURNED BY SUB QUERY
ANY MEANS MORE THAN LOWEST, WHICH CAN BE ANY.

ALL

SELECT ENAME, SAL, JOB, DEPTNO
FROM EMP
WHERE SAL > ALL (SELECT DISTINCT SAL
FROM EMP
WHERE DEPTNO=30)
ORDER BY SAL DESC

------------end ---------

Select Statement

2009-06-21T02:25:00.000-07:00

Select Statement

SELECT STATEMENT / QUERY DATA FROM TABLE

We shall learn how to query data from table. This very simple; we can see data from table as well as other database. After log in to SQL in the SQL prompt we have to type the command below. Then we can see the data. We can user here simple command, join command to retriving data from two or more table. Where Clause, Having, Arithmetic Operator, SQL operator, Alias, Concat and so on to seeing customize data from table. We like to use all the commands to find data from table one by one. Try to do this practice, hope all will work properly.

SQL> (This is SQL prompt)

SQL>SELECT * FROM EMP;

SELECT ENAME,JOB,SAL
ROM EMP;

“ALIAS” (COLUMN)
SELECT ENAME EMPLOYEE,JOB DESIGNATION, SAL SALARY
FROM EMP;

SELECT ENAME, SAL*12 YEARLY_SAL
FROM EMP;

SELECT ENAME, SAL+12 YEARLY
FROM EMP;

SELECT ENAME, SAL+12 "YEARLY SAL"
FROM EMP;

ARITHMETIC OPERATORS
SELECT ENAME,JOB,SAL,SAL*.2 BONUS ( *, /, +, - )- Precedence
FROM EMP;

STRINGS / LETERALS and CONCAT.
SELECT CONCAT (ENAME, SAL)
FROM EMP;

SELECT ‘The name of Employee is ’ ENAME
FROM EMP;

SELECT last_name ' is a ' job_id
AS "Employee Details"
FROM employees;

SELECT department_name
q'[, it's assigned Manager Id: ]' manager_id
AS "Department and Manager"
FROM departments;

SELECT EMPNO'-'ENAME EMPLOYEE, 'WORKS IN DEPARTMENT # ', DEPTNO
FROM EMP;

SELECT EMPNO'-'ENAME EMPLOYEE, 'MONTHLY SALARY IS', SAL
FROM EMP

SELECT EMPNO'-'ENAME EMPLOYEE, 'YEARLY SALARY IS', SAL*12 + NVL

(COMM,0) ANNUL_SALARY FROM EMP

CONCATENATE ""
SELECT ENAME, EMPNOJOB EMPLOYEE
FROM EMP;

SELECT ENAME, EMPNO'-'JOB EMPLOYEE
FROM EMP;

SELECT ENAME, SAL*12+ NVL(COMM,0) ANNUAL_SALARY
FROM EMP

SELECT ENAME, SAL*12+ NVL(COMM,200) ANNUAL_SALARY
FROM EMP

DISTINCT (AVOIDING DUPLICATE VALUE)
SELECT DISTINCT DEPTNO FROM EMP;

SELECT DISTINCT DEPTNO, DNAME FROM DEPT;

SELECT DISTINCT JOB
FROM EMP;

SELECT DISTINCT DEPTNO,JOB
FROM EMP;

NULL VALUE (NULL)

NVL (CHARACTER_COLUMN, 'STRING')

NVL (DATE_COLUMN, ''01-DEC-2007')

NVL (NUMBER_COLUMN, 0)

SELECT ENAME, SAL, NVL(COMM,0) FROM EMP;

WHERE CLAUSE

SELECT * FROM EMP
WHERE JOB = ‘MANAGER’
AND SAL < job =" ‘CLERK’"> 2000;

SELECT * FROM EMP
WHERE COMM IS NOT NULL;

ORDER BY

SELECT * FROM EMP
ORDER BY ENAME;

SELECT * FROM EMP
ORDER BY 2;

ORDER BY (ASCENDING)

SELECT ENAME, JOB, HIREDATE
FROM EMP
ORDER BY HIREDATE

SELECT DEPTNO,JOB,ENAME
FROM EMP
ORDER BY DEPTNO, SAL;

ORDER BY (DESCENDING)

SELECT ENAME, JOB, SAL*12, DEPTNO
FROM EMP ORDER BY ENAME DESC

SELECT DEPTNO,JOB,ENAME
FROM EMP
ORDER BY DEPTNO, SAL DESC;

SELECT DEPTNO,SAL, COMM
FROM EMP
ORDER BY COMM DESC

SELECT DEPTNO,SAL, COMM
FROM EMP
ORDER BY SAL DESC

COMPERISION OPERATOR / LOGICAL OPERATOR

{= EQUAL TO
> GREATER THAN
>= GREATER THAN OR EQUAL TO
<>= NOT EQUAL TO
=! NOT EQUAL TO }

(DATE & CHARACTER MUST BE ENCLOSED WITHIN SINGLE QUOTATION IN THE WHERE CLAUSE.)

SELECT * FROM EMP WHERE SAL > 5000;

SELECT DNAME, DEPTNO
FROM DEPT
WHERE DEPTNO>20;

ELECT ENAME, SAL, COMM
FROM EMP
WHERE COMM>SAL;

SELECT * FROM EMP
WHERE DEPTNO = 20;
SELECT ENAME, EMPNO, JOB, DEPTNO
FROM EMP
WHERE JOB ='CLERK'

SQL OPERATOR
BETWEEN
IN (LIST)
LIKE
IS NULL

NAGETIVE SQL OPERATOR
NOT BETWEEN
NOT IN
NOT LIKE
IS NOT NULL

SELECT EMPNO, ENAME, SAL, MGR
FROM EMP
WHERE MGR IN(7788,7566);

SELECT EMPNO, ENAME, SAL, MGR
FROM EMP
WHERE SAL IN(2000,3000);

SELECT * FROM EMP
WHERE DEPTNO IN (10,30,40);

BETWEEN
SELECT ENAME, JOB, SAL
FROM EMP
WHERE SAL BETWEEN 1000 AND 2000;

(LOWER LIMIT MUST BE SPECIFIED FIRST)

LIKE
SELECT ENAME, EMPNO
FROM EMP
WHERE ENAME LIKE 'A%';

SELECT ENAME, EMPNO
FROM EMP
WHERE ENAME LIKE 'AL%';

SELECT ENAME, EMPNO
FROM EMP
WHERE ENAME LIKE 'SM%';

SELECT ENAME, EMPNO
FROM EMP
WHERE ENAME LIKE '_ _ _ _';

SELECT ENAME, EMPNO
FROM EMP
WHERE ENAME LIKE '_ _ _';

SELECT ENAME, EMPNO
FROM EMP
WHERE ENAME LIKE '_ _ _ _ _';

SELECT * FROM EMP
WHERE ENAME LIKE ‘A%;’

SELECT * FROM EMP
WHERE ENAME LIKE ‘%ALI%’;

SELECT EMPLOYEE_ID, LAST_NAME, JOB_ID
FROM EMPLOYEES WHERE JOB_ID LIKE '%SA\_%' ESCAPE '\';

Is Null
SELECT ENAME, MGR
FROM EMP
WHERE MGR IS NULL;

SELECT ENAME, SAL, COMM
FROM EMP
WHERE COMM IS NULL;

NOT BETWEEN
SELECT ENAME, JOB, SAL
FROM EMP
WHERE SAL NOT BETWEEN 1000 AND 2000;

SELECT ENAME, JOB, SAL
FROM EMP
NAGATIVE SQL OPERATOR

NOT LIKE
SELECT ENAME, JOB, SAL
FROM EMP
WHERE JOB NOT LIKE 'S%';

SELECT ENAME, JOB, SAL
IMPORTANT:

AND = BOTH CONDITION MUST BE TRUE
OR = ANY CONDITION HAVE TO BE TRUE

AND WILL BE PERFORMED FIRST

SELECT EMPNO, ENAME, JOB, SAL, DEPTNO
FROM EMP
WHERE SAL>1000
AND (JOB='MANAGER'
OR JOB='CLERK')
FROM EMP
WHERE JOB NOT LIKE 'CL%'

IS NOT NULL
SELECT ENAME, JOB, SAL, COMM
FROM EMP
WHERE COMM IS NOT NULL;

SELECT ENAME, JOB, SAL, COMM
FROM EMP
WHERE JOB IS NOT NULL

SELECT ENAME, JOB, SAL, MGR
FROM EMP
WHERE MGR IS NOT NULL

SELECT EMPNO, ENAME, JOB, SAL
FROM EMP
WHERE SAL BETWEEN 1000 AND 2000
AND JOB<>'CLERK'

SELECT EMPNO, ENAME, JOB, SAL
FROM EMP
WHERE SAL>1000
AND JOB='MANAGER'
OR JOB='CLERK'

[1. Work on each row of Query
2. One result return per row
3. Many argument can be work
4. Nested query can be used
5. Can be used every where
Functions ( Single Row)]

CHARACTER FUNCTIONS

UPPER
SELECT UPPER(ENAME) FROM EMP;

SELECT LOWER (DNAME), upper('sql course') FROM DEPT

LOWER
SELECT LOWER(ENAME FROM EMP;

SELECT LOWER (DNAME), LOWER('SQL COURSE') FROM DEPT;

INITCAP
SELECT INITCAP(ENAME) FROM EMP;

INITCAP(DNAME) INITCAP(LOC)

SELECT INITCAP(ENAME), LOWER(ENAME), UPPER(ENAME)FROM EMP;

LENGTH
SELECT LENGTH(ENAME) FROM EMP;

SELECT LENGTH ('SQL COURSE'), LENGTH(DEPTNO), LENGTH(DNAME)
FROM DEPT;

SUBSTR
SELECT SUBSTR(ENAME,2,3) FROM EMP

SELECT SUBSTR('ORACLE', 2, 4), SUBSTR(DNAME, 2), SUBSTR(DNAME, 3, 5)
FROM DEPT

INSTR
SELECT INSTR(ENAME,’A’) FROM EMP;

SELECT DNAME, INSTR(DNAME, 'A'), INSTR(DNAME, 'ES'), INSTR(DNAME, 'C', 1, 2)
FROM DEPT

LPAD
SELECT LPAD(ENAME,10,’~’) FROM EMP;

SELECT LPAD(DNAME, 20,'*'),LPAD(DNAME,'20'), LPAD(DNAME, 20,'-')FROM DEPT;

RPAD
SELECT RPAD(ENAME,10,’~’) FROM EMP;
SELECT RPAD(DNAME, 20, '*'), RPAD(DNAME, '20'), RPAD(DNAME, 20, ' -'), RPAD(DEPTNO, 20, '-') FROM DEPT

LTRIM
SELECT DNAME, LTRIM(DNAME, 'A'), LTRIM(DNAME, 'AS'),
LTRIM(DNAME, 'ASOP')
FROM DEPT;

RTRIM
SELECT DNAME, RTRIM(DNAME, 'G'), RTRIM(DNAME, 'GHS'),
RTRIM(DNAME, 'N')
FROM DEPT

SOUND
SELECT ENAME, SOUNDEX (ENAME) FROM EMP
WHERE SOUNDEX(ENAME)=SOUNDEX('FRED')

SELECT job, soundex(job) FROM EMP
WHERE SOUNDEX (job)=SOUNDEX('manager')

TRANSLATE
SELECT ENAME, TRANSLATE(ENAME, 'C', 'P'), JOB, TRANSLATE (JOB, 'AR', 'TT')
FROM EMP
WHERE DEPTNO=10

REPLACE
SELECT ENAME, JOB, REPLACE(JOB, 'CLERK', 'ADMIN')
FROM EMP;

NESTED FUNCTION

SELECT DNAME, LENGTH(DNAME), LENGTH(TRANSLATE(DNAME, 'AS', 'A'))
FROM DEPT

SELECT TRANSLATE (DNAME, 'AS', 'A')
FROM DEPT;

SELECT DNAME, LENGTH(DNAME), LENGTH(REPLACE(DNAME, 'S'))
FROM DEPT;

SELECT DNAME, LENGTH(DNAME), LENGTH(DNAME), LENGTH(REPLACE(DNAME, 'S'))
FROM DEPT

CONCATENATE ()
SELECT ENAME SAL FROM EMP;

SELECT CONCAT (ENAME, SAL) FROM EMP;

NUMERIC FUNCTIONS

ROUND
SELECT ROUND(17879.879,2) FROM DUAL;

SELECT ROUND(17879.241,2) FROM DUAL;

SELECT ROUND (45.923,1),ROUND(45.923,1),ROUND(45.323,1),
ROUND(42.323,-1), ROUND (SAL/32,2) FROM EMP
WHERE DEPTNO=10

SELECT ROUND(45.923,1),
ROUND(45.923,1),
ROUND(45.323,1),
ROUND(42.323,-1),
ROUND(SAL/32,2)
FROM EMP
WHERE DEPTNO=10

SELECT ROUND(45.923) from dual

SELECT SYSDATE, ROUND(SYSDATE, 'MONTH'), ROUND(SYSDATE, 'YEAR')
FROM DUAL;

TRUNC
SELECT TRUNC(17879.879,2) FROM DUAL;

SELECT TRUNC(45.923,1),
TRUNC(45.923,1),
TRUNC(45.323,1),
TRUNC(42.323,-1),
TRUNC(SAL/32,2)
FROM EMP
WHERE DEPTNO=10

SELECT trunc(45.923) from dual

SELECT trunc(45.923), round(45.923), trunc(45.923,1), round(45.923,1)
from dual

SELECT SYSDATE, TRUNC(SYSDATE, 'MONTH'), TRUNC(SYSDATE, 'YEAR')
FROM SYS.DUAL;

CEIL
Largest Number
SELECT CEIL(SAL), CEIL(99.9), CEIL(101.76), CEIL(-11.1)
FROM EMP
WHERE SAL BETWEEN 3000 AND 5000;

FLOOR
Smallest Number

SELECT FLOOR(SAL), FLOOR(99.9), FLOOR(101.76), FLOOR(-11.1)
FROM EMP
WHERE SAL BETWEEN 3000 AND 5000

POWER
SELECT SAL, POWER(SAL,2), POWER(SAL,3), POWER(50,5)
FROM EMP
WHERE DEPTNO=10;

EXP
SELECT EXP(4)
FROM DUAL;

SQRT (Root Over)
SELECT SAL, SQRT(SAL), SQRT(40), SQRT(COMM)
FROM EMP
WHERE COMM>0;

SIGN
SELECT SAL-COMM, SIGN(SAL-COMM), COMM-SAL, SIGN(COMM-SAL)
FROM EMP
WHERE DEPTNO=30;

SELECT ENAME, SAL, COMM
FROM EMP
WHERE SIGN(SAL-COMM)=1

ABS
SELECT SAL, COMM, COMM-SAL, ABS(COMM-SAL), ABS(-35)
FROM EMP
WHERE DEPTNO=30;

MOD
SELECT MOD(15,4) FROM DUAL;

SELECT SAL,COMM, MOD(SAL,COMM), MOD(100,40)
FROM EMP
WHERE DEPTNO=30
ORDER BY COMM;

[date + number = date
date – number = date
date – date = number
date + number/24 (hours) ]

DATE FUNCTIONS

USENG ARITHMETIC OPERATION WITH DATE FUNCTION

ADD_MONTHS
SELECT ADD_MONTHS(HIREDATE,10) FROM EMP;

SELECT HIREDATE, HIREDATE+7, HIREDATE-7,SYSDATE-HIREDATE
FROM EMP
WHERE HIREDATE LIKE '%JUN%';

SELECT HIREDATE, HIREDATE+7, HIREDATE-7,SYSDATE-HIREDATE
FROM EMP
WHERE HIREDATE LIKE '%DEC%'

SELECT HIREDATE, ADD_MONTHS(HIREDATE, 3), ADD_MONTHS(HIREDATE, -3)
FROM EMP
WHERE DEPTNO=20

MONTHS_BETWEEN
SELECT MONTHS_BETWEEN(SYSDATE,HIREDATE) FROM EMP;

SELECT MONTHS_BETWEEN(SYSDATE, HIREDATE),
MONTHS_BETWEEN('01-JAN-87','05-NOV-07')
FROM EMP
Sunday = 1
Monday = 2
Tuesday = 3
Wednesday = 4
Thursday = 5
Friyday = 6
Suterday = 7
WHERE MONTHS_BETWEEN(SYSDATE, HIREDATE)>59

SELECT MONTHS_BETWEEN('01-JAN-07','05-NOV-07')
FROM dual

SELECT MONTHS_BETWEEN('05-NOV-07', '01-JAN-07')
FROM dual

NEXT_DAY
SELECT HIREDATE, NEXT_DAY(HIREDATE, 'FRIDAY'), NEXT_DAY(HIREDATE, 6)
FROM EMP
WHERE DEPTNO=10;

SELECT sysdate, NEXT_DAY(sysdate, 'FRIDAY'), NEXT_DAY(sysdate, 6)
from dual

LAST_DAY
SELECT SYSDATE, LAST_DAY(SYSDATE), HIREDATE, LAST_DAY(HIREDATE),
LAST_DAY('15-FEB-88')
FROM EMP
WHERE DEPTNO=20

SELECT LAST_DAY(SYSDATE) FROM DUAL;

CONVERSION FUNCTIONS

TO_CHAR
SELECT TO_CHAR(SYSDATE,’DD-MM-RR HH:MI’) FROM DUAL;
SELECT TO_CHAR(SAL,’999,999.00’) FROM EMP;

SELECT TO_CHAR(SYSDATE, 'DAY, DDTH MONTH YYYY')
FROM DUAL

SELECT TO_CHAR(SYSDATE, 'fmDAY, ddth month yyyy')
FROM DUAL;

SELECT TO_CHAR (SYSDATE, 'HH:MI:SS')
FROM DUAL;

SELECT TO_CHAR(SAL, '$9,999')
FROM EMP;

TO_NUMBER
SELECT TO_NUMBER(SUBSTR(HIREDATE,1,2)) FROM EMP;

SELECT EMPNO, ENAME, JOB, SAL
FROM EMP
WHERE SAL>TO_NUMBER('1500');

TO_DATE
SELECT TO_DATE(’10-10-04’,’MM-DD-YY’) FROM DUAL;

SELECT EMPNO, ENAME, HIREDATE
FROM EMP
WHERE HIREDATE=TO_DATE('DEC 3, 1981', 'MONTH DD, YYYY')

OTHER FUNCTIONS

NVL
SELECT NVL(SAL,100) FROM EMP;

SELECT SAL*12 +NVL(COMM,0), NVL(COMM, 1000), SAL*12+NVL(COMM,1000)
FROM EMP
WHERE DEPTNO=10;

NESTED FUNCTIONS REVISED
SELECT ENAME, NVL(TO_CHAR(MGR), 'UNMANAGEABLE')
FROM EMP
WHERE MGR IS NULL

DECODE
SELECT DECODE(JOB,’MANAGER’,SAL*.2,’CLERK’,SAL*.1,SAL*.05) FROM EMP;

SELECT last_name, job_id, salary,
DECODE(job_id, 'IT_PROG', 1.10*salary,
'ST_CLERK', 1.15*salary,
'SA_REP', 1.20*salary,
salary)
REVISED_SALARY
FROM employees;

SELECT last_name, salary,
DECODE (TRUNC(salary/2000, 0),
0, 0.00,
1, 0.09,
2, 0.20,
3, 0.30,
4, 0.40,
5, 0.42,
6, 0.44,
0.45) TAX_RATE
FROM employees
WHERE department_id = 80;

SELECT ENAME, JOB, DECODE (JOB, 'CLERK', 'WORKER', 'MANAGER', 'BOSS', 'UNDEFINED') DECODE_JOB
FROM EMP;

SELECT JOB, SAL, DECODE(JOB,'ANALYST', SAL*1.1, 'CLERK', SAL*1.15,
'MANAGER', SAL*0.95, SAL)DECONDED_SALARY
FROM EMP;

select grade,
decode(grade, '1', '15%', '2','10%', '3','8%','5%') Bonus
from salgrade;

GREATEST
SELECT GREATEST(1000, 2000), GREATEST(SAL,COMM)
FROM EMP
WHERE DEPTNO=30;

LEAST
SELECT LEAST(1000, 2000), LEAST(SAL,COMM)
FROM EMP
WHERE DEPTNO=30;

VSIZE
SELECT DEPTNO, VSIZE(DEPTNO), VSIZE(HIREDATE), VSIZE(SAL),
VSIZE(ENAME)
FROM EMP
WHERE DEPTNO=10;

FUNCTION (Group Functions)

SUM
SELECT SUM(SAL) FROM EMP;
SELECT SUM(SAL) FROM EMP
GROUP BY DEPTNO;
SELECT SUN(SAL) FROM EMP
GROUP BY JOB;

AVG
SELECT AVG(SAL) FROM EMP
GROUP BY DEPTNO;

MIN
SELECT MIN(SAL) FROM EMP;

SELECT MIN(SAL)
FROM EMP
WHERE JOB='CLERK';

MAX
SELECT MAX(SAL) FROM EMP;

COUNT
SELECT COUNT(*) FROM EMP;
SELECT COUNT(*) FROM EMP
GROUP BY JOB;

SELECT COUNT(*)
FROM EMP
WHERE DEPTNO=20;

STDDEV
The AVG, SUM, VARIANCE, and STDDEV functions can be used only with numeric data types. MAX and MIN cannot be used with LOB or LONG data types.

SELECT AVG(salary), STDDEV(salary),COUNT(commission_pct),MAX(hire_date)
FROM employees
WHERE job_id LIKE 'SA%';

VARIANCE

Select MAX(Noon), AVG(Noon), MIN(Noon), STDDEV(Noon), VARIANCE(Noon)
From COMFORT
Where City = ‘DHAKA’

GROUP BY COMMAND
SELECT JOB, AVG (SAL)
FROM EMP
GROUP BY JOB;

SELECT JOB, AVG (SAL), MIN (SAL), MAX (SAL)
FROM EMP
GROUP BY JOB;

SELECT JOB, AVG (SAL)
FROM EMP
WHERE JOB!='MANAGER'
GROUP BY JOB;

GROUP FUNCTION WITHIN GROUP
SELECT DEPTNO, JOB, AVG(SAL) FROM EMP
GROUP BY DEPTNO, JOB;
GROUP BY FUNCTION WITH INDIVIDUAL RESULT

SELECT MAX(SAL) FROM EMP
GROUP BY JOB;

SELECT MAX(SAL), JOB
FROM EMP
GROUP BY JOB;

SELECT DEPTNO, MIN(SAL)
FROM EMP
GROUP BY DEPTNO;

HAVING CLAUSE
SELECT DEPTNO, AVG(SAL)
FROM EMP
GROUP BY DEPTNO
HAVING COUNT(*)>3;

SELECT JOB, MAX(SAL)
FROM EMP
HAVING MAX(SAL)>=3000
GROUP BY JOB;

SELECT SUM(SAL)
FROM EMP
GROUP BY JOB
HAVING SUM(SAL) > 5000;

WHERE CALUSE
SELECT DEPTNO, AVG(SAL)
FROM EMP
GROUP BY DEPTNO
HAVING AVG(SAL)>2000;

SELECT JOB, AVG(SAL)
FROM EMP
WHERE JOB !='MANAGER'
GROUP BY JOB;

EXISTS
SELECT EMPNO, ENAME, JOB, DEPTNO
FROM EMP E
WHERE EXISTS (SELECT EMPNO FROM EMP
WHERE EMP.MGR=E.EMPNO)
ORDER BY EMPNO;

NOT EXISTS
SELECT DEPTNO, DNAME
FROM DEPT D
WHERE NOT EXISTS (SELECT 1 FROM EMP E
WHERE E.DEPTNO=D.DEPTNO);

NOT EXISTS VRSUS NOT IN

SELECT ENAME, JOB FROM EMP E
WHERE NOT EXISTS (SELECT MGR FROM EMP WHERE MGR=E.EMPNO)

--------------------------------end---------------------------------------------------

TCL-Transaction Control Language

2009-06-20T06:43:00.000-07:00

TCL (Transaction Control Language)

1. IMPECIT COMMIT (AUTO SAVE)
a. After DDL Command
b. Illegal Shutdown

2. EXPLECIT COMMIT
a. COMMIT
Commit;

b. ROLLBACK
ROLLBACK TO update_done;

c. SAVEPOINT
SAVEPOINT update_done;

Lock

It's a DBA scope of work and it is an Oracle Server internal process. Prevent destructive interaction between current transactions. Require no user action and automatically use the lowest level of restrictions. Oracle also allows the user to lock data manually. It has two modes, one is exclusive and the other is share.

-----------------------------------------------------end------------------------------------------------------------

Removing Oracle Database

2009-06-17T03:44:00.000-07:00

How to Removing All Oracle Database Components

Onece you install Oracle software; you cannot remove easily as well as others common software. If you want to remove completely the software from your hard drive / disk; please follow the procudures as describe below:
Use Oracle Universal Installer to remove Oracle components from the inventory on the computer. To do this click Oracle Installer from start menu and click install/deinstallation. Select the entire component if requires. Then follow below instruction.

Removing Oracle Keys from the Registry on Windows
1. Start the registry at the MS-DOS command prompt:
(Click Start Menu and type regedit and press ok)
Wwindow appear. In left side find:

2. Go to HKEY_CLASSES_ROOT.

3. Delete any key that starts with Oracle or ORCL.

4. Go to HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE.

5. Delete the ORACLE key.

6. Go to HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\odbcinst.ini.

7. Delete the Oracle ODBC Driver key.

8. Go to HKEY_CURRENT_USER\SOFTWARE\ORACLE.

9. Delete keys that start with Oracle or ORCL (if any exist).

10. Go to HKEY_CURRENT_USER\SOFTWARE\ODBC\odbcinst.ini.

11. Delete any Oracle keys.

12. Close the registry.

13. Reboot your computer.

If you face any problem or if you see it is not working then reboot your computer and follow the same procudures you did below.

Removing Oracle from the Start Menu

1. Delete SYSTEM_DRIVE:\Program Files\Oracle through Windows Explorer.
2. Delete icons from:
SYSTEM_DRIVE:\windows\start menu\programs\oracle - HOME_NAME
SYSTEM_DRIVE:\windows\start menu\programs\oracle installation products
Where HOME_NAME is the previous Oracle home name.
3. Delete all ORACLE_BASE directories on your hard drive.
4. Reboot your computer.

-------------- End----------

DML (Data Manipulation Language)

2009-06-16T02:49:00.000-07:00

DML (Data Manipulation Language)

(Used to interact with tables, store new records, change records, remove records, select data from tables.)
INSERT,UPDATE, DELETE, SELECT

INSERT
INSERT INTO EMP
VALUES (1,’ASAD’,5000,500,7787,10);

INSERT INTO EMP(EMPNO,ENAME,SAL)
VALUES (2,’ALEEM’,7000);

INSERT INTO EMP(EMPNO,ENAME,SAL)
VALUES (&no,’&name’,&salary);

INSERT INTO
(SELECT employee_id, last_name, email, hire_date, job_id, salary, department_id
FROM empl3
WHERE department_id = 50)
VALUES (99999, 'Taylor', 'DTAYLOR', TO_DATE('07-JUN-99', 'DD-MON-RR'),
'ST_CLERK', 5000, 50);

COPY DATA FROM ANOTHER TABLE

INSERT INTO sales_reps(id, name, salary, commission_pct)
SELECT employee_id, last_name, salary, commission_pct
FROM employees
WHERE job_id LIKE '%REP%';

UPDATE
UPDATE EMP (All field will be changed)
SET MGR = 7767;

UPDATE EMP
SET COMM = 200
WHERE COMM IS NULL;

UPDATE employees
SET job_id = (SELECT job_id
FROM employees
WHERE employee_id = 205),

MERGE
MERGE INTO table_name table_alias Syntax
USING (tableviewsub_query) alias
ON (join condition)
WHEN MATCHED THEN
UPDATE SET
col1 = col_val1,
col2 = col2_val
WHEN NOT MATCHED THEN
INSERT (column_list)
VALUES (column_values);

MERGE INTO empl3 c
USING employees e
ON (c.employee_id = e.employee_id)
WHEN MATCHED THEN
UPDATE SET
c.first_name = e.first_name,
c.last_name = e.last_name,
...
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);

DELETE
DELETE FROM EMP;

DELETE FROM EMP
WHERE SAL < 2000;

DELETE FROM employees
WHERE department_id =(SELECT department_id
FROM departments
WHERE department_name LIKE '%Public%');

TRUNCATE

TRUNCATE COMMAND FOR DELETING DATA FROM A TABLE IS ALTERNET COMMAND OF DELETE and can not be rolled back. (Only to deleting data but not structure/table.

TRUNCATE TABLE table_name;

TRUNCATE TABLE DEPT_30;

Constraints and Comments

2009-06-16T02:27:00.000-07:00

CONSTRAINTS

Constraints enforce rules at the table level and Column Level. Constraints prevent the deletion of a table, if there are dependencies. Restriction of DML (Insert, Update, Delete) Command in the Table

You can-
(At the same time as the table is created
After the table has been created
Define a constraint at the column or table level.
View a constraint in the data dictionary)

The following constraint types are valid:

Constraint Name Level
NOT NULL Column
UNIQUE Column / Table
PRIMARY KEY Column / Table
FOREIGN KEY Column / Table
CHECK Column / Table

Not Null

CREATE TABLE STUDENT_DATA
(STU_ID NUMBER(3) NOT NULL,
STU_NAME VARCHAR2(25) NOT NULL,
STU_FATHERS_NAME VARCHAR2(30),
STU_MOBILE NUMBER(15));

Unique
CREATE TABLE STUDENT_DATA
(STU_ID NUMBER(3) not null,
STU_NAME VARCHAR2(25) not null,
STU_FATHERS_NAME VARCHAR2(30) unique,
STU_CLASS VARCHAR2(7) unique,
STU_AGE NUMBER(2) unique,
STU_MOBILE NUMBER(15));

Table Level Unique Constraint
CREATE TABLE RECOVARY
(RECOVARY_ID NUMBER(10),
RECOVARY_DATE DATE,
RECOVARY_MODE VARCHAR2(15),
AMMOUNT NUMBER(12,2),
CONSTRAINT UNI_CUST_ID UNIQUE (CUSTOMER_ID))

Table Level Unique Constraint
CREATE TABLE RECOVARY_TEST
(RECOVARY_ID NUMBER(10) UNIQUE,
AMMOUNT NUMBER(12,2),
CONSTRAINT UNI_AND_ID UNIQUE (CUSTOMER_ID));

PRIMARY KEY
CREATE TABLE RECOVARY_TEST1
(RECOVARY_ID NUMBER(10) PRIMARY KEY,
CUSTOMER_ID NUMBER(10),
RECOVARY_MODE VARCHAR2(15),
AMMOUNT NUMBER(12,2));
CREATE TABLE S_USERS(
S_USER_ID VARCHAR2(10) PRIMARY KEY,
S_USER_NAME VARCHAR2(20) NOT NULL,
UNIQUE(S_USER_NAME),
S_USER_DESC VARCHAR2(50),
S_USER_TIME VARCHAR2(10) NOT NULL,
S_USER_STATUS CHAR(1) DEFAULT 'N');

CREATE TABLE RECOVARY_TEST2
(RECOVARY_ID NUMBER(10),
RECOVARY_MODE VARCHAR2(15),
AMMOUNT NUMBER(12,2),
CONSTRAINT PRI_PK_CUST_ID PRIMARY KEY (CUSTOMER_ID)));

FOREIGN KEY
CREATE TABLE USERS_TRANS(
TRAN_ID VARCHAR2(10) PRIMARY KEY,
TRAN_AMOUNT VARCHAR2(10),
CONSTRAINT FK_USER FOREIGN KEY(S_USER_ID) REFERENCES S_USERS )

CREATE TABLE USERS_LOGS(
LOG_ID VARCHAR2(10) PRIMARY KEY,
OUT_TIME VARCHAR2(10),
CONSTRAINT FK_LOG FOREIGN KEY(S_USER_ID) REFERENCES S_USERS )

CHECK
CREATE TABLE STUDENT_DATA
(STU_ID NUMBER(3) check (stu_id between 1 and 100),
STU_NAME VARCHAR2(25),
STU_FATHERS_NAME VARCHAR2(30),
STU_CLASS VARCHAR2(7),
STU_MOBILE NUMBER(15))

CREATE TABLE STUDENT_DATA
(STU_ID NUMBER(3) CHECK (STU_ID BETWEEN 10 AND 90),
STU_FATHERS_NAME VARCHAR2(30),
STU_FISH NUMBER(10,2) CHECK (STU_FISH BETWEEN 100.00 AND 400.00),
STU_MOBILE NUMBER(15));

CREATE TABLE STUDENT_DATA
(STU_ID NUMBER(3),
STU_NAME VARCHAR2(25),
STU_B_DATE DATE,
CONSTRAINT CH_STU_F CHECK (STU_FISH BETWEEN 100.00 AND 400.00));

OTHER CONSTRAINT

COMPOSITE PRIMARY KEY
CREATE TABLE STUDENT_DATA
(STU_ID NUMBER(3),
STU_NAME VARCHAR2(25),
STU_FATHERS_NAME VARCHAR2(30),
STU_CLASS VARCHAR2(7),
CONSTRAINT PRI_COM_ID_NAME PRIMARY KEY(STU_ID, STU_NAME));

CREATE TABLE CUSTOMER
(CUSTOMER_ID NUMBER(10),
FAX VARCHAR2(15),
EMAIL VARCHAR2(50),
CONSTRAINT PRI_PK_ID_CUST PRIMARY KEY (CUSTOMER_ID, CUSTOMER_NAME,PHONE));

ALTER (CONSTRAINT)

(USED TO ADD, MODIFY, DROP, ENABLE AND DISABLE CONSTRAINT).

ALTER TABLE EMP
ADD CONSTRAINT EMP_MGR_FK
FOREIGN KEY (MGR) REFERENCE EMP(EMPNO)

ADD NOT NULL
ALTER TABLE PRODUCT
MODIFY PRODUCT_ID NUMBER(10) NOT NULL;

ADD UNIQUE CONSTRAINT
ALTER TABLE PRODUCT
ADD CONSTRAINT UK_PID UNIQUE (PRODUCT_ID)

ADD PRIMARY KEY
ALTER TABLE PRODUCT
ADD CONSTRAINT PK_PRI_PRO_ID PRIMARY KEY(PRODUCT_ID);

ADD FOREIGN KEY
ALTER TABLE PR_DETAIL
ADD CONSTRAINT FK_PRTN_ID FOREIGN KEY (PR_ID) REFERENCES PR_MASTER(PR_ID)

ADD CHECK
ALTER TABLE PR_DETAIL
ADD CONSTRAINT CHK_PRTN_ID CHECK (PR_ID BETWEEN 1 AND 100)

Disable Constraints
ALTER TABLE EMP
DISABLE CONSTRAINT EMP_MGR_FK CASECAD

ALTER TABLE PRODUCT
DISABLE CONSTRAINT PK_PRI_PRO_ID

ALTER TABLE emp2
DISABLE CONSTRAINT emp_dt_fk;

Enable Constraints
ALTER TABLE EMP
ENABLE CONSTRAINT EMP_MGR_FK CASECAD

ALTER TABLE PRODUCT
ENABLE CONSTRAINT PK_PRI_PRO_ID

Drop Constraints
ALTER TABLE EMP
DROP CONSTRAINT EMP_MGR_FK

ALTER TABLE PRODUCT
DROP CONSTRAINT PK_PRI_PRO_ID

COMMENTS
COMMENT ON TABLE EMP IS 'FOR OUR EMPLOYEE';

COMMENT ON COLUMN EMP.EMPNO IS 'IS A UNIQUE NUMBER';

DDL(Data Definition Language)

2009-06-15T05:58:00.000-07:00

DDL(Data Definition Language)
Used to interact with database
Define objects, change objects, remove objects
CREATE, ALTER, DROP

CREATE TABLE

CREATE TABLE EMP(
EMPNO NUMBER(10) primary key,
ENAME VARCHAR2(30),
SAL NUMBER(12,2),
COMM NUMBER(12,2),
DEPTNO NUMBER(10));

CREATE TABLE web_orders
(ord_id number primary key,
order_date TIMESTAMP WITH TIME ZONE);

CREATE TABLE shipping (
delivery_time TIMESTAMP WITH LOCAL TIME ZONE);

CREATE TABLE shipping (
delivery_time TIMESTAMP WITH LOCAL TIME ZONE);
INSERT INTO shipping VALUES(current_timestamp + 2);

CREATE TABLE warranty
(prod_id number, warranty_time INTERVAL YEAR(3) TO MONTH);
INSERT INTO warranty VALUES (123, INTERVAL '8' MONTH);
INSERT INTO warranty VALUES (155, INTERVAL '200' YEAR(3));

CREATE TABLE DEPT_30
AS
SELECT empno, ename, sal*12 ANNSAL, hiredate
from emp
where deptno=30;

ALTER TABLE
ALTER TABLE (ADD)
ALTER TABLE EMP
ADD MGR NUMBER(5);

ALTER TABLE EMP (MODIFY)
ALTER TABLE EMP
MODIFY ENAME VARCHAR2(50);

DROP TABLE

DROP TABLE EMP;

DROP TABLE PURGE
DROP TABLE dept80 PURGE; (Table will delete also from Recycle bin)

PURGE

PURGE TABLE EMP; (Table will delete from Recycle bin, which your drop before)
PURGE RECYCLEBIN; (To make empty recycle bin)

FLASHBACK

(Table will be Roll Back from Recycle bin)
FLASHBACK TABLE[schema.]table[,[ schema.]table ]...
TO { TIMESTAMP SCN } expr
[ { ENABLE DISABLE } TRIGGERS ];
SELECT original_name, operation, droptime, FROM recyclebin;
RENAME
RENAME OLD NAME TO NEW NAME
RENAME STUDENT_REGISTER TO STUDENT_DATA;

DROP WITH CASECADE OPTION
DROP TABLE STUDENT_DATA CASCADE;

------------------------- end---------------------

DCL (Data Control Language)

2009-06-15T05:46:00.000-07:00

DCL(Data Control Languages)/ Security

(In this section some example of create users, grant privilege or role, revoke or removal privilege & role, drop user. This is for control or database & your system.)

Used to Control Database Security (SYSTEM SECURITY, DATA SECURITY.)

Create User, Change Password, Assign Privilege/Rights,
GRANT PRIVILEGE / RIGHTS, REVOKE RIGHTS
CREATE ROLE, GRANT PRIVILEGE TO ROLE, GRANT ROLE TO USERS.

USER
CREATE USER
You can create new users by this command and assign password to it.
CREATE USER SCOTT IDENTIFIED BY TIGER;

ALTER USER
ALTER USER SCOTT IDENTIFIED BY TIGERS;

DROP USER
DROP USER SCOTT;

ROLE
CREATE ROLE
CREATE ROLE R1 IDENTIFIED BY R1;
CREATE ROLE R1

GRANT PRIVILEGE TO ROLE
GRANT CREATE SESSION, CREATE TABLE TO R1;
GRANT RESOURCE, CONNECT TO R1;
GRANT UPDATE, DELETE ON EMP TO R1;

GRANT CREATE SESSION, CREATE TABLE,
CREATE SEQUENCE, CREATE VIEW TO SCOTT;

GRANT WITH GRANT OPTION
GRANT select, insert ON departments TO scott WITH GRANT OPTION;

PRIVILEGE
GRANT PRIVILEGE /ROLE
GRANT RESOURCE, CONNECT, CREATE TABLE, CREATE SESSION, DBA TO SCOTT;
GRANT R1 TO SCOTT;

GRANT SELECT ON EMPLOYEES TO SUE, RICH;

REVOKE PRIVILEGE / ROLE
REVOKE CREATE SESSION FROM SCOTT;

REVOKE SELECT, INSERT ON DEPARTMENTS FROM SCOTT;

DROP ROLE
DROP ROLE R1;

-----end--------

Summary of SQL

2009-06-14T22:53:00.000-07:00

Summary of SQL

Hand note / Short note for side bag or pocket.

You will not able to read it. Please save it as picture, then print it and keep it on your table/desk.

How connect to database

2009-06-14T04:05:00.001-07:00

Accessing Oracle Database with SQL*Plus or iSQL*Plus
In this chapter I will describe how to connect to database

There are 3 ways to connect to the database
To issue SQL and PL/SQL statements to Oracle Database, you can use either SQL*Plus or its Web version, iSQL*Plus. These tools enable you to perform the same database management operations, as well as to query, insert, update, or delete data directly in the database.

A. To start SQL*Plus:
1. From the Start menu, select Programs, then Oracle - HOME_NAME, then Application Development, then SQL Plus

2. In the Log On dialog box, enter the user name, password, and for the host string, the name of the database to which you want to connect.

B. To start SQL*Plus:

Alternatively, at the command line, you can enter the following command at a Windows command prompt:

Run your computer
Click start menu
Click Run
Type CMD
Then Click OK

A black screen appears. In this window type as below:

c:\> sqlplus username/password@database_name

For example, to log on as SYSTEM using the password welcome, you would enter:
c:\> sqlplus system/welcome@Test.

If you are logging on as SYS, you would need to connect as SYSDBA:
c:\> sqlplus sys/welcome@Test as sysdba

C. To start SQL*Plus: (iSQLPlus)

Without installing oracle database to your hadrdisk you may connect to database (internet). This called isqlplus.

To start iSQL*Plus:
1. Open your Web browser and enter the following URL:
http://hostname(Computer_name):port/isqlplus

If you don’t know then to find correct port number visit the below site.

ORACLE_BASE\ORACLE_HOME\install\portlist.ini file.

Find some port also. It can work or not. If not work then find port number and change it.
http://localhost:5560/isqlplus
http://localhost:7776/isqlplus
http://localhost:7788/isqlplus

A log in window appears.

2. In the Login window, enter the username, password, and for the connect identifier,
the name of the database to which you want to connect.
Type user name as SCOTT and for password tigerThen click OK.

if the information is correct then you may get message

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Apr 8 15:27:16 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

Now you can start your practice with PL/SQl.

-------------------- end----------

Bangladesh Soft Co. List-3

2009-06-10T06:08:00.000-07:00

Bangladesh Software Companies List # 3

1. Data Systems Internationals (DSI)

2. Desktop Computer Connection Limited

3. Dohatec New Media

4. Dolphin Computers Ltd.

5. Grameen Communications

6. IIT Bangladesh Ltd.

7. North American Computing Dynamic Inc

8. SATCOM COMPUTERS LIMITED

9. STAR Computer Systems Ltd.

10. Southtech Limited

11. Subarna Bijoy Limited

12. TAKAO Enterprise Limited

13. The Accsees Pvt. Ltd.

14. The Computers Limited

*************************

21. Company Name
Data Systems Internationals (DSI)

Contact Details
Contact Person : Mashiur Rahman
Chairman
Address : House No. 34, Road No. 11, Dhanmondi R/A
Dhaka-1205, Bangladesh. Estd 1991
Telephone : 9114492, 9130459
Fax : 880-2-819327
E-mail : imdl@citecho.net

Activities / Role
Total personnel : 38
S/W Development : Systems package for Accounting, Financial & Personnel Management Systems, Business Information Systems Inventory, Production & Delivery Systems
Library Information System Automated Mapping System
H/W platform : UNIX based multi user system
Operating System : UNIX, Windows, Windows NT, MS-DOS
Tools Used : ARCInfo, ERDAS, AutoCAD, SPSS PC+, Oracle, Developer/2000
Services : Database Services
Network Installation & Maintenance
Database Modelling, Tuning
Client/Server System Consulting and Implementation
Information System Installation, Management & Operation Service
Software Development & Operation Service
Information Processing Service
GIS Application System.

22. Company Name
Desktop Computer Connection Limited

Contact Details
Contact Person : Borhan Uddin
Managing Director
Address : 139 Shantinagar, Dhaka-1217
Estd 1991
Telephone : 824782, 833992, 837630
Fax : 880-2-836001
E-mail : desktop@bdonline.com

Activities / Role
No. of S/W personnel : 60
H/W Platform : Intel Server
Operating System : UNIX, Windows NT, Alpha
Services : Microsoft Certified Solution Provider
Microsoft Authorized Technical Education Center (ATEC)
Authorized Prometric Testing Center in Bangladesh
By Sylvan Learning Systems Pte Ltd., Australia
Business : Compaq
Distributor :
Best Power Inc. USA
American power conversion, USA
Onward Novell India
Microsoft Corporation USA
Authorized reseller of Other H/W products
Exclusive marketer of EASE BANK (Joint venture with computer ease Ltd.).

23. Company Name
Dohatec New Media

Contact Details
Contact Person : Nazmul Huda
CEO
Address : House No. 60, Road No. 5/A
Dhanmondi R/A, Dhaka
Telephone : 880-2-9569323, 880-2-501349
Fax : 880-2-9569326
E-mail : dohatec@bol-online.com

Activities / Role
No. of S/W personnel : 19
H/W Platform : PC
Operating System : Windows NT, Windows 95 & 98
S/W Development : Dohatec Yellow Pages Interactive Product Catalogue, CD-ROM Publishing, Dohatec Interactive Glossary, Microsoft Access Database Development, Information and Monitoring System on MS Access 97, Database of Editorial Service System for Pan American Health Organization, Database of Textbook Information Management System for Latin American Countries, Online Service Systems, Dohatec Front end
Tools used : MS Office, MS Studio, FrontPage, Adobe Acrobat, Adobe Photoshop, Folio Views, MS SQL Server, Dynamic HTML, Corel Draw
Services : Database Application, Web Database Application, Multimedia Application, System Programming, Website Development, Electronic Book Development
Business : Intermax Software, Inc.

24. Company Name
Dolphin Computers Ltd.

Contact Details
Contact Person : Mohmmed A. Wohab
Mr. Ahmed Hasan
Address : 4th Floor, Anchor Tower, 1/1 B Sonargaon Road,
Dhaka-1205, Bangladesh. Estd 1986
Telephone : 880 2 9661892 (Six Lines)
Fax : 880 2 869844
E-mail : dolphin@dhaka.agni.com

Activities / Role
Web : ww.dolphi.net
No. of Personnel : 75
S/W Development : Large scale database.
Bill of lading monitoring system for Shipping Lines
Hospital management systemiv. Webbase business solution
H/W Platform : X86/Pentium based PCs and servers
Operating System : Windows NT 4.0, Windows 95/98, Windows 3.11, Novel 4.1 & Unix
Tools Used : Developer 2000 for Oracle 7.0 based systems
Visual Basic 5.0 for MISSQL 6.5 based systems
Visual FoxPro 5.0
Borland C++4.5
Perl 5.0 for NT
Specialization : Client/Server based Database Intenet/Internet.

25. Company Name
Grameen Communications

Contact Details
Contact Person : Nazneen Sultana
Acting Managing Director
Address : Grameen Bank Complex, Mirpur, Section No. 2,
Dhaka-1216, Bangladesh.
Telephone : 880-2-9005350, 9005257-68
Fax : 880-2-806319
E-mail : gc@grameen.com

Activities / Role
No. of Personnel : 55
S/W Development : Grameen Banker 2.0
Grameen Accounts
Staff Loan Monitoring Software
Grameen Inventory System
Bad Debt Software
Pond Database Management System
Corporate Accounting and MIS
H/W Platform : Pentium PC, Windows NT
Operating System : DOS, Windows, Windows NT, Novell
Tools Used : Visual Basic, Access, Visual FoxPro, Clipper, C++, JAVA., SQL Server, Oracle
Services : Data Entry
Training
Internet
Consultancy
Business Alliance : IDRC, Canada (International Development Research Center)
Infinity Technology International, Bangladesh.

26. Company Name
IIT Bangladesh Ltd.

Contact Details
Contact Person : Aftabul Islam FCA, Chairman
Address : House No. 96, Road No. 11/A, Dhanmondi R/A
Dhaka, Bangladesh.
Telephone : 880-2-9120590
Fax : 880-2-9564379
E-mail : iit@bdonline.com

Activities / Role
No. of Personnel : 4
S/W Development : GSP Management System for BGMEA (Under Development) Course Enquiry and Registration Analysis System (in House Development)
H/W Platform : NT Server, Networking with 40 Pentium Nodes
Operating System : Widows 98, NT, UNIX
Tools Used : Multi Media Projectors, OHP, Electronic Copy Boards, Hubs, Cables
Services : Computer Education and Training, Software Development
Business Alliance : CMC Ltd.

27. Company Name
North American Computing Dynamic Inc

Contact Details
Contact Person
Masum Huq(USA) masumhuq@hotmail.com
Syed Ehsanul Huq Kamal (Bangladesh)
Address : 5, Mohakhali C/A, Paragoan House (6th Floor)
Dhaka-1212, Bangladesh. Estd 1994
Telephone : 880-2-870370, 870372
Fax : 880-2-871455
E-mail : info@nacdi.com, nacd@citechco.net,
nacdin@hotmail.com
Web : http://www.nacdi.com

Activities / Role
No. of Personnel : 365 (sw 17)
S/W Development : Health care billing/transactions system
Insurance transactions processing system
Inventory control system
On-line stock exchange reporting system
Banking transaction processing & Utilities billing software solutions Software for shipping and transportation organizations
H/W Platform : APPLE G3 Station, IBM, Compaq, Intel Based LAN (200)
PC terminal, Digital Alpha Workstation
Operating System : Widows 95, Dos, OS/2, DEC OSF, UNIX, Linux, Widows NT 3.51/4.0
Tools Used : Language: COBOL, C, C++, Visual Basic, JAVA.
Dev. Tools: Access, Visual Foxpro, Power Builder, Oracle developer 2000, SQL server.
Multimedia Suite, Macromedia Director 6.0, Media-100
Specialization/Services : Full management/clerical data entry/processing supports services
INTERNET and INTRANET solutions, www page creation Microfiche/Tape/Microfilm/Scanning/Image Compressing and Conversation Facilities.
Database solution in INFORMIX, ORACLE, POWER BUILDER
Technical training in divers areas with applications and
Customized software solutions
WWW website creation with ASP, and data BASE integration
CD ROM Authoring Services.

28. Company Name
SATCOM COMPUTERS LIMITED

Contact Details
Contact Person : Md. Jahidul Hasan
Md. Kamrul Hasan, MBA
Address : 18/1, Bokshi Bazar (1st-2nd Floor),
Dhaka-1100, Bangladesh. Estd 1992
Telephone : 880-2-862466, 506158, 9664703, 017-531423
Fax : 880 2 865771
E-mail : satcom@bdmail.net

Activities / Role
Trade Name : SATCOM
Managing Director : Swadesh Ranjan Saha A.C.A
No. of S/W Personnel : 8 (Eight) Persons
S/W Development : Share Management System
Accounting System
Payroll System
Inventory System
Sales & Billing System
Port Folio Management System
Operating System : Windows NT 4.0, Windows 95/98, LINUX
Tools Used : Visual Basic, Access, Visual FoxPro, Oracle, Developer 2000, SQL Server
Services : Data Entry, Data processing
Customize Software Development.

29. Company Name
STAR Computer Systems Ltd.

Contact Details
Contact Person : Md. Ali Akbar Khan
Managing Director
Address : 140, Shantinagar, 2nd Floor,
Dhaka-1217, Bangladesh. Estd 1991
Telephone : 880-2-9349320, 9332543
Fax : 880-2-837076
E-mail : scs@citechco.net

Activities / Role
No. of S/W Personnel : 5
S/W Development : Billing System
Software handling public share
Library Management System-Network Version
Library Information System
Payroll System
H/W Platform : Windows Platform
Operating System : Dos, Microsoft Windows, Windows NT 4.0 & UNIX
Tools Used
Services : Software Development
Hardware Sales & Services
Networking
Data Entry
Training.

30. Company Name
Southtech Limited

Contact Details
Contact Person : Syed Mamun Quader, Managing Direct & CEO
Address : Head Office House # 13, Road # 14/A,
Sector # 4, Uttara Model Town
Uttara, Dhaka-1230, Bangladesh. Estd 1995
Telephone : 880 2 895061, 894170
Fax : 880 2 895061
E-mail : southech@ciehco.net

Activities / Role
No. of S/W Personnel : 25
S/W Development : Design, Develop & Install Water & Environment system
Design, Development, Implementation of GIS
Design, Development, Implementation of MIS
Billing Software
Payroll Software
Accounting Software
Share Management Software
H/W Platform : IBM and IBM Compatible PCs
Operating System : MS-DOS Windows, Windows NT, Windows 97, Novell Tools Used: Visual FoxPro 5, MS SQL 6.5, MS Access97, Visual Basic
Services : Project identification & Pre-investment studies
Feasibility Studies & Resources Planning
Projects Benefits, Monitoring & Evaluation
Systems (MIS, GIS, LIS, M&E) Review, Analysis, Design & Development
Alpha-numeric, Spatial & Image Data entry/Conversion, Cleaning & Processing
Training and Technology Transfer
Census, Inventory & survey
Infrastructure planning Design & Supervision
Organization Design & Institution Building.

31. Company Name
Subarna Bijoy Limited

Contact Details
Contact Person : Akteruzzaman, Chairman
Address : 74 Kakrail, Dhaka-1000. Estd 1998
Telephone : 880 2 833117, 841963
Fax : 880 2 839994
E-mail : subarna@bdonline.com

Activities / Role
No. of S/W Personnel : 8
S/W Development : Hospital Management Systems
Travel Agency Management Systems
Accounts, Payroll, Inventory
CD-ROM Authoring
WEB Page Designing
Internet and IntraNet
Graphics and Multimedia Software and Solutions
Bangla Avidhan (Dictionary)
H/W Platform : Mac & PC
Operating System : MacOS, Windows, Windows 95/98 and Windows NT
Tools Used : MPW, C++, HyperCard, Power Builder, Macromind Director, Authorware, SCITEX Spherous, Visual Basic, Media-100, Pagemill, Supercard, Infini-D, Maya
Services : Graphics and multimedia Services.

32. Company Name
TAKAO Enterprise Limited

Contact Details
Contact Person : S. Kabir Ahmed, Managing Director
Address : House No.13, Road No.13A, Dhanmondi R/A,
Dhaka-1205, Bangladesh. Estd 1991
Telephone : 880-2-9123185-6
Fax : 880-2-818178
E-mail : suhrid@bdonline.com

Activities / Role
No. of S/W Personnel : 10
S/W Development : Electoral database for voter ID card
Comprehensive Payroll System
Accounts Receivable System
Accounting System
Store Inventory System
H/W Platform : IBM and IBM Compatible hardware, Sun
Operating System : DOS, Windows, Windows95, Windows NT, Solaris, Novell, SCO-UNIX
Tools Used : Oracle Developer 2000, Oracle Designer 2000, Power Object of Oracle, Delphi
Services : Software Development services, Support Services for Software, Provider of Voter’s Identity card of Election Commission of Bangladesh, Bureau Service
Business Alliance : Oracle Inc. USA
DataCard Inc. USA.

33. Company Name
The Accsees Pvt. Ltd.

Contact Details
Contact Person : Muneem Hossain Rana, Director
Address : 12/12 Iqbal Road, Mohammadpur,
Dhaka-1207, Bangladesh. Estd 1986
Telephone : 822723-4, 9122580, 9122587
Fax : 880-2-812542
E-mail : ranamah@dhaka.agni.com

Activities / Role
No. of S/W Personnel : 60 professionals
S/W Development : Bengali Word Processing Software "Barna"
Disaster Relief & Rehabilitation Monitoring System
Accounts Management & MIS Software
Life Insurance Policy Software
Personnel Information Management System
Payroll System
Medical Expense Reclaim & Reconciliation Software
Inter-Branch NOSTRO Accounts Reconciliation
H/W Platform : UNIX and Windows Platform
Operating System : UNIX, Windows, Dos
Tools Used : RDBMS
Services : Manufacturer and Supplier of UPS, Stabilizer and suppliers of Computer Training Facility Software Development and Support DTP
Business Alliance : OCEAN Office Automation Ltd.
DENCO International Ltd.
CISCO
Technotex (USA) Pte. Ltd.
SAMSUNG.

34. Company Name
The Computers Limited

Contact Details
Contact Person : K Atique-e-Rabbani, Managing Director
Address : Dhaka Metro Scout Bhaban (IF),
54 Inner Circular Road, Dhaka-1000
Bangladesh, Estd 1983
Telephone : 880-2-839091, 880-2-837357, 88017 695152
Fax : 880-2-833616
E-mail : atiqtcl@dhaka.agni.com

Activities / Role
No. of S/W Personnel : 10 (Total personnel 35)
Software developed : Nikash-General Ledger accounting package with accounts receivable and accounts payable
Mojuri-Payroll Management System
Bhander-Inventory Control Management System
Blkroy-Invoicing System
Sthlr-Fixed assets register
Nirupon-Costing System
Exam assessment system with inputs from OMR
Clinic Management system
Conversion of financial accounting and other software for largest sea port, Chittagong port in Bangladesh from RPG II under IBM Sys36 to RPG II under Unix
Hardware Platform : ICL PCs and servers, Fujitsu PCs and servers, IBM Sys 36
Operating System : Unix Sys V Rel 4, SCO Unix, Windows NT, Windows
Tools Used : Xbase, Dataflex, Visual Basic, Visual FoxPro,
Visual C++
Services : Aptech career computer education
System integration
IT consulting
Network design
Software project management
Software development
Hardware and software maintenance
Business Alliance : ICL, Fujitsu ICL for hardware distribution Microsoft reseller
Aptech Computer Education-Business Partner.

Home Page Back

Bangladesh Soft Co. List-2

2009-06-10T05:58:00.000-07:00

Bangladesh Software Companies List # 2

1. Technohaven Co.

2. Alphabet Associates Ltd.

3. Ananda Computers

4. Advantech Ltd

5. ATI Limited

6. BEXIMCO Softech Ltd

7. Bangladesh Engineering and Technological Ltd.

8. CIPROCO Computers Ltd.

9. Computer Solutions Ltd.

10. Computer Today

11. Company Name
Technohaven Co.

Contact Details
Contact Person : Habibullah N. Karim, President
Address : House No. 37, Road No. 6, Dhanmondi R/A
Dhaka-1205, Bangladesh.
Estd : 1986
Telephone : 880-2- 9668334 (4 Lines)
Fax : 880-2-9668338
E-mail : mailto:beximcomp@dhaka.agni.com
Website : hdtp://www.technohaven.com

Activities / Role
S/W personnel : 32 (Total personnel 92 as of 05/01/98)
Softwares Developed : POS, MIS & Sys Administration Software for Bangladesh Railway Seat Reservation & Ticketing Network
Banking System, WAN Design, Development & Implementation for BASIC (Bank)
Stock Exchange Automation System Customization & Implementation at Chittagong Stock Exchange
Custom Software Development
Library Management Software
Food Grain Inventory Control, Sorting & Costing Software for Directorate of Food, Bangladesh
Payroll, A/R, A/P, GL Accounting Software
Other in house softwares
H/W platform : Intel PC, Stratus/HP Fault Tolarant Systems
Operating System : SCO UNIX, Windows 95 & 98, Windows NT, DOS, AIX, OS/2, OS/400
Tools Used : X-Base, Oracle, Visual BASIC, C/CC++, Sybase, Power Builder, Developer 2000, SQL Server, FoxPro, Clipper, HTML, many others
Services : Systems Integration, Facilities Management, SW Development
Business : The Santa Cruz Operation Ltd. (SCO), UK
Stratus Computer, Inc. (STRATUS), USA
Equinox Systems Inc. (EQUINOX), USA
Distributed Processing Technology Inc. (DPT), USA

12. Company Name
Alphabet Associates Ltd.

Contact Details
Contact Person : Faruk A. Chowdhury
Address : 30, Babar Road, 22/9, Block-B,
Mohammadpur, Dhaka-1207. Estd 1987
Telephone : 880-2-812198
Fax : 880-2-813142

Activities / Role
No. of Personnel : 7
S/W Development : Customized solutions in
Lease Management System
Financial Accounting
Inventory Control
Medical Records System
H/W Platform : X86/Pentium based PC & Servers
Operating System : Windows NT, Windows 95/98, Novell 4.1 and UNIX
Tools Used : C++, Visual Basic
Services : Turnkey Computerized Solutions
Network installation and management
System analysis, design & Implementation
Data Entry.

13. Company Name
Ananda Computers

Contact Details
Contact Person : Mustafa Jabbar
Chief Executive
Address : 8/6 Segun Bagicha, Dhaka-1000, Estd 1987
Telephone : 9560669, 9554731, 9569134
Fax : 9562517
E-mail : ananda@bdonline.com

Activities / Role
No. of S/W Personnel : 15
S/W Development : Bijoy Bengali Script for MacOS, Windows,
Windows 95-98, Windows NT
Bangla Dictionary for Bijoy Bangla Script System
Animation and Video-Audio Systems
Graphics and Multimedia Software and Solutions.
Hospital Management Systems
Travel Agency Management Systems
Accounts, Payroll, Inventory
CD-ROM Authoring
WEB Page Designing
Internet and IntraNet
H/W Platform : Mac & PC
Operating System : MacOS, Windows, Windows 95/98 and Windows NT.
Tools Used : MPW, C++, HyperCard, PowerBuilder, Macromind Director
Authorware, SCITEX Spherous, Visual Basic, Media-100.
Services : Graphics and Multimedia Services
Business Alliance : Master Reseller of Apple, Distributor of SCITEX Digital Video, ULTRE Imagesetter, PowerPrint, Truevision, GOMARK, Canon etc.

14. Company Name
Advantech Ltd

Contact Details
Contact Person : Dr. M. Riazul Hamid
Address : House-55, Road-4/A, Dhanmondi R/A,
Dhaka-1209, Bangladesh Estd 1994
Telephone : 9661173, 9662978
Fax : 880-2-863056
E-mail : advantech@bdmail.net

Activities / Role
No. of S/W Personnel : 8
Software Developed : MIS for Hospitals, Clinics, Diagnostic Labs, Accounting Software, Inventory Control and Payroll system
H/W Platform : IBM Compatible PC & Server
Operating System : Windows 95, Windows NT
Database : Access, FoxPro, ORACLE
Tools Used : Visual Basic
Services : System Integration, Software development, Data Entry, Corporate Training
Business Alliance : Reseller of ORACLE
Agent of Honeywell Industrial Automation Control products

15. Company Name
ATI Limited

Contact Details
Contact Person : Shaikh Abu Reza
Address : ATI Center, House No.1, Road No.9/A, Sector No. 7,
Uttara, Dhaka-1230, Bangladesh
Telephone : 880-2-892863
Fax : 880-2-866364
E-mail : atil@bangla.net

Activities / Role
Software Personnel : 21
S/W Development : Fixed Asset Management System
General Ledger
Sales Analysis and Invoicing
Store Inventory
Block List Management
P.O.S.
PMIS
Payroll
Depot Management System
Location Management System
Share Management System
Retail Shop Management System
L/C Tracking
Teach Yourself Salah (Multimedia Production)
Elementary Science (Multimedia Production)
Operating System : Windows NT, Windows, UNIX, DOS
Tools Used : Developer 2000, Visual Basic, MS Access, Visual FoxPro, Adobe Premiere, Illustrator, Photoshop, Micro DC 30, Morph, Coral Draw
Hardware Platform : PC
Services : Software Development
Multimedia Development
Education & Training
Data Entry
System Integration
System Support
Data Conversion
Maintenance & Reengineering
Consultancy & Feasibility
Business Alliance : Genetic Computer School, Singapore; Siemens Bangladesh; Adobe Systems, India; Oracle.

16. Company Name
BEXIMCO Softech Ltd

Contact Details
Contact Person : Manzoor U. Ahmed, Executive Director
Address : House No.19, Road No.1, Dhanmondi,
Dhaka-1205, Bangladesh, Estd 1996
Telephone : 880 2 868220-7, 861891-5
Fax : 880 2 863470, 867647
E-mail : info@bexisoft.com

Activities / Role
S/W Development : Computer Telephony Integration (CIT):
Attend-IT Call Attendant and Voice Mail Server with Remote
Access and Web Server
Call Management/Billing:
Tele-Hawk Call Billing Software
Data Communication and Internet Products:
Comm Trap (CT) Data Capture/Comdriver
TCP/IP, PPP Router/Proxy Server
Operating System : Novel NetWare, Microsoft Windows NT, Unix, DOS, OS/2 and Real Time OS
Services : Software development in Data Communication & Multimedia Software.

17. Company Name
Bangladesh Engineering and Technological Ltd.

Contact Details
Contact Person : M. Azharul Haque
Director
Address : House No. 44, Road No. 3/A, Dhanmondi R/A
Dhaka-1209, Bangladesh.
Telephone : 880-2-9661223
Fax : 880-2-863056, 880-2-813100
E-mail : betscorp@bdmail.net
Betsint@bangla.net

Activities / Role
No. of S/W Personnel : 24
S/W Development : Computer based Map & Khatian Printing Software for Directorate of Land Records
MIS Software for North
H/W Platform : Intel and Motorola Based Processor systems.
Operating System : DOS 6.22, Windows 3.11, Windows 95, Windows NT 4.0 and UNIX
Software : Database Management Systems, System Analysis, System Design and Development.
Programming Language : C/C++, Visual Basic
Application Software : FoxPro, Access, MS SQL Server 6.5, ORACLE 7 and Developer 2000, Arc/Info and ArcView
Services : Data Conversion, CAD Conversion, Image Interpretation, Map Printing, Geo-Data Processing, GPS Service, Market Research Analysis, Financial Accounting Systems, Business Information Systems and Network. Services (LAN/MAN/WAN)
Business Alliance : Gateway/ALR, SPSS, Autodesk, CALCOMP/ SUMMAGRAPHICS & Business Partner ESRI, ERDAS, TRIMBLE, Microsoft, Motorola, Satellite Imagery and TALLY

18. Company Name
CIPROCO Computers Ltd.

Contact Details
Contact Person : Shafquat Haider
Address : 749, Sat Masjid Road, Dhanmondi,
Dhaka-1209, Bangladesh. Estd 1981
Telephone : 9125341
Fax : 880-2-813464
E-mail : ciproco@bdonline.com

Activities / Role
No. of Personnel : Software Division-10
S/W Development : Inventory System, Production Control System, Pay Roll, Hotel Management System, Land Records Management System (GIS), Water Resource Analysis System (GIS) etc.
H/W Platform : Intel and Motorola Based Processor systems.
Operating System : DOS 6.22, Windows 3.11, Windows 95, Windows NT 4.0 and UNIX
Software : Database Management Systems, System Analysis, System Design and Development.
Programming Language : C/C++, Visual Basic
Application Software : FoxPro, Access, MS SQL Server 6.5, ORACLE 7 and Developer 2000, Arc/Info and ArcView
Services : Data Conversion, CAD Conversion, Image Interpretation, Map Printing, Geo-Data Processing, GPS Service, Market Research Analysis, Financial Accounting Systems, Business Information Systems and Network. Services (LAN/MAN/WAN)
Business Alliance : Gateway/ALR, SPSS, Autodesk, CALCOMP/ SUMMAGRAPHICS & Business Partner ESRI, ERDAS, TRIMBLE, Microsoft, Motorola, Satellite Imagery and TALLY.

19. Company Name
Computer Solutions Ltd.

Contact Details
Contact Person : Moin Khan, Managing Director
Address : House # 2, Road # 11 (New), Dhanmondi,
Dhaka-1209, Bangladesh. Estd. 1985
Telephone : 880 2 816763, 9113673
Fax : 880 2 9126523, 813186
E-mail : csl@dhaka.agni.com
moin@dhaka.agni.com

Activities / Role
S/W Personnel : 15
Operating System : DOS, Windows, Windows NT, AIX
Development Tools : C, RPG, Visual FoxPro, Visual Basic, Lotus Notes, ORACLE, SQL Server
Major Project : World Bank Project for study and design of utility billing, system for the Sri Lanka Water Board.
Software for utility billing for the authorities in Nampapa, Laos; Faisalbad, Pakistan, Thimpu, Phuentsholin in Bhutan.
Software on accounting and financial areas
Patient record keeping system including financial reporting for the national Institute of Cardiovascular Diseases.
Software for Preparing the budget for the Ministry of Finance, Bangladesh, British Overseas Development Agency project
Customized ACCPAC, an accounting package, for over 65 clients.
Major Software : Hire Sales Accounting System
Development : Consumer Billing System
Project Monitoring System
Revolving Loan Monitoring System
Restaurant Management System
Payroll Management System
Inventory Control system
Budereting System
Hospital Management System
Accounting Solutions
Order Tracking System
Business Alliance : IBM, LOTUS NOTES, International Institute for Aerospace Survey & Earth Science (ITC), ACCPAC Accounting Solutions, Calcomp.

20. Company Name
Computer Today

Contact Details
Contact Person : Zillur Rahim
Address : A&Z Center, 3/1/J Purana Paltan (Bijoy Nagar), Dhaka-1000, Bangladesh. Estd 1995
Telephone : 9569282
Fax : 880-2-9569283.

Activities / Role
No. of S/W Personnel : 14
S/W Development : Accounting, Payroll, Inventory
H/W Platform : PC
Operating System : Windows, Windows NT
Tools Used : C++, Java, Visual Basic, FoxPro
Services : Software development and System Integration.

Home Page Back

Installation of Oracle 10g Database

2009-06-09T23:54:00.001-07:00

Oracle 10g database easy installation process

Insert CD to your CD-ROM drive.

Oracle Univesal Installler window appears. Nor action required here. Wait sometimes it will hide autometically and......

Welcome window appears.

Click NEXT

Select Installation Type window appears.

Choose Enterprise Edition(.....)

Click NEXT

Specify home details window apears

Write here your HOME_NAME and PATH where you want install your database.

Click NEXT

Product specific prerequisite checks window appears
Click white check box

Click NEXT

Select Configuration option window appears

Choose Create a database (default)

Click NEXT

Select database configuration window appears.

Choose General Purpose (Default)

Then Click NEXT

Specify database configuration option window appears.
In this window you have to do as below:

Type Global database Name

Then Type SID (can be same as example)
Database character set should default; (I mean as it is)

Click left lower corner box to create database with sample schemas.

Then Click NEXT

Select database management option window appears.

Nothing to do here

Click NEXT

Database Storage Option window appears.

Choose File System (Default)

Click NEXT

Specify Backup and Recovery Options window appears.

No action required here.

Choose do not automated backups (Default)

Click NEXT

Specific database schema password window appears.

In this window you have to specify the password as below: Type your password for below users;
Click NEXT

SYS
SYSTEM
SYSMAN
DBSNMP

S summary window appears.

You can see all type of settings, product details and so on. Nothing to do here.

Just Click Install
amd Wait ...

After finish installation

End of installation window appears.

To finish the installation click Exit

---------END----------

Oracle Partner-Bangladesh

2009-06-09T09:03:00.000-07:00

IBCS Primax Software (Bangladesh) Ltd

LtdSystems Integrator (SI)

Partner Membership Level: Certified Advantage

IBCS-PRIMAX Software (Bangladesh) Ltd,

(A Bangladesh-British joint venture company established under the laws of Bangladesh in 1989 by a group of IT professionals, with the ambition of creating a dynamic software house capable of delivering IT products of the highest quality both domestically and to the international arena.)

LEADS Corporation Limited

Systems Integrator (SI)

Partner Membership Level: Certified Advantage

(A leading software development company having web-enabled application software for Core Banking, Insurance and Manufacturing Industries.)

BASE Limited

Value Added Reseller (VAR)