13 Replies Latest reply: Apr 3, 2013 9:10 AM by Ashu_Neo RSS

    Getting error pls-00103

    990187
      create or replace procedure ems is
      cursor cur(ename varchar2) is select last_name,hire_date,salary,months_between(sysdate,hire_date)/12 years from employees where last_name=ename;
      hdate date;
      years number;
      sal number;
      lname varchar(40);
      not_due_for_raise exception;
      begin
      open cur;
      loop
      fetch cur into lname,hdate,sal,years;
      if years>5 and sal<3500 and sal>25000 then
      raise not_due_for_raise;
      else
      dbms_output.put_line('not due for a raise');
      end if;
      exception
      when not_due_for_raise then
      insert into analysis(ename,years,sal) values(ename,years,sal);
      end loop;
      end;
      /


      plz specify the error and please check the statements . .
        • 1. Re: Getting error pls-00103
          S10390
          insert into analysis(ename,years,sal) values(*ename*,years,sal);
          This ename should be lname.

          Run the code and post the errors if you face any.
          • 2. Re: Getting error pls-00103
            Ashu_Neo
            See, I have added some portion with comment. Just pass a parameter to use it in cursor.
            You can change it as per your options.
            Compile it and reply with some new errora(if any)!
            create or replace procedure ems 
            (pname IN employees.last_name%type) -- Added newly
            is
            cursor cur(ename varchar2) 
            is select last_name,hire_date,salary,months_between(sysdate,hire_date)/12 years 
            from employees 
            where last_name = ename;
            hdate date;
            years number;
            sal number;
            lname varchar(40);
            not_due_for_raise exception;
            
            begin
                --open cur;
                 open cur(pname); -- added : need to pass parameter to open cursor as defined
                 loop
                      begin     -- added
                           fetch cur into lname,hdate,sal,years;
                           if years>5 and sal<3500 and sal>25000 then
                                raise not_due_for_raise;
                           else
                                dbms_output.put_line('not due for a raise');
                           end if;
                           exception
                           when not_due_for_raise then
                           insert into analysis(ename,years,sal) values(ename,years,sal);
                     end;  -- added     
                 end loop;
            end;
            /
            Thanks!
            • 3. Re: Getting error pls-00103
              990187
              hello,
              it is not executing it is displaying as pls-00103-encountered the symbol loop when expecting some of the following: ;
              • 4. Re: Getting error pls-00103
                Ashu_Neo
                You show me what you have executed with error! It's working fine with me.
                SQL> ed
                Wrote file afiedt.buf
                
                  1  create or replace procedure ems
                  2  (pname IN employees.last_name%type) -- Added newly
                  3  is
                  4  cursor cur(ename varchar2)
                  5  is select last_name,hire_date,salary,months_between(sysdate,hire_date)/12 years
                  6  from employees
                  7  where last_name = ename;
                  8  hdate date;
                  9  years number;
                 10  sal number;
                 11  lname varchar(40);
                 12  not_due_for_raise exception;
                 13  begin
                 14      --open cur;
                 15     open cur(pname); -- added : need to pass parameter to open cursor as defined
                 16     loop
                 17             begin   -- added
                 18                     fetch cur into lname,hdate,sal,years;
                 19                     if years>5 and sal<3500 and sal>25000 then
                 20                             raise not_due_for_raise;
                 21                     else
                 22                             dbms_output.put_line('not due for a raise');
                 23                     end if;
                 24                     exception
                 25                     when not_due_for_raise then
                 26                     insert into analysis values(lname,years,sal);
                 27         end;  -- added
                 28     end loop;
                 29* end;
                 30  /
                
                Procedure created.
                Edited by: Ashu_Neo on Apr 2, 2013 9:52 PM
                -- Added coding part
                • 5. Re: Getting error pls-00103
                  990187
                  hii,
                  it is now showing 26/49 ora-00984: column not allowed here . . can u say any modification
                  • 6. Re: Getting error pls-00103
                    Ashu_Neo
                    If you really run above piece of code, then you will never face any problem!
                    insert into analysis values(lname,years,sal);
                    Here I don't know the columns of the analysis table. So I assumed to be 3 columns and omit column list in the INSERT statement. If your table(ANALYSIS) has more than 3 columns then you have to pass appropriate COLUMN NAMES list in INSERT statement. By seeing your main post coding and error you have mentioned now; it seems, you are passing local variables name instead in column list! And one more thing I have changed ename to lname in VALUES clause too.
                    • 7. Re: Getting error pls-00103
                      990187
                      hello bro,
                      There are no compilation errors but it is now giving me invalid cursor when i execute the procedure. .at line 18
                      • 8. Re: Getting error pls-00103
                        S10390
                        Post the exact steps in detail how you are executing the procedure with the code and error message.
                        • 9. Re: Getting error pls-00103
                          990187
                          1 create or replace procedure ems
                          2 (pname IN employees.last_name%type) -- Added newly
                          3 is
                          4 cursor cur(ename varchar2)
                          5 is select last_name,hire_date,salary,months_between(sysdate,hire_date)/12 years
                          6 from employees
                          7 where last_name = ename;
                          8 hdate date;
                          9 years number;
                          10 sal number;
                          11 lname varchar(40);
                          12 not_due_for_raise exception;
                          13 begin
                          14 --open cur;
                          15 open cur(pname); -- added : need to pass parameter to open cursor as defined
                          16 loop
                          17 begin -- added
                          18 fetch cur into lname,hdate,sal,years;
                          19 if years>5 and sal<3500 and sal>25000 then
                          20 raise not_due_for_raise;
                          21 else
                          22 dbms_output.put_line('not due for a raise');
                          23 end if;
                          24 exception
                          25 when not_due_for_raise then
                          26 insert into analysis values(lname,years,sal);
                          27 end; -- added
                          28 end loop;
                          29* end;
                          30 /





                          There are no compilation errors but it is now giving me invalid cursor when i execute the procedure. .at line 18

                          o/p: execute snc('Grant');

                          after executing it is displaying as invalid cursor
                          • 10. Re: Getting error pls-00103
                            S10390
                            Observed couple of things here,

                            1) your procedure name is ems, and you are executing snc('Grant');

                            2) in 'ems' procedure add the exit stmt after fetching.
                            EXIT WHEN cur%notfound;
                            3)This condition will always false,so the control will go to the else part.
                            if years>5 and sal<3500 and sal>25000 then
                            Try to change the condition
                            if years>5 and sal>3500 and sal<25000 then
                            Try this and let us know.
                            • 11. Re: Getting error pls-00103
                              990187
                              hello bro,
                              iam getting the same error. invalid cursor in line 18. the code is below




                              create or replace procedure ems
                              (pname IN employees.last_name%type) -- Added newly
                              is
                              cursor cur(ename varchar2)
                              is select last_name,hire_date,salary,months_between(sysdate,hire_date)/12 years
                              from employees
                              where last_name = ename;
                              hdate date;
                              years number;
                              sal number;
                              lname varchar(40);
                              not_due_for_raise exception;
                              begin
                              --open cur;
                                   open cur(pname); -- added : need to pass parameter to open cursor as defined
                                   loop
                                        begin     -- added
                                             fetch cur into lname,hdate,sal,years;
                                             exit when cur%notfound;
                              if years>5 and sal>3500 and sal<25000 then
                                                  raise not_due_for_raise;
                                             else
                                                  dbms_output.put_line('not due for a raise');
                                             end if;
                                             exception
                                             when not_due_for_raise then
                                             insert into analysis values(lname,years,sal);
                                   end; -- added
                                   close cur;
                              end loop;
                              end;
                              /
                              • 12. Re: Getting error pls-00103
                                kendenny
                                You're closing the cursor inside the loop. On the second fetch, the cursor has already been closed. That's why you're getting invalid cursor.
                                • 13. Re: Getting error pls-00103
                                  Ashu_Neo
                                  Check this with correct executions and understand the code. Do some changes by yourself as requirement you know better.
                                  I have not changed your logic too.
                                  SQL> create or replace procedure ems
                                    2  (pname IN employees.last_name%type) -- Added newly
                                    3  is
                                    4  cursor cur(ename IN employees.last_name%type)
                                    5  is select last_name,hire_date,salary,months_between(sysdate,hire_date)/12 years
                                    6  from employees
                                    7  where last_name = ename;
                                    8  hdate date;
                                    9  years number;
                                   10  sal number;
                                   11  lname varchar(40);
                                   12  not_due_for_raise exception;
                                   13  begin
                                   14  --open cur;
                                   15  open cur(pname); -- added : need to pass parameter to open cursor as defined
                                   16  loop
                                   17     begin   -- added
                                   18             fetch cur into lname,hdate,sal,years;
                                   19             exit when cur%notfound;
                                   20             if years>5 and sal>3500 and sal<25000 then
                                   21             raise not_due_for_raise;
                                   22             else
                                   23             dbms_output.put_line('not due for a raise');
                                   24             end if;
                                   25             exception
                                   26             when not_due_for_raise then
                                   27             insert into analysis values(lname,years,sal);
                                   28     end; -- added
                                   29
                                   30  end loop;
                                   31  close cur;
                                   32  end;
                                   33  /
                                  
                                  Procedure created.
                                  
                                  SQL> exec ems('Grant');
                                  
                                  PL/SQL procedure successfully completed.
                                  
                                  SQL> select * from analysis;
                                  
                                  ENAME                     YEARS        SAL
                                  -------------------- ---------- ----------
                                  Grant                5.86241279       7000
                                  
                                  SQL>