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 232 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(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.
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;
/
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!DBMS_SQL
which allows the use of dynamic SQL to parse DML or DDL statements.
Posts
No comments:
Post a Comment