PL/SQL Records

PL/SQL RECORDS

In this article we discuss on below points :

. Declare Record
. Assigning values to a record
. Advantage using records
. Inserting a row using record
. Updating a row using record

Before starting assume we have a table xx_emp

CREATE TABLE XX_EMP (
 eno NUMBER,
 enmae VARCHAR2(15),
 dept VARCHAR2(15));

BEGIN
INSERT INTO XX_EMP VALUES (101,'Nag','EEE');
INSERT INTO XX_EMP VALUES (101,'Adi','CSE');
INSERT INTO XX_EMP VALUES (101,'Om','Mech');
COMMIT;
END;
/

Declaration of a record :

We can declare a record in 2 ways
1. Using TYPE keyword
2. Using %ROWTYPE

We can declare a record in a PL/SQL Declare section.

DECLARE
--declare record using TYPE
 TYPE AddrRecTyp IS RECORD (
  DoorNo VARCHAR2(15),
  street VARCHAR2(15),
  city   VARCHAR2(15),
  state  VARCHAR2(15),
  country VARCHAR2(15),
  pincode number(6) );
--create a variable of record type
vAddrRec1 AddrRecTyp;
--create a rec variable using %ROWTYPE
vempRec xx_emp%ROWTYPE;
BEGIN
--Assigning Values to record
vAddrRec1.DoorNo:='4-59-240/2';
vAddrRec1.street:='baystreet';
vAddrRec1.city:='vizag';
vAddrRec1.state:='Andhra';
vAddrRec1.country:='India';
vAddrRec1.pincode:=531162;
dbms_output.put_line(rpad('DoorNo',15)||rpad('street',15)||
       rpad('city',15)||rpad('state',15)||
       rpad('country',15)||rpad('pincode',15));
dbms_output.put_line(rpad('--',13,'-')||rpad('--',13,'-')||
       rpad('--',13,'-')||rpad('--',13,'-')||
       rpad('--',13,'-')||rpad('--',13,'-'));
dbms_output.put_line(rpad(vAddrRec1.DoorNo,15)||rpad(vAddrRec1.street,15)||
       rpad(vAddrRec1.city,15)||rpad(vAddrRec1.state,15)||
       rpad(vAddrRec1.country,15)||rpad(vAddrRec1.pincode,15));
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Error Occurred');
END;
/

--SELECT INTO record

DECLARE
x NUMBER;
y VARCHAR2(15);
Z VARCHAR2(15);
TYPE EmpRecTyp IS RECORD (
 no xx_emp.eno%type,
 name xx_emp.ename%type );
VempRec EmpRecTyp;
CURSOR empcur IS
SELECT eno,ename FROM XX_EMP
WHERE eno=101;
BEGIN
--so here we need 2 variables to hold
--the 2 values of emp rec
OPEN empcur;
FETCH empcur into x,y;
CLOSE empcur;
dbms_output.put_line('y : '||y);
--If we use record code becomes compact
--and need not declare separate variables
OPEN empcur;
FETCH empcur into VempRec;
CLOSE empcur;
dbms_output.put_line('Eno : '||VempRec.name);

END;
/

--Inserting a pl/sql record

DECLARE
vemprec xx_emp%ROWTYPE;
BEGIN
vemprec.eno:=104;
vemprec.ename:='Sri';
vemprec.dept:='ECE';
INSERT INTO xx_emp VALUES vemprec;
COMMIT;
END;
/

--Updating a row using record

DECLARE
vemprec xx_emp%ROWTYPE;
BEGIN
vemprec.eno:=104;
vemprec.ename:='Sri';
vemprec.dept:='Civil';
--While updating make sure you give
--all values in the record
UPDATE xx_emp SET ROW = vemprec where eno=104;
commit;
END;/

Thanks!!

Load Data into more than one table using SQl*Loader

Load Data into more than one table using SQl*Loader

Assume this is the format of the 2 tables

Table 1 :

CREATE TABLE XX_EMP
(
 record_type NUMBER,
 eno  NUMBER,
 ename  VARCHAR2(20),
 deptid NUMBER,
 phone NUMBER,
 doj DATE
);

Table 2 :

CREATE TABLE XX_DEPT
(
 record_type NUMBER,
 deptid NUMBER,
 dname VARCHAR2(20)
);

Requirement :

. Load data into xx_emp,xx_dept tables from single data file.

. If phone is NULL fill it with 'NA'

. doj column must be in "DD-MON-YYYY" format

Solution :

Control File :

LOAD DATA
INFILE '$1'
APPEND
INTO TABLE XX_EMP
WHEN record_type = '100'
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS
(
RECORD_TYPE POSITION(1),
eno,
ename,
deptid,
phone "decode(:phone,null,'NA')",
doj DATE "DD-MON-YYYY"
)
INTO TABLE XX_DEPT
WHEN record_type = '200'
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS
(
RECORD_TYPE POSITION(1),
deptid,
dname
)

Save the above file as "main.ctl" and place in the bin folder of Custom module Top, eg : $XXPO_TOP/bin

Data File :

100|1001|Nag|501|12345|30-MAY-2012
200|10|EEE
100|1002|Tom|502||10-JUN-2012

Save above file as "info.dat"

Note : POSITION(1) takes to the starting of the row data

