Oracle/Error at line 0: PL/SQL: Compilation unit analysis terminated
I have been trying to create a function but i get Error at line 0: PL/SQL: Compilation unit analysis terminated. please advice what seems to be the problem.
CREATE OR REPLACE PROCEDURE insert_emp
(p_emp_id IN f_emps.employee_id%TYPE,
p_last_name IN f_emps.last_name%TYPE,
p_salary IN f_emps.salary%TYPE,
p_dept_id IN f_emps.department_id%TYPE)
v_dept_exists := check_dept(p_dept_id);
IF v_dept_exists THEN
INSERT INTO f_emps(employee_id,last_name,salary,department_id)
DBMS_OUTPUT.PUT_LINE('Department '||p_dept_id||' does not exist');
ANSWER: Hi Intisar,
To be honest with you, I don't see anything wrong with this. Before I dig deep into it, can you confirm with me that the check_dept() function returns BOOLEAN?
---------- FOLLOW-UP ----------
QUESTION: Hi Eliot, Thanks for your prompt response. Well department Id is NUMBER value so am not sure how to know check_dept()function will return a BOOLEAN, cause it's not working. By the way am using application express sql command prompt to do this...could that be the problem?
I do not have any experience working with APEX SQL command prompt. If I am using SQL Plus, I will be using the follwoing SQL
SELECT owner, object_name, pls_type
WHERE object_name = '<OBJECT_NAME>'
AND in_out = 'OUT'
ORDER BY object_name;
Check the PLS_TYPE column, it should return BOOLEAN if the function returns BOOLEAN.
Hope it helps.