?
Solved

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

Posted on 2008-10-07
11
Medium Priority
?
321 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 1000 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 

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

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

800 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