Executing a Dynamic SQL Statement into a Sys_Refcursor

How to create dynamic sql for with sys_refcursor in oracle

I'm not sure why you're bothering with the with clause, it's simpler without a CTE; you just need to identify which table the city column is in:

function myfunc(p_city IN VARCHAR2,
  p_order IN VARCHAR2)
RETURN SYS_REFCURSOR IS
  v_result          SYS_REFCURSOR;
begin
  OPEN v_result FOR
    'select * from tableA ta
     inner join tableB tb on tb.some_col = ta.some_col
     where :p_city is null or LOWER(ta.city) like ''%''||:p_city||''%''
     order by ' || p_order || ' asc'
     using p_city, p_city;

  return v_result;
end myfunc;
/

I've guessed it's table A, just change the alias if it's the other one. You also need to specify the join condition between the two tables. (Also noticed I added a space before asc to stop that being concatenated into the order-by string).

This compiles without errors; when run I get ORA-00942: table or view does not exist which is reasonable. If I create dummy data:

create table tablea (some_col number, city varchar2(30));
create table tableb (some_col number);

insert into tablea values (1, 'London');
insert into tablea values (2, 'Londonderry');
insert into tablea values (3, 'East London');
insert into tablea values (4, 'New York');

insert into tableb values (1);
insert into tableb values (2);
insert into tableb values (3);
insert into tableb values (4);

then calling it gets:

select myfunc('lond', 'city') from dual;

  SOME_COL CITY                             SOME_COL
---------- ------------------------------ ----------
         3 East London                             3
         1 London                                  1
         2 Londonderry                             2

If you really want to stick with the CTE for some reason then (as @boneist said) that needs to be part of the dynamic statement:

  OPEN v_result FOR
    'with all_prb as (
       select * from tableA ta
       inner join tableB tb on tb.some_col = ta.some_col
     )
     select * from all_prb ff
     where :p_city is null or LOWER(ff.city) like ''%''||:p_city||''%''
     order by ' || p_order || ' asc'
     using p_city, p_city;

How to execute dynamic sql into cursor in Oracle?

You have an infinite loop. That means you're calling dbms_output.put_line forever - or until it runs out of buffer space, and throws that exception.

BEGIN
  MY_PROC(type1, rc);
  -- fetch first row from result set
  fetch rc into row;
  -- check if last fetch found something - always true
  while (rc%found) loop
    dbms_output.put_line(row);
  end loop;
  close rc;
end;

Every time around the loop you're checking the result of that first fetch, which stays true (assuming there is any data). You need to fetch each time round the loop:

BEGIN
  MY_PROC(type1, rc);
  -- fetch first row from result set
  fetch rc into row;
  -- check if last fetch found something
  while (rc%found) loop
    dbms_output.put_line(row);
    -- fetch next row from result set
    fetch rc into row;
  end loop;
  close rc;
end;

Or perhaps more commonly, only fetch inside the loop, and stop when nothing is found, using %notfound rather than %found:

BEGIN
  MY_PROC(type1, rc);
  loop
    -- fetch row from result set
    fetch rc into row;
    -- check if last fetch found something
    exit when rc%notfound;
    dbms_output.put_line(row);
  end loop;
  close rc;
end;

---

Not related to your current issue, but the predicate WHERE LINIA_PROD=NULL is never true; null isn't equal to (or not equal to) anything else. You need to use WHERE LINIA_PROD IS NULL instead.

How To: create a dynamic SQL statement, using either CURSOR or REFCURSOR?

1. You didn't replace <criteriafield#> and value with actual field name and value, so it cannot look like you wish.
2. You didn't call for EXECUTE IMMEDIATE so it was not fired and nothing happens.
3. You can avoid PL/SQL at all (your function for datatype can also be avoided) and buid all you need via single SQL statement with join on all_tab_cols. Then feed this to PL/SQL's EXECUTE IMMEDIATE and update that tables. If you'll build it as PL/SQL block with BEGIN...END and do EXECUTE IMMEDIATE on it, you will not need to split it into multiple statements and can create reproducible text for logging purposes (the logged code block can be executed via SQLPlus and debugged).

Also it is worth to mention that your dynamic SQL does not handle SQL injection.

