Re: Howto: insert a LOB>4000 byte into 8i

From: Martin Haltmayer <Martin.Haltmayer_at_0800-einwahl.de>
Date: 2000/05/01
Message-ID: <390DEB95.E7CBFF4B@0800-einwahl.de>

Use a long and convert it to NCLOB.

Here is a similar example for LONG RAW <-> BLOB which we used when IIS (ASP) with MDAC 2.1 could not understand the new Oracle type BLOB:

• blob.sql for testing blob access procedures.
• Martin Haltmayer, 16/02/2000
• Last change:

@spool.sql

spool blob.sql.lst

create table testblob (

	id number
	, pict blob
	, constraint testblob_pk primary key (id)

)
/

create global temporary table testlong (

	id number
	, lr long raw
	, constraint testlong_pk primary key (id)

)
/

whenever sqlerror exit failure rollback

create or replace
package testblob_arr_types is

d_a4000 varchar2 (4000);

type t_pict is table of d_a4000%type index by binary_integer;

procedure blob2arr (

	b in blob
	, a out nocopy t_pict

);
pragma restrict_references (blob2arr, rnps, wnps, rnds, wnds);

procedure arr2blob (

	a in t_pict
	, b in out nocopy blob

);
-- No pragma allowed because writeappend is used and this does not have any pragmata.
-- pragma restrict_references (arr2blob, rnps, wnps, rnds, wnds);

end testblob_arr_types;
/

show errors

create or replace
package body testblob_arr_types is

procedure blob2arr (

	b in blob
	, a out nocopy t_pict
) is
	l_len integer:= dbms_lob.getlength (b);
	p pls_integer:= 1;	-- pointer into b
	j pls_integer:= 1;	-- array index into a
begin
	a.delete;
	while (p <= l_len) loop
		a (j):= rawtohex (
			dbms_lob.substr (
				lob_loc => b
				, offset => p
				, amount => least (2000, l_len - p + 1)
			)
		);
		p:= p + least (2000, l_len - p + 1);
		j:= j + 1;
	end loop;

end blob2arr;

procedure arr2blob (

	a in t_pict
	, b in out nocopy blob

) is
begin

	if (a.first <= a.last) then
		for j in a.first.. a.last loop
			dbms_lob.writeappend (
				lob_loc => b
				, amount => utl_raw.length (hextoraw (a (j)))
				, buffer => hextoraw (a (j))
			);
		end loop;
	end if;

end arr2blob;

end testblob_arr_types;
/

show errors

create or replace procedure testblob_ins (

	i_id in number
	, i_pict in long raw

) is
begin

	insert into testblob (
		id
		, pict
	)
	values (
		i_id
		, i_pict
	)
	;
	commit;

end testblob_ins;
/

show errors

create or replace procedure testblob_ins_arr (

	i_id in number
	, i_pict in testblob_arr_types.t_pict
) is
	l_b blob;
begin
	dbms_lob.createtemporary (l_b, true, dbms_lob.call);
	testblob_arr_types.arr2blob (i_pict, l_b);
	insert into testblob (
		id
		, pict
	)
	values (
		i_id
		, l_b
	)
	;
	commit;
	dbms_lob.freetemporary (l_b);

end testblob_ins_arr;
/

show errors

create or replace procedure testblob_mod (

	i_id in number
	, i_pict in long raw

) is
begin

	update testblob t
	set t.pict = i_pict
	where 1 = 1
	and i_id = t.id
	;
	commit;

end testblob_mod;
/

show errors

create or replace procedure testblob_mod_arr (

	i_id in number
	, i_pict in testblob_arr_types.t_pict
) is
	l_b blob;
begin
	testblob_arr_types.arr2blob (i_pict, l_b);
	update testblob t
	set t.pict = l_b
	where 1 = 1
	and i_id = t.id
	;
	commit;

end testblob_mod_arr;
/

show errors

create or replace procedure testblob_del (

        i_id in number
) is
begin

	delete testblob t
	where 1 = 1
	and i_id = t.id
	;
	commit;

end testblob_del;
/

show errors

create or replace procedure testblob_out (

	i_id in number
	, o_pict out long raw

) is
begin

	delete from testlong;
	execute immediate 'insert into testlong (id, lr) select t.id, t.pict from
testblob t where 1 = 1 and :1 = t.id' using i_id;
	begin
		select
			lr
		into
			o_pict
		from
			testlong t
		where 1 = 1
		and i_id = t.id
		;
	exception
		when no_data_found then
		o_pict:= null;
	end;

end testblob_out;
/

show errors

create or replace procedure testblob_out_arr (

	i_id in number
	, o_pict out nocopy testblob_arr_types.t_pict
) is
	l_b blob;
begin
	begin
		select
			t.pict
		into
			l_b
		from
			testblob t
		where 1 = 1
		and i_id = t.id
		;
		testblob_arr_types.blob2arr (l_b, o_pict);
	exception
		when no_data_found then
		o_pict.delete;
	end;

end testblob_out_arr;
/

show errors

declare

        l_l long raw;
begin

        testblob_out (1, l_l);
end;
/

show errors

declare

        l_l testblob_arr_types.t_pict;
begin

	testblob_out_arr (1, l_l);
	if (l_l.first <= l_l.last) then
		for r in l_l.first.. l_l.last loop
			dbms_output.put_line (substr (l_l (r), 1, 80));
		end loop;
	end if;

end;
/

show errors

declare

	l_l testblob_arr_types.t_pict;
	b1 blob;
	b2 blob;
begin
	delete from testblob where id = 2;
	testblob_out_arr (1, l_l);
	testblob_ins_arr (2, l_l);
	commit;
	select
		pict
	into
		b1
	from
		testblob
	where 1 = 1
	and 1 = id
	;
	select
		pict
	into
		b2
	from
		testblob
	where 1 = 1
	and 2 = id
	;
	dbms_output.put_line ('compare = ' || dbms_lob.compare (b1, b2));

end;
/

whenever sqlerror continue

spool off

Martin

Andreas Jung wrote:

> 
> Dear all,
> 
> since days I am looking for a way to insert long strings (more than 4000 bytes)
> into a NCLOB field under Oracle 8i. I tried this by using a Python interface
> and PHP4. I tried using the bind mechanism but in every case I get an
> ORA-01461 error (can bind a LONG value only for insert into a LONG column).
> This occurs with data longer than 4000 bytes.
> 
> Can anyone tell me how to get rid of this problem ?!
> 
> Thanks,
> Andreas
>

Received on Mon May 01 2000 - 00:00:00 CDT