Parsing DBMS_OUTPUT
Testing with DBMS_OUTPUT.PUT_LINE
is always a bit of a problem when you have strings longer than 80 characters in length, which occurs more frequently with Oracle Database 12c. An example of managing output occurs when you want to print a string with embedded line breaks. My solution is the following parse_rows
procedure:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 | CREATE OR REPLACE PROCEDURE parse_rows ( pv_text VARCHAR2 ) IS /* Declare parsing indexes. */ lv_start NUMBER := 1; lv_end NUMBER := 1; lv_length NUMBER; BEGIN /* Assign an end value based on parsing line return or length. */ IF INSTR(pv_text,CHR(10),lv_start) = 0 THEN lv_end := LENGTH(pv_text) + 1; ELSE lv_end := INSTR(pv_text,CHR(10),lv_start); END IF; /* Assign a length value to the parsed string. */ lv_length := lv_end - lv_start; /* Print first line. */ DBMS_OUTPUT.put_line(SUBSTR(pv_text,lv_start,lv_length)); /* Print the rows of a multiple line string. */ WHILE (lv_end < LENGTH(pv_text)) LOOP /* Assign a new start value. */ lv_start := lv_end + 1; /* Assign a new end value. */ IF INSTR(pv_text,CHR(10),lv_start + 1) = 0 THEN lv_end := LENGTH(pv_text) + 1; ELSE lv_end := INSTR(pv_text,CHR(10),lv_start + 1); END IF; /* Assign a new length. */ lv_length := lv_end - lv_start; /* Print the individual rows. */ DBMS_OUTPUT.put_line(SUBSTR(pv_text,lv_start,lv_length)); END LOOP; END; / |
Here’s the example of a test program for the procedure:
1 2 3 4 5 6 7 | DECLARE /* Declare text. */ lv_text VARCHAR2(200) := 'This is too much'||CHR(10)||'information'||CHR(10)||'on one line.'; BEGIN parse_rows(lv_text); END; / |
It prints the following:
1 2 3 | This is too much information on one line. |
Hope this benefits those looking for quick solution.