Q

Creating a stored procedure with a function in it

I am trying to create a stored procedure that has a function in it. Unfortunately, I can't seem to be able to call the function within the procedure.

Here is the code.

 CREATE OR REPLACE PROCEDURE  New_Emp(p_EMP_NAME IN VARCHAR2, p_EMP_JOB IN VARCHAR2, p_DEPT_NO IN NUMBER) IS
    v_NEW_EMP_ID EMP.EMPNO%TYPE;
    v_HIGHEST_DEPTNO DEPT.DEPTNO%TYPE;
    v_Avg_min EMP2.SAL%TYPE;

      FUNCTION Avg_Emp RETURN REAL AS
      BEGIN
      SELECT ROUND(AVG(SAL)) - MIN(SAL)
        INTO v_Avg_min
    FROM EMP;
      RETURN NUMBER;
      END;

	BEGIN
     	 SELECT MAX(EMPNO) + 1
	        INTO v_NEW_EMP_ID
		   FROM EMP;
	
     	 SELECT MAX(DEPTNO)
	       INTO v_HIGHEST_DEPTNO
            FROM DEPT;

      INSERT INTO EMP2(EMPNO, ENAME, JOB, MGR, HIREDATE,                 
				   SAL, COMM, DEPTNO, STARS)
		      VALUES
			      (v_NEW_EMP_ID, p_EMP_NAME, p_EMP_JOB, 						7566, '20-DEC-2001', v_Avg_min, 						NULL, v_HIGHEST_DEPTNO, NULL); END;
/ 

What am I doing wrong?

In your function, please replace RETURN NUMBER statement with RETURN V_AVG_MIN and it should work.
This was first published in January 2005

Dig deeper on Using Oracle PL-SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close