[sqr-users] RE: print a CLOB field in Oracle Database

[Enrique Munoz <ryhen2000@yahoo.com>]

Hi Ray,

I proved your steps that you said me and I got the
same error, but I try with the SQR Function TO_CHAR
and IT WORKS!

I just use the funcion with the field CLOB and I got
the result.

BEGIN-SELECT
ap.localidad                                    
&lw_localidad     () ON-BREAK LEVEL=1 PRINT=NEVER
BEFORE=p_imp_localidad
loc.nombre                                      
&lw_loc_desc
det.denominacion                                
&lw_denominacion  (+10,038, 04) EDIT '9999'
pr.proyecto       &lw_proy  (   ,070, 08) EDIT
'99999999'
DECODE(pr.fecha_termino, NULL,'PE', 'IT') TIPO  
&lw_tipo                    (   ,300, 02)
TO_CHAR(pr.objetivo)       &lw_obj   (   ,340, 50)
!<--- THIS IS A CLOB FIELD
FROM   sips_proyectos pr, sips_detalle_prog det,
       sips_areas_proyectos ap, localidades loc
WHERE  det.clave_det_prog = pr.clave_det_prog
AND    ap.proyecto        = pr.proyecto
AND    ap.principal       = 'S'
AND    loc.localidad      = ap.localidad
ORDER BY pr.proyecto
END-SELECT

Anyway thank you for your help...

Enrique

-----------------------------------------------------
>Enrique,

>This is a hard one.  Carefully follow these
>instructions:

>1) Break this into two queries.  The first will look
>just like 
>this one without the CLOB.  The second one should be
>a single-row
>query that gets the corresponding CLOB value.

>2) Move the second query to a PL/SQL block, and
>return the value
>into a PL/SQL variable.  Then, 1999 characters at a
>time, extract
>pieces of the PL/SQL variable into $string_0,
>$string_1, ... 
>$string_n variables using dbms_lob.substr().  Note
>that the order
>of parameters for dbms_lob.substr() is different from
>that of 
>sql substr().

>3) Finally, assemble your $string_0, $string_1, ...
>$string_n
>variables into your final variable.

>Depending on your version of SQR or operating
>platform, you may
>be limited to 32767 characters in your final
variable.

>Hope this helps.

>Ray
-----------------------------------------------------
On Mon, May 03, 2004 at 03:42:26PM -0700, Enrique
Munoz wrote:
> I need that someone says me how I can print a field
> CLOB with SQR.
> 
> When I try to print it I get this error:
> 
> (SQR 1305) CMPSQL: Unknown data type in database:
112.
> 
> I have this code in my program:
> 
> BEGIN-SELECT
> ap.localidad                                    
> &lw_localidad     () ON-BREAK LEVEL=1 PRINT=NEVER
> BEFORE=p_imp_localidad
> loc.nombre                                      
> &lw_loc_desc
> det.denominacion                                
> &lw_denominacion  (+10,038, 04) EDIT '9999'
> pr.proyecto       &lw_proy  (   ,070, 08) EDIT
> '99999999'
> DECODE(pr.fecha_termino, NULL,'PE', 'IT') TIPO  
> &lw_tipo                    (   ,300, 02)
> pr.objetivo       &lw_obj   (   ,340, 50) !<--- THIS
> IS A CLOB FIELD
> FROM   sips_proyectos pr, sips_detalle_prog det,
> sips_areas_proyectos ap, localidades loc
> WHERE  det.clave_det_prog = pr.clave_det_prog
> AND    ap.proyecto        = pr.proyecto
> AND    ap.principal       = 'S'
> AND    loc.localidad      = ap.localidad
> ORDER BY pr.proyecto
> END-SELECT
> 
> This are the releases:
> Win 2000
> Oracle 9i
> PersonalSQR for Oracle 6.2
> 
> Thanks for any help...!!
> 
> Enrique



        
                
__________________________________
Do you Yahoo!?
Win a $20,000 Career Makeover at Yahoo! HotJobs  
http://hotjobs.sweepstakes.yahoo.com/careermakeover 

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users