Re: Populating Long Columns

   The following procedure will break off the line at the first

       carriage return. If there is no carriage return, the procedure will
       start at the Nth character, where N is the line length, and search
       backword for the beginning of a word. If the word is longer than
       the line, then the word will be broken at the Nth character.
       Otherwise, the line will be broken at the end of the last word to
       fit completely into the N character limit.


       1) ASSIGNING TO MULTIPLE FIELDS IN THE SAME RECORD.

       This procedure takes the lines broken off from the long field and
       assigns them to fields within the same record, all with the same
       name except for the last character. The fields below are named
       TARGET1, TARGET2, TARGET3 and so on. The field of type long is
       name LONGFIELD, and the block is named BLOCK. These names should
       all be changed to the actual field and block names in the form.


          /*The two arguments passed to the procedure are MAXLEN, which
            is the maximum length a single segment can be, in characters,
            and MAXLINE, the maximum number of segments to make. */

            PROCEDURE BREAKUP (MAXLEN IN NUMBER,MAXLINE IN NUMBER) IS
                 B     NUMBER(6) := 1;     /*beginning of the line */
                 E     NUMBER(6);          /*end of the line       */
                 FIELD NUMBER(6) := 1;     /*number of segments done */
            BEGIN
             /*keep looping while we have not reached the end of the
               string, or exceeded the maximum number of segments to
               break off */
             WHILE (B <LENGTH(:BLOCK.LONGFIELD) AND FIELD<=MAXLINE) LOOP


                /*assign the end to be at the next carriage return*/


                E := INSTR(:BLOCK.LONGFIELD,CHR(10),B);


                /*if it is 0, then there is no more carriage returns,


                  so set the end to be the end of the long field*/
                IF (E=0) THEN
                   E := LENGTH(:BLOCK.LONGFIELD) +1;
                END IF;


                /*if the length of this segment is more than MAXLEN,


                  make it MAXLEN and set it to LASTWORD to cut if off
                  at a word*/
                IF (E-B > MAXLEN+1) THEN
                   E := MAXLEN+B-1;
                   LASTWORD(B,E);  /*this will reset the value of E*/
                END IF;


                /*copy the segment from B to E into the target field */


                COPY(SUBSTR(:BLOCK.LONGFIELD,B,E-B),
                     'BLOCK.TARGET'||TO_CHAR(FIELD));


                /*reset the B to the beginning of the next segment */


                B := E+1;
                FIELD := FIELD+1;
             END LOOP;
           END;


            /* This procedure just starts at a point in a string and
               searches back to find the beginning of the last word.
               It takes the beginning of the segment B and the end of
               the segment E as arguments */

            PROCEDURE LASTWORD(B IN NUMBER,E IN OUT NUMBER) IS
               W     NUMBER(6) := 0;         /*length of the last word*/
            BEGIN
               /* search backward from the end of the string looking for
                  a space */
               WHILE SUBSTR(:BLOCK.LONGFIELD,E-W,1) != ' ' AND E-W>B LOOP
                  W := W + 1;
               END LOOP;
               /* if the word length is not the length of the segment,
                  make the new end be the old end minus the length of
                  the word, W. If not then do nothing, and the segment
                  will be ended at E */
               IF E-W!=B THEN
                  E := E-W;
               END IF;
            END;

       The above code assigns the broken up long field into fields on
       the same record, named all the same, but ending in number,
       TARGET1, TARGET2, TARGET3... The Maxline gives a maximum number
       of segments to break off, since there will be a maximum number of
       fields to assign to.

       Advantages of Same Record method:
           o Can be called from any trigger
           o Can display multiple LONG fields in a block
       Disadvantages
           o Must have a preset maximum number of lines
           o No scrolling of lines off the screen


       2) ASSIGNING TO MULTIPLE RECORDS IN THE BLOCK

       The above example assigns values to multiple fields in a record,
       but it only takes a simple alteration to the code to assign the
       values to a different records in a multi-record block instead.
       In this example, the long field would be written to a block
       called DISPLAYBLOCK which has one field called TARGET.

       Make the following changes to the BREAKUP main procedure.

       Change the:

            COPY(SUBSTR(:BLOCK.LONGFIELD,B,E-B),
                 'BLOCK.TARGET'||TO_CHAR(FIELD));
       to
            COPY(SUBSTR(:BLOCK.LONGFIELD,B,E-B),'DISPLAYBLOCK.TARGET');
            DOWN;

       Add the following code just after the BEGIN:

           :GLOBAL.CURFIELD := :SYSTEM.CURSOR_FIELD;
           GO_BLOCK('DISPLAYBLOCK');
           FIRST_RECORD;

       And finally add the following code just before the END:

           FIRST_RECORD;
           GO_FIELD(:GLOBAL.CURFIELD);


       Advantages of Multi Record method:
           o There is no need to a maximum number of lines preset.
           o Can scroll text off the screen
       Disadvantages:
           o Must be called from a key-trigger or on-new-field-instance
           o Can only display one LONG field in a block at a time.

"The Views expressed here are my own and not necessarily those of Oracle Corporation"

Venkatesh Kumar <vkumar_at_fundu.com> wrote in message news:379A951D.ABDA4C11_at_fundu.com...
> Hi guys,
> We are trying to figure out a way to populate the Long columns in a table
> using PL/Sql. We have a Web application with a Text area field. The data in the
> Text area field is to be stored in a long column. However PL/Sl puts limit on
> the size of a variable ( I think 2000K). I would like to know if there any other
> solutions to this issue or if any any one has tried other methods using Pl/Sql.

>
> Thanks
> Venkatesh
>