Welcome to the Blog of Mike Ross

Let's flesh out our loops a bit and put some real-world HTML in there. Remember our cursors look like this:

cursor fun_src is
select distinct fund_source
from proj_table;

cursor ind_projs(fc varchar2) is
select *
from proj_table
where fund_source = fc;

I hope you are getting more comfortable with the notion of passing in a variable to a cursor (which value actually comes from the first cursor). A happy combination.

Here's the outer loop:

for i in fun_src loop

Let's add some HTML in here. The projects form uses a new table for each fund type, then one table row for each project, like this (THANKS to Summer who showed me how to print out the HTML without having it interpreted by using lt; and gt; directives!):

< table width=95% border=1 class=graytable>
<tr><th>'||j.proj_type||'</th></tr>
<tr align=left>
<td width=22%><b>Project Name</b></td><td width=7% align=center><b>Est Cost</b></td>
<td width=7% align=center><b>Encum</b></td><td width=10% align=center><b>Proj Mgr</b></td>
<td width=12% align=center>lt;b>Start Date</b></td>
<td width=12% align=center><b>Est End Date</b></td>
<td width=7%><b>Last Status</b></td><td width=7%><b>Curr Status</b></td><td width=16%><b>Comments</b></td></tr>

The outer loop gives me a table, the Project Type (i.proj_type), and a table row containing the column headings such as Est Cost, Proj Mgr, etc.

Now we will start the inner loop:

for j in ind_projs(i.fund_source) loop

and place in it the individual project information, like this:

</tr align=left>
</td width=22%></a style="color:'||vcfont||';text-decoration: underline" href="http://www2.wou.edu:7777/pls/wou/fmm30.form_projects.get_totals?pproj_no='||i.proj_no||'">'||i.proj_name||'</td><td align=right width=7%>'||i.proj_est_cost||'</td><td width=7% align=right>'||i.encumbered||'</td><td> width=10% align=center>'||i.proj_manager||'</td>
<td width=12% align=center>'||i.proj_start_date||'</td><td width=12% align=center>'||i.proj_completion_date||'</td><td width=7% align=center><img src = "https://www2.wou.edu:443/images/swamp/'||vimagelast||'" /><td width=7% align=center><img src = "https://www2.wou.edu:443/images/swamp/'||vimage||'" /></td>
<td width=16%>'||i.comments||'</td>
</tr>

This gives you a good example of the syntax required to acquire the data values from the cursor. Also, for your specific situation you might need to add in PL/SQL if-then statements, and others, to get your output correct. But I think you can now get the idea of how to use these cursor-loop combinations.

The last thing I wanted to suggest was using a cursor-loop combination for the sole purpose of passing values into your form! That's right. Of course you can pass in any number of parameters into a procedure. Here's a good example of what I'd like to avoid:

procedure displaypage(vdisabled varchar2, vcomments varchar2, vvendor_no varchar2, vname varchar2,
vsales_rep varchar2, vvendor_account_no varchar2, vfax varchar2, vtelephone varchar2,
vmobile_phone varchar2, vemail varchar2, vprice_agreement varchar2,
vconfirm varchar2, vfob varchar2, ppreq_no varchar2, vpo_no varchar2);

That's a pretty hairy procedure call! Well, SOMETIMES, certainly not always, you can use the cursor-loop combo to simply fetch a set of values from the database, and just display them by going through the loop only once. This works for a situation where you can pass in one or two parameters, and then use those to query the rest of the data with a cursor. If the values you are passing are available from the database, give this one a try.

Well, that's it for LOOPing for Data. I hope you can see a way to use the power of the cursor-loop combination to improve your own forms!