Hi Experts,
I am getting very strange problem here , i don't understand where is the issue and what is the solution.
Database version 11.2.0.4.0
I have a table let's say
tb_legal_arb_execution
Composite primary key (comp_code,sec_requisition_no)
Insert statement for inserting data into the table giving error ORA-00001: unique constraint violated
INSERT INTO tb_legal_arb_execution (comp_code,
requisition_no,
sec_requisition_no,
proposal_no,
sec_requisition_date,
zone_code,
region_code,
branch_code,
location_code,
sent_arb_aprv,
arb_approve_id,
arb_approve_date)
SELECT b.comp_code,
b.requisition_no,
legal_requisition_package.get_exe_requisition_no (
b.comp_code,
b.proposal_no,
b.location_code,
b.division_code,
b.requisition_date,
'15'
)
AS sec_requisition_no,
b.proposal_no,
TRUNC (SYSDATE),
b.zone_code,
b.region_code,
b.branch_code,
b.location_code,
'Y' AS sent_arb_aprv,
USER AS arb_approve_id,
TRUNC (SYSDATE) AS arb_approve_date
FROM temp_legal_arb_execution a,
tb_legal_arb_requisition b
WHERE a.comp_code = b.comp_code
AND a.requisition_no = b.requisition_no
AND a.comp_code = '000002'
AND a.requisition_no = 'LG/0018/C/0714/00004'
AND EXISTS
(SELECT 1
FROM tb_legal_arb_case_initiation c
WHERE c.comp_code = a.comp_code
AND c.requisition_no = a.requisition_no);
The get_exe_requisition_no is function which is written inside the package legal_requisition_package.
This legal_requisition_package.get_exe_requisition_no is generating the sec_requisition_no for the table tb_legal_arb_execution
which is SE/0018/C/0715/00002 is correct.
SELECT *
FROM tb_legal_arb_execution
WHERE comp_code = '000002'
AND sec_requisition_no = 'SE/0018/C/0715/00002';
no rows returned.
The result of the above query(which is used in the insert statement) is giving the correct result as expected.
SELECT b.comp_code,
b.requisition_no,
legal_requisition_package.get_exe_requisition_no (
b.comp_code,
b.proposal_no,
b.location_code,
b.division_code,
b.requisition_date,
'15'
)
AS sec_requisition_no,
b.proposal_no,
TRUNC (SYSDATE),
b.zone_code,
b.region_code,
b.branch_code,
b.location_code,
'Y' AS sent_arb_aprv,
USER AS arb_approve_id,
TRUNC (SYSDATE) AS arb_approve_date
FROM temp_legal_arb_execution a,
tb_legal_arb_requisition b
WHERE a.comp_code = b.comp_code
AND a.requisition_no = b.requisition_no
AND a.comp_code = '000002'
AND a.requisition_no = 'LG/0018/C/0714/00004'
AND EXISTS
(SELECT 1
FROM tb_legal_arb_case_initiation c
WHERE c.comp_code = a.comp_code
AND c.requisition_no = a.requisition_no);
OutPut
----------
| COMP_CODE | REQUISITION_NO | SEC_REQUISITION_NO | PROPOSAL_NO | TRUNC(SYSDATE) | ZONE_CODE | REGION_CODE | BRANCH_CODE | LOCATION_CODE | SENT_ARB_APRV | ARB_APPROVE_ID | ARB_APPROVE_DATE |
| 000002 | LG/0018/C/0714/00004 | SE/0018/C/0715/00002 | PG/0018/C/11/000102 | 22-Jun-2016 | 000002 | 000012 | 000018 | 0018 | Y | USER | 22-Jun-2016 |
If the insert statement written directly like this then record is inserted successfully.
INSERT INTO tb_legal_arb_execution (comp_code,
requisition_no,
sec_requisition_no,
proposal_no,
sec_requisition_date,
zone_code,
region_code,
branch_code,
location_code,
sent_arb_aprv,
arb_approve_id,
arb_approve_date)
SELECT b.comp_code,
b.requisition_no,
'SE/0018/C/0715/00002',
b.proposal_no,
TRUNC (SYSDATE),
b.zone_code,
b.region_code,
b.branch_code,
b.location_code,
'Y' AS sent_arb_aprv,
USER AS arb_approve_id,
TRUNC (SYSDATE) AS arb_approve_date
FROM temp_legal_arb_execution a,
tb_legal_arb_requisition b
WHERE a.comp_code = b.comp_code
AND a.requisition_no = b.requisition_no
AND a.comp_code = '000002'
AND a.requisition_no = 'LG/0018/C/0714/00004'
AND EXISTS
(SELECT 1
FROM tb_legal_arb_case_initiation c
WHERE c.comp_code = a.comp_code
AND c.requisition_no = a.requisition_no);
OutPut
-----------
1 row inserted
Why the record is not inserting into the table while using the package and function ?
Any help is really appreciated.