This discussion is archived
11 Replies Latest reply: Aug 3, 2013 2:08 PM by GTS (DBA) RSS

Using IN OUT parameter in procedures

GTS (DBA) Journeyer
Currently Being Moderated

Hello Experts ;


Good morning all;


i am trying to  execute a program using procedure ( IN and IN OUT ) parameter.

i have a emp table i want to update perk  column details.


>> Here is coding >>


1  create or replace procedure emp_salary

  2  (id IN emp.eid%type , salary IN OUT emp.esalary%type) IS

  3  tmp_sal number;

  4  BEGIN

  5  select esalary into tmp_sal from emp  where eid=id;

  6  if tmp_sal between 10000 and 20000 THEN

  7  salary_inout := tmp_sal*1.2;

  8  else if tmp_sal between 20001 and 29999 THEN

  9  salary_inout := tmp_sal*1.8;

10  ELS IF tmp_sal > 30000 then

11  salary_inout := tmp_sal*2.0;

12* END;

SQL> /

 

Warning: Procedure created with compilation errors.

 

SQL> show errors;

Errors for PROCEDURE EMP_SALARY:

 

LINE/COL ERROR

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

10/5     PLS-00103: Encountered the symbol "IF" when expecting one of the

           following:      := . ( @ % ;   The symbol ";" was substituted for "IF" to continue.

12/4     PLS-00103: Encountered the symbol ";" when expecting one of the

         following: if

 

 

MY DB version is : 10.2.0.1

emp details :


SQL> select * from emp;

 

|EID | ENAME  |  EQUAL |  ESALARY | ECITY     |  EPERK   |    

1    sona    |   mba  |  10000   | ********    |            |

2    maya   |   mca  |  15000   |  **********|            |

3    sony    |   msc   |  20000   |  ****         |            |

4    King    |   m.s   |  22000   |  **********|             |

5    ford     |   m.s   |  40000   | **********|              |

 

Thanks in advance ;

  • 1. Re: Using IN OUT parameter in procedures
    Justin Cave Oracle ACE
    Currently Being Moderated

    Your code has a number of syntax errors.

     

    The syntax of an IF statement is

     

    IF <<some condition>>
    THEN
    <<do something>>
    ELSIF <<another condition>>
    <<do something else>>
    ELSE
    <<do a third thing>>
    END IF;

     

    Your code has an ELSE IF, an ELS IF, and no END IF.  ELSE IF is potentially valid if you really want to create a second IF statement inside the first and end up with two END IF's.  I'm guessing, though, that you want a single IF statement with an ELSIF.  ELS IF with a space is invalid every time.

     

    Your code also declares a parameter named SALARY but appears to be assigning values to an undefined variable SALARY_INOUT.  I'm assuming that you intended the parameter to be named SALARY_INOUT.

     

    Your procedure also appears to be doing nothing with the input SALARY_INOUT value.  If you just want to return a value, the parameter really ought to be defined as an OUT parameter.  It makes no sense to define a parameter as an IN OUT if you're not going to use the input value.

     

    Justin

  • 2. Re: Using IN OUT parameter in procedures
    Most Wanted!!!! Journeyer
    Currently Being Moderated

    CREATE OR REPLACE PROCEDURE emp_salary (
       ID       IN       emp.eid%TYPE,
       salary   IN OUT   emp.esalary%TYPE
    )
    IS
       tmp_sal   NUMBER;
    BEGIN
       SELECT esalary
         INTO tmp_sal
         FROM emp
        WHERE eid = ID;

       IF tmp_sal BETWEEN 10000 AND 20000
       THEN
          salary_inout := tmp_sal * 1.2;
       ELSIF tmp_sal BETWEEN 20001 AND 29999
       THEN
          salary_inout := tmp_sal * 1.8;
       ELSIF tmp_sal > 30000
       THEN
          salary_inout := tmp_sal * 2.0;
       END IF;
    END;

  • 3. Re: Using IN OUT parameter in procedures
    ibney Explorer
    Currently Being Moderated

    you have not closed elseif  block.

    Please find the corrected sudo code for the same..

     

    CREATE OR REPLACE PROCEDURE emp_salary (

                                           ID             IN       emp.empno%TYPE,

                                           salary_inout   IN OUT   emp.sal%TYPE

    )

    IS

       tmp_sal   NUMBER;

     

     

    BEGIN

       SELECT sal

         INTO tmp_sal

         FROM emp

        WHERE empno = ID;

     

     

       IF tmp_sal BETWEEN 10000 AND 20000

       THEN

          salary_inout := tmp_sal * 1.2;

       ELSE

          IF tmp_sal BETWEEN 20001 AND 29999

          THEN

             salary_inout:= tmp_sal * 1.8;

          ELSE

             IF tmp_sal > 30000

             THEN

                salary_inout:= tmp_sal * 2.0;

             END IF;

          END IF;

       END IF;

    END;

  • 4. Re: Using IN OUT parameter in procedures
    ibney Explorer
    Currently Being Moderated

    you have not closed elseif  block.

    Please find the corrected sudo code for the same..

     

    CREATE OR REPLACE PROCEDURE emp_salary (

                                           ID             IN       emp.empno%TYPE,

                                           salary_inout   IN OUT   emp.sal%TYPE

    )

    IS

       tmp_sal   NUMBER;

     

     

    BEGIN

       SELECT sal

         INTO tmp_sal

         FROM emp

        WHERE empno = ID;

     

     

       IF tmp_sal BETWEEN 10000 AND 20000

       THEN

          salary_inout := tmp_sal * 1.2;

       ELSE

          IF tmp_sal BETWEEN 20001 AND 29999

          THEN

             salary_inout:= tmp_sal * 1.8;

          ELSE

             IF tmp_sal > 30000

             THEN

                salary_inout:= tmp_sal * 2.0;

             END IF;

          END IF;

       END IF;

    END;

  • 5. Re: Using IN OUT parameter in procedures
    GTS (DBA) Journeyer
    Currently Being Moderated


    Hello all ;


    Thanks for all replies.  I slightly modified  my code because to understand without  any doubt.

    nothing , just removed  salary_inout.


    >> My table  details >>


    SQL> desc emp;

    Name                                      Null?    Type

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

    EID                                                      NUMBER

    ENAME                                               VARCHAR2(15)

    EQUAL                                               VARCHAR2(10)

    ESALARY                                           VARCHAR2(15)

    ECITY                                                 VARCHAR2(15)

    EPERK                                                NUMBER

    ECONTACT_NO                                NUMBER


    >> Dummy Records  >>


    SQL> select * From emp;

     

        EID   ENAME       EQUAL      ESALARY     ECITY         EPERK  

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

         1       sona              mba         10000             XXXXXX   

         2       maya             mca         15000             XXXXXX

      

    so ,  i want  to create procedure to increment salary as per conditions.

     

    SQL> create  or  replace  procedure   emp_salary

      2  (id  IN  emp.eid%type ,

      3  salary IN OUT emp.esalary%type ) IS

      4  tmp_sal  number;

      5  BEGIN

      6  select esalary into tmp_sal from emp  where eid=id;

      7  if tmp_sal between 10000 and 20000 THEN

      8   salary := tmp_sal*1.2;

      9  else if tmp_sal between 20001 and 29999 THEN

    10   salary := tmp_sal*1.8;

    11  ELSE IF tmp_sal > 30000 then

    12   salary := tmp_sal*2.0;

    13  END IF;

    14  END IF;

    15  END IF;

    16  END;

    17  /

    Procedure created.

     

    >> Here , i am getting error  >>

     

    SQL> execute   emp_salary;

    BEGIN emp_salary; END;

          *

    ERROR at line 1:

    ORA-06550: line 1, column 7:

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

    ORA-06550: line 1, column 7:

    PL/SQL: Statement ignored

     

    My DB version is : 10.2.0.1.0  on OEL 4.4  ( Personal  database - for self studies).


    I have some doubts  please , please  .........  clear  my doubt !


    1.  when creating procedure  for  some columns  from  original table ,

      - i always use  following method .


      local_vaibale_name   <table_name>.<org_column_name>%type ; that is ,

        id  emp.eid%type;   - then why i am getting error ?


    Please  find my error and help me to resolve it.

    Thanks.




  • 6. Re: Using IN OUT parameter in procedures
    GTS (DBA) Journeyer
    Currently Being Moderated

    Hello all ;


    Thanks for all replies.  I slightly modified  my code because to understand without  any doubt.

    nothing , just removed  salary_inout.


    >> My table  details >>


    SQL> desc emp;

    Name                                      Null?    Type

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

    EID                                                      NUMBER

    ENAME                                               VARCHAR2(15)

    EQUAL                                               VARCHAR2(10)

    ESALARY                                           VARCHAR2(15)

    ECITY                                                 VARCHAR2(15)

    EPERK                                                NUMBER

    ECONTACT_NO                                NUMBER


    >> Dummy Records  >>


    SQL> select * From emp;

     

        EID   ENAME       EQUAL      ESALARY     ECITY         EPERK 

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

         1       sona              mba         10000             XXXXXX  

         2       maya             mca         15000             XXXXXX

     

    so ,  i want  to create procedure to increment salary as per conditions.

     

    SQL> create  or  replace  procedure   emp_salary

      2  (id  IN  emp.eid%type ,

      3  salary IN OUT emp.esalary%type ) IS

      4  tmp_sal  number;

      5  BEGIN

      6  select esalary into tmp_sal from emp  where eid=id;

      7  if tmp_sal between 10000 and 20000 THEN

      8   salary := tmp_sal*1.2;

      9  else if tmp_sal between 20001 and 29999 THEN

    10   salary := tmp_sal*1.8;

    11  ELSE IF tmp_sal > 30000 then

    12   salary := tmp_sal*2.0;

    13  END IF;

    14  END IF;

    15  END IF;

    16  END;

    17  /

    Procedure created.

     

    >> Here , i am getting error  >>

     

    SQL> execute   emp_salary;

    BEGIN emp_salary; END;

          *

    ERROR at line 1:

    ORA-06550: line 1, column 7:

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

    ORA-06550: line 1, column 7:

    PL/SQL: Statement ignored

     

    My DB version is : 10.2.0.1.0  on OEL 4.4  ( Personal  database - for self studies).


    I have some doubts  please , please  .........  clear  my doubt !


    1.  when creating procedure  for  some columns  from  original table ,

      - i always use  following method .


      local_vaibale_name   <table_name>.<org_column_name>%type ; that is ,

        id  emp.eid%type;   - then why i am getting error ?


    Please  find my error and help me to resolve it.

    Thanks.



  • 7. Re: Using IN OUT parameter in procedures
    Frank Kulash Guru
    Currently Being Moderated

    Hi,

     

    "PLS-00306: wrong number or types of arguments in call..." is one of those error messages that actually means what it says.  The emp_salary procedure requires 2 arguments, but you're calling it with 0 arguments.  That's the wrong number of arguments.  If emp_salary needs 2 arguments, then call it with 2 arguments, like this:

     

    DECLARE
        s emp.esalary%TYPE;
    BEGIN
        emp_salary (123, s);
    END;
    /
    
    
    
    

     

    The 1st argument, id, is an IN argument, so you can pass any kind of expression (such as the literal 123) for that argument, just so long as the expression has the right data type.

    The 2nd argument, salary is an IN OUT argument, so you must pass a variable (such as s) for that argument.


    In the procedure you posted, the 2nd argument isn't being used at all, but that's not necessarily wrong.  I assume the procedure is still being developed, and that you mean to add some code the reads and writes salary, but you want to test (and, if necessary, fix) what you've written so far.  That's an excellent way to write code: always take baby steps.

     

    By the way, if it makes sense to compare esalary to NUMBERs (such as 10000) and to multiply esalary by NUMBERs (such as 1.2), then shouldn't esalary be defined as a NUMBER, not a VARCHAR2?

  • 8. Re: Using IN OUT parameter in procedures
    GTS (DBA) Journeyer
    Currently Being Moderated

    Hello  Frank klush ,

     

    >> Argument  datatype  >>


    EID       NUMBER

    ESALARY      VARCHAR2(15)

     

    Ques 1 :  if i declare  esalary is  varchar2 ,  how it will affect my argument ?

     

    Ques  2 : Ok  . The emp_salary procedure requires 2 arguments,  if so , here only one argument is passed  i.e.  salary only.

    what is logic here ?


                   DECLARE 

    1. s emp.esalary%TYPE; 
    2. BEGIN 
    3. emp_salary (123, s); 
    4. END

     

    Thanks  in advance, Really i don t know this logic. Please explain  little more.

    because i am learning  pl/sql.

     

    Thanks.

  • 9. Re: Using IN OUT parameter in procedures
    Frank Kulash Guru
    Currently Being Moderated

    Hi,

     

    Sorry, I'm not ssure I understand either question.

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

     

    Hello  Frank klush ,

     

    >> Argument  datatype  >>


    EID       NUMBER

    ESALARY      VARCHAR2(15)

     

    Ques 1 :  if i declare  esalary is  varchar2 ,  how it will affect my argument ?

     

     

     

    As I said before, the salary argument isn't being used.  As long as it's not being used, it doesn't matter what datatype it has, but you must call the procedure with an argument of that datatype.

    Because you named the argument "salary", I'm guessing that it's supposed to contain a value that could be from the esalary column of the emp table.  If that's what you plan to use it for, then you have it declared correctly in the procedure, as emp.esalary%TYPE.  If you change the table defifinitin=on so that esalart is (for example) a NUMBER (which seems to be a good idea), then you won't have to re-code anything in the procedure, you just need to re-compile the procedure.

    Ques  2 : Ok  . The emp_salary procedure requires 2 arguments,  if so , here only one argument is passed  i.e.  salary only.

    what is logic here ?


                   DECLARE 

    1. s emp.esalary%TYPE; 
    2. BEGIN 
    3. emp_salary (123, s); 
    4. END

     

    Thanks  in advance, Really i don t know this logic. Please explain  little more.

    because i am learning  pl/sql.

     

    Thanks.

    Where is only one argument passed?

    In your original code, zero arguments were passed.

    In the anonymous block above, two arguments are passed:

    (1) the literal NUMBER 123.  The 1st formal argument, id, has the data type of emp.eid%TYPE, and emp.eid was declared a NUMBER, so 123 is a perfectly good value to pass as an IN argument.

    (2) the variable s.  The 2nd formal argument, salary, is defined as emp.esalary%TYPE, and that's exactly how s is defined, so s is a perfectly good variable to pass as an IN OUT argument.

  • 10. Re: Using IN OUT parameter in procedures
    GTS (DBA) Journeyer
    Currently Being Moderated

    @ Hello Frank  klush ...

     

    I can understand  you are always trying to  say  well answer. It's good  for me.

    My thanks for your  well reply.

     

    Please  my expectation and Goal  is different . I'm starting new thread .

    please see there and please reply on that thread  because you  have answered this thread  ,

    so easily you can  capture what's my expectation.

     

    Please see  my new thread on SQL/PLSQL discussions ...

    Thanks.

     

    MY AIM IS  " please see this task  => " Using IN OUT parameter in procedures :"  (bottom of the page )

    http://plsql-tutorial.com/plsql-passing-parameters-procedure-function.htm

     

    i'm planning  to  update eperk  column  in emp  table using procedure.

    so i  am testing some operations on salary column.

     

    My original goal is  " need to update eperk"

    - please  dont get confused.

     

    So , before going to my original goal , i did few tests on salary column.

    that's it.

     

    However thanks !

  • 11. Re: Using IN OUT parameter in procedures
    GTS (DBA) Journeyer
    Currently Being Moderated

    @ frank klush

     

    please see this link

    https://forums.oracle.com/thread/2566451

     

    Thanks.

Legend

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