To execute a procedure that is defined within a package in Oracle, you simply reference the procedure using the package name followed by the procedure name. This makes it easy to organize and call your procedures, especially when your application logic is grouped into packages.
Executing a Packaged Procedure Without Parameters
Suppose you have a procedure named Extract_Emp_Data within the package EMP_INTERFACE. You would execute it as follows:
BEGIN
-- Package_Name.Procedure_Name
EMP_Interface.Extract_Emp_Data;
END;
/
This format ensures that Oracle knows exactly which package and procedure you are referencing.
Executing a Packaged Procedure With Parameters
If your procedure requires parameters, you simply pass them in the usual way. For example, let’s assume the Extract_Emp_Data procedure takes an input parameter for the employee name and an output parameter for any error messages. Here’s how you would execute it:
SET SERVEROUTPUT ON;
DECLARE
v_o_error VARCHAR2(1000);
BEGIN
EMP_Interface.Extract_Emp_Data('John', v_o_error);
DBMS_OUTPUT.PUT_LINE(v_o_error);
END;
/
In this example, 'John' is passed as the employee name, and v_o_error is used to capture any error message that the procedure might return. The result is then displayed using DBMS_OUTPUT.PUT_LINE.
Calling a Procedure from Within the Same Package
When you are calling a procedure from inside another procedure within the same package, you do not need to prefix it with the package name. You can call it directly by its name. Here is how you would do it:
SET SERVEROUTPUT ON;
DECLARE
v_o_error VARCHAR2(1000);
BEGIN
Extract_Emp_Data('John', v_o_error);
DBMS_OUTPUT.PUT_LINE(v_o_error);
END;
/
This approach makes the code cleaner and easier to maintain when procedures in the same package need to interact with each other.
By following these patterns, you can efficiently execute packaged procedures in Oracle, whether from outside or within the same package.