Register the following as concurrent program with one parameter as data path : give the data file path in this parameter.

Now

select * from xx_emp;

select * from xx_dept;

Thanks!!

Associative Array and it's collection Methods

CREATE OR REPLACE PROCEDURE xx_AssocArr
AS

TYPE xx_AsscArr IS TABLE OF VARCHAR2(20)
INDEX BY PLS_INTEGER;

TYPE xx_AsscArr1 IS TABLE OF NUMBER
INDEX BY PLS_INTEGER;

TYPE xx_AsscArr2 IS TABLE OF VARCHAR2(40)
INDEX BY VARCHAR2(20);

TYPE xx_AsscArr3 IS TABLE OF NUMBER
INDEX BY VARCHAR2(20);

V1 xx_AsscArr;

V2 xx_AsscArr1;

V3 xx_AsscArr2;

V4 xx_AsscArr3;

i NUMBER;
J NUMBER;
z VARCHAR2(30);

BEGIN

dbms_output.put_line('Hi..');

/*Like in VARRAY and NESTED TABLE 
associative array doesn't require 
initialization directly values
can be assigned*/

--These are also called as key-value pairs

--v1 has values of string type and indexed by integer

v1(1000):='Vizag';

v1(1500):='Hyderabad';

v1(-100):='Atlanta';

dbms_output.put_line('FIRST INDEX of V1 : '||v1.FIRST);
dbms_output.put_line('LAST INDEX of V1 : '||v1.LAST);
dbms_output.put_line('count of V1 : '||v1.COUNT);

IF (v1.EXISTS(-100)) THEN
   dbms_output.put_line('Element exists at index -100 : '|| v1(-100));
ELSE
   dbms_output.put_line('Element does not exist');
END IF;

dbms_output.put_line('Index prior to element at 1000 : '||v1.PRIOR(1000));

dbms_output.put_line('Index next to element at 1000 : '||v1.NEXT(1000));

dbms_output.put_line('Printing V1');

j:=v1.FIRST;

WHILE j is NOT NULL LOOP
dbms_output.put_line('Element at index # '||j||' '||v1(j));
        j:=v1.NEXT(j);
END LOOP;

v1.DELETE(1000);

dbms_output.put_line('Printing V1');

j:=v1.FIRST;

WHILE j is NOT NULL LOOP
dbms_output.put_line('Element at index # '||j||' '||v1(j));
        j:=v1.NEXT(j);
END LOOP;

dbms_output.put_line('Working on V3');

/*v3 has values of string and indexed by strings!!
*/

v3('vizag'):='This is vizag city';

v3('pune'):='This is pune city';

v3('banglore'):='This is blore city';

dbms_output.put_line('Printing V3');

z:=v3.FIRST;

WHILE z is NOT NULL LOOP
dbms_output.put_line('Element at index # '||z||' '||v3(z));
        z:=v3.NEXT(z);
END LOOP;

v3.DELETE('pune');

dbms_output.put_line('Printing V3');

z:=v3.FIRST;

WHILE z is NOT NULL LOOP
dbms_output.put_line('Element at index # '||z||' '||v3(z));
        z:=v3.NEXT(z);
END LOOP;


EXCEPTION

WHEN OTHERS THEN

dbms_output.put_line('Error is : '||SQLERRM);

END xx_AssocArr;
/

Comparison of PL/SQL COLLECTIONS - III

NAME	VARRAY	NESTED TABLE	ASSOCIATIVE ARRAY
TRIM Removes one element from last in a collection	v1.TRIM	v1.TRIM	NA
TRIM(n) Removes n elements from last in a collection	v1.TRIM(3)	v1.TRIM(3)	NA
DELETE Deletes all the elements in a collection	v1.DELETE	v1.DELETE	v1.DELETE
DELETE(n) Deletes elements at index n in a collection	NA	v1.DELETE(6)	v1.DELETE(6)
DELETE(m,n) Deletes all the elements between m and n indexes in a collection	NA	v1.DELETE(4,7)	v1.DELETE(4,7)

Comparison of PL/SQL COLLECTIONS - II

NAME	VARRAY	NESTED TABLE	ASSOCIATIVE ARRAY
DECLARATION	TYPE VarTab IS VARRAY(10) of NUMBER;	TYPE VarTab IS TABLE OF NUMBER;	TYPE xx_AsscArr IS TABLE OF VARCHAR2(20) INDEX BY PLS_INTEGER;
INITIALIZATION	v1 := VarTab();	v1 := VarTab();	NA
ASSIGNMENT	v1.extend(); v1(1):=1001	v1.extend(); v1(1):=1001	v1(1):='Vizag';
LIMIT Gives Max length of collection	v1.LIMIT	NA as it is unbounded	NA as it is unbounded
EXTENDS Allocates one NULL element to a collection	v1.EXTENDS	v1.EXTENDS	NA
EXTENDS(n) Allocates n NULL elements to a collection	v1.EXTENDS(5)	v1.EXTENDS(5)	NA
EXTENDS(n,i) Allocates n elements with valueof element at index i collection	v1.EXTENDS(4,1)	v1.EXTENDS(4,1)	NA
EXISTS(n) Returns TRUE if element exists at Index n else returns FALSE	v1.EXISTS(-100)	v1.EXISTS(-100)	v1.EXISTS(-100)
COUNT Returns total no of elements in a collection	v1.COUNT	v1.COUNT	v1.COUNT
FIRST Returns the index of the first element in the collection	v1.FIRST	v1.FIRST	v1.FIRST
LAST Returns the index of the last element in the collection	v1.LAST	v1.LAST	v1.LAST
PRIOR(n) Returns the index of the element prior to the current element	v1.PRIOR(n)	v1.PRIOR(n)	v1.PRIOR(n)
NEXT(n) Returns the index of the element next to the current element	v1.NEXT(n)	v1.NEXT(n)	v1.NEXT(n)

