PROCEDURE IS
--##########################################################################
#######
-- long type ---->varchar2 type
- create table yylongtab ( a number, b long );
- create table yyvartab ( a number, b varchar2(10));
- insert into yylongtab
values(3,'123456789012345678901234567890');
- insert into yylongtab values(4,'abcdefghijklmnopqrstuvwxyz');
--##########################################################################
#######
set serveroutput on
DECLARE
length_var NUMBER;
loop_cnt NUMBER;
idx1 NUMBER;
cursor c1 is
SELECT a, b FROM yylongtab;
yyval c1%ROWTYPE;
BEGIN
- Read long type using record cursor
for yyval in c1 loop
--Get lenth of long type column
length_var := LENGTH(yyval.b);
- Count of loop for insert varchar2(10)
loop_cnt := CEIL(length_var/10);
- Check Value.
DBMS_OUTPUT.PUT_LINE('a: '||yyval.a);
DBMS_OUTPUT.PUT_LINE('b length: '||length_var);
DBMS_OUTPUT.PUT_LINE('loop_cnt: '||loop_cnt);
- Cut long type and insert varchar2 Type.
FOR idx1 IN 0..loop_cnt-1 LOOP
insert into yyvartab
values(yyval.a,substr(yyval.b,idx1*10+1,10));
END LOOP;
END LOOP;
COMMIt;
END;
/
--##########################################################################
####
-- RESULT
- a: 3
- b length: 30
- loop_cnt: 3
- a: 4
- b length: 26
- loop_cnt: 3
--
- select * from yyvartab;
- A B
- ---------- ----------
- 3 1234567890
- 3 1234567890
- 3 1234567890
- 4 abcdefghij
- 4 klmnopqrst
- 4 uvwxyz
--
"½Å¸í¼·" <shinmyun_at_shinbiro.com> wrote in message
news:9qiqkk$d4m$1_at_hiline.shinbiro.com...
> We use a 8.x orcle version.
> I want to convert long type data to varchar2 type.
> Using procedure, i tried. but i cannot declare varchar2(4000) only can
> varchar2(2000).
> why cannot? on manual we can declare varchar2(4000) in version 8.xx.
>
> thank you!! Have a good day!!
>
>
Received on Wed Oct 17 2001 - 03:44:32 CDT
