How to get input from user at runtime

Question

I want to take runtime input from user in Oracle 10g PL/SQL blocks (i.e. interactive communication with user). Is it possible?

declare
x number;
begin
x=&x;
end

this code gives error as

& can't be used in oracle 10g

Cooperative

Answer 1

To read the user input and store it in a variable, for later use, you can use SQL*Plus command ACCEPT.

Accept <your variable> <variable type if needed [number|char|date]> prompt 'message'

example

accept x number prompt 'Please enter something: '

And then you can use the x variable in a PL/SQL block as follows:

declare 
  a number;
begin
  a := &x;
end;
/

Working with a string example:

accept x char prompt 'Please enter something: '

declare 
  a varchar2(10);
begin
  a := '&x';   -- for a substitution variable of char data type 
end;           -- to be treated as a character string it needs
/              -- to be enclosed with single quotation marks

Vedanta

:sir i have tried this code,it gives following error: ORA-00900: invalid SQL statement – Cooperative

sir is it possible to take input directly, without using SQLPLUS. – Cooperative

i m running it on oracle 10g database express edition.not on sqlplus – Cooperative

sqlplus - it's a default oracle database client that can run SQL and PL/SQL commands and display their results. What client do you use? – Vedanta

yeah sir i m using oracle 10g ,i m running program on GUI based page 127.0.0.1:8081/apex/f?p=4500:1003:3308902186929148::NO:1003:: – Cooperative

yeah i know sqlplus is tool provided by oracle 10g.but i m running program on GUI based Editor(start-> programs ->Oracle Database 10g Express Edition->Go To Database Home Page) – Cooperative

Then make your X variable a bind variable. For example: a := :x. The window for accepting your data will appear after you press run. – Vedanta

For number datatype, it works perfectly how about few examples for string, varchar,varchar2 datatype. – Gripper

@Gripper Example is added. – Vedanta

Answer 2

you can try this too And it will work:

DECLARE
  a NUMBER;
  b NUMBER;
BEGIN
  a := &aa; --this will take input from user
  b := &bb;
  DBMS_OUTPUT.PUT_LINE('a = '|| a);
  DBMS_OUTPUT.PUT_LINE('b = '|| b);
END;

Kibler

Gives ORA-01008: not all variables bound ORA-06512: at "SYS.DBMS_SQL", line 1721 error in Oracle Live SQL – Budworth

This code doesn't work. It should read a:= &aa; b=&bb; Then SQL will prompt for aa and bb and assign it to a and b. – Morpho

Answer 3

    a number;
    b number;
    begin
    a:= :a;-- instead of "&" use ":" here 
    b:= :b;
    if a>b then
    dbms_output.put_line('Large number is '||a);
    else
    dbms_output.put_line('Large number is '||b);
    end if;

end;

Actually when I tried this it worked perfectly and actually "&" is giving error so you can use ":".

hope it you got answer :)

Ringleader

As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center. – Blockade

Answer 4

That is because you have used following line to assign the value which is wrong.

x=&x;

In PL/SQL assignment is done using following.

:=

So your code should be like this.

    declare
    x number;
    begin
    x:=&x;
-- Below line will output the number you received as an input
    dbms_output.put_line(x);
    end;
    /

Backstop

Answer 5

declare
a number;
b number;
begin
a:= :a;
b:= :b;
if a>b then
dbms_output.put_line('Large number is '||a);
else
dbms_output.put_line('Large number is '||b);
end if;
end;

Educatory

could you explain how does this work? Note the question was posted 5 years ago, has an accepted, well voted answer with few lines, so posting a longer approach after all this time should require some kind of comment on how it is improving the existing answers. – Uneasy

Answer 6

`DECLARE
c_id customers.id%type := &c_id;
c_name customers.name%type;
c_add customers.address%type;
c_sal customers.salary%type;
a integer := &a`   

Here c_id customers.id%type := &c_id; statement inputs the c_id with type already defined in the table and statement a integer := &a just input integer in variable a.

Charlatanry

Answer 7

SQL> DECLARE
  2     a integer;
  3     b integer;
  4  BEGIN
  5     a:=&a;
  6     b:=&b;
  7     dbms_output.put_line('The a value is : ' || a);
  8     dbms_output.put_line('The b value is : ' || b);
  9  END;
 10  /

Vivica

Answer 8

You can use this to GET and PRINT the prompted value:

    set SERVEROUTPUT ON;

   /
     accept v_x number prompt 'Please enter something: '
    declare 
            v_x NUMBER;
    begin
            v_x := &v_x;
            dbms_output.put_line('the entered value was : '  ||  v_x); 
    end;
    
    /

Tenon

Answer 9

If you are trying to do this in livesql read this . To my knowledge, this is not possible on livesql. I can't really think of a use case for this, but in any event, you can log feedback in livesql and the team will look at the request.

Epithalamium

Answer 10

TRY THIS

declare 
  a number;
begin
  a := :a;
dbms_output.put_line('Inputed Number is >> '|| a);
end;
/  

OR

declare 
  a number;
begin
  a := :x;
dbms_output.put_line('Inputed Number is >> '|| a);
end;
/

Ramiform

Answer 11

its very simple

just write:

//first create table named test....

create table test (name varchar2(10),age number(5));

//when you run the above code a table will be created....

//now we have to insert a name & an age..

Make sure age will be inserted via opening a form that seeks our help to enter the value in it

insert into test values('Deepak', :age);

//now run the above code and you'll get "1 row inserted" output...

/now run the select query to see the output

select * from test;

//that's all ..Now i think no one has any queries left over accepting a user data...

Clinquant

Please use the code-formatting or the inline code-element to make your posts more readable. – Paranoiac