Thanks. That is what I had to do -- DBMS_SQL that is.

This is related to my original question, but I had to use DBMS_SQL to execute dynamic queries for a multiple parameter inquiry form for an intranet site using the Oracle Application Server. It only took me about 5 hours yesterday to figure out all the pieces to get my DBMS_SQL code to work. I have 2 Advanced PL/SQL books and between them, I was able to fill in the critical missing tidbits that actually make all this work. I finally had to convert my PK number column to character before it would work.

I am not binding any variables, so I did not need any of those DBMS_SQL commands, but have you had problems with non-character columns [e.g., Number columns]? My books allude to VARCHAR2 data only in DBMS_SQL defined columns and column values. I find that hard to believe because the example in one book is retrieving a numeric ID column and their output is numeric. However, I could not get DBMS_SQL to work until all the columns I retrieved were converted using TO_CHAR.

The books I have are confusing on this significant point. The code I came up with for this feature looks like the following. The calls to Header, Footer and HTMLClass are my own SPs that wrap other htp commands:

  PROCEDURE Retrieve_Facilities (v_Query VARCHAR2)
   IS
      v_cursor_id     PLS_INTEGER := 0;
      v_rowcount      PLS_INTEGER := 0;
      v_Permit_Num    Inquiry_View.Permit_Num%TYPE;
      v_Facility      Inquiry_View.Facility%TYPE;
      v_City          Inquiry_View.City%TYPE;
      v_State         Inquiry_View.State%TYPE;
      v_KeyID         CHAR(8);
      NO_DATA_FOUND   EXCEPTION;
   BEGIN
      v_cursor_id := DBMS_SQL.OPEN_CURSOR;
      DBMS_SQL.PARSE(v_cursor_id, v_Query, DBMS_SQL.NATIVE);
      DBMS_SQL.DEFINE_COLUMN(v_cursor_id, 1, v_Permit_Num, 9);
      DBMS_SQL.DEFINE_COLUMN(v_cursor_id, 2, v_Facility, 40);
      DBMS_SQL.DEFINE_COLUMN(v_cursor_id, 3, v_City, 30);
      DBMS_SQL.DEFINE_COLUMN(v_cursor_id, 4, v_State, 2);
      DBMS_SQL.DEFINE_COLUMN(v_cursor_id, 5, v_KeyID, 8);
      v_rowcount := DBMS_SQL.EXECUTE(v_cursor_id);
      IF DBMS_SQL.FETCH_ROWS(v_cursor_id) = 0 THEN
         If DBMS_SQL.IS_OPEN(v_cursor_id) THEN
            DBMS_SQL.CLOSE_CURSOR(v_cursor_id);
         End If;
         RAISE NO_DATA_FOUND;
      End If;
      header('Facility Listing', 'NPDESTest.find_facilities');
      htp.center(htf.Header(3,'Facilities Found:'));
      htp.Br;
      htp.p('<center>');
      htp.tableOpen(cattributes => 'BORDER=2 CELLSPACING=0 CELLPADDING=2 WIDTH="90%"');
      htp.TableRowOpen;
      HTMLClass.TableDataHeader('Permit #', 'left');
      HTMLClass.TableDataHeader('Facility', 'left');
      HTMLClass.TableDataHeader('City', 'left');
      HTMLClass.TableDataHeader('State', 'left');
      htp.TableRowClose;
      LOOP
         DBMS_SQL.COLUMN_VALUE(v_cursor_id, 1, v_Permit_Num);
         DBMS_SQL.COLUMN_VALUE(v_cursor_id, 2, v_Facility);
         DBMS_SQL.COLUMN_VALUE(v_cursor_id, 3, v_City);
         DBMS_SQL.COLUMN_VALUE(v_cursor_id, 4, v_State);
         DBMS_SQL.COLUMN_VALUE(v_cursor_id, 5, v_KeyID);
         htp.TableRowOpen;
         htp.TableData(htf.anchor('NPDESTest.show_facility?v_KeyID=' || v_KeyID, v_Permit_Num));
         htp.TableData(RTRIM(v_Facility) || v_NBSP);
         htp.TableData(RTRIM(v_City)     || v_NBSP);
         htp.TableData(RTRIM(v_State)    || v_NBSP);
         htp.TableRowClose;
         IF DBMS_SQL.FETCH_ROWS(v_cursor_id) = 0 THEN
            EXIT;
         END IF;
      END LOOP;
      DBMS_SQL.CLOSE_CURSOR(v_cursor_id);
      htp.TableClose;
      htp.p('</center>');
      htp.Br;
      Footer('NPDESTest.find_facilities');
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        error_page(v_Error_101);
      WHEN OTHERS THEN
        If DBMS_SQL.IS_OPEN(v_cursor_id) THEN
           DBMS_SQL.CLOSE_CURSOR(v_cursor_id);
        End If;
        error_page(SQLERRM);
  END Retrieve_Facilities;

FWIW, the SP that generates the v_Query dynamic SQL string is:

  PROCEDURE find_facilities 
     (v_pmtnum VARCHAR2,
      v_facname VARCHAR2,
      v_city VARCHAR2,
      v_county VARCHAR2,
      v_sic VARCHAR2,
      v_btnSearch VARCHAR2)
   AS
      v_cSQL  VARCHAR2(800);
      v_Dummy CHAR(1);
   BEGIN
      If v_pmtnum is not null THEN
         v_cSQL := 'Permit_Num LIKE ~' || UPPER(v_pmtnum) || '~';
      End If;
      If v_facname is not null THEN
         If v_cSQL is not null THEN
            v_cSQL := v_cSQL || ' AND ';
         End If;
         v_cSQL := v_cSQL || 'UPPER(Facility) LIKE ~' || UPPER(v_facname) || '~';
      End If;
      If v_city is not null THEN
         If v_cSQL is not null THEN
            v_cSQL := v_cSQL || ' AND ';
         End If;
         v_cSQL := v_cSQL || 'UPPER(City) LIKE ~' || UPPER(v_city) || '~';
      End If;
      If v_county is not null THEN
         If v_cSQL is not null THEN
            v_cSQL := v_cSQL || ' AND ';
         End If;
         v_cSQL := v_cSQL || 'UPPER(County) LIKE ~' || UPPER(v_county) || '~';
      End If;
      If v_sic is not null THEN
         If v_cSQL is not null THEN
            v_cSQL := v_cSQL || ' AND ';
         End If;
         v_cSQL := v_cSQL || 'Std_Ind_Code LIKE ~' || v_sic || '~';
      End If;
      v_cSQL := REPLACE(v_cSQL, 'LIKE ~', 'LIKE ''%');
      v_cSQL := REPLACE(v_cSQL, '~', '%''');
      v_cSQL := 'SELECT Permit_Num, Facility, City, State, ' ||
                'TO_CHAR(KeyID) KeyID ' ||
                'FROM Inquiry_View WHERE ' || v_cSQL ||
                ' ORDER BY State, Permit_Num';
      Retrieve_Facilities(v_cSQL);
  END find_facilities;