10 Replies Latest reply: Jan 27, 2014 5:16 PM by William Robertson RSS

    pl/sql block explain

    Arpitha Karanam

      Hi Masters,

       

      I was attended one technical interview two days back. The interviewer asked me the below pl/sql output and explain the process. I said it won't give any error and answer also. He is not happy with execution process.

      Please find below pl/sql block. Please explain me how control will execution the pl/sql block?

       

      begin

         v_sal numbber(5);

             select sal into v_sal
             from emp
             where deptno=60;

      exception

           when no_data_found then
                   do something;

           end;

          begin

                    something;
                    something;

         exception

                when others then
                      something;

          end;

       

      Please advise...!!

       

      Thanks in adv.

      Ar

        • 1. Re: pl/sql block explain
          Hoek

          Doh, the declaration section is missing.

          The WHEN OTHERS is totally wrong, since it does not RERAISE the original error.

          It is a piece of PL/SQL cr@p.

          • 2. Re: pl/sql block explain
            LPS

            If there is more than one record for the deptno 60 then exception is raised and in the second part the when others will handle those exception.In PL/SQL select should return only one record.

            • 3. Re: pl/sql block explain
              Arpitha Karanam

              there are no records on deptno=60 in the emp table. then what will happend?

              • 4. Re: pl/sql block explain
                Hoek

                Here's what happens:

                 

                SQL> begin

                  2

                  3     v_sal numbber(5);

                  4

                  5         select sal into v_sal

                  6         from emp

                  7         where deptno=60;

                  8

                  9  exception

                10

                11       when no_data_found then

                12               do something;

                13

                14       end;

                15

                16      begin

                17

                18                something;

                19                something;

                20

                21     exception

                22

                23            when others then

                24                  something;

                25

                26      end;

                27  /

                   v_sal numbber(5);

                         *

                ERROR at line 3:

                ORA-06550: line 3, column 10:

                PLS-00103: Encountered the symbol "NUMBBER" when expecting one of the following:

                := . ( @ % ;

                The symbol ":=" was substituted for "NUMBBER" to continue.

                ORA-06550: line 12, column 17:

                PLS-00103: Encountered the symbol "SOMETHING" when expecting one of the following:

                := . ( @ % ;

                The symbol ":=" was substituted for "SOMETHING" to continue.

                ORA-06550: line 16, column 5:

                PLS-00103: Encountered the symbol "BEGIN"

                • 5. Re: pl/sql block explain
                  LPS

                  I think You need to learn PL/SQL how declare varibale and how it will execute.Go through the oracle documentation.

                   


                  declare
                   
                  v_sal numbber(5);
                      
                  begin


                          select sal into v_sal

                          from emp

                          where deptno=60;

                   

                  exception

                   

                        when no_data_found then

                                 NULL;

                   

                        end;

                  • 6. Re: pl/sql block explain
                    shd-hfz-syd

                    If i get the question correctly, then there is no use in executing the code when "something" is written in it..

                    To explain correctly, i will consider the first block.

                     

                    begin

                       v_sal numbber(5);

                           select sal into v_sal
                           from emp
                           where deptno=60;

                    exception

                         when no_data_found then
                                 do something;

                         end;

                     

                    There is a Syntax error, which if corrected will look like this :

                     

                    declare

                       v_sal number(5);

                    begin

                           select sal into v_sal
                           from emp
                           where deptno=60;

                    exception

                         when no_data_found then
                              NULL;

                              //   do something;

                         end;

                     

                    The symantix should also include the possibility that there will be more than one employee in any dept. so must include a too_many_rows exception.

                     

                    Hope that helps.

                    • 7. Re: pl/sql block explain
                      Hoek

                      I think You need to learn PL/SQL how declare varibale and how it will execute.

                      Yeah, I guess you're right...I totally have no clue after working with Oracle for more than a decade

                      • 8. Re: pl/sql block explain
                        AnnPricks E

                        This is for only testing purpose. See below code. In block1 I am getting no data found error and the code run successfully because i am handling the exception and doing something. If i am not handling that error then it will errored out.

                        SQL>  DECLARE

                          2   v_sal NUMBER(10);

                          3  BEGIN

                          4   ----- BLOCK 1---------

                          5   BEGIN

                          6    SELECT sal INTO v_sal FROM emp

                          7    WHERE deptno = 60;

                          8   EXCEPTION

                          9    WHEN NO_DATA_FOUND THEN

                        10     SELECT 1 INTO v_sal FROM DUAL;

                        11   END;

                        12   -------------------------------

                        13   ------ Block 2 ------------

                        14   BEGIN

                        15    NULL;

                        16   EXCEPTION

                        17    WHEN OTHERS THEN

                        18     RAISE;

                        19   END;

                        20   ------------------------------

                        21   DBMS_OUTPUT.PUT_LINE(v_sal);

                        22  EXCEPTION

                        23  WHEN OTHERS THEN

                        24   RAISE;

                        25  END;

                        26  /

                        1

                         

                        PL/SQL procedure successfully completed.

                         

                        If you want to know about exception handling then see below link

                        PL/SQL 101 : Exception Handling

                        • 9. Re: pl/sql block explain
                          rp0428
                          I said it won't give any error and answer also.

                          Tell us the FULL answer that you gave the interviewer. What do you think happens? Why?

                          • 10. Re: pl/sql block explain
                            William Robertson

                            Hoek is quite right - as presented, the code is full of errors and it appears that the compiler begins by pointing out the unexpected word 'numbber' after the undeclared variable 'v_sal', a similar issue with 'do' and 'something' at line 12, and the stray keyword 'begin' at line 16. It is not at all clear how it should be corrected to preserve the intention of the author.