CODEWITHSUNDEEP
oracle-databasesqlplus
Dan C
Dan C

Reputation: 41

Using a bind variable in an INSERT statement

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?

Upvotes: 0

Views: 4529

Answers (1)

Littlefoot
Littlefoot

Reputation: 142798

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>

Upvotes: 1

Related Questions