Comparison of PL/SQL COLLECTIONS - I

NAME	VARRAY	NESTED TABLE	ASSOCIATIVE ARRAY
SIZE	Bounded	Unbounded	Unbounded
DENSE/SPARSE	DENSE	Both	Both
INDEX/SUBSCRIPT DATATYPE	Integer	Integer	Integer/String
WHERE CREATED	pl/sql block or at schema level	pl/sql block or at schema level	Only in a pl/sql block
CAN BE OBJECT TYPE ATTRIBUTE	Yes	No	No

PL/SQL Collection : Nested Table and it's Collection Methods

PL/SQL Collection : Nested Table and it's Collection Methods

Here I have created a procedure to see how to declare,initialize and manipulate a Nested Table.

Collection Methods on a Nested Table are :
FIRST,LAST,COUNT,
DELETE,DELETE(m,n),DELETE(n),
NEXT,PRIOR,
EXTENDS(n,i),EXTENDS(n),
EXISTS(n)

CREATE OR REPLACE PROCEDURE xx_nesttab
as

TYPE VarTab IS TABLE OF NUMBER;

V1 varTab;
--Initialize at declaration itself
v2 varTab := varTab(5001,5002,5003);
i NUMBER;
j NUMBER;

BEGIN

/*
Initialize the nestedTable using constructor
VarTab() is constructor which takes
same name as collection
*/

v1 := varTab();

v1.extend();

dbms_output.put_line(v1.COUNT);

v1(1):=1001;

dbms_output.put_line('First element in NestedTable : '||v1(1));

--assign 9 elements to existing Nested Table v1
v1.EXTEND(9);

FOR i in 2..10 LOOP
v1(i):=1000+i;
END LOOP;

--First and Last collection Methods
dbms_output.put_line('First Element Index : '||v1.FIRST);
dbms_output.put_line('Last Element Index  : '||v1.LAST);

--Print NestTab elements using First and Last methods

dbms_output.put_line('Elements in the NestTab are : ');

FOR i in v1.FIRST..v1.LAST LOOP
dbms_output.put_line('Element at index # '||i||' '||v1(i));
END LOOP;

--DELETE Method
v1.DELETE(10); -- Deletes element at index # 10

dbms_output.put_line('Elements in the NestTab are : ');

FOR i in v1.FIRST..v1.LAST LOOP
dbms_output.put_line('Element at index # '||i||' '||v1(i));
END LOOP;

--Say if we delete an element at index # 5
v1.DELETE(5); -- Deletes element at index # 5
--Now we cannot print using first and last methods

--Here we use NEXT and PRIOR Methods
dbms_output.put_line('Next index of an element : '||v1.NEXT(4));
dbms_output.put_line('Previous index of an element : '||v1.PRIOR(4));

dbms_output.put_line(' ');
dbms_output.put_line('Elements in the NestTab are : ');

j:=v1.FIRST;

WHILE j is NOT NULL LOOP
dbms_output.put_line('Element at index # '||j||' '||v1(j));
        j:=v1.NEXT(j);
END LOOP;

v1.DELETE(6,8); -- Deletes elements from 6th to 8th indexes

dbms_output.put_line(' ');
dbms_output.put_line('Elements in the NestTab are : ');

j:=v1.FIRST;

WHILE j is NOT NULL LOOP
dbms_output.put_line('Element at index # '||j||' '||v1(j));
        j:=v1.NEXT(j);
END LOOP;

v1.DELETE; -- Deletes all elements

dbms_output.put_line(v1.COUNT);

--Once we delete an element we need to assign and extending

--assign elements to existing Nested Table v1
v1.EXTEND(5);

FOR i in 1..5 LOOP
v1(i):=5000+i;
END LOOP;

dbms_output.put_line('Elements in the NestTab are : ');

j:=v1.FIRST;

WHILE j is NOT NULL LOOP
dbms_output.put_line('Element at index # '||j||' '||v1(j));
        j:=v1.NEXT(j);
END LOOP;

--EXTEND(n,i) assign element at index # 2 to 3 elements after last element
v1.EXTEND(3,2);

dbms_output.put_line('Elements in the NestTab are : ');

j:=v1.FIRST;

WHILE j is NOT NULL LOOP
dbms_output.put_line('Element at index # '||j||' '||v1(j));
        j:=v1.NEXT(j);
END LOOP;

dbms_output.put_line('Count before trim : '||v1.COUNT);
--TRIM Function 
v1.TRIM(2);
dbms_output.put_line('Count after trim : '||v1.COUNT);

--EXISTS(n) returns True if element exists at index # n

