I am getting below error message when trying to execute F_INSERT_ORDER_Details_DATA funtion
ORA-01006: bind variable does not exist
ORA-06512: at "ADMIN.F_INSERT_ORDER_DETAILS_DATA", line 37
ORA-06512: at line 5
CREATE OR REPLACE FUNCTION F_INSERT_ORDER_Details_DATA ( P_Order_ID IN NUMBER,
P_PRODUCT_ID IN NUMBER,
P_UNIT_PRICE IN NUMBER,
P_QUANTITY IN NUMBER,
P_DISCOUNT IN NUMBER,
P_ORDER_STATUS IN VARCHAR2,
P_NOTES IN VARCHAR2)
RETURN NUMBER
IS
vCreated_By VARCHAR2 (32) := 'SYSTEM';
vCreated_On DATE := SYSDATE;
sql_stmt VARCHAR2(2048);
BEGIN
sql_stmt:='Insert into ORDER_DETAILS
(ORDER_ID,
PRODUCT_ID,
UNIT_PRICE,
QUANTITY,
DISCOUNT,
ORDER_STATUS,
CREATED_BY,
CREATED_ON)
VALUES (:Order_ID, --ORDER_ID,
:PRODUCT_ID, --PRODUCT_ID,
:UNIT_PRICE, --UNIT_PRICE,
:QUANTITY, --QUANTITY,
:DISCOUNT, --DISCOUNT,
:ORDER_STATUS, --ORDER_STATUS,
:CREATED_BY, --CREATED_BY,
:CREATED_ON) --CREATED_ON';
--DBMS_OUTPUT.PUT_LINE(sql_stmt); /* For Testing Purpose */
EXECUTE IMMEDIATE sql_stmt USING P_Order_ID,
P_PRODUCT_ID,
P_UNIT_PRICE,
P_QUANTITY,
P_DISCOUNT,
P_ORDER_STATUS,
P_NOTES,
vCreated_By,
vCreated_On;
return 1;
--exception when others then
-- raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END F_INSERT_ORDER_Details_DATA;
function calling
/* Formatted on 1/21/2017 6:40:55 PM (QP5 v5.215.12089.38647) */
DECLARE
var NUMBER;
BEGIN
var := F_INSERT_ORDER_Details_DATA (182, --P_Order_ID,
1000, --P_PRODUCT_ID,
50, ---P_UNIT_PRICE,
1, ---P_QUANTITY,
0, ---P_DISCOUNT,
'1', --P_ORDER_STATUS,
'P_NOTES');
DBMS_OUTPUT.PUT_LINE (var);
END;
SQL> CREATE OR REPLACE FUNCTION F_INSERT_ORDER_Details_DATA ( P_Order_ID IN NUMBER,
2 P_PRODUCT_ID IN NUMBER,
3 P_UNIT_PRICE IN NUMBER,
4 P_QUANTITY IN NUMBER,
5 P_DISCOUNT IN NUMBER,
6 P_ORDER_STATUS IN VARCHAR2,
7 P_NOTES IN VARCHAR2)
8 RETURN NUMBER
9 IS
10 vCreated_By VARCHAR2 (32) := 'SYSTEM';
11 vCreated_On DATE := SYSDATE;
12 sql_stmt VARCHAR2(2048);
13 BEGIN
14 sql_stmt:='Insert into ORDER_DETAILS
15 (ORDER_ID,
16 PRODUCT_ID,
17 UNIT_PRICE,
18 QUANTITY,
19 DISCOUNT,
20 ORDER_STATUS,
21 CREATED_BY,
22 CREATED_ON)
23 VALUES (:Order_ID, --ORDER_ID,
24 :PRODUCT_ID, --PRODUCT_ID,
25 :UNIT_PRICE, --UNIT_PRICE,
26 :QUANTITY, --QUANTITY,
27 :DISCOUNT, --DISCOUNT,
28 :ORDER_STATUS, --ORDER_STATUS,
29 :CREATED_BY, --CREATED_BY,
30 :CREATED_ON) --CREATED_ON';
31
32 EXECUTE IMMEDIATE sql_stmt USING P_Order_ID,
33 P_PRODUCT_ID,
34 P_UNIT_PRICE,
35 P_QUANTITY,
36 P_DISCOUNT,
37 P_ORDER_STATUS,
38 P_NOTES,
39 vCreated_By,
40 vCreated_On;
41
42 return 1;
43 END;
44 /
Function created.
SQL>
SQL>
SQL> DECLARE
2 var NUMBER;
3 BEGIN
4 var := F_INSERT_ORDER_Details_DATA (182, --P_Order_ID,
5 1000, --P_PRODUCT_ID,
6 50, ---P_UNIT_PRICE,
7 1, ---P_QUANTITY,
8 0, ---P_DISCOUNT,
9 '1', --P_ORDER_STATUS,
10 'P_NOTES');
11 DBMS_OUTPUT.PUT_LINE (var);
12 END;
13 /
DECLARE
*
ERROR at line 1:
ORA-01006: bind variable does not exist
ORA-06512: at "MCDONAC.F_INSERT_ORDER_DETAILS_DATA", line 32
ORA-06512: at line 4
P_NOTES doesn't have a matching counterpart in the insert. You have 8 binds and 9 'using' variables.