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.

Answers


To read the user input and store it in a variable, for later use, you can use sqlplus 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 sting 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

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.


you can try this too And it will work:

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

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

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


Need Your Help

Is it possible to generate plain-old XML using Haml?

ruby-on-rails xml ruby haml builder

I've been working on a piece of software where I need to generate a custom XML file to send back to a client application. The current solutions on Ruby/Rails world for generating XML files are slow...

Android SDK folder taking a lot of disk space. Do we need to keep all of the System Images?

android android-studio sdk android-sdk-tools diskspace

There are a lot of system images piling up on my disk in the android sdk folder. i hardly use the emulator. May be once in 6 months. Most of my development is directly on device. What i wanted to c...