Re: How to convert long to char?

From: Daniel Fink <danielwfink_at_yahoo.com>
Date: Thu, 29 Jun 2006 12:17:36 -0700 (PDT)
Message-ID: <20060629191736.37364.qmail@web37210.mail.mud.yahoo.com>

The technique I have used is to create a temporary table with a CLOB datatype, insert the LONG data into the CLOB, then you can manipulate it.

I also used the following code to get around a dbms_metadata bug in 9i.

CREATE GLOBAL TEMPORARY TABLE parsed_view_text

  (view_name VARCHAR2(30), 
   text_id NUMBER, 
   view_text VARCHAR2(4000)

  ) ON COMMIT PRESERVE ROWS; DECLARE
    num_iter NUMBER := 0;
    whole_clob CLOB;
    parsed_string VARCHAR2(32767);
    start_pos NUMBER := 1;
    num_chars NUMBER := 3000;

    CURSOR view_text_cur IS

       SELECT o.name view_name, v.text text, v.textlength text_length, v.cols view_columns
       FROM sys.obj$ o,
            sys.view$ v
       WHERE o.obj# = v.obj#
         AND o.owner# = &&schema_id;

    view_text_rec view_text_cur%ROWTYPE;

BEGIN    FOR view_text_rec IN view_text_cur
   LOOP
      whole_clob := TO_CLOB(view_text_rec.text);  

      DBMS_OUTPUT.PUT_LINE('View Name: '||view_text_rec.view_name||' Text Length :'|| view_text_rec.text_length);
      LOOP
         IF (view_text_rec.text_length - start_pos) < 3000
         THEN
              parsed_string := SUBSTR(whole_clob, start_pos);
              INSERT INTO parsed_view_text VALUES (view_text_rec.view_name, (view_text_rec.view_columns + num_iter), parsed_string||CHR(10)||'/');
            EXIT;
         END if;
         parsed_string := SUBSTR(whole_clob, start_pos, 3000);
         num_chars := GREATEST(INSTR(parsed_string, ', ', -1, 1), INSTR(parsed_string, ',"', -1, 1),
                               (INSTR(parsed_string, '),', -1, 1)+1), INSTR(parsed_string, ')', -1, 1));
         parsed_string := SUBSTR(whole_clob, start_pos, num_chars);
         INSERT INTO parsed_view_text VALUES (view_text_rec.view_name, (view_text_rec.view_columns + num_iter), parsed_string);
         start_pos := start_pos + num_chars;
         num_iter := num_iter + 1;
      END LOOP;
      COMMIT;
      start_pos := 1;
      num_chars := 3000;
      num_iter := 1;

   END LOOP;
END;
/

Regards,
Daniel Fink

Yechiel Adar <adar666_at_inter.net.il> wrote:
--

Adar Yechiel
Rechovot, Israel

--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l Received on Thu Jun 29 2006 - 14:17:36 CDT