CODEWITHSUNDEEP
oracle-databaseplsqloracle-apex-5
Nhat Minh
Nhat Minh

Reputation: 23

How to set variable in for loop with oracle pl/sql

I have a variable v_table contains value dynamic and I want to set value v_table in loop:

declare 
   v_table varchar(2000);
begin
   v_table := 'select ven.name from vendors ven ';
   htp.p('<select class="selectlist apex-item-select"  id="fname" name="fname">');
   for i in  v_table
   loop
      htp.p('<option value="'||i.name||'">'||i.name||'</option>');
   end loop;
end;

I don't know much about it. Please help me to resolve my problem.

Upvotes: 0

Views: 4132

Answers (1)

Alex Poole
Alex Poole

Reputation: 191275

The for i in ... construct is expecting a cursor, not a string. You can use a string variable for a cursor but only with the open ... for syntax, which is a bit more work.

This doesn't need to be dynamic though, and doesn't need the v_table string; you can just do:

begin
   htp.p('<select class="selectlist apex-item-select"  id="fname" name="fname">');
   for i in (select ven.name from vendors ven)
   loop
      htp.p('<option value="'||i.name||'">'||i.name||'</option>');
   end loop;
end;

Read more about cursor for ... loop statements in the documentation.

value of variable v_table unstable, it can be a different statements

Then you need to open the cursor from the statement explicitly, and fetch within the loop:

declare 
   v_table varchar(2000);
   v_cursor sys_refcursor;
   v_name varchar2(30);
begin
   v_table := 'select ven.name from vendors ven ';
   htp.p('<select class="selectlist apex-item-select"  id="fname" name="fname">');

   open v_cursor for v_table;
   loop
      fetch v_cursor into v_name;
      exit when v_cursor%notfound;
      htp.p('<option value="'||v_name||'">'||v_name||'</option>');
   end loop;
   close v_cursor;
end;

See the first documentation I linked to above, including the examples.

This still assumes that the dynamic statement will always produce a single value, which you will use for the option value and label. Slightly more generally those might be different, in which case you dynamic query could (always) get both, and fetch into two local variables - v_value and v_label perhaps - and then use both of those variables in the htp.p call. Either way the dynamic query always has to get exactly the same number of expressions in its select list, so it fetches into the same number of variables. That seems reasonable here. (Sometimes it isn't, and then you're look at dbms_sql, but that's overkill for this situation.)

Presumably you need to end the select after the loop too:

...
   end loop;
   close v_cursor;

   htp.p('</select>');
end;

Upvotes: 1

Related Questions