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:
- Store resultset inside a cursor.
- Enable dbms_output.
- Loop through the cursor.
- 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; /
No comments:
Post a Comment