Oracle Cast Function Use

The Oracle CAST function converts one data type to another.  The CAST function can convert built-in and collection-typed values into other built-in or collection typed values.

 CAST can convert a date or other unnamed operand (or a nested table or other named collection) into a type-compatible datatype or named collection.  For this use of CAST, type_name and/or operand must be of (or evaulate to) a built-in datatype or collection type .

CAST and ANYDATA Type

When using CAST to convert an operand, the expr can also be either a built-in datatype or a collection type; however, it can be an instance of an ANYDATA type as well. When the expr is of an ANYDATA type, CAST attempts an extraction of the value of the ANYDATA expr and returns it if it matches the CAST target type.  If the there is no match to the CAST target type, NULL is returned.

CAST with LOB Datatypes

 LOB datatypes are not directly supported by the CAST function.  Using CAST to convert CLOB values into a character datatypes or BLOB values into the RAW datatype results in the database converting the LOB value implicity to character or raw data.  Once this implicit conversion is done, the resulting value is CAST into the target datatype.  This process will throw an error if the resulting value is larger than the target type.

The Oracle docs note the syntax for Oracle CAST as follows:

CAST({ expr | MULTISET (subquery) } AS type_name)

With the Oracle CAST function a block could be re-written as:

DECLARE
  v NUMBER;
BEGIN
  a(CAST (v AS INTEGER));
END;

On the other hand, the function could be re-written with Oracle CAST as:

DECLARE
  v NUMBER;
BEGIN
  a(v::INTEGER);
END;

Each of these uses of the CAST function will produce the same result.

Make Calendar in Oracle Apps

Followed these Steps which is of no use in Oracle apps.

1. Create a new MODULE in Forms Builder
2. Then open the stndrd20.olb file (this .olb file is located
in the ORACLE_HOME/TOOLS/DEVDEMXX/Forms FOLDER)
3. You will see 'STANDARDS' appear under 'Object Libraries' in the Object
Navigator
4. Double click on the book-like icon next to 'STANDARDS'
5. Expand the object library window that appears
6. Click on the 'COMPONENTS' tab
7. Look for the 'CALENDAR' object library
8. Drag-n-drop the 'CALENDAR' object into your new form
9. Notice all the new items are now in your form marked with a red arrow*
*The red arrow means that the items are subclassed.
10. Attach the PL/SQL library CALENDAR.PLL
(this .pll file is also located in the ORACLE_HOME/TOOLS/DEVDEMXX/Forms
FOLDER)
11. In Forms, Create a new block on a new canvas
12. Create a Key-Listval trigger on the date item for which you
would like to use the Date LOV Window. Add the below code to display the
calendar using the Date_LOV package.

