overloading procedure in package

Question

Below is the HEADER for the Package TASK5

CREATE OR REPLACE PACKAGE TASK5 
    AS 
PROCEDURE  TASK5APROCEDURE ( 
REG_NO  IN  NUMBER,
CERT_TITLE   OUT  VARCHAR2,
E_DATE     OUT DATE,
    C_MARKS OUT INTEGER);


PROCEDURE  TASK5BPROCEDURE ( 
CERT_ID  IN  CHAR, 
C_T OUT CHAR) ;    

 END TASK5;

The BODY for the PACKAGE TASK5

CREATE OR REPLACE PACKAGE BODY TASK5 
AS 


 PROCEDURE    TASK5APROCEDURE ( 
    REG_NO  IN  NUMBER,
    CERT_TITLE   OUT  VARCHAR2,
    E_DATE     OUT DATE,
    C_MARKS OUT INTEGER)
            IS
            BEGIN

SELECT 
    O.PCP_TITLE, 
    C.CERT_EXAMDATE, 
    C.CERT_MARKS 

INTO  
    CERT_TITLE, 
    E_DATE, 
    C_MARKS

FROM 
    PROFCERTPROGRAM O
INNER JOIN 
CERTIFICATION C
    ON O.PCP_ID = C.PCP_ID
WHERE 
    C.S_REGNO LIKE REG_NO;
EXCEPTION
               WHEN NO_DATA_FOUND
THEN 
                  DBMS_OUTPUT.PUT_LINE('NO DATA FOUND');


END TASK5APROCEDURE;


PROCEDURE    TASK5BPROCEDURE ( 
CERT_ID  IN  CHAR, C_T OUT CHAR)
            IS
    BEGIN

DBMS_OUTPUT.PUT_LINE ('COURSE NAMES: ');

FOR R IN (  
    SELECT O.C_TITLE C_T
FROM 
    COURSE O
INNER JOIN 
CERTIFICATIONREQUIREMENT C
    ON O.C_ID = C.C_ID
WHERE 
    C.PCP_ID LIKE '%'||CERT_ID||'%')

LOOP
        DBMS_OUTPUT.PUT_LINE (R.C_T);
END LOOP ;

END TASK5BPROCEDURE;

END TASK5;

I wrote the package with two different procedure for 2 different input. But, I want to rewrite the header and body with overloading procedure, any suggestions?

FYI - LIKE without wild cards, as in 'C.S_REGNO LIKE REG_NO', is the same as equality. You should use the correct operator. — APC, Nov 04 '12 at 13:10

Ben · Answer 1 · 2012-11-04T16:07:37.220

Overloading means creating multiple procedures or functions of the same name in a package, which take different numbers of arguments and / or where the arguments have different datatypes. This enables you to call a procedure and have different things happen depending on the arguments given.

The answer to your question, therefore, is that simple. Rename TASK5BPROCEDURE to TASK5APROCEDURE in both the package specification and the package body. Alternatively, rename them both to something different. As an example your specification might look like this afterwards:

create or replace package task5  as 

   procedure task5procedure ( 
        , reg_no in number
        , cert_title out varchar2
        , e_date out date
        , c_marks out integer);

   procedure task5procedure ( 
        , cert_id in char 
        , c_t out char);    

end task5;

On a little side note using dbms_output.put_line in a caught exception isn't really best practice. If you're going to catch an exception you should do something with it.

As APC notes in the comment it would be normal to overload a procedure when you are doing highly related things. For example if you're sending an e-mail using a procedure and you're passing the e-mail addresses either as a string or as an array. You don't seem to be doing the same thing in your procedures here and may want to reconsider the necessity of doing this.

@PravinAgre - well clearly there's something wrong with how you're *calling* the procedures. But as we are not telepathic there's no way we'll be able to help you unless you past the code. — APC, Nov 04 '12 at 13:00
okk I will post it very soon if I couldn't figure out, thanks a lot. — Pravin Agre, Nov 04 '12 at 13:02
Good practice is that the overloaded procedures do essentially the same thing but with different parameters. The two procedures in the posted sample do different things (retrieve one row, retrieve multiple rows) and so ought to have different names. — APC, Nov 04 '12 at 13:07

overloading procedure in package

1 Answers1