Quseful #8: Execute DDL statements from a file

Steven Feuerstein — Thu, 20 Sep 2007 17:37:00 GMT

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

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)

BEGIN

l_file := UTL_FILE.fopen (dir_in, file_in, 'R');

LOOP

UTL_FILE.get_line (l_file

, lines_out (lines_out.COUNT + 1));

END LOOP;

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 directory 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.

Steven Feuerstein's Blog

Quseful #8: Execute DDL statements from a file