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