You are here:

Oracle/Error at line 0: PL/SQL: Compilation unit analysis terminated

Advertisement


Question
QUESTION: Hi
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.

thanks
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)
IS
v_dept_exists BOOLEAN;
BEGIN
v_dept_exists := check_dept(p_dept_id);

IF v_dept_exists THEN
INSERT INTO f_emps(employee_id,last_name,salary,department_id)
VALUES
 (p_emp_id, p_last_name,p_salary,p_dept_id);

ELSE
DBMS_OUTPUT.PUT_LINE('Department '||p_dept_id||' does not exist');
END IF;
END insert_emp;

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?

Elliot

---------- 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?

Answer
Good day,

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
FROM all_arguments
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.

Elliot

Oracle

All Answers


Answers by Expert:


Ask Experts

Volunteer


Elliot Mak

Expertise

I can answer questions with regarding to Oracle DB (8i, 9i, 10g, 11g) installation, configuration, administration, Data Guard, SQL, and PL/SQL (Procedures, triggers, functions).

Experience

I am an Oracle DBA, Senior PA, Project Manager, and Data Architect with 10 years of experience.

Education/Credentials
Bachelor of Science in Management System

©2016 About.com. All rights reserved.