12-10. Creating a Package Header and Body
Problem
You want to create a package and store it into the database using Oracle SQL Developer.
Solution
Use the Create Package Wizard, or type the PL/SQL package code into a SQL worksheet. To start the wizard, go to the File menu, and select the New option. Once the Create a New dialog box opens, select Package, as shown in Figure 12-29.
Figure 12-29. Creating a new package
Once you click OK, you will be prompted to select a database connection. This will open the Create PL/SQL Package Wizard. Alternatively, you can connect to the database of your choice and then expand the navigator so that all the objects within the database are available. Right-click (Ctrl+click) the Packages submenu and select New Package.
How It Works
Creating a new package with Oracle SQL Developer is much the same as creating other code objects using this tool. You can develop using the manual technique of writing all code using the SQL worksheet, or you can use the creation wizards that are provided by the tool. You can type the example code into a SQL worksheet for your data connection and click the Run Statement toolbar button to compile and save the package into the database. You can also issue a Save As and save the code to a file on your workstation when writing code using the SQL worksheet.
Alternatively, the wizard is useful for quickly creating the standard code for a package, and you can use the editor to add the details that are specific to your package. Once you have opened the New Package Wizard, you will be prompted to enter a package name. For the purposes of this recipe, enter the name PROCESS_EMPLOYEE_TIME, and click OK. If there is an existing object that has the same name, then you will be alerted via a red pop-up message (Figure 12-30).
Figure 12-30. Naming the PL/SQL package using creation wizard
Note If you want to enter all code in lowercase for readability within the tool, you can select the check box before clicking OK once the package has been named. PL/SQL is not a case-sensitive language, so case does not affect code execution.
After proceeding, the package editor is opened, and it contains some standard package creation code using the name that you placed into the wizard. As you can see from Figure 12-31, the package editor contains several tabs, along with a search bar and Run, Debug, Compile, and Profile buttons. Enter the following example code into the text box on the Code tab:
CREATE OR REPLACE PACKAGE process_employee_time IS
total_employee_salary NUMBER;
PROCEDURE grant_raises(pct_increase IN NUMBER);
PROCEDURE INCREASE_WAGE (empno_in IN NUMBER,
Pct_increase IN NUMBER) ;
END;
Figure 12-31. Package editor window
Click the Save button to compile and store the package into the database. Once this has been completed, then the package header should be successfully stored in the database. Next, a package body will need to be added in order to make the package functional. This can be done by expanding the Package subfolder within the navigator. Once expanded, select the package for which you want to create a body. Right-click the selected package, and select the Create Body option (Figure 12-32).
Figure 12-32. Creating a package body
Next, the standard package body creation code will be added to an editor much like the SQL worksheet. You can now edit this code accordingly to ensure that it performs the correct actions. Type the following package body into the editor, and then click the Save button to compile and store the package body:
CREATE OR REPLACE PACKAGE BODY process_employee_time AS
PROCEDURE grant_raises (
pct_increase IN NUMBER) as
CURSOR emp_cur is
SELECT employee_id
FROM employees;
BEGIN
FOR emp_rec IN emp_cur LOOP
increase_wage(emp_rec.employee_id, pct_increase);
END LOOP;
DBMS_OUTPUT.PUT_LINE('All employees have received the salary increase'),
END grant_raises;
PROCEDURE increase_wage (
empno_in IN NUMBER,
Pct_increase IN NUMBER) as
Emp_count NUMBER := 0;
Results VARCHAR2(50);
BEGIN
SELECT count(*)
INTO emp_count
FROM employees
WHERE employee_id = empno_in;
IF emp_count > 0 THEN
UPDATE employees
SET salary = salary + (salary * pct_increase)
WHERE employee_id = empno_in;
SELECT salary
INTO total_employee_salary
FROM employees
WHERE employee_id = empno_in;
Results := 'SUCCESSFUL INCREASE';
ELSE
Results := 'NO EMPLOYEE FOUND';
END IF;
DBMS_OUTPUT.PUT_LINE(results);
END increase_wage;
END process_employee_time;
If any compilation errors are encountered, an error window will be displayed providing the line number and specific error message that needs to be addressed. After any compile errors are repaired, the package body will be successfully created. You can then use the navigator to expand the package name and see the package body listed within it. Right-clicking the package body in the navigator offers some options such as Edit, Run, Compile, Profile, and Debug. You will learn more about debugging in Recipe 12-12. The Edit option will open the package body editor if it is not already open. The Run option will open the Run PL/SQL window, which allows you to select a procedure or function to execute from the chosen package (Figure 12-33).
Figure 12-33. Running the PL/SQL package
Once a function or procedure is chosen from the Run PL/SQL window, it is executed using the values that are assigned to the variables within the PL/SQL Block panel of the window (this code is automatically generated by SQL*Developer). These values can be changed prior to running the package by editing the code that is displayed within the panel. This window also provides the opportunity to save the code to a file or load code from an existing file.
Oracle SQL Developer makes developing PL/SQL packages easy. All the tools that are needed to successfully create, edit, and manage packages are available within the environment. Whether you are a beginner or seasoned expert, these tools will make package development and maintenance a breeze.