9 Replies Latest reply: Aug 1, 2013 4:36 PM by Frank Kulash RSS

    in out parameter in pl/sql

    GTS (DBA)

      Good Morning Experts ;

       

      i have a doubt on following procedure program.

      I want to know how the program executed by cursor ?

       

      After declaration part , when executing this program control goes to where exactly  first ?

                       -  block1  or  block 2

      - If my question is very very silly , please  apologize me !

       

      DECLARE

         a number;

         b number;

         c number;

      PROCEDURE findMin(x IN number, y IN number, z OUT number) IS

      <<BLOCK1>>

      BEGIN

         IF x < y THEN

            z:= x;

         ELSE

            z:= y;

         END IF;

      END;

      <<BLOCK2 >>

      BEGIN

         a:= 23;

         b:= 45;

         findMin(a, b, c);

         dbms_output.put_line(' Minimum of (23, 45) : ' || c);

      END;

      /

      Minimum of (23, 45) : 23

      PL/SQL procedure successfully completed.

       

      Is this true ?


      Whenever we  execute any SQL statement ( including the PL/SQL block itself as a whole ).

      Oracle  will ALWAYS open a cursor for that statement.  so we cannot guess how oracle handle this program.



      Thanks in advance.

        • 1. Re: in out parameter in pl/sql
          michaelrozar17

          First it goes to Block 2

          • 2. Re: in out parameter in pl/sql
            Frank Kulash

            Hi,

             

            Sorry, it's not clear what you're asking. The thread title mentions IN OUT; is there really a question about how the IN OUT argument z is working in your code?

             

             

            8f953842-815b-4d8c-833d-f2a3dd51e602 wrote:

             

            Good Morning Experts ;

             

            i have a doubt on following procedure program.

            I want to know how the program executed by cursor ?

            There's no cursor in the code you posted.  Is this question really about a cursor?

             

            After declaration part , when executing this program control goes to where exactly  first ?

                            -  block1  or  block 2

            - If my question is very very silly , please  apologize me !

             

            DECLARE

              a number;

              b number;

              c number;

            PROCEDURE findMin(x IN number, y IN number, z OUT number) IS

            <<BLOCK1>>

            BEGIN

              IF x < y THEN

                  z:= x;

              ELSE

                  z:= y;

              END IF;

            END;

            <<BLOCK2 >>

            BEGIN

              a:= 23;

              b:= 45;

              findMin(a, b, c);

              dbms_output.put_line(' Minimum of (23, 45) : ' || c);

            END;

            /

            Minimum of (23, 45) : 23

            PL/SQL procedure successfully completed.

             

            Is this true ?

            Finally, a clear question! Yes, 23 really is the minimum of 23 and 45.


            Whenever we  execute any SQL statement ( including the PL/SQL block itself as a whole ).

            Oracle  will ALWAYS open a cursor for that statement.  so we cannot guess how oracle handle this program.



            Thanks in advance.

             

            You can add calls to dbms_output.put_line so you can see (not guess) how the code is being executed. For example:

             

            DECLARE

               a number;

               b number;

               c number;

               PROCEDURE findMin(x IN number, y IN number, z OUT number) IS

                 -- <<BLOCK1>>

              BEGIN

                  dbms_output.put_line ('Entering BLOCK1');

                     IF x < y THEN

                            z:= x;

                     ELSE

               z:= y;

                     END IF;

                     END;

            -- <<BLOCK2 >>

            BEGIN

               dbms_output.put_line ('Entering BLOCK2');

               a:= 23;

               b:= 45;

               findMin(a, b, c);

               dbms_output.put_line(' Minimum of (23, 45) : ' || c);

            END;

            /

             

            I had to comment out the labels; they're both illegal in my versin of Oracle.

            The output is

             

            Entering BLOCK2

            Entering BLOCK1

            Minimum of (23, 45) : 23

             

            The procedure findMin is not executed until it is called.

            • 3. Re: in out parameter in pl/sql
              GTS (DBA)

              Hi  

               

              >> code little  modified  , but i am getting error , please help me to sort out  >>

               

              1  DECLARE

                2  a number;

                3  b number;

                4  c number;

                5  PROCEDURE findmin(x in number, y in number , z out number) IS

                6  BEGIN

                7  a:=20;

                8  b:=40;

                9  findmin(a, b, c);

              10  DBMS_OUTPUT.PUT_LINE(c);

              11  END;

              12  BEGIN

              13  if x < y then

              14  z:=x;

              15  else

              16  z:=y;

              17  end if;

              18* end;

              SQL>

              SQL> /

              if x < y then

                 *

              ERROR at line 13:

              ORA-06550: line 13, column 4:

              PLS-00306: wrong number or types of arguments in call to 'X'

              ORA-06550: line 13, column 1:

              PL/SQL: Statement ignored

              • 4. Re: in out parameter in pl/sql
                SomeoneElse

                A little judicious formatting and indenting can help:

                 

                DECLARE

                   a number;

                   b number;

                   c number;

                   PROCEDURE findmin(x in number, y in number , z out number) IS

                   BEGIN

                      a:=20;

                      b:=40;

                      findmin(a, b, c);

                      DBMS_OUTPUT.PUT_LINE(c);

                   END;

                BEGIN

                   if x < y then

                      z:=x;

                   else

                      z:=y;

                   end if;

                end;

                 

                 

                 

                It looks to me like you have your BEGIN/END code blocks completely switched around.  Notice how you're calling your procedure inside itself.

                • 5. Re: in out parameter in pl/sql
                  Frank Kulash

                  Hi,

                   

                  It helps if you indent your code, to show the extent of procedure, BEGIN blocks and IF statements.  (Maybe you did; this site now makes it really hard to post formatted code.)

                   

                  Here's your code formatted,with a couple of comments added:

                   

                  DECLARE

                      a  NUMBER;

                      b  NUMBER;

                      c  NUMBER;

                      PROCEDURE findmin (x IN NUMBER, y IN NUMBER , z OUT NUMBER) IS

                          BEGIN          -- x, y and z are in scope below

                              a := 20;

                              b := 40;

                              findmin (a, b, c);

                              DBMS_OUTPUT.PUT_LINE (c);

                         END;  -- x, y and z are in scope above

                  BEGIN    -- I'm calling this the "main" BEGIN statement

                      IF x < y THEN

                          z := x;

                      ELSE

                          z := y;

                      END IF;

                  END;

                   

                  The variable names x, y and z only have meaning inside the procedure findmin.  There are no variables called x, y or z defined in the main BEGIN block.

                   

                  It may be a good thing that you're getting the compilation error.  If you ever did call findmin, you'd be in an infinite loop, since findmin calls itself without any end condition.

                  • 6. Re: in out parameter in pl/sql
                    GTS (DBA)

                    Hello  FrankKulash

                    Why  it's showing error again ?


                    SQL> DECLARE

                      2  a NUMBER;

                      3  b NUMBER;

                      4  c NUMBER;

                      5  PROCEDURE findmin (x IN NUMBER, y IN NUMBER , z OUT NUMBER) IS

                      6  BEGIN

                      7  a:=20;

                      8  b:=40;

                      9  findmin(a,b,c);

                    10  DBMS_OUTPUT.PUT_LINE (c);

                    11  END;

                    12  BEGIN

                    13  IF x < y THEN

                    14  z:=x;

                    15  ELSE

                    16  z := y;

                    17  end if;

                    18  END;

                    19  /

                    IF x < y THEN

                       *

                    ERROR at line 13:

                    ORA-06550: line 13, column 4:

                    PLS-00306: wrong number or types of arguments in call to 'X'

                    ORA-06550: line 13, column 1:

                    PL/SQL: Statement ignored

                     

                    SQL> ed

                    Wrote file afiedt.buf


                      1  DECLARE

                      2  a NUMBER;

                      3  b NUMBER;

                      4  c NUMBER;

                      5  PROCEDURE fidmin (x IN NUMBER, y IN NUMBER , z OUT NUMBER) IS

                      6  BEGIN

                      7  a:=20;

                      8  b:=40;

                      9  fidmin(a,b,c);

                    10  DBMS_OUTPUT.PUT_LINE (c);

                    11  END;

                    12  BEGIN

                    13  IF x<y THEN

                    14  z:=x;

                    15  ELSE

                    16  z:= y;

                    17  end if;

                    18* END;

                    SQL> /

                    IF x<y THEN

                       *

                    ERROR at line 13:

                    ORA-06550: line 13, column 4:

                    PLS-00306: wrong number or types of arguments in call to 'X'

                    ORA-06550: line 13, column 1:

                    PL/SQL: Statement ignored

                    • 7. Re: in out parameter in pl/sql
                      Frank Kulash

                      Hi,

                       

                      The code you posted in reply #6 is getting the same error as the code in reply #3, for the same reason.  In fact, it looks (to me) like the code in those 2 messages is exactly the same, except that the procedure is called fidmin instead of findmin in one version of the code in reply #6.  Is there any other difference between them?  Why do you expect different results?

                       

                      If it is the same code, then the answers given by Someoneelse (reply #4 above) and me (reply #5) still apply.  Basically, variable x only exists inside the procedure (lines 5-11 of your code).  There is no variable named x in scope at line 13.

                      • 8. Re: in out parameter in pl/sql
                        GTS (DBA)

                        Hello frank  klush ;

                         

                        i  am  sorry for making inconvenience .   I  don't know how can i express my question here ?

                        really i  did copy and paste someone else code.  it gives me following error.

                        i dont understand  the error. if it 's working , please show me output from your system.

                         

                        SQL> DECLARE

                          2    a number;

                          3    b number;

                          4    c number;

                          5    PROCEDURE findmin(x in number, y in number , z out number) IS

                          6    BEGIN

                          7       a:=20;

                          8       b:=40;

                          9       findmin(a, b, c);

                        10       DBMS_OUTPUT.PUT_LINE(c);

                        11   END;

                        12  BEGIN

                        13     if x < y then

                        14       z:=x;

                        15  else

                        16       z:=y;

                        17    end if;

                        18  END;

                        19  /

                           if x < y then

                              *

                        ERROR at line 13:

                        ORA-06550: line 13, column 7:

                        PLS-00306: wrong number or types of arguments in call to 'X'

                        ORA-06550: line 13, column 4:

                        PL/SQL: Statement ignored

                         

                        my db version is :

                         

                        SQL> select * from v$version;

                         

                         

                        BANNER

                        ----------------------------------------------------------------

                        Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

                        PL/SQL Release 10.2.0.1.0 - Production

                        CORE    10.2.0.1.0      Production

                        TNS for Linux: Version 10.2.0.1.0 - Production

                        NLSRTL Version 10.2.0.1.0 - Production

                         

                        OS : OEL 4.4


                        thanks  , . please post your reply.

                        i will come  tomorrow , i'm  from eastern  country.


                        • 9. Re: in out parameter in pl/sql
                          Frank Kulash

                          Hi,

                           

                           

                          8f953842-815b-4d8c-833d-f2a3dd51e602 wrote:

                           

                          ... really i  did copy and paste someone else code.  it gives me following error.

                          i dont understand  the error. if it 's working , please show me output from your system.

                          ...

                          It's NOT working.

                          Someoneelse (in reply #4) and I (in reply #5) were just posting your code, with all its mistakes, re-formatted so you could see the mistakes more clearly.

                           

                          How to fix the mistakes depends on what the code is supposed to do.