Logging Dynamic SQL

Dynamic SQL is a wonderful feature with a lot of flexibility. Unfortunately, it is not trivial to see the executed SQL statements. A simple trick helps to test and debug PL/SQL code with Dynamic SQL.

There are many situations in an Oracle Data Warehouse where Dynamic SQL (EXECUTE IMMEDIATE) can be helpful. It is often used to create tables or views for data transformation, to dynamically generate INSERT or MERGE statements based on the data dictionary or on metadata tables, for partition exchange or partition maintenance operations and much more. The following procedure is a simple example for the usage of Dynamic SQL. It creates and loads a stage table based on the data dictionary information of a source table. If the stage table already exists, it is dropped at the beginning.

CREATE OR REPLACE
PROCEDURE create_stage (p_source_table IN VARCHAR2)
IS
v_stage_table VARCHAR2(30) := 'STG_'||p_source_table;
v_column_list VARCHAR2(32767);
v_sql VARCHAR2(32767);
BEGIN
-- drop previous created stage table (if exists)
BEGIN
v_sql := 'DROP TABLE '||v_stage_table||' PURGE';
EXECUTE IMMEDIATE v_sql;
EXCEPTION
WHEN OTHERS THEN NULL; -- ignore errors
END;
-- get column list from source table
SELECT listagg(column_name, ',') WITHIN GROUP (ORDER BY column_id)
INTO v_column_list
FROM user_tab_columns
WHERE table_name = p_source_table;
-- create new stage table
v_sql := 'CREATE TABLE '||v_stage_table||' AS '||
'SELECT '||v_column_list||' FROM '||p_source_table;
EXECUTE IMMEDIATE v_sql;
END create_stage;

For more complex PL/SQL procedures or packages, it is sometimes hard to understand what SQL statement is composed in the PL/SQL code and then executed with Dynamic SQL. A little enhancement in your PL/SQL library helps to make debugging and testing such code much easier. Instead of using the EXECUTE IMMEDIATE statement directly, use a utility procedure. Within this procedure, every SQL statement is written to a log table before it is executed. In case of an error, the error message can be logged as well. Because the generated SQL statements can be large, for example for long attribute lists, it is recommended to use a CLOB column in the log table.

CREATE TABLE log_table
(log_id NUMBER GENERATED ALWAYS AS IDENTITY
,log_date DATE DEFAULT SYSDATE
,module VARCHAR2(100)
,severity VARCHAR2(10)
,log_message CLOB);

If you use a central log package or a standard PL/SQL library for logging, you can use it for logging the generated SQL statements. For the example here, this simple log procedure is sufficient:

CREATE OR REPLACE
PROCEDURE write_log (p_module IN VARCHAR2
,p_severity IN VARCHAR2
,p_message IN CLOB)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO log_table(module, severity, log_message)
VALUES(p_module, p_severity, p_message);
COMMIT;
END write_log;

The utility procedure execute_sql in our example writes the SQL statement to the log table before it is executed. An additional optional parameter allows to ignore errors. This can be helpful for statements that are allowed to fail during the load jobs, typically DROP statements if the object does not exist yet.

CREATE OR REPLACE
PROCEDURE execute_sql (p_sql IN VARCHAR2
,p_ignore_errors IN BOOLEAN := FALSE)
IS
v_procedure_name VARCHAR2(30) := $$PLSQL_UNIT;
BEGIN
write_log (v_procedure_name, 'DEBUG', p_sql);
EXECUTE IMMEDIATE p_sql;
EXCEPTION
WHEN OTHERS THEN
IF NOT p_ignore_errors THEN
write_log (v_procedure_name, 'ERROR', SQLERRM);
RAISE;
END IF;
END execute_sql;

Now we can rewrite our procedure create_stage. Instead of an EXECUTE IMMEDIATE, we can call the procedure execute_sql. For the DROP command at the beginning, no nested PL/SQL block with an exception handler is used anymore because of the parameter p_ignore_errors.

CREATE OR REPLACE
PROCEDURE create_stage (p_source_table IN VARCHAR2)
IS
v_stage_table VARCHAR2(30) := 'STG_'||p_source_table;
v_column_list VARCHAR2(32767);
v_sql VARCHAR2(32767);
BEGIN
-- drop existing stage table (if exists)
v_sql := 'DROP TABLE '||v_stage_table||' PURGE';
execute_sql (v_sql, p_ignore_errors => TRUE);
-- get column list from source table
SELECT listagg(column_name, ',') WITHIN GROUP (ORDER BY column_id)
INTO v_column_list
FROM user_tab_columns
WHERE table_name = p_source_table;
-- create new stage table
v_sql := 'CREATE TABLE '||v_stage_table||' AS '||
'SELECT '||v_column_list||' FROM '||p_source_table;
execute_sql (v_sql);
END create_stage;

Finally, we run the procedure in SQL Developer and check the executed SQL statements in the log table:

SQL Developer 1

Each SQL statement is written on one line. This is not very easy to read. But in SQL Developer, this is not a real issue. We just copy the statement and paste it to the worksheet. With SQL Formatter (context menu command “Format”, or Ctrl-F7), the Dynamic SQL statement can be formatted in an easy to read form:

SQL Developer 2

Advertisements

2 thoughts on “Logging Dynamic SQL

  1. Štefan, you are right – a COMMIT within the execute_sql procedure is a bad idea. That’s the reason why I usually use a central logging procedure or package in real projects. I will adapt the example in the blog post to avoid confusion. Thank’s for the hint.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s