[sqr-users] Clob, sqr and sql variables

[Melody Sayles <Melody.Sayles@alaska.edu>]

Oracle 9, sqr 8, unix
I'm using an sqr program to create a report based on a table where one of the
columns is a CLOB. I found the DBMS_LOB.GETLENGTH function. All I need from the
clob column is if there is text in there or is it null. I haven't been able to
find the right syntax to move the value of a variable from the sql to sqr  so
that I can set a flag of null or 'Y'.
Code so far (and I'm open to suggestions):
begin-procedure Get-Text
begin-sql
DECLARE
  locator_var     clob;;
  length_var      integer;;
BEGIN
   select sor2ppt_text into locator_var
     from sor2ppt
    where sor2ppt_pidm              = $control_pidm
      and sor2ppt_contact_date    = $orig_contact_date
      and sor2ppt_contact_from_time = $contact_from_time;;
   length_var := DBMS_LOB.GETLENGTH(locator_var);;
END;;
end-sql

!if $length_var > 0
! Let $comments = 'Y'
!else
! Let $comments = ''
!end-if;
end-procedure Get-Text

Thanks in advance,
Melody



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