[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 325
  • Last Modified:

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?
0
gram77
Asked:
gram77
  • 4
  • 4
  • 3
2 Solutions
 
gram77Author Commented:
***************************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;
0
 
dqmqCommented:
What you want to do makes no sense to me.  Your call with an untyped argument is illegal and will fail unconditionally.  You might as well just raise an error instead of making a call that is guaranteed to raise an error.

Perhaps what you want to do is decide which procedure you want to invoke and cast that null argument as the corresponding datatype:
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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 ?
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
gram77Author Commented:
nav_kum_v:
I am preparing a test case. Just in case one parameter is passed and another one is not passed.
0
 
dqmqCommented:
>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.  
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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
0
 
gram77Author Commented:
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?
0
 
dqmqCommented:
>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.
0
 
gram77Author Commented:
When the subroutine gets a null parameter, it raises an exception:
RAISE_APPLICATION_ERROR(..);
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
does this work for you ?

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
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
we can handle it accordingly in the exception section
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now