Convert Month to Date in oracle

 Convert Month to Date in oracle

            If user has number between 1 and 12, He can convert that number to Date

Syntax:

           to_date(month,fmt)  

        

SQL> select to_date(2,'mm') from dual;

TO_DATE(2,'mm')
--------------------
01-FEB-2014

SQL> select to_date(12,'mm') from dual;

TO_DATE(12,'mm')
--------------------

01-DEC-2014

If number exceeds 12. or less than 1, user gets following error message.

ERROR at line 1:
ORA-01843: not a valid month

Tags: Convert month  to date,convert month  to Date using To_Date function,oracle convert month  to date ,oracle convert month  to date using to_date,oracle convert month  to current month date.

Convert Day to Date in oracle

Convert Day to Date in oracle

            If user has some number between 1 and 31, He can convert that number to Date

Syntax:

           to_date(day,fmt)  

        

SQL> select to_date(2,'dd') from dual;

TO_DATE(2,'DD')
--------------------
02-MAY-2014

SQL> select to_date(31,'dd') from dual;

TO_DATE(31,'DD')
--------------------

31-MAY-2014

If number exceeds 31. or less than 1, user gets following error message.

ERROR at line 1:
   ORA-01847: day of month must be between 1 and last day of month

Tags: Convert day to date,convert day to Date using To_Date function,oracle convert day to date ,
oracle convert day to date using to_date,
oracle convert day to current month date.

Calculated values in the Where Clause Oracle

Calculated Values are not allowed in where clause, It should be part of inner view or join.

In Hr Schema , Display all employees who is more than 10,000 after 20% increment in their salary.

SQL> select Employee_ID,First_name,Salary,(Salary+salary*20/100) as "20%" from employees
  2  where e."20%">10000;
where e."20%">10000
      *
ERROR at line 2:
ORA-00904: "E"."20%": invalid identifier

In order to solve this. There are 2 methods.

                 1.Using Inner Views
                 2.Using Inner join.

Method 1:Using Inner Views

SQL>  select  * from (select employee_ID,first_name,salary,(salary+salary*20/100) as "20%" from employees) e
  where e."20%">10000

Inner view gives a table called e, using columns in table user can filter in where clause.


Method 2: Join Employees table with Inner View.

select  ec.Employee_ID,ec.first_name,ec.salary,ec."20%",e.last_name from employees e

inner join

(select employee_ID,first_name,salary,(salary+salary*20/100) as "20%" from employees) ec

on e.employee_ID=ec.employee_ID

where ec."20%">10000

This method also produces same results, I prefer method 1, because Table scan is 1.

Tags:Calculated values in the Where Clause Oracle,Filter based on calculated value, compute column in where clause, join table and inner view, inner views in oracle, filter based on pseudo column.

How to count Null values in Oracle

How to count Null values in Oracle

                Oracle count function counts non-null values only.  If u want to count null values user can use  NVL function

Employees Table hr schema,  

Display Number of employees not getting Commission_pct.

SQL> select count(nvl(commission_pct,0)) from employees where commission_pct is null;

COUNT(NVL(COMMISSION_PCT,0))
----------------------------
                          72

Tags:How to count Null values in Oracle, counting null values in oracle, counting null values with nvl function.

Extract Day from Date Oracle

Extract Day   from Date Oracle

                             Extract function extracts date parts. such as Year ,Day ,day, hours,minutes,seconds,fractions and time zone.

Extracting/Getting Day   from date/timestamp column

Extract Day   for Sysdate
            SQL>select extract(Day   from sysdate)  from dual;

 Extract Day   from orderdate column
           SQL>select extract(Day   from orderdate)  from orders;

Extract Day   from string date
           SQL>select extract(Day   from to_date('2020-12-12','yyyy-mm-dd'))  from dual ;

Extract Day   from current_timestamp
           SQL>select extract(Day   from current_timestamp) from dual;

Tags: using extract function, Extract function in oracle, get current Day , get Day   from date column,oracle 11g

Extract Month from Date Oracle

Extract Month  from Date Oracle

                             Extract function extracts date parts. such as Year ,month,day, hours,minutes,seconds,fractions and time zone.

Extracting/Getting Month  from date/timestamp column

Extract Month  for Sysdate
            SQL>select extract(Month  from sysdate)  from dual;

 Extract Month  from orderdate column
           SQL>select extract(Month  from orderdate)  from orders;

Extract Month  from string date
           SQL>select extract(Month  from to_date('2020-12-12','yyyy-mm-dd'))  from dual ;

Extract Month  from current_timestamp
           SQL>select extract(Month  from current_timestamp) from dual;

Tags: using extract function, Extract function in oracle, get current Month , get Month  from date column,oracle 11g

