Solved

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

Posted on 2008-10-07
11
305 Views
Last Modified: 2013-12-18
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
Comment
Question by:gram77
  • 4
  • 4
  • 3
11 Comments
 

Author Comment

by:gram77
ID: 22665540
***************************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
 
LVL 42

Accepted Solution

by:
dqmq earned 250 total points
ID: 22666035
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
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 22666905
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
 

Author Comment

by:gram77
ID: 22667848
nav_kum_v:
I am preparing a test case. Just in case one parameter is passed and another one is not passed.
0
 
LVL 42

Expert Comment

by:dqmq
ID: 22670352
>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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 22675649
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
 

Author Comment

by:gram77
ID: 22676970
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
 
LVL 42

Expert Comment

by:dqmq
ID: 22682742
>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
 

Author Comment

by:gram77
ID: 22684833
When the subroutine gets a null parameter, it raises an exception:
RAISE_APPLICATION_ERROR(..);
0
 
LVL 28

Assisted Solution

by:Naveen Kumar
Naveen Kumar earned 250 total points
ID: 22684845
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
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 22684848
we can handle it accordingly in the exception section
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now