v1.DELETE(4);

if (v1.EXISTS(4)) THEN
dbms_output.put_line('Element Exists');
ELSE
dbms_output.put_line('Element doest not exist');
END IF;

EXCEPTION

WHEN OTHERS THEN

dbms_output.put_line(SQLERRM);

END;
/

PL/SQL Collection : VARRAY and it's collection Methods Usage

PL/SQL Collection : VARRAY and it's collection Methods Usage

Collection Methods are used to manipulate Varray's

Collection Methods on VARRAY

1. count
2. limit
3. trim
4. next
5. prior
6. delete
7. extends
8. first
9. last

Here is a procedure which uses all collection methods

create or replace procedure xx_varray as -- Declare a user defined datatype Varray TYPE VarTab IS VARRAY(10) of NUMBER; -- Declare a variable of varray type v1 VarTab; i number; j number; BEGIN /* Initialize the varray using constructor VarTab() is constructor which takes same name as collection which is mandatory! */ v1 := VarTab(); --Limit gives maximum size of collection dbms_output.put_line('Max size of Varray : '||v1.limit); dbms_output.put_line(' '); --Count gives no of elements present in a collection dbms_output.put_line('No of elements in varray : '||v1.count); dbms_output.put_line(' '); --Assigning values to varray by extending till it's limit for i in 1..10 loop v1.extend(); v1(i):=i+100; end loop; -- to traverse from 1st element to last element use -- first and last methods dbms_output.put_line('Collection Method First : '||v1.first); dbms_output.put_line('Collection Method Last : '||v1.last); dbms_output.put_line(' '); --printing varray elements for i in v1.first..v1.last LOOP dbms_output.put_line(v1(i)); END LOOP; --v1.delete; dbms_output.put_line(' '); dbms_output.put_line('Using TRIM method'); v1.trim; --trims last element in the varray dbms_output.put_line('Collection Method First : '||v1.first); dbms_output.put_line('Collection Method Last : '||v1.last); dbms_output.put_line(' '); --printing varray elements for i in v1.first..v1.last LOOP dbms_output.put_line(v1(i)); END LOOP; dbms_output.put_line(' '); dbms_output.put_line('Using TRIM(n) Method'); v1.trim(2);--trims or deletes last 2 elements dbms_output.put_line('No of elements in varray : '||v1.count); dbms_output.put_line(' '); --printing varray elements after trimming for i in v1.first..v1.last LOOP dbms_output.put_line(v1(i)); END LOOP; --Using Next and Prior Methods, these returns index values j:=3; dbms_output.put_line('next(3) : '||v1.next(3)); dbms_output.put_line(' '); dbms_output.put_line('prior(3) : '||v1.prior(3)); --Using method EXISTS(n), returns TRUE if element exists at index - n --else returns FALSE if (v1.exists(6)) then dbms_output.put_line('Element exists at index 6'); else dbms_output.put_line('Element doesnt exist at index 6'); end if; if (v1.exists(9)) then dbms_output.put_line('Element exists at index 9'); else dbms_output.put_line('Element doesnt exist at index 9'); end if; --Using DELETE method, deletes complete collection v1.delete; dbms_output.put_line(' '); dbms_output.put_line('No of elements in varray after DELETE Method : '||v1.count); dbms_output.put_line(' '); dbms_output.put_line('Again if we need to add an element use extend method'); v1.extend(); v1(1):=101; dbms_output.put_line(v1(1)); --Using EXTENDS(n,i) appends n copies of the ith element to a collection. v1.extend(3,1); dbms_output.put_line('Check the Varray Now: '); --printing varray elements for i in v1.first..v1.last LOOP dbms_output.put_line(v1(i)); END LOOP; --We can traverse Varray using next Collection method for printing/accessing collection elements j:=1; WHILE j is NOT NULL LOOP dbms_output.put_line(v1(j)); j:=v1.next(j); end loop; EXCEPTION When OTHERS then dbms_output.put_line(sqlerrm); END; /

Global Local Public Private terminology in a pl/sql Package

Upload Data into Table using SQL*Loader in Oracle Apps

Upload Data into Table using SQL*Loader in Oracle Apps

For SQL*Loader we need
control file
badfile
discardfile
and a data file which has data to be uploaded. We create a table : employee, in which data has to be uploaded

create table employee(eid number(3),ename char(20),dept char(20));

save below code as main.ctl
LOAD DATA INFILE '$1' APPEND INTO TABLE employee FIELDS TERMINATED BY ',' ( eid integer external, ename char, dept char )
below is the data file and save it as file.txt
999,ram,cse 101,vee,mpc 201,haa,bipc

Now control file and datafiles are ready. We need to create a concurrent program for this.
Place the control file in custom top, $XX_TOP/bin and mention data file path in the parameter form of the concurrent program. Create an executable

Create a concurrent program with a parameter which has the datafile path

Attach to a request group and run the program.

Over Loading in PL/SQL - Function Over Loading

OverLoading in PL/SQL - Function OverLoading

OverLoading means creating objects with similar but varying in type of the parameters and
number of parameters

Overloading on standalone functions is not allowed in PL/SQL.

See below example :

SQL> create function xx_ovrload(p IN number)
  2  return number
  3  as
  4  begin
  5  dbms_output.put_line('Input parameter type is Number');
  6  return 1;
  7  end;
  8  /

