What is wrong in the program?

Question

Ihave created procedures in a package... While compiling in TOAD, there were no errors but after submitting my jsp page it is showing that

""package body "USERINFO.FORM_PKG" has errors ORA-06508: PL/SQL: could not find program unit being called: "USERINFO.FORM_PKG"""

CREATE OR REPLACE PACKAGE form_pkg AS  -- package body
PROCEDURE Insert_receipts(
p_receipt NUMBER, 
p_transaction NUMBER, 
p_amount NUMBER);
PROCEDURE Insert_stkqntys(
p_itemid NUMBER, 
p_date VARCHAR2,
p_supplier VARCHAR2,
p_type VARCHAR2,
p_transaction NUMBER,
p_quantity NUMBER);
END form_pkg;
/
CREATE OR REPLACE PACKAGE BODY form_pkg AS  -- package body

PROCEDURE Insert_receipts (

 p_receipt NUMBER(6), 
 p_transaction NUMBER(5), 
 p_amount NUMBER(10),
 seq_value  NUMBER) IS
 BEGIN

 INSERT INTO receipt
 ( ID, Receipt_Number,Transaction_ID,Amount) 
  VALUES (seq.nextval, p_receipt, p_transaction, p_amount); 
END Insert_receipts
;
PROCEDURE Insert_stkqntys 
(
p_itemid NUMBER(8), 
p_date VARCHAR2(50),
p_supplier VARCHAR2(50),
p_type VARCHAR2(50),
p_transaction NUMBER(8),
p_quantity NUMBER,
seq_value  NUMBER) IS
 BEGIN
 INSERT INTO stock_quantity(ID,Item_ID,Date_Received,Supplier_Challan,Transaction_Type,Transaction_ID,Quantity)VALUES (seq.nextval, p_itemid, p_date,p_supplier,p_type,p_transaction,p_quantity);
 END Insert_stkqntys;
 End form_pkg;
/

please, have a look at the details: `select * from user_errors where name = upper('form_pkg')` — Dmitry Bychenko, Jul 18 '18 at 11:16
You can't define limits on parameters - remove the lengths of the characters/specificity of the numbers. — Ben, Jul 18 '18 at 11:17
I see different declarations: `PROCEDURE Insert_receipts(p_receipt NUMBER, ` and `PROCEDURE Insert_receipts (p_receipt NUMBER(6)` - please, notice **6**. Package and its body must have the same procedures/functions declarations — Dmitry Bychenko, Jul 18 '18 at 11:18
Getting errors after writing select * from user_errors where name = upper('form_pkg') I am getting error as PLS-00103: Encountered the symbol "(" when expecting one of the following: := . ) , @ % default character The symbol ":=" was substituted for "(" to continue. in line numbers 5,6,7,18,19,20,21,22 — mayukhsroy, Jul 18 '18 at 11:23
select object_name, object_type, object_status from user_objects where object_name = 'FORM_PKG'; connect userinfo schema and execute this query and check its returning row and whats the status — Gaj, Jul 18 '18 at 11:23
remove size in all the procedure parameters in the package body — Gaj, Jul 18 '18 at 11:25

Dmitry Bychenko · Answer 1 · 2018-07-19T07:08:19.610

Procedures'/Functions' declarations must be equal in both package and its body. Try to avoid magic numbers like p_amount NUMBER(10): what is the meaning of 10? But use p_amount receipt.Amount%Type which is clealy the type of receipt.Amount field.

CREATE OR REPLACE PACKAGE form_pkg AS  
  -- interface: procedure with 3 arguments
  PROCEDURE Insert_receipts(
    p_receipt     receipt.Receipt_Number%Type, 
    p_transaction receipt.Transaction_ID%Type, 
    p_amount      receipt.Amount%Type);

  ...
END form_pkg;
/

