FOLD_STR function to break SQL at given lengths

This script creates a function that breaks/folds a SQL string into multiple lines of a given length.

This script is designed to create a function that breaks/folds a SQL string into multiple lines of a given length...

without any syntax errors. Lines are broken at a location that provides a syntatically correct SQL statement for direct cutting/pasting. This funciton has been tested on Oracle 8.1.7.3 Enterprise Edition.

                
create function fold_str (sql_text_in varchar2,line_length number) 
return varchar2 is 
str_length number; 
chr_pos    number; 
cur_line   number; 
sql_text_out varchar2(4000); 
begin 
        chr_pos         := 1; 
        sql_text_out    := ''; 
        cur_line        := 0; 

        select length(sql_text_in) into str_length from dual; 
        while str_length - chr_pos >= 0 LOOP 
           if substr(sql_text_in,chr_pos,1) = ' ' or 
                substr(sql_text_in,chr_pos,1) = ',' or 
              substr(sql_text_in,chr_pos,1) = ' ' then 
                if cur_line >= line_length then 
                        sql_text_out := sql_text_out||substr(sql_text_in,chr_pos,1)||chr(10); 
                        cur_line := 0; 
                else 
                        sql_text_out := sql_text_out||substr(sql_text_in,chr_pos,1);    
                end if; 
           else 
                sql_text_out := sql_text_out||substr(sql_text_in,chr_pos,1); 
                cur_line := cur_line + 1; 
          end if; 
        chr_pos := chr_pos + 1; 
        end loop; 
        return sql_text_out; 
end fold_str; 
/ 

Reader Feedback

Andy W. writes: Very nice and quite simple. I have included some suggestions for improvement.

--** create function fold_str (sql_text_in varchar2,line_length number)

--** Defaults are nice
create function fold_str (sql_text_in varchar2 default null,line_length number default 80)
return varchar2 is

--** pls_integer more efficient than number
str_length pls_integer := length(sql_text_in);
chr_pos    pls_integer := 1;
cur_line   pls_integer := 0;
sql_text_out varchar2(4000);
begin

--** select length(sql_text_in) into str_length from dual; (not necessary, initialized above)

        while str_length - chr_pos >= 0 LOOP
          --** if substr(sql_text_in,chr_pos,1) = ' ' or
          --**   substr(sql_text_in,chr_pos,1) = ',' or
          --** substr(sql_text_in,chr_pos,1) = ' ' then  (same as 2 lines back or some special character??)

            --** how about this instead?
             if substr(sql_text_in,chr_pos,1) in (' ',',',' ') then

                if cur_line >= line_length then
                        sql_text_out := sql_text_out||substr(sql_text_in,chr_pos,1)||chr(10);
                        cur_line := 0;
                else
                        sql_text_out := sql_text_out||substr(sql_text_in,chr_pos,1);
                end if;
             else
                sql_text_out := sql_text_out||substr(sql_text_in,chr_pos,1);
                cur_line := cur_line + 1;
          end if;
        chr_pos := chr_pos + 1;
        end loop;
        return sql_text_out;

  --** always put in exception handler, return null to signify error exception
    when others then -- possibly length sql_text_out > 4000
      return null;
end fold_str;

For More Information

  • Feedback: E-mail the editor with your thoughts about this tip.
  • More tips: Hundreds of free Oracle tips and scripts.
  • Tip contest: Have an Oracle tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
  • Ask the Experts: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
  • Forums: Ask your technical Oracle questions--or help out your peers by answering them--in our active forums.
  • Best Web Links: Oracle tips, tutorials, and scripts from around the Web.

This was last published in July 2002

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close