JL Computer Consultancy

A simple wrapper for dynamic SQL (excluding select)

January 1999


A short while ago, whilst designing the infrastructure for a data warehousing system, I found that I needed to create a very large number of partition views (under Oracle version 7.3) which changed on a daily basis.

Since the list of tables in the views could be geneated mechanically the obvious strategy for dealing with this requirement was to populate a PL/SQL array, one item per table used in the view plus a top and tail, then pass the array to the DBMS_SQL package to execute.

To make things even simpler and enhance the interface available to the programmer I actually hid the dbms_sql call inside a little package of my own, which I reproduce below.

The package has two overloaded functions in it, one to execute a DDL defined by a PL/SQL array, and one to execute a DDL defined by a simple varchar2().

The functions have two parameters:- the text to be executed and a boolean which tells the package how to respond to an error occurring as the SQL is executed. If the SQL is allowed to fail (e.g. the procedure has been called to drop an index which may or may not exist) the procedure will silently pass the exception raised by Oracle back to the calling procedure to handle; if the SQL is not allowed to fail, then the procedure uses dbms_output to list the text that has been passed to it, trying to highlight the location in the text that caused the failure; the procedure then raises a special exception (defined in a 'global constants' package) that should be handled by the calling procedure in a fashion dictated by the in-house programmers' guide.

The functions pass back an integer which is zero for DDL, but the number of rows affected for insert, update, and delete statements.

Back to Main Index of Topics


The Package Declaration
rem
rem   Script:     c_ddl2_p.sql
rem   Author:     Jonathan Lewis
rem   Dated:      5-Jan-1999
rem   Purpose:    Create package to try to execute a DDL.
rem
rem   Procedures
rem   ----------
rem   execute_ddl       execute ddl.
rem
rem   There are two functions identical in usage which overload;
rem   one accepts a simple varchar2, the other accepts an array of
rem   strings (type dbms_sql.varchar2s)
rem
rem   The functions return the 'count' which is the normal return
rem   function of dbms_ddl, so can be used to call and check for
rem   effects.
rem
rem   Any DDL that an ID wishes to execute must be allowed to that
rem   ID as a directly granted privilege, NOT just as a role-granted
rem   privilege.
rem
rem   The input array for the array based version assumes the array
rem   starts at row 1 (not 0) and is packed, with no gaps so that 
rem   executing from lines 1 to array.count() is correct.
rem
rem   The user may be willing for the SQL to fail catastrophoically,
rem   this is indicated by setting the 'i_may_fail' flag to TRUE (the
rem   default is FALSE).
rem
rem   In this case, the error detection code is not invoked, and the
rem   exception is re-raised to the user for explicit handling.
rem
create or replace package jpl_ddl as
function execute_ddl ( 
            i_ddl_text  in    varchar2,
            i_may_fail  in    boolean default FALSE
) return number;
 
function execute_ddl ( 
            i_ddl_text  in    dbms_sql.varchar2s,
            i_may_fail  in    boolean default FALSE
) return number;
end jpl_ddl;
/
drop public synonym jpl_ddl;
create public synonym jpl_ddl for jpl_ddl;
grant execute on jpl_ddl to public;

Back to Main Index of Topics


The Package Body
rem
rem   Script:     c_ddl2_pb.sql
rem   Author:     Jonathan Lewis
rem   Dated:      5-Jan-1999
rem   Purpose:    Create package to try to execute a DDL.
rem
rem   Note: v_count returns the number of rows affected by
rem   most DML statements.
rem
rem   If an unexpected error occurs then the SQL is dumped in
rem   64-byte chunks, and a marker placed under the point where
rem   the error appeared to occur (according to 'last_error_position')
rem
create or replace package body jpl_ddl as
v_package_name      constant varchar2(32) := 'jpl_ddl';
function execute_ddl ( 
            i_ddl_text  in    varchar2,
            i_may_fail  in    boolean default false
) return number is
v_procedure_name    varchar2(64) := v_package_name || '.execute_ddl';
v_block_name        varchar2(30) := 'Main';
v_cursor    number;
v_count     number;
v_error_pos number := 0;
begin
      v_cursor := dbms_sql.open_cursor;
      dbms_sql.parse(
            c             => v_cursor,
            statement     => i_ddl_text,
            language_flag => dbms_sql.v7
      );
      v_count := dbms_sql.execute(v_cursor);
      dbms_sql.close_cursor(v_cursor);
      return v_count;
