Dear All I am writing a code which is working fine in sqlplus or toad but not working in oracle forms can please anyone give me hint where i am doing worng
INSERT INTO PROD_CUT_ISSU_MST (TRANS_ID, LOC_ID, DATED, FORM_DEPT_CODE, TO_DEPT_CODE,
FINAL_IND, ISS_NUM, BUNDLE_QTY, COMP_CODE,ACTUAL_DATE)
VALUES ((select MAX(T.TRANS_ID)+1 from prod_cut_issu_mst t ), 2, TRUNC(SYSDATE),
13, 14, 'N',(prod_validations.get_autobar('PCI', 1,2 )),5,1, TRUNC(SYSDATE));
it throws this error in oracle forms but works fine plsql or toad
encountered the symbol "select" when expecting one of the following: (-+case…
>Solution :
Rewrite it to
INSERT INTO prod_cut_issu_mst (trans_id,
loc_id,
dated,
form_dept_code, --> is this really "form"? Not "from"?
to_dept_code,
final_ind,
iss_num,
bundle_qty,
comp_code,
actual_date)
SELECT MAX (t.trans_id) + 1 trans_id,
2 loc_id,
TRUNC (SYSDATE) dated,
13 form_dept_code,
14 to_dept_code,
'N' final_ind,
prod_validations.get_autobar ('PCI', 1, 2) iss_num,
5 bundle_qty,
1 comp_code,
TRUNC (SYSDATE) actual_date
FROM prod_cut_issu_mst t;
Though, note that what you’re doing is most probably wrong and it’ll fail sooner or later in a multi-user environment, when two (or more) users select thte same t.trans_id and add 1 to it. If trans_id is supposed to be unique (such as a primary key column), insert will fail for all users but one (the one that commits first).