Home » SQL & PL/SQL » SQL & PL/SQL » Procedure
Procedure [message #666791] Sat, 25 November 2017 22:38 Go to next message
rkapex
Messages: 10
Registered: November 2017
Junior Member
I created one procedure I execute the below procedure but no ouput showing . Please tell how can I execute the below procedure.


create or replace PROCEDURE OVERLOAD_PROCEDURE 
IS
VAR_NUM NUMBER(10);
DEPTNAME VARCHAR2(10);
PROCEDURE OVER_LOAD(DEPTNO NUMBER) 
IS 
BEGIN
SELECT  COUNT(*) INTO VAR_NUM FROM EMPLOYEES WHERE DEPARTMENT_ID=DEPTNO;
SELECT DEPARTMENT_NAME INTO DEPTNAME FROM DEPARTMENTS WHERE DEPARTMENT_ID=DEPTNO;
DBMS_OUTPUT.PUT_LINE('Number of Employees are  '||VAR_NUM);
OVER_LOAD(DEPTNAME);
END ;

PROCEDURE OVER_LOAD(DEPTNAME VARCHAR)
IS 
BEGIN
SELECT  COUNT(*) INTO VAR_NUM FROM DEPARTMENTS WHERE DEPARTMENT_NAME=DEPTNAME;
DBMS_OUTPUT.PUT_LINE('Number of Employees are  '||VAR_NUM);
END;
BEGIN
OVER_LOAD(90);
OVER_LOAD('Marketing');
END ;
Re: Procedure [message #666794 is a reply to message #666791] Sun, 26 November 2017 00:59 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
Since your OVER_LOAD(DEPTNO NUMBER) calls your OVER_LOAD(DEPTNAME VARCHAR), the PROCEDURE OVER_LOAD(DEPTNAME VARCHAR) must be declared first. Otherwise the calling procedure does not know the other one exists and tries to pass a varchar value where a number parameter is expected. Please see the demonstration below that only corrects this to make it run. I don't know exactly what you are actually trying to do, but OVER_LOAD(DEPTNAME VARCHAR) is not returning the number of employees.

HR@orcl_12.1.0.2.0> CREATE OR REPLACE PROCEDURE OVERLOAD_PROCEDURE
  2  IS
  3    VAR_NUM NUMBER(10);
  4    DEPTNAME VARCHAR2(10);
  5  
  6    PROCEDURE OVER_LOAD(DEPTNAME VARCHAR)
  7    IS
  8    BEGIN
  9  	 SELECT  COUNT(*) INTO VAR_NUM FROM DEPARTMENTS WHERE DEPARTMENT_NAME=DEPTNAME;
 10  	 DBMS_OUTPUT.PUT_LINE('Number of Employees are	'||VAR_NUM);
 11    END;
 12  
 13    PROCEDURE OVER_LOAD(DEPTNO NUMBER)
 14    IS
 15    BEGIN
 16  	 SELECT  COUNT(*) INTO VAR_NUM FROM EMPLOYEES WHERE DEPARTMENT_ID=DEPTNO;
 17  	 SELECT DEPARTMENT_NAME INTO DEPTNAME FROM DEPARTMENTS WHERE DEPARTMENT_ID=DEPTNO;
 18  	 DBMS_OUTPUT.PUT_LINE('Number of Employees are	'||VAR_NUM);
 19  	 OVER_LOAD(DEPTNAME);
 20    END;
 21  BEGIN
 22    OVER_LOAD(90);
 23    OVER_LOAD('Marketing');
 24  END;
 25  /

Procedure created.

HR@orcl_12.1.0.2.0> SHOW ERRORS
No errors.
HR@orcl_12.1.0.2.0> SET SERVEROUTPUT ON
HR@orcl_12.1.0.2.0> EXECUTE OVERLOAD_PROCEDURE
Number of Employees are  3
Number of Employees are  1
Number of Employees are  1

PL/SQL procedure successfully completed.


Re: Procedure [message #666796 is a reply to message #666791] Sun, 26 November 2017 03:34 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
rkapex

I execute the below procedure but no ouput showing
A much less sophisticated guess than Barbara's: did you SET SERVEROUTPUT ON?
Re: Procedure [message #666817 is a reply to message #666796] Mon, 27 November 2017 03:25 Go to previous message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
The number one calls the varchar one and the varchar one calls the number one and itself.
So any call to either will result in an infinite loop.
Barbara's rewrite has fixed this.
Previous Topic: Using views to summarise payment info
Next Topic: How to Avoid ORA-01438: value larger than specified precision allowed for this column
Goto Forum:
  


Current Time: Fri Mar 29 10:59:15 CDT 2024