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.
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;
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; /
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