Oracle Web Based Teaching

Our professors are Sr. Oracle professionals in Dubai and Faisalabad with OCP

Many years of large company experience and teaching experience with high reputations

Web based teaching, many hands-on practice sessions with real examples, high performance and an affortable cost. Learn everything straight from your computer!

High transaction Oracle 10g RAC OLTP database system with ASM and Hitachi/EMC/NetApp SAN on Unix and Linux servers

Multi-Terabyte Oracle Data Warehouse system for ERP, CRM and OLAP Full function DBA from server system setup, monitoring, maintenance, backup, recovery, replication and performance tuning

Oracle Database system installation and support services

more viist : http://oranak.comuf.com

Oracle Enterprise Manager

Oracle Enterprise Manager is a system management tool which provides an integrated solution for managing your
heterogeneous environment. It combines a graphical console, agents, common services, and tools to provide an
integrated, comprehensive systems management platform for managing Oracle products.

From the Oracle Enterprise Manager's Console, you can do the following tasks:

Administer, diagnose, and tune multiple databases
Distribute software to multiple servers and clients
Schedule jobs on multiple nodes at varying time intervals
Monitor objects and events throughout the network
Customize your display using multiple graphic maps and groups of network objects, such as nodes and databases
Administer Oracle Parallel Servers
Integrate participating Oracle or third-party tools

Inventory Database Examples

create table company
(COCODE                                   CHAR(2),
NAME                                      VARCHAR2(50),
 AC1                                      CHAR(2),
 AC2                                      CHAR(2),
 AC3                                      CHAR(2),
 ST15                                     CHAR(6),
 ST3                                      CHAR(6));


create table customer
(cocode                                   char(2),
cuscode                                   char(4),
cusname                                   varchar2(50),
addr                                      varchar2(150),
stregno                                   varchar2(15),
telephone                                 varchar2(15),
fax                                       varchar2(15),
email                                     varchar2(60));


create table machine
(cocode                                   char(2),
mcode                                     char(2),
name                                      varchar2(50),
dia                                       varchar2(10),
guage                                     varchar2(10),
capacity                                  number(8));


create table product
(cocode                                   char(2),
pcode                                     char(10),
name                                      varchar2(50),
count1                                    varchar2(10),
count2                                    varchar2(10),
count3                                    varchar2(10),
lycra                                     varchar2(10),
c1per                                     number(10,2),
c2per                                     number(10,2),
c3per                                     number(10,2),
lper                                      number(10,2),
c1bname                                   varchar2(10),
c2bname                                   varchar2(10),
c3bname                                   varchar2(10),
lbname                                    varchar2(10));


create table recyarn
(cocode                                   char(2),
docno                                     char(10),
cuscode          char(4),
PCODE                  CHAR(10),
recdate          date,
qty          number(10),
progref                                   varchar2(15),
dcno          char(10),
ref                                       varchar2(15),
FABRIC                                    VARCHAR2(30),
vehicalno                                 varchar2(20),
count1                                    varchar2(10),
count2                                    varchar2(10),
count3                                    varchar2(10),
lycra                                     varchar2(10),
c1bname                                   varchar2(10),
c2bname                      varchar2(10),
c3bname          varchar2(10),
lbname                  varchar2(10),
c1qty          number(10,2),
c2qty                  number(10,2),
c3qty                          number(10,2),
lqty                                      number(10,2),  
c1per                                     number(10,2),
c2per                                     number(10,2),
c3per                                     number(10,2),
lper                                      number(10,2),                  
transferfrom              char(15),
qtytrf              number(10,2),
qtybal            number(10,2),
qtyout                  number(10,2));


