FOLD_STR function to break SQL at given lengths

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

    Requires Free Membership to View

    Login

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

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.

    Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.

    Back to top