2

I'm trying to do the following using PL/SQL:

  1. Use a query to get a list of strings
  2. Use a for loop to query using the list as an input.

I have this so far:

DECLARE 
sub2 varchar2(12);

cursor sub is Select ID_SUBLIN from TABLE 1 group by ID_SUBLIN;

BEGIN
for sub2 in sub LOOP
for inner in (select * from TABLE2 where PARAMETER=sub2.ID_SUBLIN )
loop
DBMS_OUTPUT.PUT_LINE( sub2 );
end loop;
end loop;
END;
/

However it doesn't work. I'm only iterating over a 11 items list

Thanks in advance

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
MasterC
  • 173
  • 1
  • 1
  • 13

2 Answers2

2

You have some structural problems and typoes. Try the following instead :

DECLARE 
-- sub2 varchar2(12);  
--> record_index "sub2" should exist in "for sub2 in sub", not as variable.
  cursor sub is Select ID_SUBLIN from TABLE1 group by ID_SUBLIN;
                                    --TABLE^1 [table name can't contain a space]   
BEGIN
for sub2 in sub 
loop
  for inner in (select * from TABLE2 where PARAMETER=sub2.ID_SUBLIN )
  loop
   dbms_output.put_line( sub2.ID_SUBLIN ); -- must be as <record_index>.<aColumn>
   dbms_output.put_line( inner.parameter );
-- by the way, both of the record_indexes with columns of cursors may be used here.
  end loop;
end loop;
END;
/
Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
  • for the above code can we assign the output of the for loop to a SYS_REFCURSOR ? – Linnea Mar 13 '21 at 12:18
  • Sure you can @Linnea , [this](https://stackoverflow.com/questions/64295880/how-can-i-make-a-function-to-return-a-constraints-column-names/64296180#64296180) or [this](https://stackoverflow.com/questions/65171838/returned-my-cursor-in-my-oracle-pl-slq-function-but-not-all-rows-are-being-retur/65171968#65171968) might be good starting points. – Barbaros Özhan Mar 13 '21 at 12:39
  • would you mind checking my post ? I am trying to iterate and assign the select output to a SYS_REFCURSOR https://stackoverflow.com/questions/66605055/oracle-for-loop-does-not-iterate-in-sys-refcursor – Linnea Mar 13 '21 at 13:08
  • 1
    Hi @Barbaros the countries are fetched based on ID's available in table 2 with alias s. Below is the Requirement Overview for more clarity--> ```1.So basically fetch all the ID's matching based on the t_id passed to procedure. 2.Store the ID's in some variable /collections 3.Iterate the ID's in next select queries as input 4. Generate the output as sys_refcursor``` – Linnea Mar 13 '21 at 13:27
  • @Barbasor I will also add Data of Table 2 to be more specific – Linnea Mar 13 '21 at 13:33
  • @Linnea I reached the same statement which,contains join among those tables as the last statement, already proposed by Littlefoot and Robertson. In the curent case, the variables for rc are overridden as the loop continues. Btw, holding t_id( or account) columns within both of the tables seems as a design issue. – Barbaros Özhan Mar 13 '21 at 13:59
  • @Barbasor ok, but that is how the tables are designed, however we are fetching ID based on the T_ID and storing them in a variable the reason being One T_ID would be linked to minimum 3 ID's and then those 3 ID's needs to act as an input for further selects. Correct me if I am wrong join would have worked if there was one ID fetched at a time but since there are multiple ID's being fetched it would be difficult I beleive – Linnea Mar 13 '21 at 14:04
  • hi, can you please take a look at my question? it is similar to trimming last characters. thank you https://stackoverflow.com/questions/69834411/update-one-column-of-each-row-in-a-table-pl-sql-unix-scripting – dcdum2018 Nov 04 '21 at 05:33
  • Hi @dcdum2018 , lately I'm able to observe your question. The answer for that question seems OK at a first look(need to be tested within your DB). – Barbaros Özhan Nov 04 '21 at 11:38
1

Alternate way with 2 queries combined in cursor and then looping.

DECLARE
    sub2   VARCHAR2(12);
cursor sub is 
SELECT id_sublin
FROM TABLE1
JOIN table2 
ON table2.parameter = TABLE1.id_sublin 
group    by    id_sublin;

BEGIN
    FOR sub2 IN sub 
    LOOP
        dbms_output.put_line(sub2.id_sublin);
        EXIT WHEN sub%notfound;
    END LOOP;
END;
kanagaraj
  • 442
  • 1
  • 3
  • 8