I wrote a procedure a long time ago (put_long_line ) as a substitute for DBMS_OUTPUT.put_line, the latter which had a 255-byte line limit before 10g. Put_long_line handles strings up to 32K, breaking them up into multiple lines on whitespace, including any delimiters you choose to include (commas, for example).
Several years later, I needed some way to dynamically generate and compile a very large PL/SQL package. The Oracle-supplied package DBMS_SQL has an array form of parse() so that you can have a PL/SQL block up to 2
32 lines, each 256 bytes long (matching the exposed package type DBMS_SQL.varchar2s, an index-by array of varchar2(256)). So I took put_long_line and retrofitted it to format a 32K string and populate an index-by array for parsing, instead of buffering it for printing.
This code uses the sample HR schema that comes with Oracle.
CREATE OR REPLACE PROCEDURE str2arr(
p_source_string IN VARCHAR2 -- the source text string
-------------------------------------------------------------------------
-- p_output_array is the destination array, and is compatible with DBMS_SQL.parse
--
-- NOTE that DBMS_OUTPUT.put_line array has a 255 byte limit, but
-- ---- DBMS_SQL.parse array has a 256 char limit. We'll fix the line
-- limit at 255 for compatibility reasons.
-------------------------------------------------------------------------
,p_output_array IN OUT NOCOPY DBMS_SQL.varchar2s
-------------------------------------------------------------------------
-- p_array_element is the array element to start writing to
--
-- NOTE when the array is partially filled (provided you started with
-- ---- offset=0) you can use array.COUNT to pick up at the subsequent
-- element.
-------------------------------------------------------------------------
,p_array_element IN BINARY_INTEGER DEFAULT 0
-------------------------------------------------------------------------
-- p_line_length is the line limit, must be between 10 and 255 (the default)
-------------------------------------------------------------------------
,p_line_length IN NUMBER DEFAULT 255
-------------------------------------------------------------------------
-- p_whitespace is the set of delimiter chars to consider as whitespace
--
-- NOTE like put_long_line, actual whitespace (newline, space, tab) are
-- ---- consumed when breaking up a line on a whitespace char, but any
-- other char depicted as whitespace must actually be written.
-------------------------------------------------------------------------
,p_whitespace IN VARCHAR2 DEFAULT CHR(10)||CHR(9)||' ;,()=')
IS
---------------------------------------------------------------------------
-- Copy the contents of a string to an index-by array, in a manner similar
-- to put_long_line.
--
-- Lines are normally broken up on embedded newlines. If one is not found
-- before the requested end of line, the line is broken up on the last
-- whitespace character found (as identified by the set of delimiter
-- characters.)
---------------------------------------------------------------------------
c_newline CONSTANT VARCHAR2(1) := CHR(10); -- newline character (OS-independent)
v_array_element BINARY_INTEGER := p_array_element; -- used to increment array offset
v_start_pos INTEGER := 1 ; -- start of string to print
v_sub_end INTEGER ; -- end of substring to print
v_str_end INTEGER := LENGTH(p_source_string); -- end of string to print
v_newline_pos INTEGER ; -- point where newline found
v_string_length INTEGER := GREATEST(
LEAST(p_line_length ,255 )
, 10); -- 10 <= len <= 255!
BEGIN
IF (v_str_end <= v_string_length) THEN -- short enough to write in one chunk
p_output_array(v_array_element) := p_source_string;
ELSE -- must break up string
WHILE (v_start_pos <= v_str_end) LOOP
v_newline_pos := INSTR(
SUBSTR(p_source_string, v_start_pos, v_string_length)
, c_newline
)
+ v_start_pos - 1;
------------------------------------------------------------------------
IF (v_newline_pos >= v_start_pos) THEN -- found a newline to break on
p_output_array(v_array_element) := SUBSTR( p_source_string
, v_start_pos
, v_newline_pos-v_start_pos
);
v_array_element := v_array_element + 1; -- next array offset
v_start_pos := v_newline_pos + 1; -- skip past newline
ELSE -- no newline exists in chunk; look for whitespace
-- next chunk not EOS
v_sub_end := LEAST(v_start_pos+v_string_length-1, v_str_end);
---------------------------------------------------------------
IF (v_sub_end < v_str_end) THEN -- intermediate chunk
FOR i IN REVERSE v_start_pos .. v_sub_end LOOP
------------------------------------------------------------------
IF (INSTR( p_whitespace
, SUBSTR( p_source_string, i , 1)
) != 0) THEN
v_sub_end := i; -- found suitable breaking point
EXIT; -- break out of loop
END IF;
------------------------------------------------------------------
END LOOP; -- find breaking point
ELSE -- this is the last chunk
v_sub_end := v_sub_end + 1; -- point just past EOS
END IF; -- last chunk?
---------------------------------------------------------------
p_output_array(v_array_element) := SUBSTR( p_source_string
, v_start_pos
, v_sub_end-v_start_pos+1);
v_array_element := v_array_element + 1; -- next array offset
v_start_pos := v_sub_end + 1; -- next chunk
END IF; -- find newline to break on
------------------------------------------------------------------------
END LOOP; -- writing chunks
END IF; -- short enough or break up string?
END str2arr;
/
I added an offset parameter so that on multiple calls, I can pick up where I left off in the array and concatenate in new rows as needed.
So for example, I can generate a package containing collection types based on all the tables in the current schema:
Declare
v_array DBMS_SQL.varchar2s; -- index-by array to hold generated code
v_cursor integer; -- cursor handle
Begin
v_cursor := DBMS_SQL.open_cursor; -- DBMS_SQL cursors are reusable
--+------------------------------------------------------------------
--| Write the first line of the code to be generated to the array
--| passing the first 3 parameters, and defaulting on the line length
--| and the values For whitespace.
--+------------------------------------------------------------------
str2arr( 'create or replace package typs is ' --p_source_string
, v_array --p_output_array
, 0); --p_array_element
--+------------------------------------------------------------------
--| Write a line of code to the array For each table in User_Tables.
--| Again, default on the line length and whitespace values.
--+------------------------------------------------------------------
For rec in ( select lower(table_name) table_name
from user_tables
order by table_name)
Loop
str2arr( ' type typ_' || rec.table_name ||
' is table of ' || rec.table_name ||
'%rowtype;' --p_source_string
, v_array --p_output_array
, v_array.count); --p_array_element
End Loop;
--+------------------------------------------------------------------
--| Write the last line of the code to generate to the array.
--+------------------------------------------------------------------
str2arr( 'End typs;' --p_source_string
, v_array --p_output_array
, v_array.count); --p_array_element
--+------------------------------------------------------------------
--| Display contents of array
--+------------------------------------------------------------------
For i in 0..v_array.last Loop
dbms_output.put_line(v_array(i));
End Loop;
--+------------------------------------------------------------------
--| The act of parsing DDL creates the object
--+------------------------------------------------------------------
DBMS_SQL.parse( v_cursor -- the cursor in which to parse the statement
, v_array -- Sql statement to parse
, 0 -- lower bound for elements
, v_array.last -- upper bound for elements
, true -- line feed after each element
, DBMS_SQL.native -- normal behavior for the database
);
DBMS_SQL.close_cursor(v_cursor);
End;
/
This transformation of an old utility into a new one took 15 minutes. I leveraged it for an entirely new purpose. You can recycle old code!
Click here to read the Oracle documentation about
DBMS_SQL
which allows the use of dynamic SQL to parse DML or DDL statements.
Posts