Extract Year from Date Oracle

Extract Year from Date Oracle

                             Extract function extracts date parts. such as year,month,day, hours,minutes,seconds,fractions and time zone.

Extracting/Getting Year from date/timestamp column

Extract Year for Sysdate

            SQL>select extract(year from sysdate)  from dual

 Extract year from orderdate column

           SQL>select extract(year from orderdate)  from orders

Extract year from string date 

           SQL>select extract(year from to_date('2020-12-12','yyyy-mm-dd'))  from dual 

Extract Year from current_timestamp

           SQL>select extract(year from current_timestamp) from dual;

Tags: using extract function, Extract function in oracle, get current year, get year from date column

Oracle Create Unique Index on a Column

Oracle Create Unique Index on a Column 

                          Unique index makes sure column has unique values.  it includes one NULL value too.

for ex:-  Create an Unique index of person's Email ID

Syntax:

              create unique index <index name> on tablename(col1,[col2,col3...]);

index_name is mandatory

tablename is mandatory

atleast one column should be present.

create a table called Person

SQL> create table person(id number(2),name varchar2(20),dob date, email varchar2(250));

Create unique Index on email ID

SQL>create unique index idx_persons_emailid_unique on person(email);

Insert data into Person table

INSERT INTO Person (Id, Name, dob, email) 

VALUES (1, 'Peter', to_date('1997-12-12','yyyy-mm-dd'), '1@gmail.com')
 

INSERT INTO Person (Id, Name, dob, email) 

VALUES (6, 'life style', to_date('1997-09-11','yyyy-mm-dd'), NULL)
 

INSERT INTO Person (Id, Name, dob, email) 

VALUES (2, 'shyam purru', to_date('2000-01-12','yyyy-mm-dd'), '2@gmail.com')
 

INSERT INTO Person (Id, Name, dob, email) 

VALUES (3, 'Jho', to_date('2002-11-01','yyyy-mm-dd'), '3@gmail.com')
 

INSERT INTO Person (Id, Name, dob, email) 

VALUES (4, 'Jhony', to_date('1960-08-30','yyyy-mm-dd'), '4@gmail.com')
 

INSERT INTO Person (Id, Name, dob, email) VALUES (5, 'pearso', to_date('2013-04-21 00:19:32','yyyy-mm-dd hh24:mi:ss'), '5@gmail.com')

Inserting duplicate records throws an exception/error

INSERT INTO Person (Id, Name, dob, email) VALUES (5, 'pearso', to_date('2013-04-21 00:19:32','yyyy-mm-dd hh24:mi:ss'), '5@gmail.com')

throws an error  because e-mail id 5@gmail.com already exists in the table.

ERROR at line 1:
ORA-00001: unique constraint (SCOTT.IDX_PERSONS_EMAILID_UNIQUE) violated

Ex:  Creating an Unique Index on Existing Table with Data.

   Make sure all column values are unique

Tags: Oracle Create Unique Index on a Column , Create unique index syntax, oracle create unique index syntax, oracle plsql unique index, oracle unique index on single column,oracle create a unique index on existing table.

ORACLE PLSQL CASE STATEMENT

ORACLE PLSQL CASE STATEMENT

                         Oracle case statement uses selector rather than multiple boolean expressions. Selects sequence based on selector, Its more readable compared to IF THEN ELSE.

Note:CASE Statement is an alternative to IF THEN ELSE statement in Oracle PL/SQL.

SYNTAX:

            case selector 

              when condition(s) then 

              ....

             ....

            ....

            else 

          End Case;

CASE Statement in  Select Statement

Create table called "Sports"

SQL> create table sports(ID number(2),name varchar2(20),country char(2));

SQL> insert into sports values(1,'cricket','IN');
SQL> insert into sports values(2,'cricket','UK');
SQL> insert into sports values(3,'Hand FootBall','US');
SQL> insert into sports values(4,'Base Ball','US');

SQL> select * from sports;

        ID NAME                 CO
---------- -------------------- --
         1 cricket              IN
         2 cricket              UK
         3 Hand FootBall        US
         4 Base Ball            US

Display Custom message using Case Statement

select Name, case country
when 'IN' then Name||' most played game in '||country
when 'US' then Name||' most played game in '||country
when 'UK' then Name||' most played game in '||country
End "MSG"
from sports

SQL>/

NAME                 MSG
-------------------- -------------------------------------------
cricket              cricket most played game in IN
cricket              cricket most played game in UK
Hand FootBall        Hand FootBall most played game in US
Base Ball            Base Ball most played game in US

CASE STATEMENT IN PL/SQL ANONYMOUS BLOCK

declare
       msg varchar2(20);
  cursor sportsCur is select * from sports;
