0

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.

1
  • 2
    You haven't said what problem you are having; you could at least include the error you get in the question. Commented Mar 25, 2020 at 8:57

1 Answer 1

1

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;
Sign up to request clarification or add additional context in comments.

3 Comments

Thank for your replied, but value of variable v_table unstable, it can be a different statements
@NhatMinh - OK, added the open ... for version.
Thank for you resolve, now my trouble have been resolved

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.