Function created.

Now we try to overload the function by changing the IN parameter datatype and see 

SQL> create function xx_ovrload(p IN varchar2)
  2  return number
  3  as
  4  begin
  5  dbms_output.put_line('Input parameter type is varchar2');
  6  return 2;
  7  end;
  8  /
create function xx_ovrload(p IN varchar2)
                *
ERROR at line 1:
ORA-00955: name is already used by an existing object

Still we have any doubts that it might be due to same no of parameters, we also test by adding an extra parameter to function and see

SQL> create function xx_ovrload(p IN varchar2,p1 IN varchar2)
  2  return number
  3  as
  4  begin
  5  dbms_output.put_line('Input parameter type is varchar2');
  6  return 2;
  7  end;
  8  /
create function xx_ovrload(p IN varchar2,p1 IN varchar2)
                *
ERROR at line 1:
ORA-00955: name is already used by an existing object

So where can we acheive overloading? In a package :)

SQL> create or replace package xx_ovrload_pkg
  2  as
  3  function xx_ovrload(p IN number) return number;
  4  function xx_ovrload(p IN varchar2) return number;
  5  end;
  6  /

Package created.

SQL> create or replace package body xx_ovrload_pkg
  2  as
  3  function xx_ovrload(p IN number)
  4  return number
  5  as
  6  begin
  7  dbms_output.put_line('1');
  8  return 1;
  9  end;
 10
 11  function xx_ovrload(p IN varchar2)
 12  return number
 13  as
 14  begin
 15  dbms_output.put_line('2');
 16  return 2;
 17  end;
 18  end;
 19  /

Package body created.

Test by executing :

SQL> select xx_ovrload_pkg.xx_ovrload(10) from dual;

XX_OVRLOAD_PKG.XX_OVRLOAD(10)
-----------------------------
                            1

1
SQL> select xx_ovrload_pkg.xx_ovrload('10') from dual;

XX_OVRLOAD_PKG.XX_OVRLOAD('10')
-------------------------------
                              2

2


NOTE : If the type and no of parameters are the same and having different RETURN datatypes, function overloading fails in this case.

Thanks!!

Oracle Joins : ANSI Standard

Oracle Joins using ANSI Standard Syntax :

Have a look at data in part and supplier table before we start:

SQL> select * from part;

PART SUPP
---- ----
P1   S1
P2   S2
P3
P4

SQL> select * from supplier;

SUPP SUPPLIER_NAME
---- --------------------
S1   Supplier#1
S2   Supplier#2
S3   Supplier#3

Inner Join :

select p.part_id,p.supplier_id,s.supplier_name
from part p INNER JOIN supplier s
on (p.supplier_id = s.supplier_id)
;

OR

select p.part_id,supplier_id,s.supplier_name
from part p INNER JOIN supplier s
USING(supplier_id);

Note : We need not qualify join column like p.supplier_id/s.supplier_id
            when we utilize USING keyword.

part_id    suppid           supp_name
-----         -----         --------------------
P1            S1            Supplier#1
P2            S2            Supplier#2

Left Outer Join :

select p.part_id,p.supplier_id,s.supplier_name
from part p LEFT OUTER JOIN supplier s
on (p.supplier_id = s.supplier_id)
;

or

select p.part_id,supplier_id,s.supplier_name
from part p LEFT OUTER JOIN supplier s
USING(supplier_id)
;

Output :

part_id    SuppId           SuppName
----------    -----------    ----------------
P1            S1            Supplier#1
P2            S2            Supplier#2
P4
P3

Right Outer Join :

select p.part_id,p.supplier_id,s.supplier_name
from part p RIGHT OUTER JOIN supplier s
on (p.supplier_id = s.supplier_id)
;

or

select p.part_id,supplier_id,s.supplier_name
from part p RIGHT OUTER JOIN supplier s
USING(supplier_id)
;

Output :

part_id    SuppId           SuppName
----------    -----------   ----------------
P1            S1            Supplier#1
P2            S2            Supplier#2
                            Supplier#3


Full Outer Join

select p.part_id,p.supplier_id,s.supplier_name
from part p FULL OUTER JOIN supplier s
on (p.supplier_id = s.supplier_id)
;

or

select p.part_id,supplier_id,s.supplier_name
from part p FULL OUTER JOIN supplier s
USING(supplier_id)
;

See the output

part_id    SuppId           SuppName
----------    -----------   ----------------
P1            S1            Supplier#1
P2            S2            Supplier#2
P3
P4
                            Supplier#3


Note : ANSI Standard is most effective than traditional way of joining the tables.

Oracle Joins : Traditional Syntax

Oracle Joins :

1. Traditional Syntax
2. ANSI standard since Oracle 9i

Types of Joins :
1. Self Join
2. Inner/Simple Join
3. Outer Join
     a. Left Outer
     b. Right Outer
     c. Full Outer

We use the below PART and SUPPLIER tables to understand the oracle joins in a lucid manner.

CREATE TABLE PART(
part_id         varchar2(5) constraint part_id_nn NOT NULL,
supplier_id     varchar2(5)
);