begin
             for i in sportsCur
              loop
                case i.country
                 when 'IN' then 

                                   Dbms_output.put_line(i.id||' '||i.name||' is most played game in INDIA');
                 when 'US' then 

                                  Dbms_output.put_line(i.id||' '||i.name||' is most played game in United States');
                 when 'UK' then 

                               Dbms_output.put_line(i.id||' '||i.name||' is most played game in United Kingdom');
               else  dbms_output.put_line(i.id||' '||i.name||' '||i.country);
              end case;
             end loop;
end;
 

Execute the Script

SQL>/

1 cricket is most played game in INDIA
2 cricket is most played game in United Kingdom
3 Hand FootBall is most played game in United States
4 Base Ball is most played game in United States

PL/SQL procedure successfully completed.

Tags:  ORACLE PLSQL CASE STATEMENT,Oracle case statement, PL/SQL case statement,

PLSQL case statement, how to use PL/SQL case statement, PL/SQL case statement syntax,Using Case statement inside for loop.

Delete Duplicate Records in Oracle

Delete Duplicate Record in Oracle 

   Deleting Duplicate Records , if all column values are repeated,

   

Suppose 

Create table called Sports

 

SQL>create table sports(ID number(2),name varchar2(20),country char(2));

Insert records into Sports table

SQL>insert into sports values(1,'cricket','IN');

SQL> insert into sports values(2,'cricket','UK');

SQL> insert into sports values(3,'Hand FootBall','US');

SQL> insert into sports values(4,'Base Ball','US');

Display Records 

SQL> Select * from Sports

ID NAME                 Co
-- -------------------- --
 1 cricket              IN
 2 cricket              UK
 3 Hand FootBall        US
 4 Base Ball            US

Re-insert same values into Sports table

SQL> Insert into Sports Select * from sports

4 rows created.

Display Records again

SQL> Select * from sports;

ID NAME                 CO
-- -------------------- --
 1 cricket              IN
 2 cricket              UK
 3 Hand FootBall        US
 4 Base Ball            US
 1 cricket              IN
 2 cricket              UK
 3 Hand FootBall        US
 4 Base Ball            US

8 rows selected.

Deleting Duplicate Records from Sports Table.

SQL>delete from sports where rowid in (select max(rowid) from sports group by id);

Display Records again, it has all unique rows,

SQL> Select * from Sports

ID NAME                 Co
-- -------------------- --
 1 cricket              IN
 2 cricket              UK
 3 Hand FootBall        US
 4 Base Ball            US

Tags:Delete Duplicate Records in Oracle,How to delete duplicate values in oracle table, remove duplicate values in oracle, oracle insert,oracle create table, oracle insert using select,

ORACLE PL SQL IF THEN STATEMENT

PL SQL  IF THEN STATEMENT
              IF statement is used for conditional execution.If Condition is true then code will be executed,otherwise control goes to next executable statement in the PL/SQL block.


SYNTAX:
               IF CONDITION THEN
                  begin
                           --statements here
                   end;
                END IF;


Example 1: Comparing 2 numbers;
SQL>declare
i integer:=20;
j integer:= 20;
begin
   if( i = j)
    then
                   dbms_output.put_line('i=j');
    end if;
end;

SQL>/
i=j

PL/SQL procedure successfully completed.

Example 1: BOOLEAN VALUE CONDITION COMPARISION

SQL>declare
b boolean:=true;
begin
   if( b)
    then
                   dbms_output.put_line('b is true');
    end if;
end;
/
SQL> /
b is true

PL/SQL procedure successfully completed.

 Example 1: COMPARE STRINGS for equality

declare
a varchar2(10):='PARIS';
b varchar2(10):='PARIS';
begin
   if(a= b)
    then
                   dbms_output.put_line('a'|| '&'|| 'b  is same');
    end if;
end;

SQL> /
a&b  is same

PL/SQL procedure successfully completed.
 
 Example 1: COMPARING 2 DATE for equality

SQL>declare
a date:=to_date('1997-12-12','yyyy-MM-dd');
b date:=to_date('1997-12-12','yyyy-MM-dd');
begin
   if(a= b)
    then
                   dbms_output.put_line('a'|| '&'|| 'b  has same dates');
    end if;
end;











SQL>/
 a&b has same dates

PL/SQL procedure successfully completed.

 Example 1: COMPARING 2 DATE for non-equality

declare
a date:=to_date('1997-12-12','yyyy-MM-dd');
b date:=to_date('1998-12-12','yyyy-MM-dd');
begin
   if(a <> b)
    then
                   dbms_output.put_line('a'|| '&'|| 'b  dates are different');
    end if;
end;

SQL>/
a&b dates are different

