Bind variables and cursor parameters give a large amount of flexibility with modifying queries using weak cursors. They do not allow you to fundamentally alter the structure of a query though. These limitations can be overcome by constructing SQL in strings and using weak cursors to reference the resulting query, as illustrated in this example.
declare crs SYS_REFCURSOR; v_sql varchar2(200) := 'select <expression> from employees where salary > :amount'; v_string_data varchar2(4000); begin v_sql := replace(v_sql, '<expression>', 'last_name||'' ''||first_name'); /* v_sql := replace(v_sql, '<expression>', 'employee_id||'';''||last_name||'';''||first_name||'';''||salary'); */ open crs for v_sql using 10000; loop fetch crs into v_string_data; exit when crs%notfound; dbms_output.put_line(v_string_data); end loop; close crs; end;
The commented line contains a significantly different query (one that returns a different number of columns separated by semicolons). Run the block as written and them uncomment the commented line to see the difference. This example uses queries against the same table, but the only restriction is that the cursor reference a valid SQL statement and that the value(s) retrieved correspond to a record or variable that can hold each returned result.