2

I have set up an Oracle Package with a procedure similar to the code below. I had to set up a couple of globals first:

CREATE
OR REPLACE type cr_list_type AS object
(
        flag VARCHAR2(1));
CREATE
OR REPLACE type t_cr_list
IS
        TABLE OF cr_list_type;

I also define a type in the package header:

type flagTableType is table of varchar2(1) index by binary_integer;

I then created a proc as follows:

PROCEDURE get_stuff
                   (
                           o_results OUT SYS_REFCURSOR, 
                           o_files out sys_refcursor, 
                           in_list        IN flagTableType  
                   )
IS
    v_flag_list t_cr_list;
BEGIN

    v_flag_list := t_cr_list();
    v_flag_list.extend(in_list.count);

    for i in in_list.first .. in_list.last
    loop
    v_flag_list(i) := cr_states_type(in_list(i));
    end loop;

    OPEN o_results FOR
    select MyID, MyData
    FROM   MyTable
    where  MyFlag IN (SELECT flag from TABLE(v_flag_list));

  open o_files for 
    select * from MyTableFiles a
    where a.MyID in (select  MyID
                              from MyTable
                              where MyFlag in (SELECT flag from TABLE(v_flag_list)) 
                              );
EXCEPTION
WHEN NO_DATA_FOUND THEN
    NULL; -- Normal result
end get_stuff;

In the end, when i run it, i get a error occurred at recursive SQL level 1 error followed by a no statement parsed error.

I'm not using recursive queries. I know it's in the array, since if i remove the "in (SELECT flag from TABLE(v_flag_list))" from the second cursor and go with a "= 'X'", then both cursors return values. If i remove the second cursor query altogether, the array works fine on the first cursor. This has me flummoxed!

SpaceCowboy74
  • 1,367
  • 1
  • 23
  • 46
  • The message 'error occurred at recursive SQL level 1' suggests to me that the error is arising inside a trigger. See for example http://stackoverflow.com/a/7794570 . – Luke Woodward Mar 05 '12 at 23:38
  • 1
    "recursive" in this context refers to SQL that is run in order to run the SQL you've requested - e.g. trigger code, or SQL that Oracle runs on your behalf behind the scenes. – Jeffrey Kemp Mar 06 '12 at 03:47
  • No triggers were found. I did nothing this though. If i change the query to `open o_files for select * from MyTableFiles a where a.MyID in (select MyID from MyTable where MyFlag in ('X','Y','Z');` it runs ok. It has to do with that table cast in a nested select i think. – SpaceCowboy74 Mar 06 '12 at 14:45

1 Answers1

2

I solved my own problem. I had to cast the array for some reason into the array again.

open o_files for 
   select * from MyTableFiles a
   where a.MyID in (select  MyID
                    from MyTable
                    where MyFlag in 
                    (SELECT flag from TABLE(cast(v_flag_list as t_cr_states)));

I don't know why that wasn't necessary for the first cursor, but it is for the second.

SpaceCowboy74
  • 1,367
  • 1
  • 23
  • 46
  • I'd just been trying to recreate this in 11g, and couldn't; and wondered if you might still be on an earlier version. This suggests you might, we used to have to cast things in an earlier version, though I can't remember if it was related to it being buried a few layers down - think it might. Can you note your exact version number somewhere for future reference? – Alex Poole Mar 06 '12 at 15:40
  • Sorry, I keep forgetting that Oracle can be very different among versions. For the record, we have 9.2.0.7.0 on this database. – SpaceCowboy74 Mar 08 '12 at 17:12
  • thanks, that matches my experience then, we were on 9.2.0.8 when we had this, and I believe it was resolved in 10g. And it was only a problem sometimes, depending on the complexity of the query. Shame I didn't dredge that up in time to help you. – Alex Poole Mar 08 '12 at 17:26