Re: convert db2 varchar2(8000) to oracle blob

Chet Justice wrote:
> >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 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.

In case anyone has a similar problem in the future, I wrote a simple java app to do this.

Unfortunately, I cannot load it into the database because the driver I have is a Type 2 driver (not pure java). I looked into purchasing a 3rd party Type 4 driver, but that requires creating some sort of table list on the remote site (upon establising the initial connection) which is impossible because I only have CONNECT priviledges. Received on Thu May 19 2005 - 08:59:27 CDT