begin
insert into part values ('P1','S1');
insert into part values ('P2','S2');
insert into part values ('P3',' ');
insert into part values ('P4',' ');
commit;
end;
/

CREATE TABLE SUPPLIER(
supplier_id        varchar2(5) constraint supplierid_nn NOT NULL,
supplier_name    varchar2(20) constraint suppname_nn NOT NULL
);

begin
insert into supplier values ('S1','Supplier#1');
insert into supplier values ('S2','Supplier#2');
insert into supplier values ('S3','Supplier#3');
commit;
end;
/

Have a look at data in part and supplier table before we start:

SQL> select * from part;

PART SUPP
---- ----
P1   S1
P2   S2
P3
P4

SQL> select * from supplier;

SUPP SUPPLIER_NAME
---- --------------------
S1   Supplier#1
S2   Supplier#2
S3   Supplier#3

Joins using traditional syntax:

Here we use operators '=' and '+' for joining tables.

Simple Join :

When a simple join is made on 2 tables, common data between the 2 tables are fetched.

select
p.part_id,p.supplier_id,s.supplier_name
from
part p,supplier s
where p.supplier_id=s.supplier_id;

part_id    suppid           supp_name
-----         -----         --------------------
P1            S1            Supplier#1
P2            S2            Supplier#2

Here we did not get the parts : P3 and P4 which doesn't have a supplier yet.
Also we did not get the Supplier#3 which doesn't have a part yet.

To fetch a full table data while joining we use 'Outer Join'

Left Outer Join :

select
p.part_id,p.supplier_id,s.supplier_name
from
part p,supplier s
where p.supplier_id=s.supplier_id(+)
;

See the result now, we can see the other 2 parts even though they have a supplier yet.
Here we have fetched entire rows from PART table.

part_id       SuppId         SuppName
----------    -----------    ----------------
P1            S1             Supplier#1
P2            S2             Supplier#2
P4
P3

Right Outer Join :

select
p.part_id,p.supplier_id,s.supplier_name
from
part p,supplier s
where p.supplier_id(+)=s.supplier_id
;

See the results now, We will fetch all suppliers even though they donot supply any Parts.

part_id    SuppId         SuppName
---------- -----------    ----------------
P1            S1          Supplier#1
P2            S2          Supplier#2
                          Supplier#3

Now how about this situation if we need all rows from PART and SUPPLIER Tables??

We go for FULL JOIN:

select
p.part_id,p.supplier_id,s.supplier_name
from
part p,supplier s
where p.supplier_id=s.supplier_id(+)
UNION
select
p.part_id,p.supplier_id,s.supplier_name
from
part p,supplier s
where p.supplier_id(+)=s.supplier_id
;

See the output

part_id       SuppId         SuppName
----------    -----------    ----------------
P1            S1             Supplier#1
P2            S2             Supplier#2
P3
P4
                             Supplier#3


For ANSI Standard please go to



Oracle Joins : ANSI Standard

Thanks!

Commands to see APPS, DB, Form and Workflow Versions

Commands to see APPS, DB, Form and Workflow Versions

--Query to fetch Apps Version (11i/R12/12i)select release_name from apps.fnd_product_groups;

--Query to fetch Oracle Database versionselect * from v$version;

--to see whether RAC is implemented on DB show parameter cluster_database

--Workflow version with APPSselect TEXT "Version" from  WF_RESOURCES
where name='WF_VERSION'
and language='US';

#Web Server/Apache or Application Server in Apps 11i/R12#LOG into unix box using telnet/putty
#STEPS:
#1.Log in as Application user, set environment variable and run below query
#check if IAS_ORACLE_HOME env variable is set?
echo $IAS_ORACLE_HOME
#if exists set env variable as
set IAS_ORACLE_HOME
$IAS_ORACLE_HOME/Apache/Apache/bin/httpd -version

Output for 11i instance looks like

Server version: Oracle HTTP Server Powered by Apache/1.3.19 (Unix)
Server built:   Jun 21 2012 23:59:43 (iAS 1.0.2.2.2 rollup 5)

#to get Oracle forms versionset ORACLE_HOME
$ORACLE_HOME/bin/f60run | grep Version | grep Forms

Output looks like

Forms 6.0 (Forms Runtime) Version 6.0.8.28.0 (Production)

#File Version on file systemadident Header <filename>
orstrings <file_name> | grep Header

Here adident is AD Utility (Oracle Apps) and strings is Unix utility



Send email using PL/SQL

SEND EMAIL USING PL/SQL

Below is the code used :

We are creating a procedure to achieve this,

CREATE OR REPLACE PROCEDURE xx_sendmail
AS

crlf VARCHAR2(2) := CHR(13)||CHR(10);

v_Mail_Conn utl_smtp.Connection;

V_MAIL_HOST VARCHAR2(1000):= NULL;

V_MAIL_HOST1 VARCHAR2(1000):= NULL;

V_RECIPIENT VARCHAR2(1000):= NULL;

V_SUBJECT   VARCHAR2(1800):= NULL;

v_sender varchar2(400);

v_tot_msg varchar2(300);