UPD: Added a code without cursors and other row-by-row processing stuff. Finally you'll get a PL/SQL block that you need to pass to EXECUTE IMMEDIATE and update your tables.

Some points to mention:

• I didn't use quoted identifiers because they will make your life a nightmare in one day. So all the content of modification table tblMODIFY_TEST need an update to uppercase.
• Don't play with regional formatting of dates, timestamps and numbers, use ISO format, because it is deterministic and can be read by anybody without guessing month position and group separator.
• Implement some checks on values to avoid SQL injections.

Your data is a good example of dynamic SQL issues, because it already contains invalid column names.

with unp as (
  select
    /*Turn criteria to rows, because in one day you can need more than 5*/
    mdfy_rank,
    tbl_name,
    fld_name,
    fld_value,
    criterianum,
    criteriafield,
    criteriavalue
  from tblmodify_test
  unpivot(
    (criteriafield, criteriavalue) for criterianum in (
      (criteriafld1, criteriavalue1) as 1,
      (criteriafld2, criteriavalue2) as 2,
      (criteriafld3, criteriavalue3) as 3,
      (criteriafld4, criteriavalue4) as 4,
      (criteriafld5, criteriavalue5) as 5
    )
  ) p
  where valid_dte >= data_dte or valid_dte is null
)
, prep as (
  select
    unp.tbl_name,
    unp.mdfy_rank,
    unp.fld_name,
    /*Build formatted field value*/
    case
      when atc_val.data_type like '%CHAR%'
      then '''' || fld_value || ''''
      when atc_val.data_type = 'DATE'
      then 'to_date(''' || fld_value || ''', ''dd.mm.yyyy'')'
      when atc_val.data_type in ('NUMBER', 'FLOAT', 'BINARY_FLOAT', 'BINARY_DOUBLE')
      then fld_value
    end as fld_value,
    atc_val.data_type as value_datatype,
    /*Build where clause*/
    listagg(criteriafield || ' = :wb' || criterianum, ' and ') within group (order by criterianum) as where_clause,
    /*Build formatted bind variables to get rid of infinite sequences of quotes*/
    listagg(
        /*Add quotes*/
        case
          when atc_where.data_type like '%CHAR%'
          then '''' || criteriavalue || ''''
          when atc_where.data_type = 'DATE'
          then 'to_date(''' || criteriavalue || ''', ''dd.mm.yyyy'')'
          when atc_where.data_type in ('NUMBER', 'FLOAT', 'BINARY_FLOAT', 'BINARY_DOUBLE')
          then criteriavalue
        end
      , ', ') within group (order by criterianum) as where_bind_vars
  from unp
    left join all_tab_cols atc_val
      on upper(unp.tbl_name) = atc_val.table_name
        and upper(unp.fld_name) = atc_val.column_name
    /*Check validity of columns and tables*/
    left join all_tab_cols atc_where
      on upper(unp.tbl_name) = atc_where.table_name
        and upper(unp.criteriafield) = atc_where.column_name
  group by
    tbl_name,
    mdfy_rank,
    fld_name,
    fld_value,
    atc_val.data_type
  order by
    tbl_name,
    mdfy_rank
)
select
  /*
  tbl_name,
  mdfy_rank,
  fld_name,
  fld_value,
  */
  /*Build final PL/SQL block of updates*/
  'BEGIN' || chr(10) || chr(9) ||
  listagg(
    'execute immediate ''update ' || upper(tbl_name)
      || ' set ' || fld_name || ' = :vb where '
      || where_clause || ''' using '
      || fld_value || ', ' || where_bind_vars || ';'
    , chr(10) || chr(9)
  ) within group(order by mdfy_rank)
  || chr(10) || 'END;' as stmt
from prep

Oracle EXECUTE IMMEDIATE into a cursor

Assuming that your SQL is not longer than 32K (as @Tony Andrews hinted at), you should be able to use something like this:

declare
   SQL_Text varchar2(32760) := 'select * from dual'; --your query goes here
   cur sys_refcursor;
begin
   open cur for SQL_Text;
end;

When working with Ref Cursors, open-for can be used directly, instead of execute immediate.

---

Related Topics