Link to home
Start Free TrialLog in
Avatar of gram77
gram77Flag for India

asked on

Compile time error when second parameter of an overloaded procedure is null

I have a package MSG_DLM_PROCESS.

It has 2 procedures (p_process) overloaded in MSG_DLM_PROCESS package.
The p_process differs in the second parameter p_ctxt_in.
In first procedure p_ctxt_in is of type MSG_BE.t_gtx_ctxt
In second procedure p_ctxt_in is of type MSG_BE.t_atd_ctxt

I create a test case to test p_process procedure with second parameter as null.

I call p_process procedure with the second parameter p_ctx_in as null;
    MSG_DLM_PROCESS.p_process(v_BusinessEvent.TRANS_TRADEID_TRADEIDVALUE,null);

I get the error:
Error(112,5): PLS-00307: too many declarations of 'P_PROCESS' match this call

obviously, oracle is not able to decide which overloaded procedure to call in the package.

Q) Now in such scenario, I need to terminate the procedure with an exception raised and handled in the
calling procedure. Just like i get an exception raised if the first parameter of p_process is null.

BEGIN
    MSG_DLM_PROCESS.p_process(v_BusinessEvent.TRANS_TRADEID_TRADEIDVALUE,null);
EXCEPTION
WHEN OTHERS THEN
  dbms_output.put_line('Test Case 3 FAILED - ' || sqlcode ||' : '||sqlerrm);
END;

Instead of a runtime error, this case becomes compile time error and the test procedure p_test_case_3_gtx itself
becomes invalid.

How do I handle this?
Avatar of gram77
gram77
Flag of India image

ASKER

***************************code***************************
create or replace
PACKAGE BODY MSG_DLM_PROCESS AS

  PROCEDURE p_process
    (p_espear_trade_no_in IN CHAR,
    p_ctxt_in            IN MSG_BE.t_gtx_ctxt
    ) AS

  BEGIN
      null;
  EXCEPTION
            WHEN others
            THEN
            dbms_output.put_line('error raised '||sqlerrm);
  END p_process;
/********************************overloaded p_process********************************/
  PROCEDURE p_process
  (
    p_espear_trade_no_in IN CHAR,
    p_ctxt_in            IN MSG_BE.t_atd_ctxt ) AS
  BEGIN
      null;
  EXCEPTION

            WHEN othres
            THEN
            dbms_output.put_line('error raised '||sqlerrm);
  END p_process;

END MSG_DLM_PROCESS;

=================================
My test case:p_test_case_3_gtx testing what happens when the second parameter is null
=================================

PROCEDURE p_test_case_3_gtx
IS
  v_part_key_ctxt MSG_DLM_CACHE.t_partition_key_context;
  v_part_key NUMBER;
BEGIN
  FOR v_BusinessEvent IN
  (SELECT
    /*+ index(GOOD_TRADES_XML_INDX_003)*/
    *
     FROM GOOD_TRADES_XML
    WHERE rownum<2
  )
  LOOP
      --passing second parameter as null to MSG_DLM_PROCESS.p_process. compilation error comes in this procedure.
      --I want a run time error that is handled by an exception handler of this procedure in the when others block.
    MSG_DLM_PROCESS.p_process(v_BusinessEvent.TRANS_TRADEID_TRADEIDVALUE,null);
  END LOOP;
EXCEPTION
WHEN OTHERS THEN
  dbms_output.put_line('Test Case 3 FAILED - ' || sqlcode ||' : '||sqlerrm);
END p_test_case_3_gtx;
ASKER CERTIFIED SOLUTION
Avatar of dqmq
dqmq
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Naveen Kumar
i am just surprised and thinking as to why you want to call a procedure which has one arugment with two parameters ? what is the reason behind this ?
Avatar of gram77

ASKER

nav_kum_v:
I am preparing a test case. Just in case one parameter is passed and another one is not passed.
>I am preparing a test case. Just in case one parameter is passed and another one is not passed.

But, you've proven that can never happen because it won't compile.  

Now, if your test case is that two parameters are passed, but one of them is null, then you have a valid test case.  The solution is to pass two variables to the called procedure, setting either one or both to null first.  
Can you try the below to see if the below can work for you. We are basically passing null but casting it to a datatype which it can detect to avoid the error.

  MSG_DLM_PROCESS.p_process(v_BusinessEvent.TRANS_TRADEID_TRADEIDVALUE,
   cast(null as MSG_BE.t_gtx_ctxt) ); -- to call the first procedure

  MSG_DLM_PROCESS.p_process(v_BusinessEvent.TRANS_TRADEID_TRADEIDVALUE,
   cast(null as MSG_BE.t_atd_ctxt ) ); -- to call the second procedure
Avatar of gram77

ASKER

dgmg:
The solution is to pass two variables to the called procedure, setting either one or both to null first.  

If I initialize one of the variables to null and the set the variable as a parameter to the procedure, the program works why? Why does't oracle sense that the parameter is null now?
>Why does't oracle sense that the parameter is null now?

Because it's not an error to pass a null parameter.  The question is, what does the subroutine do when it receives the null?  If you want to that condition to raise an error, then test for null and raise the error.  But often, it's preferable to take some default action when you pass the null.
Avatar of gram77

ASKER

When the subroutine gets a null parameter, it raises an exception:
RAISE_APPLICATION_ERROR(..);
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
we can handle it accordingly in the exception section