Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: convert db2 varchar2(8000) to oracle blob
>Sorry - for clobs, it works on 8.1.7:
>(traced back the original code from august 2001)
>CREATE TABLE LIVE_FRAMESET(
> LIVE_FRAMESETID NUMBER(38, 0) NOT NULL,
> FRAMESETID NUMBER(38, 0) DEFAULT 0 NOT NULL,
> FRAMESETCODE CLOB,
> FRAMESETNAAM VARCHAR2(150),
> FRAMESETBESCHRIJVING VARCHAR2(250),
> CONSTRAINT PK_LIVE_FRAMESET PRIMARY KEY (LIVE_FRAMESETID)
>) ;
>procedure upd_remote_lfs(v_id IN number) as
>begin
> update live_frameset_at_testde lfsr
> set (
> FRAMESETID,
> FRAMESETCODE,
> FRAMESETNAAM,
> FRAMESETBESCHRIJVING
> ) =
> (select
> FRAMESETID,
> FRAMESETCODE,
> FRAMESETNAAM,
> FRAMESETBESCHRIJVING
> from live_frameset lfs
> where lfs.live_framesetid = v_id)
> where lfsr.live_framesetid = v_id;
>EXCEPTION
No go. I am using CLOBs. I mistyped the subject.
ERROR at line 2:
ORA-06550: line 2, column 3:
PL/SQL: ORA-00932: inconsistent datatypes: expected NUMBER got LONG
ORA-06550: line 2, column 3:
PL/SQL: SQL Statement ignored
I tried to split up the field as well (worth a shot):
CREATE TABLE t
(
cas_chron_sq_r NUMBER(10),
first4000 VARCHAR2(4000),
second4000 VARCHAR2(4000)
);
INSERT INTO t (cas_chron_sq_r) VALUES (6478152000);
BEGIN
UPDATE t SET (first4000, second4000) =
(SELECT SUBSTR(chron_x, 1, 4000), SUBSTR(chron_x, 4001, 8000)
FROM reppd.pfs_cas_chron_note_at_hsn
WHERE cas_chron_sq_r = 6478152000);
END;
/
but got the same error.
I found a similar a possible resolution on asktom.
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:238014476571
I am going to give that a try.
Thanks Frank for your help. Received on Wed Apr 27 2005 - 08:28:49 CDT