Locating an error in a query run using Dynamic SQL

Well not everyone has managed to get to San Francisco. This morning I was debugging some of my code which was falling over trying to run a complex query though a refcursor. Unfortunately, in 9iR2 at least, Oracle's error message was a terse "Invalid argument" and didn't tell me the location of the problem.

So I threw together a quick procedure that uses the LAST_ERROR_POSITION function in DBMS_SQL to help me out. As you can see, its easy enough to plug into any routine and you can just put in the query and it will parse it, locate the position of the error and let you know. Obviously this example is trivial, but with an SQL with a couple of dozen lines it is pretty handy.

declare
 v_query varchar2(2000);
 rc_dummy sys_refcursor;
 PROCEDURE p_dynamic_sql_err_pos (p_query IN VARCHAR2) IS
   v_cursor        NUMBER;
   v_temp          VARCHAR2(32000) := p_query;
   v_tab           DBMS_SQL.VARCHAR2S;
   v_ind           NUMBER := 1;
 begin
   -- Pass in a query and it will be parsed and the location
   -- of any error will be reported
   v_cursor := dbms_sql.open_cursor;
   IF length(v_temp) > 2000 THEN
     WHILE nvl(length(v_temp),0) > 0 LOOP
       v_tab(v_ind) := substr(v_temp,1,250);
       v_temp := substr(v_temp,251);
       v_ind := v_ind + 1;
     END LOOP;
     dbms_sql.parse(v_cursor, v_tab, 1, v_ind - 1, FALSE, 1);
   ELSE
     dbms_sql.parse(v_cursor, v_temp, 1);
   END IF;
   dbms_sql.close_cursor(v_cursor);
 exception
   WHEN OTHERS THEN
     v_ind := dbms_sql.last_error_position;
     v_temp := substr(sqlerrm,1,100)||' before '||
                 substr(p_query,v_ind,60)||' ('||v_ind||') ';
     raise_application_error(-20001,v_temp);
 end;
begin
 v_query := 'select decode(1) from dual';
 begin
   open rc_dummy for v_query;
 exception when others then
   p_dynamic_sql_err_pos(v_query);
 end;
 close rc_dummy;
end;