Home » RDBMS Server » Server Administration » how do you use a CLOB datatype in PL/SQL function?
how do you use a CLOB datatype in PL/SQL function? [message #370575] Mon, 27 December 1999 15:11 Go to next message
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 Go to previous message
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;
Previous Topic: Blank space insertion into a Database fiel
Next Topic: simple select
Goto Forum:
  


Current Time: Sat Oct 31 21:02:13 CDT 2020