Oracle DBMS_OUTPUT and PostgreSQL RAISE

Oracle’s DBMS_OUTPUT and PostgreSQL’s RAISE are utilities that let you display status information and handle errors during the migration process.

Feature compatibility	AWS SCT / AWS DMS automation level	AWS SCT action code index	Key differences
		N/A	N/A

Oracle usage

The Oracle DBMS_OUTPUT package is typically used for debugging or for displaying output messages from PL/SQL procedures.

Examples

In the following example, DBMS_OUTPUT with PUT_LINE is used with a combination of bind variables to dynamically construct a string and print a notification to the screen from within an Oracle PL/SQL procedure. In order to display notifications on to the screen, you must configure the session with SET SERVEROUTPUT ON.

SET SERVEROUTPUT ON
DECLARE
CURSOR c1 IS
SELECT last_name, job_id FROM employees
WHERE REGEXP_LIKE (job_id, 'S[HT]_CLERK')
ORDER BY last_name;
v_lastname employees.last_name%TYPE; -- variable to store last_name
v_jobid employees.job_id%TYPE; -- variable to store job_id
BEGIN
OPEN c1;
LOOP -- Fetches 2 columns into variables
FETCH c1 INTO v_lastname, v_jobid;
DBMS_OUTPUT.PUT_LINE ('The employee id is:' || v_jobid || ' and his last name is:' ||
v_lastname);
EXIT WHEN c1%NOTFOUND;
END LOOP;
CLOSE c1;
END;

In addition to the output of information on the screen, the PUT and PUT_LINE procedures in the DBMS_OUTPUT package enable you to place information in a buffer that can be read later by another PL/SQL procedure or package. You can display the previously buffered information using the GET_LINE and GET_LINES procedures.

For more information, see DBMS_OUTPUT in the Oracle documentation.

PostgreSQL usage

You can use the PostgreSQL RAISE statement as an alternative to DBMS_OUTPUT. You can combine RAISE with several levels of severity including.

Severity	Usage
DEBUG1..DEBUG5	Provides successively-more-detailed information for use by developers.
INFO	Provides information implicitly requested by the user
NOTICE	Provides information that might be helpful to users
WARNING	Provides warnings of likely problems
ERROR	Reports an error that caused the current command to abort.
LOG	Reports information of interest to administrators, e.g., checkpoint activity.
FATAL	Reports an error that caused the current session to abort.
PANIC	Reports an error that caused all database sessions to abort.

Examples

Use RAISE DEBUG (where DEBUG is the configurable severity level) for similar functionality as Oracle DBMS_OUTPUT.PUT_LINE feature.

SET CLIENT_MIN_MESSAGES = 'debug';
-- Equivalent To Oracle SET SERVEROUTPUT ON

DO $$
BEGIN
RAISE DEBUG USING MESSAGE := 'hello world';
END $$;

DEBUG: hello world
DO

Use the client_min_messages parameter to control the level of message sent to the client. The default is NOTICE. Use the log_min_messages parameter to control which message levels are written to the server log. The default is WARNING.

SET CLIENT_MIN_MESSAGES = 'debug';

For more information, see Errors and Messages and When to Log in the PostgreSQL documentation.

Summary

Feature	Oracle	PostgreSQL
Disables message output.	DISABLE	Configure “client_min_message” or “log_min_message” for the desired results.
Enables message output.	ENABLE	Configure “client_min_message” or “log_min_message” for the desired results.
Retrieves one line from buffer.	GET_LINE	Consider storing messages in an array or temporary table so that you can retrieve them from another procedure or package.
Retrieves an array of lines from buffer.	GET_LINES	Consider storing messages in an array or temporary table so that you can retrieve them from another procedure or package.
Terminates a line created with PUT and places a partial line in the buffer.	PUT + NEW_LINE BEGIN DBMS_OUTPUT.PUT ('1,'); DBMS_OUTPUT.PUT('2,'); DBMS_OUTPUT.PUT('3,'); DBMS_OUTPUT.PUT('4'); DBMS_OUTPUT.NEW_LINE(); END; /	Store and concatenate the message string in a varchar variable before raising do $$ DECLARE message varchar :=''; begin message := concat(message,'1,'); message := concat(message,'2,'); message := concat(message,'3,'); message := concat(message,'4,'); RAISE NOTICE '%', message; END$$;
Places line in buffer	PUT_LINE	RAISE
Returns the number code of the most recent exception	SQLCODE + SQLERRM	SQLSTATE + SQLERRM
Returns the error message associated with its errornumber argument.	DECLARE Name employees.last_name%TYPE; BEGIN SELECT last_name INTO name FROM employees WHERE employee_id = -1; EXCEPTION WHEN OTHERS then DBMS_OUTPUT.PUT_LINE (CONCAT('Error code ', SQLCODE,': ',sqlerrm); END; /	do $$ declare Name employees%ROWTYPE; BEGIN SELECT last_name INTO name FROM employees WHERE employee_id = -1; EXCEPTION WHEN OTHERS then RAISE NOTICE 'Error code %: %', sqlstate, sqlerrm; end$$;

For more information, see PostgreSQL Error Codes in the PostgreSQL documentation.