PL/SQL procedure successfully completed.


Tags: ORACLE PL SQL  IF THEN STATEMENT ,PL/SQL IF Statement, PL/SQL IF statement syntax,using PL/SQL IF statement, COMAPARE 2 strings,Compare dates,Compare number variables, Check Boolean value using if statement.

ORACLE NATURAL JOIN

ORACLE NATURAL JOIN

Oracle natural join joins tables on parent and child tables without condition clause.

SYNTAX:

select * from [table 1]

natural join

[table 2]

Table 1 &table 2  should have foreign and primary key relation ships.

for ex:  customers and orders tables.

Orders table must contain valid customer id, otherwise he/she can't place an order.

So Customer table has primary key customer ID

Orders tables has CustomerID as a foreign key(who placed the order).

SQL>DESC customers;

 Name                                      Null?    Type
 ----------------------------------------- -------- --------------
 CUSTOMERID                                NOT NULL NCHAR(5)
 COMPANYNAME                               NOT NULL NVARCHAR2(40)
 CONTACTNAME                                        NVARCHAR2(40)
 CONTACTTITLE                                       NVARCHAR2(30)
 ADDRESS                                            NVARCHAR2(60)
 CITY                                               NVARCHAR2(15)
 REGION                                             NVARCHAR2(15)
 POSTACODE                                          NVARCHAR2(10)
 PHONE                                              NVARCHAR2(24)
 FAX                                                NVARCHAR2(24)

SQL> DESC ORDERS;
 Name                                      Null?    Type
 ----------------------------------------- -------- --------------
 ORDERID                                   NOT NULL NUMBER(10)
 CUSTOMERID                                         NCHAR(5)
 EMPLOYEEID                                         NUMBER(10)
 ORDERDATE                                          DATE
 REQUIREDDATE                                       DATE
 SHIPPEDDATE                                        DATE
 SHIPVIA                                            NUMBER(10)
 FREIGHT                                            NUMBER(19,4)
 SHIPNAME                                           NVARCHAR2(40)
 SHIPADDRESS                                        NVARCHAR2(60)
 SHIPCITY                                           NVARCHAR2(15)
 SHIPREGION                                         NVARCHAR2(15)
 SHIPPOSTALCODE                                     NVARCHAR2(10)
 SHIPCOUNTRY                                        NVARCHAR2(15)

NATUARL JOIN ON CUSTOMER and ORDERS TABLES

select * from
customers
natural join
orders

Note: There is no ON condition.

Tags: oracle joins, oracle natural join, How to use oracle natural join, How to use natural joins in queries, Oracle natural join syntax, Oracle natural join usage.

USING GOTO statement in PL/SQL Block

USING GOTO statement in PL/SQL Block

                                   GOTO statement is used to branch a section of code unconditionally.

SYNATX:

            Goto  Label;

            <<Label>>

             begin

           end;

Note:  a branch can be forward/backward.

Here  You have a table called Student

create table student2(id number(2),
name varchar2(20),
constraint PK_ID primary key(ID)
)

Insert 2 records into Student

begin
for i in 1..2
loop
insert into student2 values(i,concat('student ',i));
dbms_output.put_line('inserted...');
end loop;
end;

SQL> select * from student2;

        ID NAME
---------- --------------------
         1 student 1
         2 student 2

Again user wants to add records from the begining, it will throw an exception

using GOTO statement avoid those exceptions continue inserting remaning items.

begin
for i in 1..5
loop
<<Label>>
begin
insert into student2 values(i,concat('student ',i));
exception
when others then
 goto Label;
end;
end loop;
end;

The above code throws exception for ID 1,2,  using goto statement ignore those 2 records and continue remaining 3 records

SQL> select * from student2;

        ID NAME
---------- --------------------
         1 student 1
         2 student 2
         3 student 3
         4 student 4
         5 student 5

Tags:USING GOTO statement in PL/SQL Block, Oracle GOTO statement, PL/SQL goto statement syntax, usage of GOTO Statement in PL/SQL, How to use goto statement in PL/SQL block

PL/SQL function returning boolean

PL/SQL function returning Boolean

            in PL/SQL stored function can return boolean values tool. but it should be checked in IF or CASE statements only.

Here is the Example: Function checks if record exists or not

if exists return true else false.

create or replace function custExists(p_custid in nchar)

return boolean

as

recfound number(2);

begin

select count(*) into recfound from customers where customerid=p_custid;

DBMS_OUTPUT.PUT_LINE(P_CUSTID||' '||RECFOUND);

        if( recfound >=1) then

                           return true;

         ELSE
                           return false;

         end if;

exception

      when others then

       begin

               dbms_output.put_line('no record found');

               return false;

end;

end;

