DBMS_SQL obsession

Just a tip on running dynamic SQL within Oracle. There seems to be a lot of people still using the old syntax via DBMS_SQL. For example, to count the records in a table, we might have (in the past) used something like:

declare
  c integer;
  s varchar2(80) := 'select count(*) from my_table';
  r number;
  d number;
begin
    c := dbms_sql.open_cursor;
    dbms_sql.parse(c, s, dbms_sql.native);
    dbms_sql.define_column(c, 1, r);
    d := dbms_sql.execute_and_fetch(c);
    dbms_sql.column_value(c, 1, r);
    dbms_sql.close_cursor(c);
end;

Note that from 8i, it a easier (and more efficient) to use "native" dynamic SQL - the above example then looking like:

declare
  s varchar2(80) := 'select count(*) from my_table';
  r number;
begin
  execute immediate s into r;
end;

You probably only need to use DBMS_SQL if you have an unknown number of columns in the query (which should be very rare)