Oracle PL/SQL Exercises and Solutions

Oracle PL/SQL

Exercises & Solutions

Section: New PL/SQL syntax and functions

Determine the time in 45 minutes and 30 seconds from now, using a Time Interval and the Current Timestamp; use the format HH24:MI:SS. Hint: see page 288

declare
l_ti interval DAY TO SECOND;
begin
l_ti:= to_dsinterval('0 0:45:30');
dbms_output.put_line( to_char( current_timestamp + l_ti, 'HH24:MI.SS'));
end;

Verify that use of the NLS_INITCAP (, 'NLS_SORT = XDutch' ) ensures that the Dutch ‘ij’ correctly is init-capped as ‘IJ’.
begin
dbms_output.put_line( NLS_INITCAP ('ijsbaan', 'NLS_SORT = XDutch' ));
end;


Write a PL/SQL block that writes out the numbers one to ten: as number, in English and with their spelled and ordinat format: 1 ONE FIRST, 2 TWO SECOND etc.




Hint: the SP and SPTH suffixes in the TO_CHAR function – see page 270 in PL/SQL Programming


begin
for i in 1..100 loop
dbms_output.put_line
( rpad(i,4)
||rpad(to_char(to_date(i,'J'),'JSP'),15)
||to_char(to_date(i,'J'),'JSPTH')
);
end loop;
end;

The CASE operator that was introduced in SQL in Oracle 8i could be used in SQL embedded in PL/SQL starting with Oracle 9i. PL/SQL also got its CASE at that time. In two flavors: the CASE statement and the CASE expression (9i) (page 93-100 in PL/SQL Programming). Write a PL/SQL block that will take the fractional seconds from the current timestamp and write a string depending on the value of the first two digits in the fractional section: discern the cases of smaller than 30, smaller than 70 and the rest.
declare
l_random integer;
begin
for i in 1..10 loop
l_random:= to_number(substr(to_char(current_timestamp, 'FF'),1,2));
case
when l_random < 30
then
dbms_output.put_line( l_random||' not so big.');
when l_random < 70
then
dbms_output.put_line( l_random||' pretty middle of the road.');
else
dbms_output.put_line( l_random||' in the upper segment.');
end case;
dbms_lock.sleep(0.3);
end loop;
end;
/



Resource Table


Documentation

Oracle 9i R2 Database Library

dbms_metadata in Supplied Packages Manual



Records and Collections - Chapter 11
NOCOPY Hint – page 590-593
CASE statement and expression (9i) (page 93-100)
SUBTYPE (page 167)
CAST (9i) (page 172-174)
LENGTH_, SUBSTR-, INSTR-variations (page 200-220)
TRANSLATE..USING (page 220)
TRIM (page 221)
NLS_INITCAP, NLS_LOWER, NLS_UPPER, NLSSORT (page 222-225)
DATE (page 256-321)
NUMERIC types (page 231-232)
TO_CHAR(sysdate, ‘YEAR’) -> not NLS though!

CLOB relaxation: page 423-427
XMLType/URIType/AnyType (page 428)


Other Resources
New Datatypes, New Possibilities (Steven Feuerstein)

Tom Kyte on dbms_metadata



Section: Bulk Processing and Cursors


Bulk collection and DML:
Bulk Collect all job-values into a collection of VARCHAR2 using FETCH INTO the collection.
declare
type jobArray_nt is table of emp.job%type;
jobArray jobArray_nt;
begin
select e.job bulk collect
into jobArray
from emp e;
end;
Alternatively, you can fetch values into a collection using the RETURNING clause of an update statement. Populate the JobArray using an update emp set job=job statement using the returning clause. Hint: see page 450 in PL/SQL Programming
declare
type jobArray_nt is table of emp.job%type;
jobArray jobArray_nt;
begin
update emp
set job = job
returning job bulk collect into jobArray;
end;
Create a record emp_rt with fields empno and job. Create a nested table of emp_rec. Bulk Fetch all employees into this collection.
declare
type emp_rt is record
(empno emp.empno%type
,job emp.job%type
);
type emp_ntt is table of emp_rt;
emp_nt emp_ntt;
begin
select empno, job
bulk collect
into emp_nt
from emp;
end;
Try an alternative approach for bulk collecting or fetching: use of the MULTISET operator to bulk-select the records into a collection. Create the type jobArray_nt in the database server.
declare
jobArray jobArray_nt;
begin
SELECT cast
( multiset ( select job
from emp
)
as jobArray_nt
)
INTO jobArray
FROM dual;
for i in 1..jobArray.count loop
dbms_output.put_line(jobArray(i));
end loop;
end;


Section: Table Functions

Create a table function NAMES that returns rows with two ‘columns’ : firstname and lastname . The function accepts a single string as input; this string contains comma-separated names with space-separated firstname-lastname pairs (for example: ‘George Bush,John Kerry,John Edwards,Dick Cheney’). The function will return one row per name with two columns for both firstname and lastname. Write a query that orders these names alphabetically by lastname.