How to convert long to char? 2006-06-29 - By Daniel Fink
Back
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@(protected)> wrote:
--
Adar Yechiel
Rechovot, Israel
--
http://www.freelists.org/webpage/oracle-l
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.<br><br>I also
used the following code to get around a dbms_metadata bug in 9i.<br><br>CREATE
GLOBAL TEMPORARY TABLE parsed_view_text <br> (view_name VARCHAR2(30), <br
> text_id NUMBER, <br> view_text VARCHAR2(4000)<br> 
; ) ON COMMIT PRESERVE ROWS;<br><br>DECLARE<br> num_iter
NUMBER := 0;<br> whole_clob CLOB;<br>
parsed_string VARCHAR2(32767);<br> start_pos NUMBER := 1;<br>
num_chars NUMBER := 3000;<br><br> CURSOR
view_text_cur IS <br> SELECT o.name view
_name, v.text text, v.textlength text_length, v.cols view_columns<br>  
; FROM sys.obj$ o,<br>
sys.view$
v<br> WHERE o.obj# = v.obj#<br>  
; AND o.owner# = &&schema_id;<br><br
> view_text_rec view_text_cur%ROWTYPE;<br><br>BEGIN<br><br>
FOR view_text_rec IN view_text_cur<br> LOOP<br>
whole_clob := TO_CLOB(view_text_rec.text);<br> <br
> DBMS_OUTPUT.PUT_LINE('View Name: '||view_text
_rec.view_name||' Text Length :'|| view_text_rec.text_length);<br>
LOOP<br> IF
(view_text_rec.text_length - start_pos) < 3000<br>
THEN<br>  
; parsed_string := SUBSTR(whole_clob, start_pos);
<br>
INSERT INTO parsed_view_text VALUES
(view_text_rec.view_name, (view_text_rec.view_columns + num_iter), parsed
_string||CHR(10)||'/');<br>  
; EXIT;<br> END if;
<br> parsed_string := SUBSTR
(whole_clob, start_pos, 3000);<br>
num_chars := GREATEST(INSTR(parsed_string, ', ', -1, 1), INSTR(parsed
_string, ',"', -1, 1),<br>
(INSTR(parsed_string, '),', -1,
1)+1), INSTR(parsed_string, ')', -1, 1));<br>
parsed_string := SUBSTR(whole_clob, start_pos, num_chars);<br
> INSERT INTO parsed_view_text
VALUES (view_text_rec.view_name,
(view_text_rec.view_columns + num_iter), parsed_string);<br>
start_pos := start_pos + num_chars;<br>
num_iter := num_iter + 1;<br>
END LOOP;<br> COMMIT;<br>
start_pos := 1;<br>
num_chars := 3000;<br> num_iter := 1;<br>
END LOOP;<br>END;<br>/<br><br><br>Regards,<br>Daniel Fink<br><br><b><i
>Yechiel Adar <adar666@(protected)></i></b> wrote:<blockquote class=
"replbq" style="border-left: 2px solid rgb(16, 16, 255); margin-left: 5px;
padding-left: 5px;"> <br>-- <br>Adar Yechiel<br>Rechovot, Israel<br><br>--<br
>http://www.freelists.org/webpage/oracle-l<br><br><br></blockquote><br>
|
|