Calling/Consuming Function returning boolean

SQL>begin
if( custExists('ABCDE')) then
dbms_output.put_line('record exists');
else dbms_output.put_line('record not exists');
end if;
end;

--execute the above code.

SQL>/

ABCDE 1
record exists

PL/SQL procedure successfully completed.

Note: User can not consume/call in SELECT statement.

Tags:PL/SQL function returning boolean, Oracle function returning Boolean value, Executing boolean returned function in PL/SQL block, PL/SQL anonymous block.

insert PL/SQL record into Database

insert PL/SQL record into Database

 PL/SQL record is a collection of related fields, it can be single/multiple fields.

Suppose peter schema has following customers table.

SQL>DESC Customers
CUSTOMERID                                NOT NULL NCHAR(5)
COMPANYNAME                               NOT NULL NVARCHAR2(40)
CONTACTNAME                                        NVARCHAR2(40)
CONTACTTITLE                                       NVARCHAR2(30)
ADDRESS                                            NVARCHAR2(60)
CITY                                               NVARCHAR2(15)
REGION                                             NVARCHAR2(15)
POSTACODE                                          NVARCHAR2(10)
PHONE                                              NVARCHAR2(24)
FAX                                                NVARCHAR2(24)


Declare a variable cust  of type Customers ROW.
i.e cust  tablename%rowtype;

variable cust has all columns of customers table. and it can accommodate only one row.

PL/SQL Anonymous block Insert PL/SQL Record into Database.

SQL>
declare
cust customers%rowtype;
begin

cust.CUSTOMERID := 'ABCDE';
cust.COMPANYNAME :='PETER JOHNSON';
cust.CONTACTNAME:='PETER HANS';
cust.CONTACTTITLE:='Mr.';
cust.ADDRESS:='450 N Mathilda Ave.';
cust.CITY:='SunnyDale';
cust.REGION:='NA';
cust.POSTACODE:='95086';
cust.PHONE:='4084268989';
cust.FAX:='4084268989';

--insert PL/SQL record.
insert into customers values cust;

commit;
end;

Note:PL/SQL record insert --> instead of values just specify record type variable.

SQL> select * from customers where customerid='ABCDE';


CUSTO COMPANYNAME
----- ----------------------------------------
CONTACTNAME                              CONTACTTITLE
---------------------------------------- ------------------------------
ADDRESS                                                      CITY
------------------------------------------------------------ --------------
REGION          POSTACODE  PHONE                    FAX
--------------- ---------- ------------------------ -----------------------
ABCDE PETER JOHNSON
PETER HANS                               Mr.
450 N Mathilda Ave.                                          SunnyDale
NA              95086      4084268989               4084268989

Tags:insert PL/SQL record into Database,Declare PL/SQL record type,Using PL/SQL record type, assign values PL/SQL record, Initialize values to PL/SQL record type. PL/SQL record Insert.

Oracle Group By on Date Column

Oracle Group By on Date Column

          Group by clause is used to aggregate data.

Suppose Orders table has following fields

SQL> desc orders
 Name                                      Null?    Type
 ----------------------------------------- -------- ---------------------
 ORDERID                                   NOT NULL NUMBER(10)
 CUSTOMERID                                         NCHAR(5)
 EMPLOYEEID                                         NUMBER(10)
 ORDERDATE                                          DATE
 REQUIREDDATE                                       DATE
 SHIPPEDDATE                                        DATE
 SHIPVIA                                            NUMBER(10)
 FREIGHT                                            NUMBER(19,4)
 SHIPNAME                                           NVARCHAR2(40)
 SHIPADDRESS                                        NVARCHAR2(60)
 SHIPCITY                                           NVARCHAR2(15)
 SHIPREGION                                         NVARCHAR2(15)
 SHIPPOSTALCODE                                     NVARCHAR2(10)
 SHIPCOUNTRY                                        NVARCHAR2(15)

Group by on  Date

select orderdate,count(*) from orders

group by orderdate;

GROUP BY On YEAR

select to_char(orderdate,'yyyy'),count(*) from orders
group by to_char(orderdate,'yyyy')

Group By on Year ,Month

select to_char(orderdate,'yyyy'),to_char(orderdate,'MON'),count(*) from orders
group by to_char(orderdate,'yyyy'),to_char(orderdate,'MON')
order by 1,2

Tags:  Oracle Group By , Oracle Group by Date,Oracle Group by Year,Oracle Group by Month,

How to use group by clause on Date column, Oracle Group By on Date Column

PL/SQL Package Function returning PL/SQL Record

PL/SQL Package Function returning PL/SQL Record 

PL/SQL Record Type hold one record at a time.

Syntax: PL/SQL Record Type

Type [typename]  is Record(type1,type2, ...);

 

for ex:  Emp Table Record

 Type empRecType is Record(p_empno number(4),p_ename varchar2(20));

This tutorial explains how to return Record Type from a stored procedure.

Create a Package Specification

which has Record Type and one Procedure returning Record Type as Output Parameter.

create or replace package pkgRec
is
type empRecType is record(p_empno number(4),p_ename varchar2(20));
function get_empRecord(p_empno in number) return empRecType;
end pkgRec;

Create a Package Body

create or replace package body pkgRec
is
 

create or replace package body pkgRec
is
function get_empRecord(p_empno in number) return empRecType
as
v_empRec empRecType;
begin
select empno,ename into v_empRec from  emp where empno=p_empno ;
return v_empRec ;
end;
end pkgRec;
 

Consuming  a PL/SQL Package Procedure

SQL>declare
v_empRec pkgRec.empRecType;
begin
dbms_output.put_line('Package function returing Record Type');
v_empRec:=pkgRec.get_empRecord(&empno);
dbms_output.put_line(v_empRec.p_empno||' '||v_empRec.p_ename);
Exception
when no_data_found then
dbms_output.put_line(sqlerrm||' '||sqlcode);
end;

Note: before executing 

SQL>set serveroutput on

SQL>/

Enter value for empno: 7900

Enter value for empno: 7900
old   5: pkgRec.get_empRecord(&empno,v_empRec);
new   5: pkgRec.get_empRecord(7900,v_empRec);
Package function returing Record Type
7900 JAMES

PL/SQL procedure successfully completed.

Tags:PL/SQL Package function returning PL/SQL Record , PL/SQL Record Type,using PL/SQL Record in Stored functions, using PL/SQL Record type in Packages.Consuming PL/SQL package functions in Anonymous blocks.

PL/SQL Package Procedure returning PL/SQL Record

PL/SQL Package Procedure returning PL/SQL Record 

PL/SQL Record Type hold one record at a time.

Syntax: PL/SQL Record Type

Type [typename]  is Record(type1,type2, ...);

 

for ex:  Emp Table Record

 Type empRecType is Record(p_empno number(4),p_ename varchar2(20));

This tutorial explains how to return Record Type from a stored procedure.

Create a Package Specification

which has Record Type and one Procedure returning Record Type as Output Parameter.

create or replace package pkgRec
is
 

type empRecType is record(p_empno number(4),p_ename varchar2(20));
 

procedure sp_get_empRecord(p_empno in number,p_emprec out empRecType);
 

end pkgRec;

Create a Package Body

create or replace package body pkgRec
is
 

procedure sp_get_empRecord(p_empno in number,

p_emprec out empRecType)
as
 

begin
select empno,ename into p_emprec from  emp where empno=p_empno;
end;
 

end pkgRec;

Consuming  a PL/SQL Package Procedure

SQL>declare

v_empRec pkgRec.empRecType;

begin

dbms_output.put_line('Package procedure returing Record Type');

pkgRec.sp_get_empRecord(&empno,v_empRec);

dbms_output.put_line(v_empRec.p_empno||' '||v_empRec.p_ename);

Exception

when no_data_found then

dbms_output.put_line(sqlerrm||' '||sqlcode);

end;

Note: before executing 

SQL>set serveroutput on

SQL>/

Enter value for empno: 7900

Enter value for empno: 7900
old   5: pkgRec.sp_get_empRecord(&empno,v_empRec);
new   5: pkgRec.sp_get_empRecord(7900,v_empRec);
Package procedure returing Record Type
7900 JAMES

PL/SQL procedure successfully completed.

Tags:PL/SQL Package Procedure returning PL/SQL Record , PL/SQL Record Type,using PL/SQL Record in Stored Procedures, using PL/SQL Record type in Packages.Consuming PL/SQL package procedures in Anonymous blocks.

PL/SQL Package procedure returning VARRAY

PL/SQL Package procedure returning VARRAY

VARRAYs are used to if user knows record count in advance. i.e fixed size Data structures in PL/SQL.

It can contain array of single column or array of record Types.

Here is the Package which has function which returns array of Hiredates.

Create a Package Specification

• Here i specified varray size as 11  , u can specify any number.

• Procedure has one output parameters.

 

create or replace package pkgVARRY1
is
type varrayType is varray(11) of date;
procedure sp_get_hiredate(v_hireArray out varrayType );

end pkgVARRY1;

Create a Package Body

 

create or replace package body pkgVARRY1
is
procedure sp_get_hiredate(v_hireArray out varrayType )
as
begin
select hiredate bulk collect into v_hireArray from where  emp rownum<=11;
end;
end pkgVARRY1;

Create a PL/SQL anonymous block to consume above package function

 

declare

v_array pkg1.varrayType;

begin

dbms_output.put_line('Package procedure returning VARRAY');

pkg1.sp_get_hiredate(v_array);

for i in v_array.first..v_array.last

loop

dbms_output.put_line(v_array(i));

end loop;

end;

Tags:PL/SQL Package Procedure returning VARRAY,PL/SQL VARRAYS, how to declare varrays in PL/SQL package. How to return VARRAY from a procedure in PL/SQL,

PL/SQL Package function returning VARRAY

PL/SQL Package function returning VARRAY

VARRAYs are used to if user knows record count in advance. i.e fixed size Data structures in PL/SQL.

It can contain array of single column or array of record Types.

Here is the Package which has function which returns array of Hiredates.

Create Package Specification

• Here i specified varray size as 11  , u can specify any number.

• Function has no input parameters. so no need to specify ().

• Function return type must be type name, not variable name.

create or replace package pkgVARRY1
is
type varrayType is varray(11) of date;
function get_hiredate return varrayType;

end pkgVARRY1;

Create Package Body

• User can remove where condition , if row count knows in advance.
• exception will be thrown if select statement has more records than 11.

create or replace package body pkgVARRY1
is
function get_hiredate return varrayType
as
v_t varrayType;
begin
select hiredate bulk collect into v_t from emp where rownum<=11;
return v_t;
end;
end pkgVARRY1;

Consume Package in PL/SQL Anonymous Block

SQL>declare
v_array pkgVARRY1.varrayType; -- declare variable of type varrayType

-- Syntax:- variable name   packagename.typename.
begin
dbms_output.put_line('function returning varray of DATES');

--calling package function , no need to specify ()
v_array:=pkgVARRY1.get_hiredate;

--display using for loop.
for i in v_array.first..v_array.last
loop
dbms_output.put_line(v_array(i));
end loop;

SQL>Set serveroutput on;

OUTPUT:

SQL> /
function returning varray of DATES
23-JAN-82
17-DEC-80
20-FEB-81
22-FEB-81
02-APR-81
28-SEP-81
01-MAY-81
09-JUN-81
19-APR-87
17-NOV-81
08-SEP-81

PL/SQL procedure successfully completed.

Tags:PL/SQL Package function returning VARRAY,PL/SQL VARRAYS, how to declare varrays in PL/SQL package. How to return VARRAY from a function in PL/SQL,

PL/SQL Procedure Returning Ref Cursor Example

Oracle 9i Introduced in-built Ref cursors sys_refcursor, Independent procedure/function can return data in terms of ref cursors. Application programmers can consume ref cursor data in terms of ResultSets. Note:Unlike Static cursors Ref cursors are used for dynamic queies.

Creating a Procedure which returns Ref cursors as Output Parameter.

   CREATE OR REPLACE Procedure REFSP(p_orders OUT sys_refcursor)
  as
  begin
  open p_orders for select * from pearson.orders;

 end;

 Calling a Procedure Returning Ref Cursor.

declare
v_order spathi.orders%rowtype;
v_orders sys_refcursor;
begin
refsp(v_orders);
loop
fetch v_orders into v_order;
exit when v_orders%notfound;
dbms_output.put_line(v_order.ORDERID||' '||
v_order.CUSTOMERID||' '||
v_order.EMPLOYEEID||' '||
v_order.ORDERDATE||' '||
v_order.REQUIREDDATE||' '||
v_order.SHIPPEDDATE||' '||
v_order.SHIPVIA||' '||
v_order.FREIGHT||' '||
v_order.SHIPNAME||' '||
v_order.SHIPADDRESS||' '||
v_order.SHIPCITY||' '||
v_order.SHIPREGION||' '||
v_order.SHIPPOSTALCODE||' '||
v_order.SHIPCOUNTRY
);
end loop;
end;

OUTPUT:

11066 WHITC 7 01-MAY-98 29-MAY-98 04-MAY-98 2 44.72 White Clover Markets 1029 - 12th Ave. S. Seattle
WA 98124 USA
11067 DRACD 1 04-MAY-98 18-MAY-98 06-MAY-98 2 7.98 Drachenblut Delikatessen Walserweg 21 Aachen
52066 Germany
11068 QUEEN 8 04-MAY-98 01-JUN-98  2 81.75 Queen Cozinha Alameda dos Canαrios, 891 Sao Paulo SP
05487-020 Brazil
11069 TORTU 1 04-MAY-98 01-JUN-98 06-MAY-98 2 15.67 Tortuga Restaurante Avda. Azteca 123 MΘxico D.F.

Tags:   Function Returning Ref Cursor Example,Sys_refcursor example, using Sys_refcursor in functions,

