DBS501 Lab 5 Solution

You need to provide BOTH --- Code and Output

1) Write the code for the Function called Get_Descr that will for a provided Section Id return its Course DESCRIPTION. Test your Function for a Valid and Invalid input by using BIND variables. Show both tests with Bind variables as well. Here are the outputs:

If you test with 150 then:

Course Description for Section Id 150 is Intro to Java Programming


If you test with 999 then:


There is NO such Section id: 999

Here are the outputs (SYSDATE here is 20-NOV-11)

SQL> execute show_bizdays

The index is : 1 and the table value is: 21-NOV-11
The index is : 2 and the table value is: 22-NOV-11
The index is : 3 and the table value is: 23-NOV-11
The index is : 4 and the table value is: 24-NOV-11
The index is : 5 and the table value is: 25-NOV-11
The index is : 6 and the table value is: 28-NOV-11
The index is : 7 and the table value is: 29-NOV-11
The index is : 8 and the table value is: 30-NOV-11
The index is : 9 and the table value is: 01-DEC-11
The index is : 10 and the table value is: 02-DEC-11
The index is : 11 and the table value is: 05-DEC-11
The index is : 12 and the table value is: 06-DEC-11
The index is : 13 and the table value is: 07-DEC-11
The index is : 14 and the table value is: 08-DEC-11
The index is : 15 and the table value is: 09-DEC-11
The index is : 16 and the table value is: 12-DEC-11
The index is : 17 and the table value is: 13-DEC-11
The index is : 18 and the table value is: 14-DEC-11
The index is : 19 and the table value is: 15-DEC-11
The index is : 20 and the table value is: 16-DEC-11
The index is : 21 and the table value is: 19-DEC-11

PL/SQL procedure successfully completed.

SQL> execute show_bizdays(sysdate+7,10)

The index is : 1 and the table value is: 28-NOV-11
The index is : 2 and the table value is: 29-NOV-11
The index is : 3 and the table value is: 30-NOV-11
The index is : 4 and the table value is: 01-DEC-11
The index is : 5 and the table value is: 02-DEC-11
The index is : 6 and the table value is: 05-DEC-11
The index is : 7 and the table value is: 06-DEC-11
The index is : 8 and the table value is: 07-DEC-11
The index is : 9 and the table value is: 08-DEC-11
The index is : 10 and the table value is: 09-DEC-11

PL/SQL procedure successfully completed.


3) A) Write the Package specification called Lab5 for the Procedure and Function created for this Lab
B) Then write the Package Body and compile without warnings.
C) Test your Package by providing input as for Question 2)

B) Compile your package specification and body without warnings
C) Test your OVERLOADED Procedure (like for Q2 examples) by using your Package