CODEWITHSUNDEEP
oracle-databaseplsql
DmitryB
DmitryB

Reputation: 1179

How to set cursor field name from another cursor? Oracle

I need to take cursor fields names from another cursor like this:

FOR rec1 IN (SELECT * FROM table1) LOOP  
   FOR rec2 IN (SELECT * FROM table2) LOOP  
       IF rec1.[rec2.field_name] <> '*' THEN 
         ...                                          
       END IF;
   END LOOP;
END LOOP;

Upvotes: 1

Views: 1558

Answers (2)

Allan
Allan

Reputation: 17429

Oracle is really not designed for this kind of behavior. The only way I can think of to achieve this is to use dynamic PL/SQL to produce the functionality you're looking for:

declare
  v_field_value varchar2(2000);
begin
  FOR rec1 IN (SELECT * FROM table1) LOOP  
     FOR rec2 IN (SELECT * FROM table2) LOOP  
         EXECUTE IMMEDIATE 'begin :value := :rec1.' 
                           || :rec2.field_name || '; end;' 
            USING OUT v_field_value, IN rec1;
         IF v_field_value  <> '*' THEN 
           ...                                          
         END IF;
     END LOOP;
  END LOOP;
end;

However, just because this approach can work doesn't mean you should use it. If your field is not a string, for instance, Oracle will implicitly convert the value, which may result in a different value than what you expect. If this were my code, I would only use this as a last resort, after considering implementing the same functionality outside the database and redesigning the database's structure to avoid the need for this type of code.

Based on the comment the error mentioned in the comment, I've modified the code so to pass the records in using bind variables.

Upvotes: 2

Robert Giesecke
Robert Giesecke

Reputation: 4314

I am not sure, I understand what your problem is, you can access the outer cursor-loop's record just like you would expect from a variable that is declared in a scope above the current one.

for rec1 in (SELECT * FROM table1) loop
   for rec2 in (SELECT * FROM table2) loop
       if rec1.field = 1 and rec2.field_name <> '*' then
         ...                                          
       end if;
   end loop;
end loop;

kinda like

declare
  i Integer;
begin
  declare 
     x Integer;
  begin
     i := x;
  end;
end;

Upvotes: 0

Related Questions