Help with compilation error

From: Gigi Lipori <pflugg_at_bellsouth.net>
Date: Fri, 11 Oct 2002 13:06:52 -0400
Message-ID: <3da703e6$1_1_at_corp-news.newsgroups.com>


The code below gives a compilation error. The select works if I extract it and run it independently, and the logic works when it's done in VB (pretty sad...develop in VB and move into Oracle...am admittedly pl/sql novice). Assume the error is in the code starting at the begin....Appreciate any help...

CREATE OR REPLACE FUNCTION sth_liver_physical_exam (p_doc_id IN

      NUMBER) return varchar as cursor c1 is

SELECT
 T1.USER_FIELD_DEFINITION_COMMENT AS EXAM_TYPE,  MAX(T1.USER_FIELD_VALUE) AS EXAM_TEXT,
 MAX(T1.USER_FIELD_ID) AS FIELD_ID FROM  (SELECT

    USER_FIELD_DEFINITION_COMMENT,
    USER_FIELD_VALUE,
    USER_TABLE_FIELD_VALUES.USER_FIELD_ID
  FROM
    DOCUMENTS,
    USER_TABLE_FIELD_VALUES,
    USER_FIELD_DEFINITIONS
  WHERE
        DOCUMENTS.DOC_KIND_ID = 10076
    AND DOCUMENTS.PAT_ID = USER_TABLE_FIELD_VALUES.PAT_ID
    AND DOCUMENTS.DOC_ID = USER_TABLE_FIELD_VALUES.DOC_ID
    AND USER_FIELD_DEFINITIONS.USER_FIELD_ID (+) = USER_TABLE_FIELD_VALUES.USER_FIELD_ID
    AND USER_FIELD_DEFINITIONS.USER_FIELD_ID IN (4478, 4479, 4480, 4481,
                                                 4482, 4483, 4484, 4671,
                                                                4672, 4673,
4674, 4853,
                                                               4854, 4855,
4856, 4858,
                                                             4859, 4860,
4857, 4861,
                                                             4862)

    AND DOCUMENTS.DOC_ID = 42957 /* Replace with p_doc_id when it really becomes a function */

  UNION SELECT
    USER_FIELD_DEFINITION_COMMENT,
    NULL AS USER_FIELD_VALUE,
    USER_FIELD_ID
  FROM
    USER_FIELD_DEFINITIONS
  WHERE

    USER_FIELD_DEFINITIONS.USER_FIELD_ID IN  (4478, 4479, 4480, 4481,
                                              4482, 4483, 4484, 4671,
                                                          4672, 4673, 4674,
4853,
                                                          4854, 4855, 4856,
4858,
                                                          4859, 4860, 4857,
4861,
                                                          4862))  T1

GROUP BY
  USER_FIELD_DEFINITION_COMMENT;

dummy_examtype varchar2(4000);
dummy_examtext varchar2(4000);
dummy_exam varchar2(4000);

begin
for rs in c1
 loop
  if c1.field_id < 4800 then

     if dummy_exam_text <> "" then
          if dummy_exam <> "" then
              dummy_exam = dummy_exam || "; ";
           end;
           dummy_exam = dummy_exam || " " || dummy_exam_type || ": " ||
dummy_exam_text;
           dummy_exam_text = "";
       end;
       dummy_exam_type = c1.exam_type;
     end;
     if dummy_exam_text <> "" then
        dummy_exam_text = dummy_exam_text || ", ";
     endif;
     dummy_exam_text = dummy_exam_text || " " || c1.exam_text;
 end loop;
 close c1;
 return dummy_exam;
end;
/ Received on Fri Oct 11 2002 - 19:06:52 CEST

Original text of this message