| how do you use a CLOB datatype in PL/SQL function? [message #370575] |
Mon, 27 December 1999 15:11  |
Mitch Abaza
Messages: 1
Registered: December 1999
|
Junior Member |
|
|
I have a PL/SQL function that returns a VARCHAR2
value. The function works just fine except when the cursor size exceeds the maximum length of a VARCHAR2 variable (4000 bytes). I'd like to use a CLOB variable instead of a VARCHAR2 in order to circumvent the size problem, but I can't seem to get it to work. Is there some special syntax/statement that needs to be used when assigning a cursor value to a CLOB? My original function follows: Any help would be appreciated.
-------------------------------------------------------
CREATE OR REPLACE FUNCTION CONCAT_PROBLOGUPD(P_NUMBERPRGN IN VARCHAR2) RETURN
VARCHAR2
AS
CURSOR C_STR(IN_NUMBERPRGN IN VARCHAR2) IS
SELECT UPDATE_ACTION LOGUPD FROM HFS_PROBLEM_UPDATEACTION WHERE NUMBERPRGN
= IN_NUMBERPRGN ORDER BY RECORD_NUMBER;
--
STR_REC C_STR%ROWTYPE;
TEMP VARCHAR2(32000);
BEGIN
OPEN C_STR(P_NUMBERPRGN);
LOOP
FETCH C_STR INTO STR_REC;
EXIT WHEN C_STR%NOTFOUND;
TEMP := TEMP||STR_REC.LOGUPD;
END LOOP;
--
CLOSE C_STR;
RETURN TEMP;
END;
/
|
|
|
|
| Re: how do you use a CLOB datatype in PL/SQL function? [message #370595 is a reply to message #370575] |
Tue, 04 January 2000 09:51  |
hmg
Messages: 40
Registered: March 1999
|
Member |
|
|
Hi,
I wrote a little script which shows a function that returns more than 32 KBytes of data.
May be this script could help you.
Bye
drop table clob_table;
create table clob_table (
id number(10),
col clob );
insert into clob_table values ( 1, empty_clob() );
select dbms_lob.getlength(col) from clob_table;
-- fill the clob column with 50000 bytes
declare
cursor cur_clob is
select * from clob_table
where id = 1;
rec cur_clob%rowtype;
buffer VARCHAR2(100);
begin
open cur_clob;
fetch cur_clob into rec;
close cur_clob;
buffer := lpad('X',99,'X') || chr(10);
for i in 0..499 loop
dbms_lob.write( rec.col, 100, i*100+1, buffer);
end loop;
end;
/
select dbms_lob.getlength(col) from clob_table;
create or replace function getclob
return clob
is
cursor cur_clob is
select * from clob_table
where id = 1;
rec cur_clob%rowtype;
begin
open cur_clob;
fetch cur_clob into rec;
close cur_clob;
return (rec.col);
end;
/
show errors
-- show all the contents in SQLPLUS
set long 50000
set linesize 100
set pagesize 103
column output format a100
select getclob output from dual;
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
