There is a function that, in theory, should fill the collection with data from any table. The input is a query string and a row of columns framed with tags to be displayed. For example:
select f1('select * from a', '<#ID#>') from dual Here is the function itself:
CREATE OR REPLACE FUNCTION f1(QUERY IN VARCHAR2, VALUESES VARCHAR2) RETURN VARCHAR2 IS cur PLS_INTEGER := DBMS_SQL.OPEN_CURSOR; cols DBMS_SQL.DESC_TAB; ncols PLS_INTEGER; answer VARCHAR2(30000); parsing varchar2(30000); TYPE pol is table of number; pol1 pol; BEGIN DBMS_SQL.PARSE (cur, query, DBMS_SQL.NATIVE); DBMS_SQL.DESCRIBE_COLUMNS (cur, ncols, cols); FOR colind IN 1 .. ncols LOOP parsing := '<#'||cols (colind).col_name||'#>'; if parsing = regexp_substr(VALUESES, '<#[[:alpha:]]+#>') then pol1 := set(dbms_sql.column_value(cur, ncols, pol1)); end if; END LOOP; DBMS_SQL.CLOSE_CURSOR (cur); return answer; END; But nothing comes out. How to add data to the collection? Well, then how to pick up the data from there to return the answer?
answer, which is not used at all in the body of the function and is not filled with anything. - Dmitriy