CREATE TABLE RETYARN
(DOCNO                                   CHAR(10),
RECDATE                                  DATE,
QTY                                      NUMBER(10),
PROGREF                                  VARCHAR2(15),
DCNO                                     CHAR(10),
vehicalno                                VARCHAR2(20),
CUSCODE                                  CHAR(4),
FABRIC                                   VARCHAR2(30),
PCODE                 CHAR(10),
REF                                      VARCHAR2(15),
count1                                   varchar2(10),
count2                                   varchar2(10),
count3                                   varchar2(10),
lycra                                    varchar2(10),
c1bname                                  varchar2(10),
c2bname                     varchar2(10),
c3bname         varchar2(10),
lbname                 varchar2(10),
c1qty         number(10,2),
c2qty                 number(10,2),
c3qty                         number(10,2),
lqty                 number(10,2),
c1per                                    number(10,2),
c2per                                    number(10,2),
c3per                                    number(10,2),
lper                                     number(10,2),
COCODE                                   CHAR(2),
TRANSFERFROM                             CHAR(15),
QTYTRF                                   NUMBER(10),
QTYBAL                                   NUMBER(10),
QTYOUT                                   NUMBER(10));


CREATE TABLE PRODINFHEAD
(DOCNO                                    CHAR(10),
DOCDATE                                   DATE,
WEIGHT                                    NUMBER(7,2),
MCODE                                     CHAR(2),
PROGREF                                   VARCHAR2(15),
CUSCODE                                   CHAR(4),
SHIFT                                     CHAR(1),
FABRIC                                    VARCHAR2(30),
WORKER                                    VARCHAR2(30),
GUAGE                                     VARCHAR2(10),
DIA                                       VARCHAR2(10),
WASTAGE                                   NUMBER(5,2),
GSM                                       VARCHAR2(10),
REMARKS                                   VARCHAR2(150),
TOTALWGHT                                 NUMBER(10,2),
COCODE                                    CHAR(2),
CHECKBOX                                  CHAR(1),
HOURS                                     VARCHAR2(10),
PCODE                                     VARCHAR2(10),
count1                                    varchar2(10),
count2                                    varchar2(10),
count3                                    varchar2(10),
lycra                                     varchar2(10),
c1bname                                   varchar2(10),
c2bname                                   varchar2(10),
c3bname                                   varchar2(10),
lbname                                    varchar2(10),
c1qty          number(10,2),
c2qty                  number(10,2),
c3qty                          number(10,2),
lqty                  number(10,2),
c1per                                     number(10,2),
c2per                                     number(10,2),
c3per                                     number(10,2),
lper                                      number(10,2),
SNO                                       NUMBER(2),
WGHT                                      NUMBER(10,2));


CREATE TABLE PRODINFDET
(DOCNO                                    CHAR(10),
DOCDATE                                   DATE,
WEIGHT                                    NUMBER(7,2),
MCODE                                     CHAR(2),
PROGREF                                   VARCHAR2(15),
CUSCODE                                   CHAR(4),
SHIFT                                     CHAR(1),
FABRIC                                    VARCHAR2(30),
WORKER                                    VARCHAR2(30),
GUAGE                                     VARCHAR2(10),
DIA                                       VARCHAR2(10),
WASTAGE                                   NUMBER(5,2),
GSM                                       VARCHAR2(10),
REMARKS                                   VARCHAR2(150),
TOTALWGHT                                 NUMBER(10,2),
COCODE                                    CHAR(2),
CHECKBOX                                  CHAR(1),
HOURS                                     VARCHAR2(10),
PCODE                                     VARCHAR2(10),
count1                                    varchar2(10),
count2                                    varchar2(10),
count3                                    varchar2(10),
lycra                                     varchar2(10),
c1bname                                   varchar2(10),
c2bname                                   varchar2(10),
c3bname                                   varchar2(10),
lbname                                    varchar2(10),
c1qty          number(10,2),
c2qty                  number(10,2),
c3qty                          number(10,2),
lqty                  number(10,2),
c1per                                     number(10,2),
c2per                                     number(10,2),
c3per                                     number(10,2),
lper                                      number(10,2),
SNO                                       NUMBER(2),
WGHT                                      NUMBER(10,2));