BEGIN

 SELECT UTL_INADDR.GET_HOST_ADDRESS into v_mail_host from dual;

 dbms_output.put_line('host_addre :'||v_mail_host);

 v_mail_host1:='abc@gmail.com';

 v_sender:='abc@gmail.com';

 V_RECIPIENT:='xyz@gmail.com';

 v_subject:='Hi....';

 v_tot_msg:='mail send through pl sql code';

        V_MAIL_CONN := UTL_SMTP.OPEN_CONNECTION(V_MAIL_HOST, 25);

        UTL_SMTP.HELO(V_MAIL_CONN, V_MAIL_HOST);

        UTL_SMTP.MAIL(V_MAIL_CONN, v_sender);

        UTL_SMTP.RCPT(V_MAIL_CONN, V_RECIPIENT);

        UTL_SMTP.DATA(V_MAIL_CONN,

        'DATE: ' || TO_CHAR(SYSDATE, 'DY, DD MON YYYY HH24:MI:SS') || CRLF ||

        'FROM: ' || V_MAIL_HOST1 || CRLF ||

        'SUBJECT: '|| V_SUBJECT || CRLF ||

        'TO: ' || V_RECIPIENT || CRLF ||

        CRLF ||

        CRLF ||

        CRLF ||

        V_TOT_MSG|| CRLF -- MESSAGE BODY

        );

        UTL_SMTP.QUIT(V_MAIL_CONN);

        EXCEPTION

        WHEN UTL_SMTP.TRANSIENT_ERROR OR UTL_SMTP.PERMANENT_ERROR THEN

        RAISE_APPLICATION_ERROR(-20000, 'UNABLE TO SEND MAIL: '||SQLERRM);

        When others then

        dbms_output.put_line(sqlerrm);

END;
/

execute the procedure xx_sendmail

Thanks!!

Add responsibility through pl/sql

PL/SQL block to add responsibility

We have an API to do this : fnd_user_pkg.addresp

procedure AddResp(username       varchar2,
                  resp_app       varchar2,
                  resp_key       varchar2,
                  security_group varchar2,
                  description    varchar2,
                  start_date     date,
                  end_date       date)

--   Usage example in pl/sql
--   begin fnd_user_pkg.addresp('SCOTT', 'FND', 'APPLICATION_DEVELOPER',
--                              'STANDARD', 'DESCRIPTION', sysdate, null); end;
--   Input (Mandatory)
--   username:       User Name
--   resp_app:       Application Short Name
--   resp_key:       Responsibility Key
--   security_group: Security Group Key
--   description:    Description
--   start_date:     Start Date
--   end_date:       End Date

Take responsibility key value from

select responsibility_key from fnd_responsibility_vl
where responsibility_name = 'System Administrator' -- Query with your resp name to be added
;

Take resp_app from

select application_short_name from fnd_application
where application_short_name like 'SYS%';

CODE :

begin
fnd_user_pkg.addresp(
                        username       =>  v_user_name -- user_name from fnd_user table
                       ,resp_app       => 'SYSADMIN'
                        ,resp_key       => 'SYSTEM_ADMINISTRATOR'
                      ,security_group => 'STANDARD'
                        ,description    => 'Auto Assignment'
                         ,start_date     =>  SYSDATE
                        ,end_date       =>  SYSDATE + 365);
        COMMIT;       
        DBMS_OUTPUT.put_line ('Responsibility Added Successfully');
EXCEPTION
        WHEN OTHERS
        THEN
        DBMS_OUTPUT.put_line (  ' Responsibility is not added due to '|| SQLCODE ||SUBSTR (SQLERRM, 1, 100));
        ROLLBACK;
END;
/


Thanks!!

PL/SQL Exception Handling - III

PL/SQL exception Handling-III

User defined exceptions

These are invoked using
-RAISE
-RAISE_APPLICATION_ERROR Procedure

--take a table and insert few rows
create table XX_TEST
(
ref_id number(4),
Name varchar2(20),
mobile number(10)
);


begin
insert into xx_test values (1001,'Oracle',12345);
insert into xx_test values (1002,'Jave',54321);
commit;
end;
/

create or replace procedure user_excp_proc
as
cnt number;
my_excp exception;
begin
select count(*) into cnt
from xx_test
where name='dotnet';
if cnt = 0 then
raise my_excp;
dbms_output.put_line('next line in code after excpetion');
end if;
exception
when my_excp then
dbms_output.put_line('No subject ');
when others then
dbms_output.put_line(sqlerrm);
end;
/

begin
user_excp_proc;
end;
/

user defined exceptions using
RAISE_APPLICATION_ERROR

Syntax :

raise_application_error(
error_number, message[, {TRUE | FALSE}]);
where error_number is a negative integer in the range -20000..-20999 and message
is a character string up to 2048 bytes long. If the optional third parameter is TRUE, the
error is placed on the stack of previous errors. If the parameter is FALSE (the default),
the error replaces all previous errors. RAISE_APPLICATION_ERROR is part of package
DBMS_STANDARD, and as with package STANDARD, you need not qualify references to
it.

create or replace procedure user_excp_proc
as
cnt number;
begin
select count(*) into cnt
from xx_test
where name='dotnet';
if cnt = 0 then
raise_application_error(-20001,'no subject');
dbms_output.put_line('next line in code after excpetion');
end if;
exception
when others then
dbms_output.put_line('user exception..');
end;
/

begin
user_excp_proc;
end;
/

