parameterized SQL script

Question

SET ECHO ON
SET VERIFY ON

ACCEPT CNUMBER CHAR PROMPT 'CUSTOMER NUMBER == '
ACCEPT PNUMBER CHAR PROMPT 'PART NUMBER == '
ACCEPT OD date format 'YYYYMMDD' PROMPT 'ORDER DATE == '


DELETE FROM ORDERS WHERE CNUM = &CNUMBER AND PNUM = &PNUMBER AND ORDERDATE = to_date(&OD,'YYYYMMDD'); 

PROMPT Done.

--DELETE FROM ORDERS WHERE CNUM = 'C111' AND PNUM = 'M001' AND ORDERDATE = to_date(20130401,'YYYYMMDD');

OUTPUT:

SQL> DELETE FROM ORDERS
  2  WHERE CNUM = &CNUMBER AND PNUM = &PNUMBER AND ORDERDATE = to_date(&OD,'YYYYMMDD');
old   2: WHERE CNUM = &CNUMBER AND PNUM = &PNUMBER AND ORDERDATE = to_date(&OD,'YYYYMMDD')
new   2: WHERE CNUM = C111 AND PNUM = M001 AND ORDERDATE = to_date(20130404,'YYYYMMDD')
WHERE CNUM = C111 AND PNUM = M001 AND ORDERDATE = to_date(20130404,'YYYYMMDD')
                             *
ERROR at line 2:
ORA-00904: "M001": invalid identifier

Whats wrong??

Add some quotes: AND PNUM = 'M001' – Getz May 06 '13 at 08:20 — Getz, May 06 '13 at 08:20

GeertPt · Answer 1 · 2013-05-06T08:30:53.740

1

CNUM = '&CNUMBER' AND PNUM = '&PNUMBER'

Not sure about &OD, do you need to quote that too?

edited May 06 '13 at 08:30

answered May 06 '13 at 08:19

GeertPt

16,398
2
37
61

parameterized SQL script

1 Answers1