create table dchead
(DOCNO                                    CHAR(10),
DOCDATE                                   DATE,
WEIGHT                                    NUMBER(10,2),
MCODE                                     CHAR(2),
PROGREF                                   VARCHAR2(15),
CUSCODE                                   CHAR(4),
PCODE                                     CHAR(10),  
SHIFT                                     CHAR(1),
FABRIC                                    VARCHAR2(30),
WORKER                                    VARCHAR2(30),
GUAGE                                     VARCHAR2(10),
DIA                                       VARCHAR2(10),
WASTAGE                                   NUMBER(5,2),
GSM                                       VARCHAR2(10),
REMARKS                                   VARCHAR2(150),
REFNO                                     VARCHAR2(10),
TOTALWGHT                                 NUMBER(7,2),
GEN                                       CHAR(1),
INVNO                                     VARCHAR2(15),
INVDATE                                   DATE,
COCODE                                    CHAR(2),
count1                                    varchar2(10),
count2                                    varchar2(10),
count3                                    varchar2(10),
lycra                                     varchar2(10),
c1bname                                   varchar2(10),
c2bname                                   varchar2(10),
c3bname                                   varchar2(10),
lbname                                    varchar2(10),
c1qty          number(10,2),
c2qty                  number(10,2),
c3qty                          number(10,2),
lqty                  number(10,2),
c1per                                     number(10,2),
c2per                                     number(10,2),
c3per                                     number(10,2),
lper                                      number(10,2));


CREATE TABLE DCDET
(DOCNO                                    CHAR(10),
DOCDATE                                   DATE,
WEIGHT                                    NUMBER(10,2),
MCODE                                     CHAR(2),
PROGREF                                   VARCHAR2(15),
CUSCODE                                   CHAR(4),
PCODE                                     CHAR(10),  
SHIFT                                     CHAR(1),
FABRIC                                    VARCHAR2(30),
WORKER                                    VARCHAR2(30),
GUAGE                                     VARCHAR2(10),
DIA                                       VARCHAR2(10),
WASTAGE                                   NUMBER(5,2),
GSM                                       VARCHAR2(10),
REMARKS                                   VARCHAR2(150),
REFNO                                     VARCHAR2(10),
TOTALWGHT                                 NUMBER(7,2),
GEN                                       CHAR(1),
INVNO                                     VARCHAR2(15),
INVDATE                                   DATE,
COCODE                                    CHAR(2),
count1                                    varchar2(10),
count2                                    varchar2(10),
count3                                    varchar2(10),
lycra                                     varchar2(10),
c1bname                                   varchar2(10),
c2bname                                   varchar2(10),
c3bname                                   varchar2(10),
lbname                                    varchar2(10),
c1qty          number(10,2),
c2qty                  number(10,2),
c3qty                          number(10,2),
lqty                  number(10,2),
c1per                                     number(10,2),
c2per                                     number(10,2),
c3per                                     number(10,2),
lper                                      number(10,2),
SNO                                       NUMBER(2),
WGHT                                      NUMBER(10,2));






CREATE TABLE FABRETHEAD
(cocode                                   char(2),
docno                                     char(10),
cuscode          char(4),
PCODE                  CHAR(10),
recdate          date,
qty          number(10),
progref                                   varchar2(15),
dcno          char(10),
ref                                       varchar2(15),
FABRIC                                    VARCHAR2(30),
vehicalno                                 varchar2(20),
count1                                    varchar2(10),
count2                                    varchar2(10),
count3                                    varchar2(10),
lycra                                     varchar2(10),
c1bname                                   varchar2(10),
c2bname                      varchar2(10),
c3bname          varchar2(10),
lbname                  varchar2(10),
c1qty          number(10,2),
c2qty                  number(10,2),
c3qty                          number(10,2),
lqty                                      number(10,2),  
c1per                                     number(10,2),
c2per                                     number(10,2),
c3per                                     number(10,2),
lper                                      number(10,2),                  
transferfrom              char(15),
qtytrf              number(10,2),
qtybal            number(10,2),
qtyout                  number(10,2));

Useful Triggers for Oracle Developers

set_window_property(forms_mdi_window,window_state,maximize);
set_window_property('WINDOW1',window_state,maximize);
-------------------------------------
DECLARE
A TIMER;
BEGIN
A:=CREATE_TIMER('ABC',200,REPEAT);
END;
-----------------------------------------------
:DUMMY.A:= TO_CHAR(SYSDATE,'DAY   DD-MON-YYYY   HH:MI:SS AM');
-----------------------------------------

DECLARE
A NUMBER;
BEGIN
SELECT MAX(TO_NUMBER(INVNO)) INTO A FROM SHEAD
WHERE SHEAD.CCODE=:SHEAD.CCODE;
IF A IS NULL THEN
A:=1;
ELSIF A IS NOT NULL THEN
A:=A+1;
END IF;
:SHEAD.INVNO:=A;
END;
-------------------------------
key-next-item
DECLARE
a_value_chosen BOOLEAN;
BEGIN
a_value_chosen := Show_Lov('customer');
IF NOT a_value_chosen THEN
Message('You have not selected a value.');
Bell;
RAISE Form_Trigger_Failure;
END IF;
end;
----------------------------------------
when-button-pressed(print)
Declare
     pl_id ParamList;
BEGIN

   Pl_Id := Get_Parameter_List('tmpdata');
   IF NOT Id_Null(Pl_Id) THEN
     Destroy_Parameter_List( pl_id );
   END IF;
    Pl_Id := Create_Parameter_List('tmpdata');
    Add_Parameter(pl_id,'P_ccode',TEXT_PARAMETER,:GLOBAL.CCODE);
    Add_Parameter(pl_id,'P_INVNO',TEXT_PARAMETER,:SHEAD.INVNO);
    Add_Parameter(pl_id,'destype',TEXT_PARAMETER,'screen');
    Run_Product(REPORTS, 'D:\AC\sale_invoice.rdf', SYNCHRONOUS, RUNTIME,FILESYSTEM,Pl_Id, NULL);
End;
--------------------------------------

:amount:=round(nvl(:qty,0),2)*round(nvl(:price,0),2);
IF :AMOUNT >= 50000 THEN
:DISCAUNT:=5;
:TAMOUNT:=NVL(:AMOUNT,0)*5/100;
:STAX:=NVL(:AMOUNT,0)-NVL(:TAMOUNT,0);
ELSIF :AMOUNT >= 30000 THEN
:DISCAUNT:=3;
:TAMOUNT:=NVL(:AMOUNT,0)*3/100;
:STAX:=NVL(:AMOUNT,0)-NVL(:TAMOUNT,0);
ELSIF :AMOUNT >= 10000 THEN
:DISCAUNT:=1;
:TAMOUNT:=NVL(:AMOUNT,0)*1/100;
:STAX:=NVL(:AMOUNT,0)-NVL(:TAMOUNT,0);
ELSE
:DISCAUNT:=0;
:TAMOUNT:=0;
:STAX:=NVL(:AMOUNT,0)-NVL(:TAMOUNT,0);
END IF;
-----------------------------------------

SQL Commands

Create Command:
------------------------
Create command is used to create a table.

Syntax:
---------------
Create table
< table_name> ( col_name1 datatype(size),
col_name2 datatype(size), ,, ,, ,
col_namen datatype(size) );

ex:
------

create table student ( sno number(3), sname varchar2(20), marks number(3));
Output:
---------
Table Created.

Insert command: 
--------------------
Insert command is used to insert the rows into the table.

Syntax:
----------
Insert into < table_name> values ( val1, val2, ....., valn );

Ex:
-----
insert into student values (101, 'ali', 80);
insert into student values (102, 'kiran', 85);
insert into student values (103, 'asif', 66);

For every insert command , the response we get is " 1 row created. "

Using null keyword: 
--------------------------
Null keyword is used , if we do not have value for a column.

Ex:
---
insert into student values (104,'ali', null);
insert into student values (105,null, null);

2nd syntax of insert command: 
----------------------------------------
insert into student( col1, col2, ..coln ) values ( val1, val2, ..., valn );

ex:
-----
insert into student ( sno, sname ) values (106,'haider');

The leftover column will have null value.

Selecting the rows from the table: 
--------------------------------------------

To retrieve the rows from the table select command is used.

Basic Syntax:
----------------
select * from < table_name>;

Ex:
-----

select * from student;

In the above command, * is a special character which is used to display all the information from the table.

To select specific columns: 
-----------------------------------
select col1, col2... , coln from the <table_name>;

ex:
-----
select sno, sname from student;
select sname from student;

Selecting specific rows:
-------------------------------

