1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

How to enter values on running time

Discussion in 'SQL PL/SQL' started by xmaissax, Nov 2, 2013.

  1. xmaissax

    xmaissax Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    Hello ,
    i am a beginner at pl/sql and i'm kinnda in a problem
    basically i have to write a program to display the names of the employees whom salaries the user inputs(during run time) and then handling the errors
    i think i could do everything except making the user enter the salary , i know i have to use ( =: ) but it doesn't work
    can someone explain how am i supposed to do it ?
     
  2. nilesh.dudhane

    nilesh.dudhane Active Member

    Messages:
    42
    Likes Received:
    2
    Trophy Points:
    135
    Location:
    India
    Hi
    xmaissax

    Use =& instead of =: to take value from user at run-time.
    Hope this will help you.

    Regards
    Nilesh.
     
  3. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    =: is called bind variables. Before using them we need to define.
    Instead of this we can use parameters like =&

    If we are writing just pl/sql block then we can use =&
    else if we are using any procedures or functions or packages then we can use parameters.
     
  4. xmaissax

    xmaissax Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    you know what ? the problem is i am forced to use =: "bind variables" and i am writing in a pl/sql block !
     
  5. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi
    xmaissax,

    Try like this....

    declare
    v varchar2(30):='&enter_name';
    begin
    dbms_output.put_line('Hi My name is'||' ' ||v);
    end;
     
    xmaissax likes this.
  6. xmaissax

    xmaissax Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    Hi Jagadekara,
    thank you , i now know the bigger picture and it finally worked
    i did this
    declare
    s number;
    x employees.first_name %type;
    begin
    select first_name into x from employees where salary = :s;
    dbms_output.put_line(x||s);
    exception
    when too_many_rows then
    dbms_output.put_line('there are too many employees with this salary');
    when no_data_found then
    dbms_output.put_line('sorry no employee has this income');
    end;

    the only problem now is it wont show the salary , only the name
     
  7. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    In select take salary also....
     
  8. xmaissax

    xmaissax Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    even though i only declared x as type of first name ?
     
  9. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You don't need to include the salary in the select list:

    Code (SQL):
    SQL> variable s NUMBER
    SQL>
    SQL> EXEC :s:=6100
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL>
    SQL> DECLARE
      2          x employees.first_name %TYPE;
      3  BEGIN
      4          SELECT first_name INTO x FROM employees WHERE salary = :s;
      5          dbms_output.put_line(x||'  '||:s);
      6  exception
      7          WHEN too_many_rows THEN
      8                  dbms_output.put_line('there are too many employees with this salary');
      9          WHEN no_data_found THEN
     10                  dbms_output.put_line('sorry no employee has this income');
     11  END;
     12  /
    Sundita  6100
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL>
    You have passed in a SQL*Plus variable so you do not need to declare it again in the PL/SQL block; pass it to dbms_output just like you did to the select statement.
     
    xmaissax likes this.
  10. xmaissax

    xmaissax Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    so thats my problem , i didn't include the ( : ) never gonna forget now !

    i am extremely thankful to everybody for you help . thank you very much !

    much love
     
  11. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    To be honest the way to pass runtime values to a PL/SQL block is with the & and && variables; the way you are currently doing this requires that you set a variable in a SQL*Plus session so the PL/SQL block can use it. That requires preparation -- knowing the variable name and what value to pass it before the script runs. If you do not reset the value between runs the same output is generated each time you execute the block:

    Code (SQL):
    SQL> --
    SQL> -- Declare SQL*Plus variable
    SQL> --
    SQL> variable s NUMBER
    SQL>
    SQL> --
    SQL> -- Assign value
    SQL> --
    SQL> EXEC :s:=9200
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL>
    SQL> --
    SQL> -- Execute the code, several times
    SQL> --
    SQL> -- Produces the same output since variable has not been
    SQL> -- assigned a new value
    SQL> --
    SQL> DECLARE
      2          x employees.first_name %TYPE;
      3  BEGIN
      4          SELECT first_name INTO x FROM employees WHERE salary = :s;
      5          dbms_output.put_line(x||'  '||:s);
      6  exception
      7          WHEN too_many_rows THEN
      8                  dbms_output.put_line('there are too many employees with this salary ('||:s||')');
      9          WHEN no_data_found THEN
     10                  dbms_output.put_line('sorry no employee has this income ('||:s||')');
     11  END;
     12  /
    sorry no employee has this income (9200)
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL>
    SQL> /
    sorry no employee has this income (9200)
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL>
    SQL> /
    sorry no employee has this income (9200)
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL>
    SQL> /
    sorry no employee has this income (9200)
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL>
    SQL> /
    sorry no employee has this income (9200)
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL>
    SQL> --
    SQL> -- Assign new value
    SQL> --
    SQL> EXEC :s:=8900
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL>
    SQL> --
    SQL> -- Execute the code
    SQL> --
    SQL> DECLARE
      2          x employees.first_name %TYPE;
      3  BEGIN
      4          SELECT first_name INTO x FROM employees WHERE salary = :s;
      5          dbms_output.put_line(x||'  '||:s);
      6  exception
      7          WHEN too_many_rows THEN
      8                  dbms_output.put_line('there are too many employees with this salary ('||:s||')');
      9          WHEN no_data_found THEN
     10                  dbms_output.put_line('sorry no employee has this income ('||:s||')');
     11  END;
     12  /
    sorry no employee has this income (8900)
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL>
    Using & or && variables 'fixes' this, but introduce their own issues:

    Code (SQL):
    SQL> --
    SQL> -- Use & variable
    SQL> --
    SQL> -- Single & requires you to type in variable
    SQL> -- assignment each time the variable is
    SQL> -- used
    SQL> --
    SQL> DECLARE
      2          x employees.first_name %TYPE;
      3  BEGIN
      4          SELECT first_name INTO x FROM employees WHERE salary = &s;
      5          dbms_output.put_line(x||'  '||&s);
      6  exception
      7          WHEN too_many_rows THEN
      8                  dbms_output.put_line('there are too many employees with this salary ('||&s||')');
      9          WHEN no_data_found THEN
     10                  dbms_output.put_line('sorry no employee has this income ('||&s||')');
     11  END;
     12  /
    Enter VALUE FOR s: 6100
    Enter VALUE FOR s: 6100
    Enter VALUE FOR s: 6100
    Enter VALUE FOR s: 6100
    Sundita  6100
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL>
    SQL> --
    SQL> -- Use && variable
    SQL> --
    SQL> -- Using && retains initially assigned value
    SQL> -- throughout all uses of the variable
    SQL> --
    SQL> -- The downside is the variable now needs to be
    SQL> -- undefined to accept a new value
    SQL> --
    SQL> DECLARE
      2          x employees.first_name %TYPE;
      3  BEGIN
      4          SELECT first_name INTO x FROM employees WHERE salary = &&s;
      5          dbms_output.put_line(x||'  '||&&s);
      6  exception
      7          WHEN too_many_rows THEN
      8                  dbms_output.put_line('there are too many employees with this salary ('||&&s||')');
      9          WHEN no_data_found THEN
     10                  dbms_output.put_line('sorry no employee has this income ('||&&s||')');
     11  END;
     12  /
    Enter VALUE FOR s: 6100
    Sundita  6100
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL>
    SQL> /
    Sundita  6100
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL>
    SQL> undefine s
    SQL>
    SQL> /
    Enter VALUE FOR s: 8900
    sorry no employee has this income (8900)
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL>
    You should know the different ways to pass in values to PL/SQL blocks, and the issues those methods can cause.