Quick Tip: Slicing A Long PLSQL String Into Smaller Pieces

In Oracle 11g, the maximum size for a VARCHAR2 field in a table is 4,000 bytes. In PL/SQL your VARCHAR2 variables can be defined as up to 32,767 bytes. So, what if you i.e. have a log table where you would want to put all the content of your PL/SQL variable?

In this Quick Tip I will show how you can easily split a PL/SQL VARCHAR2 into smaller pieces, and still be able to store all the content into your table.

The Setup

For the sake of this Quick Tip I am only going to present the principle, and the way I am going to do that is to use Oracle’s DBMS_OUTPUT.PUT_LINE procedure as my “logging” procedure. You will need to replace this code with your own.

The Code

SQL> set serveroutput on
SQL>
SQL> DECLARE
  2  	TYPE typStringTab IS TABLE OF VARCHAR2(200) INDEX BY BINARY_INTEGER;
  3  	vglText		VARCHAR2(2000);
  4
  5
  6  	-- #### --------------------------------------------------------------
  7
  8
  9  	FUNCTION slice_string(pText IN VARCHAR2) RETURN typStringTab
 10  	IS
 11  	   	iMaxLength	INTEGER := 200;
 12      	tStringTab	typStringTab;
 13          iLength		INTEGER;
 14          vText		VARCHAR2(200);
 15          iIndex		INTEGER;
 16          iPos		INTEGER;
 17  	BEGIN
 18  		iLength := LENGTH(pText);
 19
 20  		IF (iLength <= iMaxLength) THEN
 21          	-- The string can be returned as-is
 22  			tStringTab(1) := pText;
 23  		ELSE
 24          	-- The string needs to be sliced into chunks of lengths of iMaxLength
 25              -- Each chunk will be inserted as a row in a PL/SQL Collection
 26  			iPos := 1;
 27  			iIndex := 0;
 28
 29  			-- Loop until we have chunked the whole string
 30  			WHILE iPos <= iLength LOOP
 31              	iIndex := iIndex + 1;
 32
 33  				vText := SUBSTR(pText, iPos, iMaxLength);
 34  				tStringTab(iIndex) := vText; /* Insert into collection */
 35                  iPos := iPos + iMaxLength;
 36              END LOOP;
 37  		END IF;
 38
 39  		RETURN tStringTab;
 40  	END slice_string;
 41
 42
 43  	-- #### --------------------------------------------------------------
 44
 45
 46  	PROCEDURE put_log(pText IN VARCHAR2)
 47  	IS
 48  		tStringTab	typStringTab;
 49  	BEGIN
 50      	tStringTab := slice_string(pText);
 51
 52  		-- Loop through slices returned as a PL/SQL collection by slice_string function
 53          FOR indx IN tStringTab.FIRST..tStringTab.LAST LOOP
 54              --	Exchange the line below with your code piece
 55              --	that inserts your log text to your log table
 56  			DBMS_OUTPUT.PUT_LINE('Part # ' || indx || ': [' || tStringTab(indx) || ']');
 57          END LOOP;
 58
 59          tStringTab.DELETE; /* Clean up memory used by collection */
 60  	END put_log;
 61
 62
 63  	-- #### --------------------------------------------------------------
 64
 65
 66  BEGIN
 67  	-- This string is just split like this because of visuality in the example
 68  	vglText := 'Nulla facilisi. Quisque id lectus vel nulla semper suscipit. Cras ornare mi in odio pretium ';
 69      vglText := vglText || 'molestie. Vivamus egestas egestas placerat. Vivamus nec bibendum tortor. Maecenas nec ';
 70      vglText := vglText || 'elit sapien. Donec facilisis turpis quam. In in pharetra tortor. Donec eu diam ligula, ';
 71      vglText := vglText || 'sed tristique odio. Etiam magna enim, ultrices nec consequat sit amet, accumsan in arcu. ';
 72      vglText := vglText || 'Vivamus eu metus a nulla eleifend adipiscing. Aenean tincidunt placerat sagittis. Fusce ';
 73      vglText := vglText || 'non nibh at sapien scelerisque ultrices eget vel nisi. Proin tempor augue non odio porta ';
 74      vglText := vglText || 'in dignissim augue congue.';
 75
 76  	put_log(vglText);
 77  END;
 78  /

Part # 1: [Nulla facilisi. Quisque id lectus vel nulla semper suscipit. Cras ornare mi in odio pretium molestie. Vivamus egestas egestas placerat. Vivamus nec bibendum tortor. Maecenas nec elit sapien. Donec fac]
Part # 2: [ilisis turpis quam. In in pharetra tortor. Donec eu diam ligula, sed tristique odio. Etiam magna enim, ultrices nec consequat sit amet, accumsan in arcu. Vivamus eu metus a nulla eleifend adipiscing. ]
Part # 3: [Aenean tincidunt placerat sagittis. Fusce non nibh at sapien scelerisque ultrices eget vel nisi. Proin tempor augue non odio porta in dignissim augue congue.]

PL/SQL procedure successfully completed

Conclusion

That’s all folks!  As you can see, this is one way to split a long PL/SQL string into smaller pieces, but there are most likely many other different ways to do this.

Hope this was helpful for you.

If  you liked this Quick Tip – please share it with others. Or, if you have comments or questions – use the comment area below the post, or use our contact form.