I need to ask the user for a variable, use that variable in a SELECT statement, and then use that same variable in an INSERT statement. Right now I am just trying to get using the variable for an INSERT to work:
var num NUMBER(10)
exec num := &ExpNum
INSERT INTO ExpDet
VALUES(num, &LineNum, &Descrip, &Amt, &HCode, &SubCode)
When I run this, I get an error saying
Bind variable 'num' is not declared.
Do I have the right idea here, or do I need to use some other method entirely?
A working example:
SQL> create table expdet
2 (num number,
3 linenum number,
4 descrip varchar2(20),
5 amt number,
6 hcode number,
7 subcode number);
Table created.
SQL> var num number
SQL> exec :num := &expnum
Enter value for expnum: 100
PL/SQL procedure successfully completed.
SQL> insert into expdet values
2 (:num, &linenum, &descrip, &amt, &hcode, &subcode);
Enter value for linenum: 1
Enter value for descrip: 'a'
Enter value for amt: 1
Enter value for hcode: 1
Enter value for subcode: 1
old 2: (:num, &linenum, &descrip, &amt, &hcode, &subcode)
new 2: (:num, 1, 'a', 1, 1, 1)
1 row created.
SQL>
