Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, ana.

Asked: January 21, 2017 - 4:49 pm UTC

Last updated: January 22, 2017 - 3:39 am UTC

Version: 11G R2

Viewed 10K+ times! This question is

You Asked

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;

and Connor said...

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.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, January 22, 2017 - 8:29 pm UTC


More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library