Where clause is used to filter the rows from the table.

Syntax:
---------

select * from < table_name> where < condition >;

ex:
------

select * from student where marks > 70;

Combination of selecting specific row and selecting specific columns:
------------------------------------------------------------------------------------------

select sno from student where marks > 70;

Using Arithmetic operations with select command: 
-----------------------------------------------------------------

select empno, ename , sal, sal* 12 , deptno from emp;

Using column Alias: 
-------------------------

Column alias is process of providing user definied column heading. select empno, ename , sal, sal* 12 annual_sal , deptno from emp; In the above query annual_sal is the column alias.

Using Distinct keyword: 
-------------------------------

Distinct keyword is used to get the distinct ( unique ) values. Duplicates will be supressed.

ex:
------

select distinct deptno from emp;

Update command: 
-----------------------

This command is used to change the data present in the table.

Syntax:
---------

Update < table_name> set <col_name> = < value> where < condition> ;

ex:
----

update student set marks =95 where sno =101;

Output:
---------

1 row updated.

Updating multiple columns:
-----------------------------------

update student set sname='nabil' , marks =48 where sno =102;

Note:
------

Update command without where clause , will update all the rows.

ex:
---

update student set marks=80;

Using Delete command: 
------------------------------

Delete command is used to delete the rows from the table.

Syntax:
---------

delete from < table_name> where < condition >;

ex:
-----

delete from student where sno=103; Output: 1 row deleted.

Note:
------

Delete command without where clause will delete all the rows.

ex:
----

delete from student;

( All the rows are deleted )

++++++++

Alter command: 
--------------------

Alter command is used to change the structure of the table.
We can perform following activities using Alter command

1) Adding a new column
2) Droping an existing column
3) Modifying a column
4) Renaming a column

Adding a new column:
----------------------------

Alter table < table_name> add ( col_name1 datatype(size), col_name2 datatype(size), ,, ,, ,, col_namen datatype(size) );

ex:
-----

Alter table student add ( city varchar2(10), state varchar2(10)); Table altered.

Note:
The new columns will have null values.

Droping a column:
-----------------------

Alter table < table_name > drop ( col_name1, col_name2,..., col_namen);

Ex:
----

Alter table student drop ( city, state);

Table Altered.

Modifying a column:
-------------------------

By using modifying we can increase and decrease the size of the column.

ex:
---

Alter table student modify ( sname varchar(20)); Table Altered.

Decreasing the size : 
--------------------------

Alter table student modify ( sname varchar(15)); Table altered.

Note:
-------

To decrease the size , the existing table data should fit into new size. By using Modify keyword , we can also change the datatype of the column.

Ex:
-----

Alter table student modify ( sname number(15));

Note:
-------

To change the datatype, column should be empty.

Renaming a column:
--------------------------
Syntax:
-----------

Alter table <table_name> rename column <old_name> to <new_name>;

Ex:
----

Alter table student rename column sno to roll_no; Table Altered.

Truncate command: 
-------------------------

This command is used to remove all the rows from the table.
Truncate table < table_name>;
Truncate table student;

Rename command:
------------------------

This command is used to change the table name.

Syntax:
--------

Rename <old_table_name > to < new_table_name>;

Syntax:
--------

Rename student to stu1; Table renamed. From now, we need to access the table by using the new name.

Drop command: 
--------------------

This command is used to remove the table from the database.

Syntax:
----------------

Drop table < table_name >;

ex:
----

Drop table stu1; Table dropped.

Some Constraints Example

CONSTRAINTS

PRIMARY CONSTRAINTS        : PRIMARY, UNIQUE, CHECK, REFERENCES)
SECONDARY CONSTRAINTS      : NOT NULL, DEFAULT)

CONSTRAINTS BASED ON 2 LEVELS COLUMN LEVEL and TABLE LEVEL
Except Not null all others can be defined as both table and column level.
But not null as only column level.

Eg. For SECONDARY CONSTRAINTS
~~~~~~~~~~~~~~~~~~~~~~~

1. NOT NULL CONSTRAINT

  CREATE TABLE EMP9( ENO NUMBER(3)  NOT NULL,
           ENAME VARCHAR2(10));


   2. DEFAULT CONSTRAINT

CREATE TABLE EMP9(ENO NUMBER(3)  NOT NULL,
        ENAME VARCHAR2(10),DOJ DATE DEFAULT SYSDATE);

   3. PRIMARY CONSTRAINT(COLUMN LEVEL)

UNIQUE

CREATE TABLE EMP9(
ENO NUMBER(3)  NOT NULL CONSTRAINT UNIEMP UNIQUE,
ENAME VARCHAR2(10));

PRIMARY KEY

CREATE TABLE EMP9(
ENO NUMBER(3) CONSTRAINT PKEMP9 PRIMARY KEY,
ENAME VARCHAR2(10));

   4. CHECK CONSTRAINT

CREATE TABLE BANK(
ACNO NUMBER(2) CONSTRAINT PKBANK PRIMARY KEY,
ACTYPE VARCHAR2(2) CONSTRAINT CKBANK CHECK ( ACTYPE IN
        ('SB','CA','RD')),
ACNAME VARCHAR2(10),
AMOUNT NUMBER(4));


   5. REFERENCES

CREATE TABLE EMP9(
ENO NUMBER(3) CONSTRAINT PKE9 PRIMARY       KEY,
JOB VARCHAR2(10),
ENAME VARCHAR2(10),
MGR NUMBER(4) REFERENCES EMP9(ENO));

   6. REFERENCES(REFERING TO DIFFERENT TABLE)

CREATE TABLE DEPT9(DEPTNO NUMBER(2) CONSTRAINT PKDNO PRIMARY  KEY, DNAME VARCHAR2(10), LOC VARCHAR2(10));


CREATE TABLE EMP9( EMPNO NUMBER(4),ENAME VARCHAR2(10),
       SAL NUMBER(7,2),DEPTNO NUMBER(2) CONSTRAINT FKDNO REFERENCES DEPT9(DEPTNO));




7. TABLE LEVEL CONSTRAINTS


     UNIQUE  TABLE LEVEL

CREATE TABLE BANK( ACNO NUMBER(3),ACTYPE VARCHAR2(10),
        BAL NUMBER(7,2),PLACE VARCHAR2(10),CONSTRAINT UNIBANK          UNIQUE(ACNO,ACTYPE));

     PRIMARY KEY(TABLE LEVEL)

CREATE TABLE BANK( ACNO NUMBER(2), ACTYPE VARCHAR2(2) CONSTRAINT   CKBANK CHECK (ACTYPE IN ('SB','CA','RD')),AMOUNT NUMBER)

Computerized Weaving Software in ORACLE

All enterprise strives for optimized results and performance. With computerized system, enterprise can achieve their desired goals.
 Can make accurate intime decisions leading to maximum benefits. Our weaving management software allows to save record at each step
of weaving. You can manage the quality production contracts, yarn records for warp & weft, bags required, conversion rates etc.. Contract
 based yarn buying, local storage and issuance to sizing and beem receiving, article assignment to Looms & Workers. Daily Production,
 Performance & Stop-age records with reasons. Calculate workers efficiency and salary by production. Article delivery and inward / outward
 gate passes.

Always get the current status of all contracts, yarn requirements, Sizing status, Loom Productions, Fabric Stock delivered and returned all
 in one software. Not only this, integrated accounts management system adds recovery options, payments made, expenses done, cash & bank
 statements parties ledgers and more. Software has also built in users management system and is multi terminal supported.

Key Features:

• Contract based orders management.
• Current status of all contracts.
• Warp / Weft Yarn bags report.
• Production Reports.
• Fabric delivery & return records.
• Sizing Reports & and beem status.
• Worker Production and Salary Calculation.
• Integrated Accounting System.
• Contract based Invoice Recoveries.
• Expense records, Party Ledgers, Payments Records.
• Standardized Accounts Reports.
• Current Stock Reports.
• One click Backup option.
• A complete Business Manager.
• Users Management Included.
• Software extendable to multi-terminals.
• Software Applicable in…
• Weaving Units.
• Looms Management.
• Production Management & Performance Tracking.
• Related Businesses.