Back to: Oracle Tutorials for Beginners and Professionals
Forward Declaration in Oracle with Examples
In this article, I am going to discuss Forward Declaration in Oracle with Examples. Please read our previous article where we discussed Overloading Standard Package in Oracle. In most programming languages when we need to call a procedure or to call a variable, the procedure, or the variable need to be declared above the package in order to call it. This is the rule of the programming language. Now, let us understand this using an example.
create or replace package proc_rules_Calling is procedure print_emp_details (p_emp_id number); end;
So, from the above package example, we can see the package name is proc_rules_Calling which has the procedure in it. The procedure name is print_emp_details which takes p_emp_id with a number as a datatype. Let’s try to run this package specification.
As the package specification is now compiled. Let’s try to look at the package body.
create or replace package body proc_rules_Calling is function get_no_work_days (p_emp_id number) return number is v_hiredate date; begin select HIRE_DATE into v_hiredate from employees where EMPLOYEE_ID=p_emp_id; return round(sysdate-v_hiredate); end; procedure print_emp_details (p_emp_id number) is -- we will call the funcion from this procedure --so it should be defined above in order to invoke it v_details employees%rowtype; begin select * into v_details from employees where EMPLOYEE_ID=p_emp_id; dbms_output.put_line( 'id:'||v_details.EMPLOYEE_ID); dbms_output.put_line( 'fname:'||v_details.FIRST_NAME); dbms_output.put_line( 'salary:'||v_details.salary); dbms_output.put_line( 'hire date:'||v_details.HIRE_DATE); dbms_output.put_line( 'no of days work:'||get_no_work_days(p_emp_id)); end; end;
From the above package body, we can see that there is a function that is not declared in the package specification. So, this function is local to the package body. The function name is get_no_work_Days. This function will return the number of days the employee has worked in his life.
The function selects the hire_date of the employee and subtracts the hire_date from the current sysdate. So, this will display the number of days worked by the employee. We will use the variable v_hiredate and the hire_date is stored inside the variable v_hiredate. This function is very helpful and I will call this function from the package body itself.
The procedure print_emp_details is declared in the package specification. We can call the function get_no_work_days inside this procedure because the function is declared in the package body and it is local to the package body. The procedure takes emp_id as the input and it is stored in p_emp_id and where it selects the table employees with the provided employee id and is stored into v_details. This v_details will store the query output from the employees table. The procedure displays dbms_output values with first_name, employee_id, salary, and hire_date. This will also display the number of days worked by the employee by calling the function get_no_work_days.
The oracle will understand this function call because in order to call anything it should be defined above. Let’s try to compile the package body.
Let’s now try to call the procedure print_emp_details.
execute proc_rules_calling.print_emp_details(101);
We can see the dbms output which shows id as 101 and first_name, salary, hire_date, and number of days working. So, the procedure is working fine and called the function as well. So, let’s try something new. Let’s now change the order inside the package body. Let’s compile the package specification again.
create or replace package proc_rules_Calling is procedure print_emp_details (p_emp_id number); end;
The package specification is the same. Let’s try to compile this.
So, the package specification is now compiled. Let’s try to see the package body.
create or replace package body proc_rules_Calling is procedure print_emp_details (p_emp_id number) is v_details employees%rowtype; begin select * into v_details from employees where EMPLOYEE_ID=p_emp_id; dbms_output.put_line( 'id:'||v_details.EMPLOYEE_ID); dbms_output.put_line( 'fname:'||v_details.FIRST_NAME); dbms_output.put_line( 'salary:'||v_details.salary); dbms_output.put_line( 'hire date:'||v_details.HIRE_DATE); dbms_output.put_line( 'no of days work:'||get_no_work_days(p_emp_id)); end; function get_no_work_days (p_emp_id number) return number is v_hiredate date; begin select HIRE_DATE into v_hiredate from employees where EMPLOYEE_ID=p_emp_id; return round(sysdate-v_hiredate); end; end;
So, from the package body, you can see the procedure declared before creating the function and calling the function without being created. The entire code is completely the same. In this case, the procedure will search for the function get_no_work_days in the above but the function is not present above and Oracle gives us the error. Let’s try to compile this code.
We can see the error ‘GET_NO_WORK_DAYS’ is not declared in this scope. So, Oracle is looking for the function which is not declared above. In order to solve this problem oracle came up with a new method called forward declaration. Actually, this method is not widely used but this is good to know. Let’s drop the package and create the package again.
drop package proc_rules_Calling;
So, the package is now dropped. Let’s try to compile the package specification again.
create or replace package proc_rules_Calling is procedure print_emp_details (p_emp_id number); end;
Let’s try to compile the package body.
So, the package specification is now compiled. Let’s try to check the package body.
create or replace package body proc_rules_calling is function get_no_work_days (p_emp_id number) return number; -- we put the function specification only procedure print_emp_details (p_emp_id number) is -- we will call the funcion from this procedure --so it should be defined above in order to invoke it v_details employees%rowtype; begin select * into v_details from employees where EMPLOYEE_ID=p_emp_id; dbms_output.put_line( 'id:'||v_details.EMPLOYEE_ID); dbms_output.put_line( 'fname:'||v_details.FIRST_NAME); dbms_output.put_line( 'salary:'||v_details.salary); dbms_output.put_line( 'hire date:'||v_details.HIRE_DATE); dbms_output.put_line( 'no of days work:'||get_no_work_days(p_emp_id)); end; function get_no_work_days (p_emp_id number) return number is v_hiredate date; begin select HIRE_DATE into v_hiredate from employees where EMPLOYEE_ID=p_emp_id; return round(sysdate-v_hiredate); end; end;
We can see the package body is the same as the previous but we have made a change called forward declaration. We have mentioned the function specification header before the procedure.
function get_no_work_days (p_emp_id number) return number;
So, the package will not face the issue again. Remaining else about the procedure and function is the same. So, whenever the function is called inside the procedure it looks for the function which is declared above and then the pointer goes to the function definition and displays the output returned by the function. This package will run in the normal way as usual. Let’s try to compile the package body.
So, the package is now compiled successfully. Let’s try to call the procedure and see if the package is correct or not.
execute proc_rules_calling.print_emp_details(104);
So, the package is now run successfully. We can see the employee details and number of days worked by the employee which is calling the function.
In the next article, I am going to discuss the Persistent State of Packages in Oracle with Examples. Here, in this article, I try to explain Forward Declaration in Oracle with Examples. I hope you enjoy this Forward Declaration in Oracle article.