DBS501-Lab 5 Solved

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

 

2.   Write the code for procedure called show_bizdays that will display what business days (NOT Saturday, Sunday) are ahead (present day will be included). It will accept two arguments – start date (it may be any day) and how many business days are needed to show. It will store each date into PL/SQL table. Both  arguments will have default values – TODAY and 21 days.

Use WHILE LOOP to scan for desired number of business days.

                                    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)

 

4)   A) Now OVERLOAD your Package with NEW variation of Procedure show_bizdays

that will accept only ONE input parameter - Start Date and will prompt user to enter how many days are needed to show.

       B) Compile your package specification and body without warnings

       C) Test your OVERLOADED Procedure  (like for Q2 examples) by using your Package