Oracle DBMS_OUTPUT and MySQL SELECT

Oracle DBMS_OUTPUT is a package that lets you send messages from stored procedures, functions, and anonymous blocks to a message buffer. MySQL SELECT is a statement used to retrieve data from one or more tables in a MySQL database. The following sections will provide details on using DBMS_OUTPUT in Oracle and SELECT statements in MySQL with AWS DMS.

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

MySQL usage

You can use SELECT to display output messages in Aurora MySQL.

Examples

delimiter //

CREATE PROCEDURE emp_counter (param1 INTEGER)
BEGIN
SELECT "" 'OUTPUT: Before count';
SELECT COUNT(*) INTO param1 FROM EMPS;
SELECT concat('Employees count: ', param1) as '';
SELECT "" 'OUTPUT: After count';
END//

delimiter ;
call simpleproc1(1);

OUTPUT: Before count
1 row in set (0.19 sec)

Employees count: 8
1 row in set (0.20 sec)

OUTPUT: After count
1 row in set (0.21 sec)

Query OK, 0 rows affected (0.22 sec)

For more information, see SELECT Statement in the MySQL documentation.