I’m running a stored procedure in oracle11g that populates data in different tables. The procedure is created with no errors,when i try to execute the procdure I get the following messages: 1. Probe: Timeout Occurred 2. Probe: Exception raised in the DBMS_DEBUG package.I have tried resetting the probe timeout to higher values which din work for me
Why do I get this message and what can I do to correct the situation?
…
CREATE OR REPLACE PROCEDURE DATAMAN.sp_savesalesbill (
iv_bill_no NUMBER,
iv_jobsheet_id NUMBER,
iv_customer_id NUMBER,
iv_name VARCHAR2,
iv_phone1 VARCHAR2,
iv_phone2 VARCHAR2,
iv_phone3 VARCHAR2,
iv_phone4 VARCHAR2,
iv_email VARCHAR2,
iv_remarks VARCHAR2,
iv_created_date VARCHAR2,
iv_bill_date VARCHAR2 ,
iv_net_amt NUMBER,
iv_bill_type NUMBER,
iv_delivered_by NUMBER,
iv_prod_id varchar2,
iv_serial_no VARCHAR2,
iv_part_code VARCHAR2,
iv_service number,
iv_amt number,
iv_discount number,
iv_discount_approved_by number,
–iv_voucher_number varchar2,
iv_client_status number,
iv_issue_date VARCHAR2,
iv_scheme_name VARCHAR2,
iv_prod_cat_id number,
iv_discount_percent number,
iv_flat_discount number,
iv_expiry_date VARCHAR2,
iv_curr_date VARCHAR2,
iv_voucher_id VARCHAR2,
iv_scheme_id number
)
IS
–lv_customer_id VARCHAR2 (25);
lv_cust_name VARCHAR2 (50);
lv_prod_id varchar2(50);
i NUMBER;
n NUMBER;
pos NUMBER;
r NUMBER;
LN_CUST_ID VARCHAR2(10);
BEGIN
i := 1;
n := LENGTH (iv_prod_id);
r := 1;
INSERT INTO customers
(customer_id, NAME, phone1, phone2, phone3, phone4, email,remarks)
SELECT iv_customer_id, iv_name, iv_phone1, iv_phone2, iv_phone3,
iv_phone4, iv_email, iv_remarks
FROM DUAL
WHERE NOT EXISTS (SELECT *
FROM customers
WHERE customer_id = iv_customer_id);
INSERT INTO bill_header
(bill_id, bill_number, js_id,
customer_id, created_date, bill_date, net_amount,
bill_type, delivered_by
)
VALUES (billid_seq.NEXTVAL, iv_bill_no, iv_jobsheet_id,
iv_customer_id, to_date(iv_created_date,‘dd-mm-yy’) ,SYSDATE, iv_net_amt,
iv_bill_type, iv_delivered_by
);
WHILE (i < n)
LOOP
pos := (INSTR (iv_prod_id, 1, r) - i);
IF pos < 0
THEN
pos := n;
END IF;
SELECT SUBSTR (iv_prod_id, i, pos)
INTO lv_prod_id
FROM DUAL;
INSERT INTO bill_item
(bill_id, product_id, serial_number,
part_code, service, amount, discount,
discount_approved_by, voucher_number
)
VALUES (billid_seq.CURRVAL, lv_prod_id, iv_serial_no,
iv_part_code, iv_service, iv_amt, iv_discount,
iv_discount_approved_by, iv_voucher_id
);
i := i + LENGTH (lv_prod_id) + 1;
r := r + 1;
END LOOP;
INSERT INTO vouchers
(voucher_number, status, expiry_date,
issued_date, scheme_id
)
VALUES (voucher_seq.NEXTVAL, iv_client_status, to_date(iv_expiry_date,‘dd-mm-yy’),
to_date(iv_issue_date,‘dd-mm-yy’), iv_scheme_id
);
INSERT INTO schemes
(scheme_id, scheme_name, category_id,
discount_percentage, flat_discount
)
VALUES (scheme_seq.NEXTVAL, iv_scheme_name, iv_prod_cat_id,
iv_discount_percent, iv_flat_discount
);
COMMIT;
END sp_savesalesbill;
/
