5 Replies Latest reply: Dec 10, 2008 8:57 AM by dmcghan RSS

    Insert a result of a select into CLOB

    666000
      Hi, i would like to insert the result of a select into a CLOB with a trigger.

      Whevner a number is saved my trigger searchs how often the number is used in several objects and now my problem i also would like to sabe the record-ID where the number is used.

      SELECT "ITEXT"
      FROM "CHECK_INR"
      WHERE "VERWENDUNG">1

      This select results 2 records how can i insert the result into a CLOB with my trigger?

      thanks ahead
        • 1. Re: Insert a result of a select into CLOB
          dmcghan
          Hello,

          What is your name?

          Do you mean into a column with CLOB as the type? Being that you're trying to insert information retrieved from multiple rows into one, how would you want it formatted? You would essentially loop through the results to build what you would eventually insert/update with.

          Regards,
          Dan

          http://danielmcghan.us
          http://sourceforge.net/projects/tapigen
          • 2. Re: Insert a result of a select into CLOB
            666000
            Hello, my name is steven :)

            You´re right i would like to fill a CLOB (type) with the result from multiple rows, serperated by ", "

            Regards,
            steven
            • 3. Re: Insert a result of a select into CLOB
              dmcghan
              Steven,

              Why did you settle on a CLOB datatype?

              Regards,
              Dan

              http://danielmcghan.us
              http://sourceforge.net/projects/tapigen
              • 4. Re: Insert a result of a select into CLOB
                666000
                Well, you´re right i could also use a VARCHAR type but i also don´t know how to insert the result of my query into this VARCHAR column ..


                greetings
                Steven
                • 5. Re: Insert a result of a select into CLOB
                  dmcghan
                  Steven,

                  This is really a question better suited for a different forum as it's not really ApEx related. Also something tells me you really want to do an UPDATE not an insert. However, here's a brief example...
                  DECLARE
                  
                     l_orders VARCHAR2(4000);
                  
                  BEGIN
                  
                     FOR x IN (
                        SELECT *
                        FROM my_orders_table
                     )
                     LOOP
                        l_orders := l_orders || ', ' || x.order_data_column;
                     END LOOP;
                  
                     l_orders := l_trim(l_orders, ', ');
                  
                     UPDATE some_table
                     SET some_column = l_orders
                     WHERE id = some_id;
                  
                  END;
                  That's the gist. Use VARCHAR2 unless you need CLOB.

                  Regards,
                  Dan

                  http://danielmcghan.us
                  http://sourceforge.net/projects/tapigen