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

[Ray Ontko <rayo@ontko.com>]

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
----------------------------------------------------------------------
Ray Ontko   rayo@ontko.com   Phone 1.765.935.4283   Fax 1.765.962.9788
Ray Ontko & Co.   Software Consulting Services   http://www.ontko.com/

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