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

pl/sql block explain

Arpitha Karanam Newbie
Currently Being Moderated

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 Guru
    Currently Being Moderated

    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 Journeyer
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

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

  • 4. Re: pl/sql block explain
    Hoek Guru
    Currently Being Moderated

    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 Journeyer
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Guru
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated

    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points