0

I have a stored procedure with two input, two output params and 5 sys_refcursors. I had a succesful IF/ELSE where I opened these cursors by calling different stored procs but now need a third option which is another stored proc call. The third option is virtually identical to the second with one difference.

I was pretty sure I got the nested if statement correct but I keep getting ora-24338 Statement handle not executed when it tries to get the cursors from the this new call.

The problem stored procedure call is the middle one.

create or replace Procedure  procedure_name (
    OutVar out varachar2,
    Outvar2  out number,
    inParam1 date,
    REf-Cur1 in out sys_refcursor,
    REf-Cur2 in out sys_refcursor,
    REf-Cur3 in out sys_refcursor,
    REf-Cur4 in out sys_refcursor,
    REf-Cur5 in out sys_refcursor
) 
is
  tIsBindVar1 varchar2(100);
  tIsBindVar2 varchar2(100);
  tOutVar1    varchar2(100);
  TOutVar2    varchar2(100);
Begin
  Select Max(T.Var1) 
    into tIsBindVar1
    From table1 
   where T.aField = inParam1;

Select Function_Name (inParam1) 
  into tIsBindVar2 
  from Dual;

IF tIsBindVar1 is NOT NULL 
THEN
  Select P.Field_A P.Field_B 
    INTO tOutVar1, tOutVar2
    FROM table1 
  WHERE P.Field_A = inParam1;

  Stored_Proc_One (tInParam => tOutVar1, 
                   inParam1 => inParam1, 
                   5 cursors => 5 cursors);
ELSE 
  IF tIsBindVar2 = 'Y' 
  THEN
    Stored_Proc_Two (inParam1 => inParam1, 
                     5 cursors => 5 cursors);
  ELSE 
    Stored_Proc_Three ();
    Stored_Proc_Two ( inParam1 => inParam1, 5 cursors => 5 cursors);
  END IF;
END IF;

SELECT tOutVar1, tOutVar2 
  INTO OutVar1, OutVar2 
  FROM DUAL;

Some quick extra notes.

Stored_procs one and two are straight up data grabs, nothing fancy, stored proc 3 generates some data based on some input params (not listed) and stored proc 2 is called to collect.

I can change these stored proc calls around and I always wind up with the same error from the middle one. That includes changing the conditions in any order.

I tried to simplify the code as I'm not looking for anyone to do the work for me but to try and get understanding what the problem is.

Hopefully I did not leave anything important out but I think the issue is something with how I'm doing the nested if. I certainly don;t think the issue is in the the stored procs themselves as I say they work when I change the order.

So if long winded and very hard to read code. Going to try and find where they keep the editing info and clean it up.

Thanks in advance.

Improve this question
2
  • To add to what Justin said Stored_Proc_Three (); is incorrect. It should be Stored_Proc_Three; ( no brackets as there are no parameters ). Commented May 5, 2012 at 23:08
  • Issue Resolved. Thanks Ben, That's true.. I just went into c# mode there for a moment while writing it out. As it turns out the issue was firstly as Justin corrected it yesterday, but there was also an issue where if the first condition was false the second condition would never evaluate to true. It was a problem with the table it was referencing producing the field's value as sometimes Null and other times 0 ( which != null). Much thanks to Justin for pointing me in the right direction and cleaning up my code for me. Commented May 7, 2012 at 15:57

1 Answer 1

Reset to default
1

What are the values of tIsBindVar1 and tIsBindVar2?

Do you really want the IF statement structured the way I formatted it? Or do you really want

IF  tIsBindVar1 is NOT NULL 
THEN
  <<do something>>
ELSIF tIsBindVar2 = 'Y' 
THEN
  <<do something else>>
ELSE 
  <<do one more thing>>
END IF;

If you want to ensure that at least one path is followed in all cases, you'd want an IF ELSIF ELSE.

Incidentally, there is no need for all those SELECT FROM dual statements. You can simply assign variables in PL/SQL

tIsBindVar2 := Function_Name (inParam1);

and

tOutVar1 := OutVar1;
tOutVar2 := OutVar2;

are more conventional.

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Firstly, thank you very much for taking the time to not only answer but to edit my question for me. I spent a few mins making the code cleaner based on your adivice. I then switched it to an elsif... I was mistakenly trying to do it this way yesterday but was spelling it els<E>if. Unfortunately, it did not make the problem go away. The value for tIsbindVar1 is Number and tIsBindVar2 is VarChar2(2)... will different data types affect the process?
@dee - What are the values of tIsBindVar1 and tIsBindVar2? Is tIsBindVar1 NULL, for example? Or is it a non-NULL value? Have you tried debugging the code to verify that it is going down the path you expect?
Sorry I misread --- I gave you the data types not the values. The initial value for tIsBindVar1 is null although I've changed it to 0 for testing with no luck. The value for tIsBindvar2 was intially 'N' but I've since switched it to := Function_Name (inParam1); as per your suggestion.
@dee - Which of the three paths do you expect the code to use? Which path does it actually use (based on debugging)?
The procedure works when I set it up to test either the first (tIsBindVar1 IS NOT NULL) or the last condition. (tIsBindVar1 IS NULL & tIsBindVar2 != 'Y') When I try to set up a scenario that should fire the middle condition (tIsBindVar1 IS NULL & tIsBindVar2 = 'Y'), I get the error. I'm not near my project now to test it but I think your saying that the issue must be with the conditions I've set. Specifically that if the first condition is null then the second condition will never be 'Y'. That seems likely. I'll test it tomorrow. Tbh I have trouble debugging, need to look into tools/info on it

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.