Re: How to convert long to char?

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@xxxxxxxxxxxx> wrote: 
-- 
Adar Yechiel
Rechovot, Israel

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

Other related posts: