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?

  • And you can explain the essence of the problem? For now, it looks like trying to scratch your right ear with your left heel. For example, in the function body you fill the collection, and you output the string variable answer , which is not used at all in the body of the function and is not filled with anything. - Dmitriy
  • one
    Another confusing is that you want to "frame tags" in this way. Maybe SQL / XML will save the father of Russian democracy? - Dmitriy

0