exception
      when others then
            if (dbms_sql.is_open(v_cursor)) then
                  v_error_pos := nvl(dbms_sql.last_error_position,0);
                  dbms_sql.close_cursor(v_cursor);
            end if;
            if (i_may_fail) then
                  raise;
            end if;
            dbms_output.put_line(sqlerrm);
            dbms_output.put_line(
                  'Raised in: ' || 
                  v_procedure_name || ' - ' || 
                  v_block_name
            );
            dbms_output.put_line(
                        'Possible parse/execute error at character ' ||
                        v_error_pos || 
                        ' marked by *****'
            );
            dbms_output.put_line('SQL text is');
            dbms_output.put_line('->');
            for v_ct in 0..trunc(length(i_ddl_text)/64) loop
                  dbms_output.put_line(substr(i_ddl_text,64*v_ct+1,64));
                  if (v_error_pos between v_ct * 64 + 1 
                                  and     v_ct * 64 + 65 
                  ) then
                        dbms_output.put_line(
                              lpad('>',
                                    v_error_pos - 64 * v_ct,
                                    '-'
                              ) || '*****'
                        );
                       end if;
            end loop;
            dbms_output.put_line('--');
            raise jpl_constants.flagged_error;
end execute_ddl;
function execute_ddl ( 
      i_ddl_text  in    dbms_sql.varchar2s,
      i_may_fail  in    boolean default false
) return number is
v_procedure_name    varchar2(30) := v_package_name || '.execute_ddl';
v_block_name  varchar2(30) := 'Main';
v_cursor    number;
v_count     number;
v_error_pos number := 0;
begin
      v_cursor := dbms_sql.open_cursor;
      dbms_sql.parse(
            c             => v_cursor,
            statement     => i_ddl_text,
            lb            => 1, 
            ub            => i_ddl_text.count,
            lfflg         => true,
            language_flag => dbms_sql.v7
      );
      v_count := dbms_sql.execute(v_cursor);
      dbms_sql.close_cursor(v_cursor);
      return v_count;
exception
      when others then
            if (dbms_sql.is_open(v_cursor)) then
                  v_error_pos := nvl(dbms_sql.last_error_position,0);
                  dbms_sql.close_cursor(v_cursor);
            end if;
            if (i_may_fail) then
                  raise;
            end if;
            dbms_output.put_line(sqlerrm);
            dbms_output.put_line(
                  'Raised in: ' || 
                  v_procedure_name || ' - ' || 
                  v_block_name
            );
            dbms_output.put_line(
                        'Possible parse/execute error at character ' ||
                        v_error_pos ||
                        ' marked by *****'
            );
            dbms_output.put_line('SQL text is');
            dbms_output.put_line('->');
            for v_ct in 1..i_ddl_text.count loop
                  dbms_output.put_line(i_ddl_text(v_ct));
                       if (v_error_pos between 1 
                                  and     length(i_ddl_text(v_ct))
                  ) then
                        dbms_output.put_line(
                              lpad('>',
                                    v_error_pos - 1,
                                    '-'
                              ) || '*****'
                        );
                  end if;
                  v_error_pos:=v_error_pos - length(i_ddl_text(v_ct));
            end loop;
            dbms_output.put_line('--');
            raise jpl_constants.flagged_error;
end execute_ddl;
end jpl_ddl;
/

Back to Main Index of Topics


Example of Use
Create a script
rem
rem   temp.sql
rem   Demo of JPL_DDL package
rem
set serveroutput on size 100000
declare
        v_ddl_text     dbms_sql.varchar2s;
        v_ddl_empty    dbms_sql.varchar2s;
        v_ddl_count    number;
begin
        v_ddl_text(1) := 'create or replace view X1 as';
        v_ddl_text(2) := 'select * from all_tab_columms';   --  spelling !!
        v_ddl_text(3) := 'where owner = user';
        v_ddl_text(4) := 'and table_name like ''T%''';
        v_ddl_count := jpl_ddl.execute_ddl(v_ddl_text);
        v_ddl_text := v_ddl_empty;
end;
/
Now run it
SQL> start temp
ORA-00942: table or view does not exist
Raised in: jpl_ddl.execute_ddl - Main
Possible parse/execute error at character 43 marked by *****
SQL text is
->
create or replace view X1 as
select * from all_tab_columms
------------->*****
where owner = user
and table_name like 'T%'
--
declare
*
ERROR at line 1: 
ORA-20001:  
ORA-06512: at "MAINDATA.JPL_DDL", line 110 
ORA-06512: at line 10 
ORA-00942: table or view does not exist 
 
SQL> spool off

Back to Main Index of Topics