Output table contents in PL/SQL stored procedure

With Oracle stored procedures the database developer can display output to the SQL client. This is very handy when you want to use a stored procedure to compute certain logic and display the output directly to the user.

Let's take a simple and common scenario where you want to output the result of a query which joins two tables from a stored procedure. The basic steps are the following:

1. Store resultset inside a cursor.
2. Enable dbms_output.
3. Loop through the cursor.
4. Use dbms_output to output each record from cursor to screen.

As you can see, the key functionality of the output is possible through the use of the DBMS_OUTPUT package. The following example procedure illustrates such usage:

CREATE OR REPLACE PROCEDURE get_category_id (
bc_text varchar2
) IS
 
CURSOR bc_cur 
   IS
        select b.betcategory_id b_id, l.langdataedit_text l_txt
        from betcategory b
        left join langdataedit l
        on b.betcategory_description = l.langdataedit_id
        where l.lang_id='eng' and lower(l.langdataedit_text) like '%' || lower(bc_text) || '%';
        
 BEGIN
 
 DBMS_OUTPUT.ENABLE (buffer_size => NULL);
   
 FOR bc_rec IN bc_cur LOOP
      DBMS_OUTPUT.PUT_LINE 
         (bc_rec.b_id ||' --> '|| bc_rec.l_txt);
   END LOOP;
 
END get_category_id;
/