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!!