Thursday, July 31, 2014

Word Wrap in Excel based Oracle Reports

Use the following oracle function to use the word wrap functionality in Oracle excel based reports.

 Create or Replace FUNCTION word_wrap (
      p_textin         IN   VARCHAR2 DEFAULT NULL,
      p_rmargin      IN   INTEGER DEFAULT 78,
      p_pad             IN   VARCHAR2 DEFAULT ' ',
      p_worddelim  IN   VARCHAR2 DEFAULT ' ',
      p_just             IN   VARCHAR2 DEFAULT 'LEFT'
   )
      RETURN VARCHAR2
/*=======================================================================
OBJECT NAME: Word_wrap
OBJECT TYPE: PUBLIC FUNCTION
DESCRIPTION: PROCEDURE USED TO WRAP THE STRING
PARAMETERS :
==========
NAME              TYPE     DESCRIPTION
----------------- -------- ---------------------------------------------
P_TEXTIN          IN      TEXT TO FORMAT - UP TO 32K
P_RMARGIN      IN      WRAP AT WHAT CCOL POSITION
P_PAD                IN      PAD THE TEXT AFTER WRAPPING WITH WHAT DEFAULT TO SPACE
P_WORDDELIM IN      WORDS DELIMEETED BY WHAT? DEFAULTS TO SPACE
P_JUST               IN      JUSTIFY HOW? LEFT,RIGHT,CENTER

RETURNS: L_TEXTOUT (WRAPPED TEXT)
=====================================================================*/
   IS
      l_textout      VARCHAR2 (32000);
      l_line         VARCHAR2 (32000);
      l_subline      VARCHAR2 (32000);
      l_sublinelen   INTEGER;
      l_nl           CHAR (1)         := CHR (10);
      l_sp           CHAR (1)         := CHR (32);
      l_textlen      INTEGER          := LENGTH (p_textin);
      l_just         CHAR (1)         := UPPER (SUBSTR (p_just, 1, 1));
      l_toffset      INTEGER          := 1;
      l_doffset      INTEGER          := 0;
      l_pad          VARCHAR2 (32000) := LPAD (l_sp, p_rmargin, l_sp);
   BEGIN
      LOOP
         EXIT WHEN l_toffset > l_textlen;
         l_line := SUBSTR (p_textin || l_pad, l_toffset, p_rmargin);
         l_doffset :=
            LEAST (REPLACE (TO_CHAR (INSTR (l_line, p_worddelim, -1), '00000'),
                            '00000',
                            '99999'
                           ),
                   REPLACE (TO_CHAR (INSTR (l_line, l_nl, -1), '00000'),
                            '00000',
                            '99999'
                           )
                  );
         l_subline := LTRIM (RTRIM (SUBSTR (l_line, 1, l_doffset - 1)));
         l_sublinelen := LENGTH (l_subline);
         l_toffset := l_toffset + l_doffset;

/******************************************************************************
CODE FOR JUSTIFICATION. BY DEFAULT THE JUSTIFICATION IS SET TO LEFT
*******************************************************************************/
         IF l_just = 'L'
         THEN
            l_textout :=
                  l_textout
               || l_subline
               || LPAD (p_pad, p_rmargin - l_sublinelen, p_pad)
               || l_nl;
         ELSIF l_just = 'R'
         THEN
            l_textout :=
                  l_textout
               || LPAD (p_pad, p_rmargin - l_sublinelen, p_pad)
               || l_subline
               || l_nl;
         ELSIF l_just = 'C'
         THEN
            l_textout :=
                  l_textout
               || LPAD (p_pad, (p_rmargin - l_sublinelen) / 2, p_pad)
               || l_subline
               || l_nl;
         END IF;
      END LOOP;

      RETURN l_textout;
   END word_wrap;


------------------------------------------------------------------------

Example:

select word_wrap ('Vendor Name', 10) from dual

No comments: