Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: convert db2 varchar2(8000) to oracle blob

Re: convert db2 varchar2(8000) to oracle blob

From: Chet Justice <chet.justice_at_pfsf.org>
Date: 27 Apr 2005 06:28:49 -0700
Message-ID: <1114608529.066515.326730@o13g2000cwo.googlegroups.com>


>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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US