Oracle 8i & Use of Clobs

[brinkley]

Hello,

I am writing a stored procedure which will create a large amount of data to be sent to MQSERIES. My stored procedure obtains data from our tables and creates the string. I have tried to do the following and I receive an error (PLS-00382: expression is of wrong type).

p_buffer := p_buffer || rpad(v_scope,5000,v_space);

Please note that p_buffer is defined as a clob and v_space is a varchar.

I have done some research and I think that I should use
the append function below

dbms_lob.Append(p_buffer, rpad(v_scope,5000,v_space))

However, I receive the following error messages.

PLS-00306: wrong number or types of arguments in call to 'APPEND'
PL/SQL: Statement ignored

I would appreciate any help/comments.

Thank you,
Teresa Brinkley

 

[AOLEURO]

Hi

Try DESC DBMS_LOB

As you can se APPEND want a LOB and not a VARCHAR

Write or WriteAppend can accept VARCHAR2 but you then have to use a buffer from a column in a table.

Remember that a LOB can't be in memory - Oracle expects us to use IO to handle the huge amount of data a LOB can have.

drop table aol;

create table aol( aolID integer primary key,
Text CLOB,
) tablespace table_data
lob (text) store as (tablespace lob_space disable storage in row
storage( initial 128k next 128k pctincrease 0)
chunk 2048 pctversion 20 nocache nologging
index (tablespace index_data storage( initial 128k next 128k ) )
);

-- CHUNK must be a multiplex of database block size (=DB block size).

declare
a clob;
begin

insert into aol values(1, Rpad('aaa', 5000 ));
insert into aol values(2, Rpad('aca', 5000 ));

select text into a from aol where aolid = 1 for update;

dbms_lob.append(a, a);

update aol
set text = a
where aolid = 1;
end;
/

commit;

select aolid, text, dbms_lob.getlength( text) len from aol;

-- Please note – that LEN returned from AOL is 8000 and 4000 and not 10,000 and 5,000
-- It is because RPAD only works to max. 4000

Regards
Allan
Icq: 346225948

 

[ChrisHunt]

You need to do some more research, Teresa! Hardly surprising because the whole LOB area is abominably documented. This should do what you asked:

DBMS_LOB.writeappend(p_buffer, 5000, RPAD(v_scope,5000,v_space));

It's quite happy to accept PL/SQL variables like v_scope and v_space - you don't have to go via a column from a table.

Be aware that PL/SQL has a longer VARCHAR2 limit of 32767 characters. I'm not sure what MQSERIES is, but if it's going to do anything with this buffer that doesn't involve SQL statements then you may be able to get away with just using VARCHAR2s. -- Chris Hunt
Extra Connections Ltd

http://www.extracon.com

 

[AOLEURO]

Thanks Chis

But I wonder how Oracle is keeping a LOB if – lets say 100,000 without a row (table) to hide the chunk size data in.
Regards
Allan
Icq: 346225948

 

[ChrisHunt]

It depends on what kind of CLOB p_buffer is. It could come from a table - in which case you can define all that stuff explicitly - or you can create it using DBMS_LOB.CREATE_TEMPORARY - in which case Oracle defines it for you.

If Teresa's writing a stored procedure then the chances are that the CLOB's an IN/OUT parameter - in which case the procedure doesn't know what kind it is (though it could find out). Fortunately it doesn't need to worry as (contrary to your statement) writeappend works equally well on both temporary and permanent CLOBs. -- Chris Hunt
Extra Connections Ltd

http://www.extracon.com

 

[AOLEURO]

Yes I agree – it must be ether a table or a temporary place

But as I was wondering – it must be defined by the code.

Thanks Regards
Allan
Icq: 346225948

 

[brinkley]

Thank you all for responding! I tried Chris' suggestion since my clob is defined as an output parameter in my stored procedure.

The stored procedure finally compiled but I now am receiving an error mesage while executing it

ORA-06502 PL/SQL numeric or value error invalid LOB locator specified ORA-22275.

Any ideas? Do I need to include a call to DBMS_LOB.CREATE_TEMPORARY even though it is declared as an output parameter?

Thanks again,
Teresa

 

[AOLEURO]

Hmm - I said it all the time - you need to have it someplace. Oracle want to save it to disk.

Remember that the LOB is just a locator - then using the LOB you need to define somewhere to save it - in a table or in TEMP. Look a my example code.

So as I said before - need to create a place to save the damm stuff.
Regards
Allan
Icq: 346225948

 

[AOLEURO]

Hi Teresa

I have just changed my example, just to make it with more data – I can then use the example generally.

