Dynamic SQL

Contact Us or call 1-877-932-8228
Dynamic SQL

Dynamic SQL

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.

Code Sample:

Data-Retrieval-Techniques/Demos/dynamic_sql_cursor.sql
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;

Code Explanation

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.

Next