PL/SQL Exception Handling -II (Pragma Excpetion_init)

PL/SQL Exception Handling - II : Pragma Excpetion_init usage

Internally defined exceptions(ORA-n errors). These are raised implicitly.
However we have only few predefined error numbers associated with names as we discussed in
PL/SQL Exception Handling - I. The other predefined errors numbers are not associated with any predefined names.
In this we can assign names for those internal excpetions which have no names using
PRAGMA EXCEPTION_INIT (exception_name, error_code).


Let us take an example,


create table xx_test
(
id number(4),
name varchar2(20),
mobile number(10)
);

begin
insert into xx_test values (1001,'Oracle',12345);
insert into xx_test values (1002,'Jave',54321);
commit;
end;
/

Now we write a procedure which updates the name column of xx_test table with value higher than it's limit.
So obviously an excpetion will be raised. As we donot have such internal excpetion with a name.
catch it using OTHERS handler.

create or replace procedure xx_test_exception
as
BEGIN
update xx_test
set name='annnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnmmmm'
where id=1001;
commit;
EXCEPTION
WHEN others THEN
dbms_output.put_line(sqlcode||'-'||sqlerrm);
END;
/
run the procedure
SQL> begin
  2  xx_test_exception;
  3  end;
  4  /
-12899-ORA-12899: value too large for column "APPS"."XX_TEST"."NAME" (actual:
47, maximum: 20)
PL/SQL procedure successfully completed.


So now we use pragma exception_init and assign a name to the error no -12899
and now it will not go to others handler. See the modifed procedure
Note : to define the pragma, define in 2 steps

var_len_small excpetion; then
PRAGMA EXCEPTION_INIT(var_len_small, -12899);

create or replace procedure xx_test_exception
as
var_len_small EXCEPTION;
PRAGMA EXCEPTION_INIT(var_len_small, -12899);
BEGIN
update xx_test
set name='annnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnmmmm'
where id=1001;
commit;
EXCEPTION
when var_len_small then
dbms_output.put_line('trying to assign value which is higher than var limit');
WHEN others THEN
dbms_output.put_line(sqlcode||'-'||sqlerrm);
END;
/
see the output now,
SQL> begin
  2  xx_test_exception;
  3  end;
  4  /
trying to assign value which is higher than var limit
PL/SQL procedure successfully completed.

Exception Handling - III 


 

PL/SQL Exception Handling - I

PL/SQL Exception Handling - I

What is an exception :
In PL/SQL, an error condition is called an exception.

What happens if an exception raises?
When an exception is raised, the pl/sql program leads to abnormal termination which leads to
improper result sets.

To handle situations when an exception raises, we use exception handlers in
a planned manner.By that we can terminate program smoothly and also displaying and handling
the errors raised.

We have 2 types of exceptions
1. Internally Defined
2. User Defined

Internally Defined Exceptions:

An internal exception is raised automatically if your PL/SQL program violates a
database rule or exceeds a system-dependent limit. PL/SQL predefined some common ORA-n errors as internal exceptions.

Some common internal exceptions have predefined names,
for ex: NO_DATA_FOUND etc.,
Other predefined exceptions have error numbers but not given any names.
These can be associated with names using pragma EXCEPTION_INIT.






Example to handle internally predefined exception associated with
predefined name.

--take a table and insert few rows

create table XX_TEST
(
ref_id number(4),
Name varchar2(20),
mobile number(10)
);


begin
insert into xx_test values (1001,'Oracle',12345);
insert into xx_test values (1001,'Jave',54321);
commit;
end;
/

select * from xx_test;

create or replace procedure exp_hand_proc
as
v_mobile xx_test.mobile%type;
v_mobile1 xx_test.mobile%type;
begin
select mobile into v_mobile
from xx_test
where name='Oracle';
dbms_output.put_line('Mobile no :'||v_mobile);
select mobile into v_mobile1
from xx_test
where name='dotnet';
dbms_output.put_line('Mobile no :'||v_mobile1);
dbms_output.put_line('End of procedure');
exception
when NO_DATA_FOUND then
dbms_output.put_line('Ora Error :'||sqlerrm);
dbms_output.put_line('No data exists exception raised');
When OTHERS then
dbms_output.put_line(SQLCODE||'-'||SQLERRM);
end;
/

-- Run procedure and see how output displayed.
begin
exp_hand_proc;
end;
/

If an internal excpetion is raised, and if doesnot know the name of predefined excpetion
then use OTHERS handler in the exception block. And dispaly
error message and error number associated with excpetion using
sqlcode and sqlerrm.

Few others predefined excpetions are
DUP_VAL_ON_INDEX,INVALID_CURSOR,INVALID_NUMBER
LOGIN_DENIED,NOT_LOGGED_ON,PROGRAM_ERROR,
ROWTYPE_MISMATCH,SELF_IS_NULL,STORAGE_ERROR,
SUBSCRIPT_BEYOND_COUNT,SUBSCRIPT_OUTSIDE_LIMIT,
SYS_INVALID_ROWID,TIMEOUT_ON_RESOURCE,TOO_MANY_ROWS,
VALUE_ERROR,ZERO_DIVIDE

Will post examples on user defined excpetions in my next article.




Exception Handling -II Pragma Exception_init

Thanks!