Hands-On Exercise Oracle 10g Pl SQL_v1 0

Hands-on Exercise: Oracle 10g PL/SQL Version: Oracle 10g PL/SQL/Hands-on Exercise/xxxx/x.x Date: 01-04-2008

Cognizant 500 Glen Pointe Center West Teaneck, NJ 07666 Ph:

201-801-0233

Hands-on Exercise – Exercise – Oracle Oracle 10g PL/SQL

TABLE OF CONTENTS

DAY 1: PL/SQL FUNDAMENTALS .............................................. ..............................................

3

Objectives .......................................................................................................................

3

Exercise 1.1 Exercise 1.2 Exercise 1.3 Exercise 1.4 Exercise 1.5

Error! Bookmark not defined. 3 Error! Bookmark not defined. 4 Error! Bookmark not defined. 4 Error! Bookmark not defined. 5 Error! Bookmark not defined. 5

DAY 2: CREATING PROCEDURES ...................................................................................... .................................................................................................... ..............2 Objectives ................................................................................................................................................................... 8


8 9 9 10 11

DAY 3: CREATING FUNCTIONS ..................................................... ..................................................................................... ................................................ ................ 12 Objectives ................................................................................................................................................................. 13

Exercise 3.1 Exercise 3.2

13 14

DAY 4: MANAGING SUBPROGRAMS ................................................. .................................................................................. ........................................... .......... 15 Objectives ................................................................................................................................................................. 22

Exercise 4.1 Exercise 4.2 Exercise 4.3

22 23 Error! Bookmark not defined. 13

DAY 5: CREATING PACKAGES ...................................................... ...................................................................................... ................................................ ................ 15 15 Objectives ................................................................................................................................................................. 22

Exercise 5.1 Exercise 5.2 Exercise 5.3

22 23 Error! Bookmark not defined. 13

DAY 7: ORACLE SUPPLIED PACKAGES .............................................................. ......................................................................................... ........................... 15 15 Objectives ................................................................................................................................................................. 22 Page 2

©Copyright 2007, Cognizant Technology Solutions, All Rights Reserved C3: Protected

Hands-on Exercise – Oracle 10g PL/SQL


22 23 Error! Bookmark not defined. 13 23 23

DAY 8: ORACLE SUPPLIED PACKAGES ......................................................................................... 15 Objectives ................................................................................................................................................................. 22

Exercise 8.1 Exercise 8.2 Exercise 8.3 Exercise 8.4

22 23 Error! Bookmark not defined. 13 23

NOTE: Run the scripts provided in file “ script_ORACLE10GPLSQL_assignments ”, before you start with the assignments.

Day 1: PL/SQL Fundamentals

Page 3



Objectives Hands-on Exercise Objective Exercise. No After completing the hands-on exercises, you will be able to: 1.1 Write a PL/SQL blocks that uses basic PL/SQL programming constructs, substitution variables Write PL/SQL blocks that uses basic PL/SQL programming 1.2 constructs, substitution variables, %type construct , insert operation Write PL/SQL blocks that uses looping constructs and dynamic 1.3 SQL Write PL/SQL blocks that uses INDEX_BY tables, Cursor 1.4 Write PL/SQL blocks that uses conditional structures 1.5

Exercise 1.1 Estimated Completion Time: 15 Minutes (10 Marks)

Complete the following assignment: Create a PL/SQL block that accepts 2 numbers from the user (use substitution variables). Perform an arithmetic Addition, Subtraction, Multiplication and Division. Store the results in different PL/SQL variables and display them with appropriate messages. Submit a document containing:  The program code  The output screenshots

Evaluation Rubrics Parameters

Completeness Accuracy Clarity of understanding Presentation Total

Weightage

4 3 2 1 10


Complete the following assignment: Page 4



Create a PL/SQL block that inserts a record into the Department table. Take the inputs for the record from the user. Commit the changes. Retrieve and display the newly added record. Submit a document containing: _ The program code _ The output screenshots



Weightage

4 3 2 1 10


Complete the following assignment: i. Write a PL/SQL block to create a table called Table_5 with the following structure. Use any looping structure to generate multiplication table of 5 and insert those values in Table_5. A NUMBER B NUMBER Result NUMBER 5 1 5 5 2 10 ……….. ………. ………….. 5 10 50 ii. iii.

Submit a document containing:  The program code  The output screenshots iv.


Completeness Accuracy Clarity of understanding

Weightage

4 3 2

Page 5



Presentation Total

1 10