Creating a function which returns Ref cursor, Creating a Independent function which returns Ref cursor,Declaring Ref cursor, Calling a function Returning Ref Cursor.

PL/SQL Function Returning Ref Cursor Example

Oracle 9i Introduced in-built Ref cursors sys_refcursor, Independent function/procedure can return data in terms of ref cursors. Application programmers can consume ref cursor data in terms of ResultSets. Note:Unlike Static cursors Ref cursors are used for dynamic queies.

Creating a Function which returns Ref cursors.

CREATE OR REPLACE function reff1
return sys_refcursor
is
v_t sys_refcursor;
begin
open v_t for select * from pearson.orders;
return v_t;
end;

 Calling a Function Returning Ref Cursor.

select reff1() from dual;

OUTPUT:

ORDERID CUSTO EMPLOYEEID ORDERDATE REQUIREDD SHIPPEDDA    SHIPVIA    FREIGHT SHIPNAME
------- ----- ---------- --------- --------- --------- ---------- ---------- --------------------
  11051 LAMAI          7 27-APR-98 25-MAY-98                    3       2.79 La maison d'Asie
  11052 HANAR          3 27-APR-98 25-MAY-98 01-MAY-98          1      67.26 Hanari Carnes
  11053 PICCO          2 27-APR-98 25-MAY-98 29-APR-98          2      53.05 Piccolo und mehr
  11054 CACTU          8 28-APR-98 26-MAY-98                    1        .33 Cactus Comidas para
  11055 HILAA          7 28-APR-98 26-MAY-98 05-MAY-98          2     120.92 HILARION-Abastos
  11056 EASTC          8 28-APR-98 12-MAY-98 01-MAY-98          2     278.96 Eastern Connection
  11057 NORTS          3 29-APR-98 27-MAY-98 01-MAY-98          3       4.13 North/South
  11058 BLAUS          9 29-APR-98 27-MAY-98                    3      31.14 Blauer See Delikates
  11059 RICAR          2 29-APR-98 10-JUN-98                    2       85.8 Ricardo Adocicados
  11060 FRANS          2 30-APR-98 28-MAY-98 04-MAY-98          2      10.98 Franchi S.p.A.
  11061 GREAL          4 30-APR-98 11-JUN-98                    3      14.01 Great Lakes Food Mar

Tags:   Function Returning Ref Cursor Example,Sys_refcursor example, using Sys_refcursor in functions,

Creating a function which returns Ref cursor, Creating a Independent function which returns Ref cursor,Declaring Ref cursor, Calling a function Returning Ref Cursor.

before Insert or Update trigger in Oracle 11g

before Insert or Update trigger in Oracle 11g

Triggers are special kind of stored procedures in Oracle Database.

Oracle Database has 2types of DML triggers.

1. Statement level Triggers
2. Table level Triggers

DML Statement Level  triggers are automatically fired when Insert,Update,Delete operations performed on a table. so that each row values can be checked.

Here is an example for Before Insert or Before Update Statement level trigger, which performs  age check  against new values.

SQL>Create table employee(empno number(5), ename varchar2(20),JoinDate date, DOB date)

If employee Date of Birth is below 18 yrs old, it displays error message "Under Age".

Creating  a Age check Trigger  for  Before Insert or Before Update.

create or replace trigger Employee_Age_check
before insert or update
on employee
for each row

declare
n number(2);
begin

select months_between(current_date,:new.DOB)/12 into n from dual;

if n >=18 then
       dbms_output.put_line('Age is >18');
else raise_application_error(-20345,'Under Age');

end if;

end;

Note: months_between function returns number of months between 2 dates  , if it is divided by 12 months gives years.

Checking  Before Insert Trigger

for ex:

insert into employee values(1,'shyam',sysdate,to_date('2000-JAN-01','yyyy-MON-DD'))

Trigger will fire an exception

ERROR at line 1:
ORA-20345: Under Age
ORA-06512: at "SCOTT.EMPLOYEE_AGE_CHECK", line 7
ORA-04088: error during execution of trigger 'SCOTT.EMPLOYEE_AGE_CHECK'


SQL> insert into employee values(1,'shyam',sysdate,to_date('1990-JAN-01','yyyy-MON-DD'));
1 row created.

SQL> select * from employee;

    EMPNO ENAME                JOINDATE  DOB
--------- -------------------- --------- ---------
        1 shyam                14-APR-14 01-JAN-90

Checking  Before Update Trigger

SQL> update employee set DOB=to_date('2000-JAN-03','YYYY-MON-DD') where empno=1

it will throw same exception as shown above.


SQL> update employee set DOB=to_date('1990-JAN-03','YYYY-MON-DD') where empno=1

will succeed