Oracle DBMS_OUTPUT and PostgreSQL RAISE - Oracle to Aurora PostgreSQL Migration Playbook
Oracle usagePostgreSQL usageSummary
Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China (PDF).

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 Amazon SCT / Amazon DMS automation level Amazon SCT action code index Key differences

Three star feature compatibility

No automation

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.