Complete the following assignment: Write a PL/SQL block to retrieve records from the employees table and print employee‟s FIRST_NAME, LAST_NAME, SALARY and HIRE_DATE, incorporating an INDEX BY table. Declare INDEX_BY table as MY_EMP_TABLE with a structure same as employees table. Using Cursor retrieves the records of every employee and store it in the indexed table. Using another loop, retrieve the details of the employees as mentioned above, from the indexed table and print them. Submit a document containing:  The program code  The output screenshots Evaluation Rubrics Parameters


Weightage

4 3 2 1 10


Complete the following assignment:

Page 6



Write a PL/SQL block that prints the number of employees who earn between 10000 and 15000 If there are no employees within that salary range, print an appropriate message “NO EMPLOYEES IN THE PROVIDED SALARY RANGE” If there are one or more employees within that range, the message should indicate how many employees are in that salary range. Any other possible exceptions should be handled with an appropriate error message Submit a document containing:  The program code  The output screenshots Evaluation Rubrics Parameters


Weightage

4 3 2 1 10

Page 7



Day 2: Creating Procedures Objectives Hands-on Exercise Objective After completing the hands-on exercises, you will be able to: Write Procedures to Insert records, invoke procedures Write Procedures to Update records, invoke procedures Write Procedures to Delete records, invoke procedures Write Procedures to retrieve desired records, invoke procedures Execute the Procedure

Exercise. No

2.1 2.2 2.3 2.4 2.5


Complete the following assignment: Create a procedure called ADD_JOB_PROC to insert a new job into the JOBS table. Provide the ID and title of the job, using two parameters. Create IT_DBA procedure that should accept job_id and job title from the user. Invoke ADD_JOB_PROC with IT_DBA. Query the JOBS table to verify the results. Invoke your procedure again, passing a job_id of ST_MAN and a job title of Stock Manager. What happens and why? Submit a document containing:  The program code  The output screenshots Evaluation Rubrics Parameters


Weightage 4 3 2 1 10

Page 8




Complete the following assignment: Create a procedure called JOB_ UPD_PROC to update the job title. Provide the job_id whose title needs to be updated along with the new title as parameters. Include the necessary exception handling if no updates occur. Compile the code. Invoke the procedure to change the job title of the job_id “IT_PROC” to Programmer_IT. Query the JOBS table to view the result. JOB_ID

JOB_TITLE

IT_PROC Programmer_IT

MIN_SALARY MAX_SALARY …….

Also check the exception handling by trying to update a job that does not exist (you can use job ID IT_WEB and job title Web Master). Submit a document containing:  The program code  The output screenshots





Complete the following assignment: Create a procedure called JOB_DEL_PROC to delete a job from the JOBS table. Include the necessary exception handling if no job is deleted. Compile the code; invoke the procedure using job_id “IT_DBA”. Query the JOBS table to view the result.

Page 9



Also, check the exception handling by trying to delete a job that does not exist (use job_id IT_WEB). You should get the message you used in the exception-handling section of the procedure as output. Submit a document containing:  The program code  The output screenshots





Complete the following assignment: Create a procedure called EMP_PROC to query the EMPLOYEES table, retrieving the salary and job id for an employee when provided with the employee id. Provide code to handle possible exceptions. Use Host variables to provide the job_id and salary to the procedure. Compile the code. Invoke the procedure to display the SALARY and JOB_ID for employee_id 120. Invoke the procedure again, passing an EMPLOYEE_ID of 300 exist in employee table ). What happens and why?

(This value does not

Submit a document containing:  The program code  The output screenshots Evaluation Rubrics Parameters

Completeness Accuracy

Weightage 4 3

Page 10



Clarity of understanding Presentation Total

2 1 10


Complete the following assignment: You are the database developer for a publishing company. You create the following stored procedure to report the year-to-date sales for a particular book title: CREATE PROCEDURE get_sales_for_title %title varchar(80), @ytd_sales int OUTPUT AS SELECT @ytd_sales = ytd_sales FROM titles WHERE title = @title IF @@ROWCOUNT = 0 RETURN(-1) ELSE RETURN(0) Create a script that will execute this stored procedure. If the stored procedure executes successfully, it should report the year-to-date sales for the book title else report the following message: “No Sales Found” Submit a document containing:  The program code  The output screenshots Evaluation Rubrics Parameters



Page 11



Day 3: Creating Functions Objectives Hands-on Exercise Objective After completing the hands-on exercises, you will be able to: Create and executing basic functions. Creating and executing functions with parameters

Exercise. No

3.1 3.2


Complete the following assignment: Create a function that returns the annual salary of the employee whose employee id is provided. Compile the code. Create a host variable v_eid and invoke the function with v_eid=10. Print the annual salary returned by the function. Submit a document containing:  The program code  The output screenshots Evaluation Rubrics Parameters



Exercise 3.2 Estimated Completion Time: 10 Minutes (10 Marks) Complete the following assignment:

Page 12



Create a function called ANNUAL_COMP that takes monthly salary and commission percentage as parameters and returns a computed value: (salary * 12) + (commission_pct * salary * 12). Use NVL function appropriately to handle null values. Call the function from a select statement th at displays every employee‟s id, first name, salary and annual compensation Submit a document containing:  The program code  The output screenshots Evaluation Rubrics Parameters



Day 4: Managing Subprograms Objectives Hands-on Exercise Objective Exercise. No After completing the hands-on exercises, you will be able to: 4.1 Write Definer‟s Privileges, Grant Permission and Execute procedure. Write Invoker‟s Priv ileges, Grant Permission and Execute 4.2 procedure. Write procedure using Autonomous Transaction 4.3


Complete the following assignment: Create any procedure which will execute with Definer‟s Privileges. Grant executes permission to other user (Eg: Hr) to run the above created procedure. Login as the permission granted user (Eg: Hr) and execute the procedure.

Page 13







Complete the following assignment: Create any procedure which will execute with I nvoker‟s Privileges. Grant executes permission to other user (Eg: Hr) to run the above created procedure. Login has the permission granted user (Eg: Hr) and execute the procedure. Submit a document containing:  The program code  The output screenshots Evaluation Rubrics Parameters




Complete the following assignment: Page 14



Create a stored procedure RECEIVE_DATA which accepts itemcode and receivedqty. This procedure will insert these details in a „pending_updates‟ table. Then it will call UPDATE_STOCK function, which will update stock, return 1 (success), or 0 (failure) and commit the transaction. If success, RECEIVE_DATA will commit else it will rollback. You are required to implement this requirement, using „ Autonomous Transaction‟ feature at the appropriate level and observe the results. Submit a document containing:  The program code  The output screenshots Evaluation Rubrics Parameters



Day 5: CREATING PACKAGES Objectives Hands-on Exercise Objective After completing the hands-on exercises, you will be able to: Creating packages Creating packages Function overloading in a package

Exercise. No

5.1 5.2 5.3

Page 15




Create a package specification and body called PACK_JOB that contains ADD_JOB_PROC, JOB_UPD_PROC and JOB_DEL_PROC procedures. Reuse the codes from the previous chapters. Make all the constructs public Invoke the ADD_JOB_PROC procedure from the package by passing appropriate parameters. Query the JOBS table to verify the result. Submit a document containing:  The program code  The output screenshots Evaluation Rubrics Parameters




Create a package called CHK_PACK that contains the procedures CHK_HIREDATE and CHK_DEPT_MGR. Save the package specification and package body in separate files. The procedure CHK_HIREDATE accepts an employee‟s ID and checks whether the employee‟s hire date is within the range (SYSDATE, SYSDATE – 57 years). If hire date is invalid an exception should be raised with appropriate error message. The procedure CHK_DEPT_MGR accepts an employee‟s ID and his manager‟s ID. The procedure checks that the employee and manager work in the same department. If department number is invalid an exception should be raised with appropriate error message. Invoke the procedures from the package and verify the result. Submit a document containing: Page 16



 

The program code The output screenshots





Create a package called OVER_LOAD with 2 functions each named SIMPLE_PRINT. The functions should accept a date or a character string and print them. The date should be printed in the following format: „ FmMonth, dd yyyy ‟ . The print out the number, use 999,999.00 as the input format. Test the SIMPLE_PRINT functions with the following set of commands. VARIABLE disp_date VARCHAR2(15) EXECUTE :disp_date := OVER_LOAD.SIMPLE_PRINT( ‟08-MAR-01‟) PRINT disp_date VARIABLE disp_num NUMBER EXECUTE :disp_num := OVER_LOAD.SIMPLE_PRINT(„33,666‟) PRINT disp_num Submit a document containing:  The program code  The output screenshots Evaluation Rubrics Parameters



Page 17



Day 7: Oracle Supplied Packages Objectives Hands-on Exercise Objective After completing the hands-on exercises, you will be able to: Use UTL_FILE package in procedure. Use DBMS_DDL package in procedure. Use DBMS_SQL.OPEN_CURSOR ,DBMS_SQL.CLOSE_CURSOR, DBMS_SQL.NATIVE and DBMS_SQL.EXECUTE packages in procedure Use DBMS_SQL package in procedure

Exercise. No

7.1 7.2 & 7.3 7.4

7.5


Create a procedure called AVGSAL that generates a text file report of employees who have exceeded the average salary of their department. Your program should accept two parameters. The first parameter identifies the output directory. The second parameter identifies the text file name to which your procedure writes. Your instructor will inform you of the directory location. When you invoke the program, name the second parameter sal_rptxx.txt where xx stands for your use number, such as 01, 15, and so on. Add an exception handling section to handle errors that may be encountered from using the UTL_FILE package. Sample output from this file follows: EMPLOYEES EARNING MORE THAN DEPARTMENT REPORT GENERATED ON 26-FEB-01 Hartstein Raphaely

20 30

AVERAGE

SALARY

$13,000.00 $11,000.00

Page 18


IN

THEIR


Marvis …

40

$6,500.00





Create a procedure called ANALYZE_OBJECT that analyzes the given object that you specified in the input parameters. Use the DBMS_DDL package, and use the COMPUTE method. Test the procedure using the EMPLOYEES table. Confirm that the ANALYZE_OBJECT procedure has run by querying the LAST_ANALYZED column in the USER_TABLES data dictionary view. LAST_ANAL 27-Sep-01 Submit a document containing:  The program code  The output screenshots Evaluation Rubrics Parameters

Completeness Accuracy Clarity of understanding

Weightage 4 3 2

Page 19



Presentation Total

1 10


Schedule ANALYZE_OBJECT by using DBMS_JOB. Analyze the DEPARTMENTS table, and schedule the job to run in five minutes time from now. (To start the job in five minutes from now, set the parameter NEXT_DATE=5/(24*60)=1/288.) Confirm that the job has been scheduled by using USER_JOBS. Submit a document containing:  The program code  The output screenshots





Create a procedure called DELETE_ALL_ROWS that would generate a dynamic SQL QUERY.

Page 20



Your program should accept two parameters. First parameter is IN parameter which identifies the table name. The second parameter is OUT parameter which returns number of rows deleted. Use cursor to process Dynamic DML Operation. Use Oracle Supplied packages to open and close the cursor by using DBMS_SQL.OPEN_CURSOR and DBMS_SQL.CLOSE_CURSOR. To check the syntax use DBMS_SQL.PARSE and DBMS_SQL.NATIVE and use DBMS_SQL.EXECUTE to execute the sql statement and return the number of rows processed Submit a document containing:  The program code  The output screenshots





Create a DROP_TABLE procedure that drops the table specified in the input parameter. Use the procedures and functions from the supplied DBMS_SQL package. To test the DROP_TABLE procedure to drop the EMP_DUP table as a copy of the EMPLOYEES table. Execute the DROP_TABLE procedure to drop the EMP_DUP table. Submit a document containing:  The program code  The output screenshots Evaluation Rubrics Parameters

Weightage

Page 21




4 3 2 1 10

Day 8: Creating Database Triggers Objectives Hands-on Exercise Objective Exercise. No After completing the hands-on exercises, you will be able to: 8.1 & 8.2 Create Trigger for data validation before a table insert. Create Trigger for data validation before a table updation 8.3 Create Trigger for data validation before a DML operation on table 8.4


Create a trigger that performs certain checks before the values are inserted in the COURSE table. If the course duration is 40 and above then the minimum course fee should be 6000. Insert the following record and observe the output… INSERT INTO course VALUES („J2ee‟, 110, 'Servlets and JSP', 45, 4000, „Basics in creating Web components‟); Submit a document containing:  The program code  The output screenshots Evaluation Rubrics Parameters

Completeness Accuracy

Weightage 4 3

Page 22



Clarity of understanding Presentation Total

2 1 10


Create a trigger that checks whether a student is eligible for scholarship or not. Whenever a record is inserted in the Student table. The percentage marks of 10 and 12 should be verified. If a student has secured greater than 70% in 10 th and 60% or above in 12 th, he should be eligible for a scholarship in all courses that he registers for. Appropriate error messages should be displayed by the trigger. Insert the following record and observe the output. INSERT INTO student VALUES( „'Roshan', 'Dhar', ‟[email protected]‟ , '12/10 ashok vihar', 'west bengal', 700101, 9876668901,'S1008', '22-Dec-80', 'BE', 70, 69, '30-Mar-02', ' Mechanical Engg.'); Submit a document containing:  The program code  The output screenshots





Page 23



Create a trigger that will prevent the users from updating first name, date of birth and hire date of employees. Verify the same by performing an update on Employees table. Submit a document containing:  The program code  The output screenshots Evaluation Rubrics Parameters




Create a trigger that will allow any form of DML operations to be performed on the Employees table in business hours only. Business hours Monday-Friday (9 am- 6 pm) Submit a document containing:  The program code  The output screenshots Evaluation Rubrics Parameters



Page 24


Hands-On Exercise Oracle 10g Pl SQL_v1 0

Recommend Documents