| CLOB to varchar conversion help!! [message #36798] |
Fri, 21 December 2001 09:06  |
Keith Sherman
Messages: 4
Registered: December 2001
|
Junior Member |
|
|
I'm trying to convert a CLOB to a varchar2 (because a reporting application can't read CLOB) by way of a function but I keep getting a PLS-00382 error. After reading oracle documentation I tried to assign a Varchar2(10000) to a to_char(CLOB) and it kept asking me for a format, but the only ones I know are for the number and date datatypes. How does one convert a clob to a varchar2?
----------------------------------------------------------------------
|
|
|
|
| Re: CLOB to varchar conversion help!! [message #36799 is a reply to message #36798] |
Fri, 21 December 2001 11:00  |
Suresh Vemulapalli
Messages: 624
Registered: August 2000
|
Senior Member |
|
|
this function helps if your clob column value not exceed 4000 bytes (varchar2 limit).if clob column's data exceeds 4000 limit, you have to follow different approach.
Suresh Vemulapalli.
create or replace function lob_to_char(clob_col clob) return varchar2 IS
buffer varchar2(4000);
amt BINARY_INTEGER := 4000;
pos INTEGER := 1;
l clob;
bfils bfile;
l_var varchar2(4000):='';
begin
LOOP
if dbms_lob.getlength(clob_col)<=4000 THEN
dbms_lob.read (clob_col, amt, pos, buffer);
l_var := l_var||buffer;
pos:=pos+amt;
ELSE
l_var:= 'Cannot convert to varchar2..Exceeding varchar2 field limit';
exit;
END IF;
END LOOP;
return l_var;
EXCEPTION
WHEN NO_DATA_FOUND THEN
return l_var;
END;
----------------------------------------------------------------------
|
|
|
|
| Re: CLOB to varchar conversion help!! [message #36801 is a reply to message #36799] |
Fri, 21 December 2001 13:13 |
Keith Sherman
Messages: 4
Registered: December 2001
|
Junior Member |
|
|
Thanks a lot for the function, but what would be the other approach for CLOB's over 4000 characters? See, this table was once a LONG datatype, and the application we're using for reporting can't use CLOB's, so I thought that the PL/SQL limit for VARCHAR2 being 32k would get around this. I figured if I turned it into a LONG I would have a problem since the PL/SQL LONG datatype limit is much lower than the limit for varchar2. What would be the correct way to do this? I need at least 11000 characters.
----------------------------------------------------------------------
|
|
|
|
|
|
|
|
| Re: CLOB to varchar conversion help!! [message #36818 is a reply to message #36799] |
Sun, 23 December 2001 11:53 |
Suresh Vemulapalli
Messages: 624
Registered: August 2000
|
Senior Member |
|
|
have a look at this solution.(this will work only if your report tool supports
ref cursor.. as far as i know, most of the tools do support ref cursors)
follow these steps
1) create temporary table with syntax below
CREATE GLOBAL TEMPORARY TABLE temp_tab(id number,varchar_col varchar2(4000));
2)
create package with script below (make modifications like table name,column name etc).
see comments in my package script
3) test this package from sql plus.
test procedure:
SQL> var r refcursor
SQL> exec lobpkg.lob_to_char(:r);
SQL> print r
if last statement prints full content of clob column ,that means package is working fine.
4) define datasource in your report as procedure name (lobpkg.lob_to_char)
HTH
Suresh Vemulapalli
create or replace package lobpkg is
type ref1 is ref cursor;
n number:=0;
PROCEDURE lob_to_char(rvar IN OUT lobpkg.ref1) ;
end;
/
create or replace package body lobpkg is
PROCEDURE lob_to_char(rvar IN OUT lobpkg.ref1) IS
buffer varchar2(4000);
amt BINARY_INTEGER := 4000;
pos INTEGER := 1;
l clob;
r lobpkg.ref1;
bfils bfile;
l_var varchar2(4000):='';
CURSOR C1 IS SELECT * FROM clob_tab;
-- change clob_tab to your_table_name
begin
n:=n+1;
FOR crec IN c1 LOOP
amt:=4000;
pos:=1;
BEGIN
LOOP
--change crec.clob_col to crec.your_column_name
dbms_lob.read (crec.clob_col, amt, pos, buffer);
--change next line if you create temporary table with different name
insert into temp_tab values (n,buffer);
pos:=pos+amt;
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
END;
END LOOP;
--change next line if you create temporary table with different name
open rvar for select vchar from temp_tab where id=n;
END;
end;
/
----------------------------------------------------------------------
|
|
|
|
| Re: CLOB to varchar conversion help!! [message #36968 is a reply to message #36798] |
Tue, 08 January 2002 04:14 |
Carlos Andres Dominguez N
Messages: 1
Registered: January 2002
|
Junior Member |
|
|
CREATE OR REPLACE ...
-- Maxima longitud permitida
cnuMAX_LENGTH Constant number := 32767 ;
exCLOB_EXCEED_VARCHAR_LENGTH EXCEPTION;
...
FUNCTION GetVarchar2 (iclCLOB IN OUT CLOB)
return VARCHAR2
IS
nuLength Number := DBMS_LOB.getlength(iclCLOB);
sbBuffer varchar2(32767);
BEGIN
if nuLength > cnuMAX_LENGTH then
raise exCLOB_EXCEED_VARCHAR_LENGTH;
END if;
DBMS_LOB.read(iclCLOB,nuLength,1,bBuffer);
return sbBuffer;
END;
|
|
|
|
| Re: CLOB to varchar conversion help!! [message #38815 is a reply to message #36801] |
Thu, 16 May 2002 12:04 |
Suchi
Messages: 6
Registered: December 2001
|
Junior Member |
|
|
Hi suresh,
I saw some queries about clob to varchar conversion, do you have an idea how the reverse process can be done.
I would like to bind a varchar to a CLOB. For this process I would like to convert the varchar2 value to clob and bind this as clob.
Any sugesstions would be valuable. If it is a trouble, please ignore my message.
Thanks
Suchi
|
|
|
|
| Re: CLOB to varchar conversion help!! [message #39888 is a reply to message #36801] |
Wed, 21 August 2002 08:03 |
Raju Muthu
Messages: 2
Registered: August 2002
|
Junior Member |
|
|
I am having trouble sending out the CLOB as an out parameter. On the VB end for size property, the error is invalid operation on null LOB.
I am using dbms_xmlgen.getxml(qryCtx) to get the XML as CLOB.
p_info_xml is declared as out parameter. and the outputof the function is assigned to it...
am i missing something here...
thanks
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
