What's the point?
This utility will make it easy for you to read in the contents of DDL statements (like CREATE OR REPLACE PACKAGE) and execute them within Oracle.
Show me the code!
It's not a terribly long program, so I will include it right in this posting:
CREATE OR REPLACE PROCEDURE exec_ddl_from_file (
dir_in IN VARCHAR2
, file_in IN VARCHAR2
)
AUTHID CURRENT_USER
IS
PRAGMA AUTONOMOUS_TRANSACTION;
--
l_cur PLS_INTEGER := DBMS_SQL.open_cursor;
l_file UTL_FILE.file_type;
l_dummy PLS_INTEGER;
l_start PLS_INTEGER;
l_end PLS_INTEGER;
-- Use DBMS_SQL.varchar2s if Oracle version is earlier
-- than Oracle Database 10g Release 10.1.
l_lines DBMS_SQL.varchar2a; -- 32767 chars per line
--l_lines DBMS_SQL.varchar2s; -- 255 chars per line
PROCEDURE read_file (lines_out IN OUT DBMS_SQL.varchar2a)
IS
BEGIN
l_file := UTL_FILE.fopen (dir_in, file_in, 'R');
LOOP
UTL_FILE.get_line(l_file
, lines_out (lines_out.COUNT + 1));
ENDLOOP;
EXCEPTION
-- Reached end of file.
WHEN NO_DATA_FOUND
THEN
-- Strip off trailing /. It will cause compile problems.
IF RTRIM (lines_out (lines_out.LAST)) = '/'
THEN
lines_out.DELETE (lines_out.LAST);
END IF;
UTL_FILE.fclose (l_file);
END read_file;
BEGIN
read_file (l_lines);
l_start := 1;
WHILE (l_lines.COUNT > 0)
LOOP
-- get next set of lines up to / all by itself.
l_end := l_start;
WHILE (l_lines (l_end) <> '/')
LOOP
l_end := l_end + 1;
END LOOP;
DBMS_OUTPUT.put_line (
'parse from lines ' || l_start || ' to ' || l_end);
-- Do not include the / symbol.
DBMS_SQL.parse ( l_cur
, l_lines
, l_start
, l_end-1
, TRUE
, DBMS_SQL.native
);
l_dummy := DBMS_SQL.EXECUTE (l_cur);
--
-- You can even determine the type of statement executed
-- by calling the DBMS_SQL.last_sql_function_code function
-- immediately after you execute the statement. Check the
-- Oracle Call Interface Programmer's Guide for an explanation
-- of the codes returned.
DBMS_OUTPUT.put_line ( 'Type of statement executed: '
|| DBMS_SQL.last_sql_function_code ()
);
l_start := l_end + 1;
l_lines.DELETE (l_start, l_end);
END LOOP;
DBMS_SQL.close_cursor (l_cur);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (
'Compile from ' || file_in || ' failed!');
DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_stack);
DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_backtrace);
IF UTL_FILE.is_open (l_file)
THEN
UTL_FILE.fclose (l_file);
END IF;
IF DBMS_SQL.is_open (l_cur)
THEN
DBMS_SQL.close_cursor (l_cur);
END IF;
END exec_ddl_from_file;
/
GRANT EXECUTE ON exec_ddl_from_file TO PUBLIC
/
CREATE PUBLIC SYNONYM exec_ddl_from_file FOR exec_ddl_from_file
/
How do I use it?
Easy enough! Just pass it the location of the file and its name.
Remember, though, that this program uses UTL_FILE, so the location of the file must either be specified as a valid diretory in the database's UTL_FILE_DIR parametr, or you must specify a database directory name on which you have read authority.
Some things to keep in mind:
- The program is compiled as AUTHID CURRENT_USER, which means that the DDL statements will be executed within the schema that is currently connected, and not the schema that owns this program.
- It is an autonomous transaction, so the implicit commit caused by the DDL statement execution will not save any other outstanding changes in your session.
- You can have multiple statements in your file and they will all be executed – as long as there is a / character to terminate each statement.
- I read the contents of the file into a collection defined on the DBMS_SQL.VARCHAR2A type, which was introduced in Oracle Database 10g Release 1. If you are on an earlier version, use the DBMS_SQL.VARCHAR2S type instead.
- This program will only allow you to execute DDL statements, or DML statements that do not contain any placeholders for bind variables.