How to get input from user at runtime

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

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...

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;
/

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;
/

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;

`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.

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

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 /

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;

/

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.