CREATE OR REPLACE PACKAGE BODY form_pkg AS  -- package body
  -- implementation
  -- The same three arguments (no seq_value!)
  PROCEDURE Insert_receipts (
    p_receipt     receipt.Receipt_Number%Type, 
    p_transaction receipt.Transaction_ID%Type, 
    p_amount      receipt.Amount%Type) 
  IS
  BEGIN
    INSERT INTO receipt( 
      ID, 
      Receipt_Number,
      Transaction_ID,
      Amount) 
    VALUES (
      seq.NextVal, 
      p_receipt, 
      p_transaction, 
      p_amount);
  END Insert_receipts;

  ...
END form_pkg;
/

When implementing a routine you can see errors' details (names, lines etc.) in USER_ERRORS view:

-- all (syntactic) errors in the form_pkg
select *
  from USER_ERRORS
 where Name = upper('form_pkg')

MT0 · Accepted Answer · 2018-07-18T11:33:51.623

The package specification and body should have the same arguments/data types for each procedure/function and cannot include a size/scale/precision.

Also, use [SCHEMA_NAME.]TABLE_NAME.COLUMN_NAME%TYPE rather than explicitly declaring the types. However, if you are going to use the underlying data types then just remove the precision. I.e. p_receipt NUMBER(6) should just be p_receipt NUMBER without the size/scale/precision.

CREATE OR REPLACE PACKAGE form_pkg AS  -- package specification
  PROCEDURE Insert_receipts(
    p_receipt     RECEIPT.RECEIPT_NUMBER%TYPE, 
    p_transaction RECEIPT.TRANSACTION_ID%TYPE, 
    p_amount      RECEIPT.AMOUNT%TYPE
  );

  PROCEDURE Insert_stkqntys(
    p_itemid      STOCK_QUANTITY.ITEM_ID%TYPE, 
    p_date        STOCK_QUANTITY.DATE_RECEIVED%TYPE,
    p_supplier    STOCK_QUANTITY.SUPPLIER_CHALLAN%TYPE,
    p_type        STOCK_QUANTITY.TRANSACTION_TYPE%TYPE,
    p_transaction STOCK_QUANTITY.TRANSACTION_ID%TYPE,
    p_quantity    STOCK_QUANTITY.QUANTITY%TYPE
  );
END form_pkg;
/

CREATE OR REPLACE PACKAGE BODY form_pkg AS  -- package body
  PROCEDURE Insert_receipts (
    p_receipt     RECEIPT.RECEIPT_NUMBER%TYPE, 
    p_transaction RECEIPT.TRANSACTION_ID%TYPE, 
    p_amount      RECEIPT.AMOUNT%TYPE
  )
  IS
  BEGIN
    INSERT INTO receipt (
      ID,
      Receipt_Number,
      Transaction_ID,
      Amount
    ) VALUES (
      seq.nextval, 
      p_receipt, 
      p_transaction, 
      p_amount
    ); 
  END Insert_receipts;

  PROCEDURE Insert_stkqntys 
  (
    p_itemid      STOCK_QUANTITY.ITEM_ID%TYPE, 
    p_date        STOCK_QUANTITY.DATE_RECEIVED%TYPE,
    p_supplier    STOCK_QUANTITY.SUPPLIER_CHALLAN%TYPE,
    p_type        STOCK_QUANTITY.TRANSACTION_TYPE%TYPE,
    p_transaction STOCK_QUANTITY.TRANSACTION_ID%TYPE,
    p_quantity    STOCK_QUANTITY.QUANTITY%TYPE
  )
  IS
  BEGIN
    INSERT INTO stock_quantity(
      ID,
      Item_ID,
      Date_Received,
      Supplier_Challan,
      Transaction_Type,
      Transaction_ID,
      Quantity
    ) VALUES (
      seq.nextval,
      p_itemid,
      p_date,
      p_supplier,
      p_type,
      p_transaction,
      p_quantity
    );
  END Insert_stkqntys;
End form_pkg;
/

What is wrong in the program?

2 Answers2