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 first published in July 2002
This Content Component encountered an error

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close