SQL & PL/SQL

Dear all

I recently put up another post about the same error message but as the message now relates to another part of my programme and, in my mind at least, a different conceptual idea, I thought I should start a new top. If that is not right thing to have done then please let me know. I am working in 10.2.

I am trying to pass through multiple variables. When I run the code at the end of this question I get an error message:

PLS-00306: wrong number or types of arguments in call to 'CUR_MAP_LIST'

This relates to the line:

FOR var_map_list IN cur_map_list (par_map_list (n))

I think the reason the error message comes up is because par_map_list is a associate array / PL/SQL table and cur_map_list is based on %rowtype. Although I could be wrong. However I am not sure what I should be doing so that I don't get such an error message.

I was reading through page 623 on Web Development 9i (by Brown; pub. McGrew-Hill) and pages 357-358 of Oracle Web Application Programming for PL/SQL Developers (by Boardman, Caffrey, Morse, Rosenzweig; pub. Prentice Hall), in order to try and write my code. As well as Oracle's Application Developer’s Guide - Fundamentals (Release 2), page 11-6. In particular the Web Development book uses the following:

create or replace procedure query_department
(in_dept_no owa_util.ident_arr)
is
cursor dept_cursor (nbt_dept_no emp.deptno%TYPE) is
select empno, ename, mgr, sal, comm
from scott.emp
where deptno = nbt_dept_no;
begin
  for x in 1 .. in_dept_no.count loop
    for dept_rec in dept_cursor(in_dept_no (x)) loop
      ...
    end loop;
  end loop;
end;

In that example the cursor selects empno, ename, mgr, sal and comm from emp. So if it is doing that the cursor must be of a VARCHAR2 and NUMBER data type. What I don't understand is the for dept_rec in part. For a start I am not sure where dept_rec comes from? If it is a NUMBER data type, how can the in_dept_no, which is a owa_util.ident_arr associate array / PL/SQL data type work with it. Unfortunately because the example is incomplete and doesn't include procedures relating to the in variables, I am unable to run it and try and learn from what it is doing, so that I can try and relate the concept to my own work.

My programme is as follows. There may be other errors in the code not relating to this error. If so I hope to find these and resolve them once I understand what I should be doing here:

--Global variables--
--------------------
gvar_mapviewer_host VARCHAR2(56) := 'http://tiger.iso.port.ac.uk:7785/mapviewer/omserver';
gvar_proc_host VARCHAR2(56) := 'http://tiger.iso.port.ac.uk:7785/www/geg50160';
gvar_xml_request VARCHAR2(100) :='?xml_request=<?xml version="1.0" standalone="yes"?>';
 
--Main calling programming--
----------------------------
PROCEDURE MAPS AS
empty owa_util.ident_arr;
var_xml_theme VARCHAR2(32767);
BEGIN
PROCMAPLIST (empty, var_xml_theme);
END maps;
 
--create checkboxes--
---------------------
PROCEDURE PROCCHECKLIST
 (par_check_list IN OUT owa_util.ident_arr,
  par_xml_theme OUT VARCHAR2
  )
  AS
   CURSOR cur_map_list IS
    SELECT MT.map_title
           MI.map_id
           OMSN.map_sheet_number_id
           WRMF.web_raster_map_id
     FROM MAP_TITLE MT
          MAP_INFO MI
          MAP_SHEET_NUMBER OMSN,
          WEB_RASTER_MAP_FILE WRMF,
      WHERE MI.map_title_id = MT.map_title_id
      AND   MI.map_id = OMSN.map_id
      AND   WRMF.map_id = MI.map_id
      AND   WRMF.map_sheet_number_id = OMSN.map_sheet_number_id;
    var_map_list cur_map_list%ROWTYPE;
    var_xml_theme VARCHAR2(32767);
BEGIN
htp.htmlOpen;
htp.headOpen;
htp.headClose;
htp.bodyOpen;
htp.print('<FORM METHOD = "post"
                 ACTION = "'||gvar_proc_host||'.mappackage.procdisplay">');
    htp.print('<FIELDSET>
                 <LEGEND> Select the maps you wish to display </LEGEND>');
  FOR n IN 1 .. par_map_list.COUNT
  LOOP
  FOR var_map_list IN cur_map_list (par_map_list (n))
  LOOP
htp.print('       <UL>
                   <LI>
                    <LABEL FOR = "WRMF'||
                                  var_map_list.web_raster_map_id||'">
                     <INPUT type = "checkbox" 
                            id = "WRMFB'||
                                  var_map_list.web_raster_map_id||'"
                            name = "WRMFB'||
                                    var_map_list.web_raster_map_id||'" 
                            value = "'||var_map_list.web_raster_map_id||'"
                            />
                             Map title: '|| var_map_list.map_title||'<BR>
                             Sheet number: '||var_map_list.map_sheet_number||'');
                    htp.print('</LABEL>
                   </LI>
                  </UL>');
    END LOOP;
  END LOOP;
     htp.print('</FIELDSET>');
     htp.print('<p>
                 <INPUT TYPE = "submit"
                        NAME = "Display&nbspselected&nbspmaps"
                        VALUE = "Display&nbsp;selected&nbsp;maps" />
                  </FORM>');
htp.bodyClose;
END PROCCHECKLIST;

Thank you for reading. Kind regards

Tim