Home » SQL & PL/SQL » SQL & PL/SQL » Unable to compile (Oracle 10g)
Unable to compile [message #428983] Sat, 31 October 2009 08:43 Go to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

Dear All i am writing one function which does the following

1.there are lot of operations for one item
like one item may go thru different operations like cut,weld,paint but it may wary also depending on the item.Some item may go thru one operation whereas some may go thru 2 operations.Along with operation there is a stage number mean ist operation 2nd operation.I will pass one sys id and it will return values



CREATE OR REPLACE FUNCTION ORION2007.getoper1 (p_sys  in  

ot_cutting_sheet_detail.csd_sys_id%TYPE)
   RETURN VARCHAR2
 IS
   l_text  VARCHAR2(32767) := NULL;
    CURSOR c1
     IS
        SELECT
              cso_csd_sys_id, CSO_OP_STAGE_NO
  FROM OT_CUTTING_SHEET_OPERATION,ot_cutting_sheet_head,ot_cutting_sheet_detail
  where csh_sys_id = csd_csh_sys_id
  and  csd_sys_id = cso_csd_sys_id
  and csd_sys_id = P_SYS
  ORDER BY CSO_OP_STAGE_NO;
  cursor c2
    is
        SELECT COUNT(*) FROM
  OT_CUTTING_SHEET_OPERATION,ot_cutting_sheet_head,ot_cutting_sheet_detail
  where csh_sys_id = csd_csh_sys_id
  and  csd_sys_id = cso_csd_sys_id
  and csd_sys_id = P_SYS
  ORDER BY CSO_OP_STAGE_NO   ;
  cursor c3 (p_stg number) is
    select   CSO_oper_code
  FROM OT_CUTTING_SHEET_OPERATION,ot_cutting_sheet_head,ot_cutting_sheet_detail
  where csh_sys_id = csd_csh_sys_id
  and  csd_sys_id = cso_csd_sys_id
  and csd_sys_id = P_SYS
  and cso_op_stage_no = p_stg
  ORDER BY CSO_OP_STAGE_NO;
 p_field6 VARCHAR2(20);
 p_field7 varchar2(20);
 p_field8 varchar2(20);
 p_field9 varchar2(20);
  m_sys number;
  m_oper varchar2(20);
  M_STG NUMBER;
  M_NO NUMBER;
  BEGIN
     OPEN c1;
     FETCH c1
      INTO M_sys,M_STG;
     CLOSE c1;
     OPEN C2;
     FETCH C2 INTO M_NO;
     CLOSE C2;
     open c3(m_stg);
     fetch c3 into m_oper;
     close c3;
   FOR j IN C2
    LOOP
     IF M_NO = 1
       THEN
    for i in c1
      loop
      for k in c3(i.cso_op_stage_no)
       loop
        p_field6 := M_OPER;
        p_field7 := 'X';
        P_FIELD8 := 'X';
        P_FIELD9 := 'X';
    l_text := (p_field6||''||p_field7||''||p_field8||''||p_field9);
         end loop;
      end loop;
     ELSIF  M_NO = 2
     THEN
     for i in c1
      loop
      for k in c3(i.cso_op_stage_no)
       loop
       if i.cso_op_stage_no = 1
        then
        P_field6 := M_OPER;
        p_field7 := 'X';
        p_field8 := 'X';
        P_FIELD9 := 'X';
        else
        P_field6 := 'X';
        p_field7 := M_OPER;
        p_field8 := 'X';
        P_FIELD9 := 'X';
      l_text := (p_field6||''||p_field7||''||p_field8||''||p_field9);
    END IF;
        END LOOP;
       END LOOP;
   END IF;
  RETURN  l_text;
   END;


error message is
87/7 PLS-00103: Encountered the symbol ";" when expecting one of the following: loop

[Mod-edit: Frank corrected closing code tag]

[Updated on: Sat, 31 October 2009 09:57] by Moderator

Report message to a moderator

Re: Unable to compile [message #428985 is a reply to message #428983] Sat, 31 October 2009 09:56 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
If you would indent your code, you would see that you miss one end loop.
Re: Unable to compile [message #429005 is a reply to message #428983] Sat, 31 October 2009 23:05 Go to previous messageGo to next message
BlackSwan
Messages: 24904
Registered: January 2009
Senior Member
do not cross/multi-post
http://www.orafaq.com/forum/m/428983/136107/#msg_428983
Re: Unable to compile [message #429019 is a reply to message #428985] Sun, 01 November 2009 01:10 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

Dear Frank,

Could you please help me telling where i missed the end loop exactly by modifying the code in red color.
Re: Unable to compile [message #429021 is a reply to message #429019] Sun, 01 November 2009 01:16 Go to previous messageGo to next message
Michel Cadot
Messages: 63801
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Did you try to correctly indent the code and see it by yourself?

If you still don't see the error, post the indented code here and we'll show you.
Before read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Use the "Preview Message" button to verify.

Regards
Michel

[Edit: typos]

[Updated on: Sun, 01 November 2009 02:18]

Report message to a moderator

Re: Unable to compile [message #429024 is a reply to message #429019] Sun, 01 November 2009 01:57 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
arif_md2009 wrote on Sun, 01 November 2009 08:10
Dear Frank,

Could you please help me telling where i missed the end loop exactly by modifying the code in red color.

Since your code is not indented properly, how can I tell?
I can not be certain what parts you want inside your loop and what parts not.
Re: Unable to compile [message #429056 is a reply to message #428983] Sun, 01 November 2009 23:08 Go to previous message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
CREATE OR REPLACE FUNCTION orion2007.getoper1 (
   p_sys   IN   ot_cutting_sheet_detail.csd_sys_id%TYPE
)
   RETURN VARCHAR2
IS
   l_text     VARCHAR2 (32767) := NULL;

   CURSOR c1
   IS
      SELECT   cso_csd_sys_id, cso_op_stage_no
          FROM ot_cutting_sheet_operation,
               ot_cutting_sheet_head,
               ot_cutting_sheet_detail
         WHERE csh_sys_id = csd_csh_sys_id
           AND csd_sys_id = cso_csd_sys_id
           AND csd_sys_id = p_sys
      ORDER BY cso_op_stage_no;

   CURSOR c2
   IS
      SELECT   COUNT (*)
          FROM ot_cutting_sheet_operation,
               ot_cutting_sheet_head,
               ot_cutting_sheet_detail
         WHERE csh_sys_id = csd_csh_sys_id
           AND csd_sys_id = cso_csd_sys_id
           AND csd_sys_id = p_sys
      ORDER BY cso_op_stage_no;

   CURSOR c3 (p_stg NUMBER)
   IS
      SELECT   cso_oper_code
          FROM ot_cutting_sheet_operation,
               ot_cutting_sheet_head,
               ot_cutting_sheet_detail
         WHERE csh_sys_id = csd_csh_sys_id
           AND csd_sys_id = cso_csd_sys_id
           AND csd_sys_id = p_sys
           AND cso_op_stage_no = p_stg
      ORDER BY cso_op_stage_no;

   p_field6   VARCHAR2 (20);
   p_field7   VARCHAR2 (20);
   p_field8   VARCHAR2 (20);
   p_field9   VARCHAR2 (20);
   m_sys      NUMBER;
   m_oper     VARCHAR2 (20);
   m_stg      NUMBER;
   m_no       NUMBER;
BEGIN
   OPEN c1;

   FETCH c1
    INTO m_sys, m_stg;

   CLOSE c1;

   OPEN c2;

   FETCH c2
    INTO m_no;

   CLOSE c2;

   OPEN c3 (m_stg);

   FETCH c3
    INTO m_oper;

   CLOSE c3;

   FOR j IN c2
   LOOP
      IF m_no = 1
      THEN
         FOR i IN c1
         LOOP
            FOR k IN c3 (i.cso_op_stage_no)
            LOOP
               p_field6 := m_oper;
               p_field7 := 'X';
               p_field8 := 'X';
               p_field9 := 'X';
               l_text :=
                  (p_field6 || '' || p_field7 || '' || p_field8 || ''
                   || p_field9
                  );
            END LOOP;
         END LOOP;
      ELSIF m_no = 2
      THEN
         FOR i IN c1
         LOOP
            FOR k IN c3 (i.cso_op_stage_no)
            LOOP
               IF i.cso_op_stage_no = 1
               THEN
                  p_field6 := m_oper;
                  p_field7 := 'X';
                  p_field8 := 'X';
                  p_field9 := 'X';
               ELSE
                  p_field6 := 'X';
                  p_field7 := m_oper;
                  p_field8 := 'X';
                  p_field9 := 'X';
                  l_text :=
                     (p_field6 || '' || p_field7 || '' || p_field8 || ''
                      || p_field9
                     );
               END IF;
            END LOOP;
         END LOOP;
      END IF;
   END LOOP;

   RETURN l_text;
END;


I can end loop whereever I want to correct the syntax Wink
so better do your homework yourself only

[Updated on: Sun, 01 November 2009 23:13]

Report message to a moderator

Previous Topic: Interesting MINUS problem (merged)
Next Topic: same version of DB, different query results.
Goto Forum:
  


Current Time: Sun Sep 25 07:46:01 CDT 2016

Total time taken to generate the page: 0.06374 seconds