Re: How to convert long to char?
- From: Daniel Fink <danielwfink@xxxxxxxxx>
- To: adar666@xxxxxxxxxxxx, ORACLE-L@xxxxxxxxxxxxx
- Date: Thu, 29 Jun 2006 12:17:36 -0700 (PDT)
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