date_lov.get_date(sysdate, -- initial date
'emp.hiredate', -- return block.item
240, -- window x position
60, -- window y position
'Start Date', -- window title
'OK', -- ok button label
'Cancel', -- cancel button label

13. If you want the end user to be able to close the Date List of Values
window by clicking the window close button (the X) in the title bar, create a
form-level When-Window-Closed trigger with the following code:

When-Window-Closed trigger:
GO_BLOCK('EMP');

VIEW-SEQUENCE-INDEX

 VIEW=  THE SUBSET OF THE TABLE. USE FOR COMPLICATED COMPLEX QUERY.

SQL> SELECT ENAME,JOB,SAL FROM EMP
  2  WHERE DEPTNO IN (10,20)
  3  OR JOB = 'SALESMAN' OR
  4  SAL <= 2500;

ENAME      JOB              SAL
---------- --------- ----------
AHMAD      CLERK          40000
MARIA      OFFICER         7000
ALI        CLERK           5000
SAHIR      CLERK           2000
ARIF       CLERK           2500
ASIF       DBA            77788
KIRAN      MANAGER         2000
SANA       MANAGER         2200
KASHIF     OFFICER         1400
IRFAN      CLERK           1200
EMRAN      CLERK           1000

ENAME      JOB              SAL
---------- --------- ----------
SIDRA      ASSISTANT        900
SMITH      CLERK            800
ALLEN      SALESMAN        1600
WARD       SALESMAN        1250
JONES      MANAGER         2975
MARTIN     SALESMAN        1250
CLARK      MANAGER         2450
SCOTT      ANALYST         3000
KING       PRESIDENT       5000
TURNER     SALESMAN        1500
ADAMS      CLERK           1100

ENAME      JOB              SAL
---------- --------- ----------
JAMES      CLERK            950
FORD       ANALYST         3000
MILLER     CLERK           1300

25 rows selected.

SQL> CREATE VIEW ABCVIEW
  2  AS
  3   SELECT ENAME,JOB,SAL FROM EMP
  4   WHERE DEPTNO IN (10,20)
  5   OR JOB = 'SALESMAN' OR
  6   SAL <= 2500;

View created.

SQL> SELECT * FROM ABCVIEW;

ENAME      JOB              SAL
---------- --------- ----------
AHMAD      CLERK          40000
MARIA      OFFICER         7000
ALI        CLERK           5000
SAHIR      CLERK           2000
ARIF       CLERK           2500
ASIF       DBA            77788
KIRAN      MANAGER         2000
SANA       MANAGER         2200
KASHIF     OFFICER         1400
IRFAN      CLERK           1200
EMRAN      CLERK           1000

ENAME      JOB              SAL
---------- --------- ----------
SIDRA      ASSISTANT        900
SMITH      CLERK            800
ALLEN      SALESMAN        1600
WARD       SALESMAN        1250
JONES      MANAGER         2975
MARTIN     SALESMAN        1250
CLARK      MANAGER         2450
SCOTT      ANALYST         3000
KING       PRESIDENT       5000
TURNER     SALESMAN        1500
ADAMS      CLERK           1100

ENAME      JOB              SAL
---------- --------- ----------
JAMES      CLERK            950
FORD       ANALYST         3000
MILLER     CLERK           1300

25 rows selected.

SQL> DROP VIEW ABCVIEW;

 SEQUENCE=  TO GENERATE AUTO NO

SQL> CREATE SEQUENCE ABCSQ
  2  START WITH 10
  3  INCREMENT BY 5;

Sequence created.

SQL> CREATE TABLE ABCDEF
  2  (RNO NUMBER(10),
  3  NAME VARCHAR2(20));

Table created.

SQL> INSERT INTO ABCDEF
  2  (RNO,NAME)
  3  VALUES
  4  (ABCSQ.NEXTVAL,'USAMA');

1 row created.

SQL> ED
Wrote file afiedt.buf

  1  INSERT INTO ABCDEF
  2  (RNO,NAME)
  3  VALUES
  4* (ABCSQ.NEXTVAL,'SAMI')
SQL> /

1 row created.

SQL> ED
Wrote file afiedt.buf

  1  INSERT INTO ABCDEF
  2  (RNO,NAME)
  3  VALUES
  4* (ABCSQ.NEXTVAL,'EJ')
SQL> /

1 row created.

SQL> ED
Wrote file afiedt.buf

  1  INSERT INTO ABCDEF
  2  (RNO,NAME)
  3  VALUES
  4* (ABCSQ.NEXTVAL,'HA')
SQL> /

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM ABCDEF;

       RNO NAME
---------- --------------------
        10 USAMA
        15 SAMI
        20 EJ
        25 HA

SQL> SELECT * FROM USER_SEQUENCES;

SEQUENCE_NAME                   MIN_VALUE  MAX_VALUE INCREMENT_BY C O CACHE_SIZE
------------------------------ ---------- ---------- ------------ - - ----------
LAST_NUMBER
-----------
EMPSEQ                                  1 1.0000E+27           10 N N         20
       8336

DBOBJECTID_SEQUENCE                     1 1.0000E+24           50 N N         50
          1

ABCSQ                                   1 1.0000E+27            5 N N         20
        110


SQL> SET LINES 300 PAGES 300
SQL> /

SEQUENCE_NAME                   MIN_VALUE  MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
------------------------------ ---------- ---------- ------------ - - ---------- -----------
EMPSEQ                                  1 1.0000E+27           10 N N         20        8336
DBOBJECTID_SEQUENCE                     1 1.0000E+24           50 N N         50           1
ABCSQ                                   1 1.0000E+27            5 N N         20         110

SQL> DROP SEQUENCE ABCSEQ;
DROP SEQUENCE ABCSEQ
              *
ERROR at line 1:
ORA-02289: sequence does not exist


SQL> ED
Wrote file afiedt.buf

  1* DROP SEQUENCE ABCSQ
SQL> /

Sequence dropped.

 INDEX= TO IMPROVE THE PERFORMANCE OF THE TABLE.WHEN UR DATA > 200 IN A TABLE THEN U APPLY INDEX.
     PK  UK IN COLUMNS AUTOMATIC CREATE INDEX.

SQL> CREATE INDEX ABCIDX
  2  ON EMP(JOB);
ON EMP(JOB)
       *
ERROR at line 2:
ORA-01408: such column list already indexed


SQL> ED
Wrote file afiedt.buf

  1  CREATE INDEX ABCIDX
  2* ON EMP(SAL)
SQL> /
ON EMP(SAL)
       *
ERROR at line 2:
ORA-01408: such column list already indexed


SQL> ED
Wrote file afiedt.buf

  1  CREATE INDEX ABCIDX
  2* ON ABCDEF(NAME)
SQL> /

Index created.

SQL> SELECT * FROM ABCDEF;

       RNO NAME
---------- --------------------
        10 USAMA
        15 SAMI
        20 EJ
        25 HA

SQL> DROP INDEX ABCIDX;

Index dropped.

OCP Database 10g Information

1Z0-042 Oracle Database 10g: Administration I exam. 
Important Exam Information and Objectives
The following objectives are included on this exam:

• Installing Oracle Database 10g Software
• Creating an Oracle Database
• Managing Schema Objects
• Managing Data
• Undo Management
• Monitoring and Resolving Lock Conflicts
• Database Interfaces
• Controlling the Database
• Oracle Database Security
• Oracle Net Services
• Backup and Recovery Concepts
• Storage Structures
• Administering Users
• Oracle Shared Servers
• Performance Monitoring
• Proactive Maintenance
• Database Backups
• Database Recovery

1Z0-043 Oracle Database 10g: Administration II exam
The following objectives are included on this exam:

• Using Globalization Support Objectives
• Securing the Oracle Listener
• Configuring Recovery Manager
• Recovering from User Errors
• Dealing with Database Corruption
• Automatic Database Management
• Using Recovery Manager
• Diagnostic Sources
• Recovering from Non-Critical Losses
• Monitoring and Managing Storage
• Automatic Storage Management
• Monitoring and Managing Memory
• Database Recovery
• Flashback Database
• Managing Resources
• Automating Tasks with the Scheduler

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.

Some Useful Queries for Beginners

SELECT * FROM EMP;
SELECT EMPNO, ENAME, SAL FROM EMP;
SELECT * FROM EMP WHERE DEPTNO=20;
SELECT * FROM EMP WHERE HIREDATE = ’02-APR-81’;
SELECT * FROM EMP WHERE SAL = 5000;
SELECT * FROM EMP WHERE HIREDATE <= ’23-MAY-86’;
SELECT * FROM EMP WHERE SAL >= 1500;
SELECT * FROM EMP WHERE SAL ! = 3000;
SELECT * FROM EMP WHERE DEPTNO <> 20;
SELECT * FROM EMP WHERE HIREDATE BETWEEN ’01-JAN-81’ AND ’31-
DEC-81’;
SELECT * FROM EMP WHERE DEPTNO IN (20, 30);
SELECT * FROM EMP WHERE JOB IN (‘ANALYST’,’SALESMAN’);
SELECT * FROM EMP WHERE HIREDATE IN (’20-FEB-81’,’03-DEC-81’);
SELECT * FROM EMP WHERE ENAME LIKE ‘%M%’;
SELECT * FROM EMP WHERE JOB LIKE ‘M%ER’;
SELECT * FROM EMP WHERE ENAME LIKE ‘__R%’;
SELECT * FROM EMP WHERE ENAME LIKE ‘%\_%’ ESCAPE ‘\’;
SELECT * FROM EMP WHERE COMM IS NULL;
SELECT * FROM EMP WHERE JOB = ‘MANAGER’ AND HIREDATE < ’10-JUN-
81’;
SELECT * FROM EMP WHERE SAL < 5000 AND COMM IS NULL;
SELECT * FROM EMP WHERE DEPTNO = 30 OR JOB = ‘PRESIDENT’;
SELECT * FROM EMP WHERE COMM IS NULL OR ENAME LIKE ‘A%’;
SELECT * FROM EMP WHERE JOB NOT LIKE ‘M%’;
SELECT * FROM EMP WHERE SAL NOT BETWEEN 2000 AND 3000;
SELECT * FROM EMP ORDER BY ENAME;
SELECT * FROM EMP ORDER BY JOB, ENAME;
SELECT EMPNO, ENAME, JOB, SAL, DEPTNO FROM EMP ORDER BY 3;
SELECT ENAME EMPLOYEE FROM EMP ORDER BY EMPLOYEE;
---------------------------------------------------------------------------------------------------
SELECT UPPER (‘asif khan’), LOWER (‘SQL PLUS’), INITCAP (‘ORACLE’) FROM 
DUAL;
SELECT EMPNO, RPAD (ENAME, 20,’.’), LPAD (SAL, 6,’*’) FROM EMP;
SELECT INSTR (‘PAKISTAN’,’A’, 1) FROM DUAL;
SELECT INSTR (‘PAKISTAN’,’A’, 3) FROM DUAL;
SELECT INSTR (‘PAKISTAN’,’A’, -1) FROM DUAL;
SELECT INSTR (‘PAKISTAN’,’A’, -3) FROM DUAL;
SELECT ROUND (125.978, 1) FROM DUAL;
SELECT ROUND (123.978,-1) FROM DUAL;
SELECT ROUND (123.978, 0) FROM DUAL;
SELECT ROUND (123.978) FROM DUAL;
SELECT ROUND (126.978,-2) FROM DUAL;
SELECT ROUND (156.978,-2) FROM DUAL;
SELECT ROUND (149.978,-2) FROM DUAL;
SELECT TO_CHAR (SYSDATE,’DD-MON-YYYY’) FROM DUAL;
SELECT TO_CHAR (TO_DATE (’27-OCT-17’, ’DD-MON-RR’),’YYYY’) FROM DUAL;
SELECT TO_CHAR (TO_DATE (’27-OCT-17’, ’DD-MON-YY’),’YYYY’) FROM DUAL;
SELECT TO_CHAR (15000, ‘$99,999.9’) FROM DUAL;
SELECT TO_CHAR (50, ‘$99, 9999.9’) FROM DUAL; 
SELECT 3000 + TO_NUMBER (‘5000’) FROM DUAL;
SELECT TO_CHAR (SYSDATE, ‘YYYY YEAR’) FROM DUAL;
SELECT TO_CHAR (SYSDATE, ‘DY DAY’) FROM DUAL;
SELECT EMPNO, DEPTNO, DNAME FROM EMP, DEPT 
WHERE EMP.DEPTNO=DEPT.DEPTNO;
SELECT EMPNO, DEPT.DEPTNO, JOB, DNAME FROM EMP, DEPT
WHERE EMP.DEPTNO=DEPT.DEPTNO AND JOB = ‘MANAGER’;
SELECT E.EMPNO, E.ENAME, S.GRADE, D.DNAME FROM EMP E, DEPT D, 
SALGRADE S WHERE E.DEPTNO=D.DEPTNO AND E.SAL BETWEEN S.LOSAL 
AND S.HISAL;
SELECT E.EMPNO, E.ENAME, D.DNAME FROM EMP E, DEPT D 
WHERE E.DEPTNO (+) =D.DEPTNO;
SELECT WORKER.ENAME||’WORK FOR’||MANAGER.ENAME FROM EMP 
WORKER, EMP MANAGER WHERE WORKER.MGR = MANAGER.EMPNO;
SELECT WORKER.ENAME||’WORK FOR’||MANAGER.ENAME FROM EMP 
WORKER, EMP MANAGER WHERE WORKER.MGR = MANAGER.EMPNO (+);
SELECT COUNT (COMM) FROM EMP;
SELECT JOB, MAX (SAL) FROM EMP GROUP BY JOB;
Q.1. Find the All Employees Who Are Earning More than Mr. JONES
SELECT * FROM EMP 
WHERE SAL> (SELECT SAL FROM EMP
WHERE ENAME = ‘JONES’);
Q2. Display All Employees Which Are Working In a Same Department Of Mr. 
SMITH
SELECT * FROM EMP
WHERE DEPTNO = (SELECT DEPTNO FROM EMP
WHERE ENAME = ‘SMITH’);
Q3. DISPLAY ALL EMPLOYEES WHO WERE HIRED AFTER MR.FORD WAS 
HIRED.
SELECT * FROM EMP
WHERE HIREDATE > (SELECT HIREDATE FROM EMP
WHERE ENAME = ‘FORD’)
Q4. DISPLAY ALL EMPLOYEES WHO ARE EARNING MORE THAN THE SALARY 
OF THAT EMPLOYEE WHICH HAS HIGHEST SALARY IN DEPARTMENT 30.
SELECT * FROM EMP
WHERE SAL > (SELECT MAX (SAL) FROM EMP
WHERE DEPTNO = 30);
=====================================================================
Questions
1. Which is the subset of SQL commands used to manipulate Oracle Database 
structures, including tables?
2. What operator performs pattern matching?
3. What operator tests column for the absence of data?
4. Which command executes the contents of a specified file?
5. What is the parameter substitution symbol used with INSERT INTO 
command?
6. Which command displays the SQL command in the SQL buffer, and then 
executes it?
7. What are the wildcards used for pattern matching?
8. State true or false. EXISTS, SOME, ANY are operators in SQL.
True
9. State true or false. !=, <>, ^= all denote the same operation.
10. What are the privileges that can be granted on a table by a user to others?
11. What command is used to get back the privileges offered by the GRANT 
command?
12. Which system tables contain information on privileges granted and privileges 
obtained?
13. Which system table contains information on constraints on all the tables 
created?
15. What is the difference between TRUNCATE and DELETE commands?
16. What command is used to create a table by copying the structure of another 
table?
17. What will be the output of the following query?
SELECT REPLACE(TRANSLATE(LTRIM(RTRIM('!! ATHEN !!','!'), '!'), 'AN', 
'**'),'*','TROUBLE') FROM DUAL;
18. What will be the output of the following query?
SELECT DECODE(TRANSLATE('A','1234567890','1111111111'), '1','YES', 'NO' );
19. What does the following query do?
SELECT SAL + NVL(COMM,0) FROM EMP;
20. Which date function is used to find the difference between two dates?

Procedure and Function Examples

Procedures:-
A Procedure is a named PL/SQL block which is compiled and stored in
the database for repeated execution.

Basic Syntax :
------------

Create or replace procedure <procedure_name>
is
begin
..............
..............
.............
end;
/
Ex 1:
-----------
Create or replace procedure p1
is
begin
dbms_output.put_line('Hello World');
end;
/

Procedure created.

To execute the procedure:
----------------------------
Exec command is used to execute the procedure.

SQL> Exec p1
Hello World

A procedure can have three types of parameters.
1) IN Parameter
2) OUT Parameter
3) IN OUT Parameter
In Parameters are used to accept values from the user.
Ex 2:
---------
Create a procedure which accepts two numbers and display its sum.

create or replace procedure add_num ( a IN number,
b IN number)
is
c number(3);
begin
c := a+b;
dbms_output.put_line(' The sum is '||c);
end;
/

Procedure created.

To execute the procedure:
--------------------------
SQL> exec add_num (10,20)


Ex 3:
--------

Create a Procedure which accepts an empno and increments his salary by 1000.
create or replace procedure inc_sal ( a in number)
is
begin
update emp set sal = sal+1000
where empno = a;
end;
/

Procedure created.

TO execute the procedure:
---------------------------

SQL> exec inc_sal(7900)

We can improve the above procedure code by using %type attribute in
 procedure parameters.

The above procedure can be re-written as below :

create or replace procedure inc_sal ( a in emp.empno%type)
is
begin
update emp set sal = sal+1000
where empno = a;
end;
/


Create a procedure which accepts deptno and display ename and salary
of employees working in that department.
create or replace procedure display_emp1 (l_deptno emp.deptno%type)
is
cursor c1
is select ename,sal from emp
where deptno = l_deptno;

begin

for emp_rec in c1 loop
dbms_output.put_line(emp_rec.ename||'...'||emp_rec.sal);
end loop;

end;
=====================================================================

Functions:-
Function is a PL/SQL block which must and should return single value.
Syntax:
-----------

Create or replace function <Function_name>
( <Par_name> <mode> <datatype>,
,, ,, ,, )
return datatype
is
Begin
..........
.........
end;
/

ex1:
-----

Create a function which accepts two numbers and display its sum.

create or replace function add_num_f1 ( a number, b number)
return number
is
c number(5);
begin

c :=a+b;
return c;
end;
/
To invoke a function from a pl/Sql block:
---------------------------------------------

declare
n number(5);
begin

n := add_num_f1(20,40);

dbms_output.put_line('The sum is '||n);
end;
/
We can invoke functions from select stmt:
----------------------------------------------
select add_num_f1(30,50) from dual;
Functions can be invoked as part of an expression:
----------------------------------------------------

select 100 + add_num_f1(50,10) from dual;
Ex2:
------

create a function which accepts sal and returns tax value ( 10% of sal is tax).

create or replace function cal_tax ( a number)
is
begin

return a*10/100;
end;
/

Note: A function can return a value using return statement.
Ex 3:
----------

Have a look at the following function:



create or replace function add_num_f2 ( a number, b number)
return number
is
c number(5);
begin

insert into dept values (50,'HR','FAISALABAD')

c :=a+b;
return c;
end;
/
The above function gets created.

The above function can be invoked from the pl/SQL block

declare
n number(5);
begin

n := add_num_f2(20,40);

dbms_output.put_line('The sum is '||n);
end;
/


But, we cannot invoke the above function using select stmt.

ex:

select add_num_f2(30,50) from dual; -- will give us error.

Note: So, functions with dml commands cannot be invoked from select stmt.

------------------------

TO see the list of all the functions
select object_name from user_objects
where object_type = 'FUNCTION';
----------------------

To drop a function

drop function <function_name>;

ex:

drop function add_num_f2;

-----------------------

Functions are mainly used for calculation purposes.
Rest of the activities, prefer procedures.Pro