Solved

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

Posted on 2008-10-07
11
313 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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
 
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

756 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