If you copy-and-paste the code – then remember to change TABLESPACE in the create table and the user must have DBA rights to change tablespace to read only and back.

I do not use function or other stuff – I only show the fun stuff about LOB and data.


column text format a50;

-- Change or create tablespace TABLE_DATA, LOB_SPACE and INDEX_DATA
create table aol( aolID integer primary key,
Text CLOB
) tablespace table_data
lob (text) store as (tablespace lob_space disable storage in row
storage( initial 128k next 128k pctincrease 0)
chunk 2048 pctversion 20 nocache nologging
index (tablespace index_data storage( initial 128k next 128k ) )
);

-- CHUNK must be a multiplex of database block size (=DB block size).

declare
a clob;
b clob;
d clob;
begin

insert into aol values(1, Rpad('aaa', 4000 )); -- This is A lob
insert into aol values(2, Rpad('aba', 4000 )); -- This is B lob
insert into aol values(3, Rpad('aca', 4000 )); -- This is C lob
insert into aol values(4, NULL ); -- This is D lob

-- Before we can work with the LOB we need to lock to row
select text into a from aol where aolid = 1 for update;

-- Append LOB to the LOB
dbms_lob.append(a, a);

-- Update LOB - we still lock the row until commit or rollback
update aol
set text = a
where aolid = 1;

-- append 4000 char to the end of LOB
dbms_lob.writeappend(a, 4000, rpad('ffffffffffffffffff',4000) );

-- Write 4000 char starting at pos 200
dbms_lob.write(a, 4000, 200, rpad('ffffffffffffffffff',4000) );

-- Now we copy the LOB to another lob - B LOB
-- We need to lock the B lob and get the LOB locator
select text into b from aol where aolid = 2 for update;


-- now lets copy A lob to B lob - we want all of A lob
dbms_lob.copy( B, A, dbms_lob.getlength(a) );

-- Update LOB - we still lock the row until commit or rollback
update aol
set text = b
where aolid = 2;


-- We can make a copy just by saying LOB D = LOB A (if same LOB type)
-- we do copy the data - not just change the locator to the same as A
update aol
set text = a
where aolid = 4;

-- save works and release LOCKS
commit;

end;
/

-- Lets see the data from pos 1 and 30 in length
select aolid, dbms_lob.SubStr(text, 30, 1 ) as text, dbms_lob.getlength( text) len from aol;

/*
PL/SQL-procedure er udført.


AOLID TEXT LEN
---------- ------------------------------ ----------
1 aaa 12000
2 aaa 12000
3 aca 4000
4 aaa 12000
*/


-- Now we change data on D lob to see what A and D lob are different
declare
d clob;
begin

-- Remember to lock the row
select text into d from aol where aolid = 4 for update;

-- Write 4000 char starting at pos 1
dbms_lob.write(d, 4000, 1, rpad('DDDDDDDDDDDDDDDDDDD',4000) );

-- Update LOB - we still lock the row until commit or rollback
update aol
set text = d
where aolid = 4;

-- save works and release LOCKS
commit;

end;
/

-- Lets see the data from pos 1 and 30 in length
select aolid, dbms_lob.SubStr(text, 30, 1 ) as text, dbms_lob.getlength( text) len from aol;

/*
PL/SQL-procedure er udført.



AOLID TEXT LEN
---------- ------------------------------ ----------
1 aaa 12000
2 aaa 12000
3 aca 4000
4 DDDDDDDDDDDDDDDDDDD 12000

*/

-- Lets see some data at pos 200 and 30 char in length
select aolid, dbms_lob.SubStr(text, 30, 200 ) as text, dbms_lob.getlength( text) len from aol;
/*

AOLID TEXT LEN
---------- ------------------------------ ----------
1 ffffffffffffffffff 12000
2 ffffffffffffffffff 12000
3 4000
4 12000
*/


-- let me prove it by setting the LOB tablespace as READONLY
-- We get an error because we do not only update locator value but create the LOB data as a copy

alter tablespace lob_space read only;

insert into aol values(5, (select text from aol where aolid = 4) );
/* *
ERROR in line 1:
ORA-00372: file 20 cannot be modified at this time
ORA-01110: Datafile 20: 'F:\ORACLE\ORADATA\SQL\LOB_SPACE01.DBF'
*/


-- So even if we only think that we change locator value - Oracle makes the magic for us
-- We then always have to think of locator as the pointer to the real data and the real
-- data must exists in a file and not just in memory


-- Cleaning
alter tablespace lob_space read write;
drop table aol;


Regards
Allan
Icq: 346225948