OraFAQ Forum: Forms » frm-40735 error occur with ora-01403

Home » Developer & Programmer » Forms » frm-40735 error occur with ora-01403

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

frm-40735 error occur with ora-01403 [message #662529]

Fri, 05 May 2017 03:53

mashhoodnasir
Messages: 26
Registered: May 2017

Junior Member

I am trying to concatenate two form items and want to show their output in other item using following statement but FRM-40735 error occur. kindly help me regarding this

BEGIN
SELECT nvl(:F_NAME,'') ||' '||nvl(:L_NAME,'') INTO :FU_NAME FROM EMP;
NEXT_ITEM;
END;

Report message to a moderator

Re: frm-40735 error occur with ora-01403 [message #662545 is a reply to message #662529]

Fri, 05 May 2017 04:44

cookiemonster
Messages: 13966
Registered: September 2008
Location: Rainy Manchester

Senior Member

Why are you using a select for this?
That can only work if emp contains exactly 1 row.
It should be a straight PL/SQL assignment.

Report message to a moderator

Re: frm-40735 error occur with ora-01403 [message #662568 is a reply to message #662545]

Fri, 05 May 2017 12:02

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Quote:

concatenate two form items

Forget everything you know about anything. If "concatenate" means "put them together", imagine you asked your grandmother to "concatenate two form items" - what do you think, what would she say? What should you do?

[Updated on: Fri, 05 May 2017 12:03]

Report message to a moderator

Re: frm-40735 error occur with ora-01403 [message #662583 is a reply to message #662568]

Sat, 06 May 2017 04:10

mashhoodnasir
Messages: 26
Registered: May 2017

Junior Member

@littlefoot: well i simply arrange them in a single item.

Report message to a moderator

Re: frm-40735 error occur with ora-01403 [message #662584 is a reply to message #662545]

Sat, 06 May 2017 04:11

mashhoodnasir
Messages: 26
Registered: May 2017

Junior Member

@cookiemonster:
do you have any other way to make such thing possible?

Report message to a moderator

Re: frm-40735 error occur with ora-01403 [message #662587 is a reply to message #662584]

Sat, 06 May 2017 05:36

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

CM and me were hoping that your grandmother would use

Code: [Select all] [Show/ hide]

:fu_name := :f_name ||' '|| :l_name;

What other way would you want to use and why?

Report message to a moderator

Re: frm-40735 error occur with ora-01403 [message #662616 is a reply to message #662545]

Mon, 08 May 2017 02:56

cookiemonster
Messages: 13966
Registered: September 2008
Location: Rainy Manchester

Senior Member

I did tell you what it should be:
cookiemonster wrote on Fri, 05 May 2017 10:44

It should be a straight PL/SQL assignment.

but apparently LF had to spell it out.

Selects are for getting data from tables. If you don't need to get data from a table don't use a select.

*Exception to that is when you want to use a function that's only available in SQL and not in PL/SQL - in which case you should use a select from dual (no other table). Almost all functions exist in PL/SQL though.

Report message to a moderator

Re: frm-40735 error occur with ora-01403 [message #662629 is a reply to message #662616]

Mon, 08 May 2017 05:44

mashhoodnasir
Messages: 26
Registered: May 2017

Junior Member

yes you are right i was bit confuse.

i have another question.

is it possible to use unique constraint for two different tables but field name remain same.for example:

table1:
=======
create table abc(empno, pay_cd, amount constraint ak unique(empno, pay_cd));
table2:
=======
create table def(empno, pay_cd, amount constraint bk unique(empno, pay_cd));

Report message to a moderator

Re: frm-40735 error occur with ora-01403 [message #662632 is a reply to message #662629]

Mon, 08 May 2017 06:29

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Your best option is to simply try it. Doesn't cost much.

Report message to a moderator

Re: frm-40735 error occur with ora-01403 [message #662700 is a reply to message #662632]

Tue, 09 May 2017 23:40

mashhoodnasir
Messages: 26
Registered: May 2017

Junior Member

i have tried but not working when i use insert into statement error occur. kindly find the attachement

/forum/fa/13513/0/

[mod-edit: imaged inserted into message body by bb]

Attachment: 002.png
(Size: 13.16KB, Downloaded 3144 times)

[Updated on: Mon, 15 May 2017 15:03] by Moderator

Report message to a moderator

Re: frm-40735 error occur with ora-01403 [message #662701 is a reply to message #662700]

Wed, 10 May 2017 00:07

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Oracle didn't let you (through unique constraint) insert values that already exist in a table. That's expected behavior. I don't understand what you mean by saying that "it is not working" ...

Could you explain the question once again, please? Provide an example - you don't have to write actual SQL code, just show us what you'd want to do.

Report message to a moderator

Re: frm-40735 error occur with ora-01403 [message #662708 is a reply to message #662701]

Wed, 10 May 2017 02:27

mashhoodnasir
Messages: 26
Registered: May 2017

Junior Member

this is all what i am doing:
1st insert statement inserting data into DSCM_SAL BREAKUP table.

2nd insert statement inserting data into DSCM_ALLOW BREAKUP table.

Example:

BEGIN
INSERT INTO DSCM_SAL_BREAKUP SELECT E.EMP_NO, P.PAY_CD, E.B_SALARY*40/100 FROM DSCM_EMP E, DSCM_PAY_DEDUCTION P
WHERE NOT EXISTS (SELECT S.EMP_NO, S.PAY_CD, S.AMOUNT FROM DSCM_SAL_BREAKUP S WHERE S.EMP_NO = E.EMP_NO AND
S.PAY_CD = P.PAY_CD)
AND P.PAY_CD='OA-01';

INSERT INTO DSCM_ALLOW_BREAKUP SELECT E.EMP_NO, P.PAY_CD, S.AMOUNT*5/100 FROM DSCM_EMP E, DSCM_PAY_DEDUCTION P,
DSCM_SAL_BREAKUP S
WHERE NOT EXISTS (SELECT AL.EMP_NO, AL.PAY_CD, AL.AMOUNT FROM DSCM_ALLOW_BREAKUP AL WHERE AL.EMP_NO = E.EMP_NO AND
AL.PAY_CD = P.PAY_CD)
AND P.PAY_CD='MA-01';
END;

Report message to a moderator

Re: frm-40735 error occur with ora-01403 [message #662722 is a reply to message #662708]

Wed, 10 May 2017 03:39

cookiemonster
Messages: 13966
Registered: September 2008
Location: Rainy Manchester

Senior Member

Before we do anything else - your error message shows the constraint is in the system schema. Don't ever create objects in system or sys. They're for the oracle data dictionary and if you mess with them you might break the DB. So move all your objects out of system into a new schema right now.

Those selects are returning rows that violate the keys on your new tables. Which is to say they are returning more than one row with the same emp_no and pay_cd. Considering the complete lack of joins in the main selects that's hardly surprising.

Report message to a moderator

Previous Topic:	How to check file exist in folder
Next Topic:	WUT-113 - Too many rows error using webutil to upload doc or pdf

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Apr 29 01:22:34 CDT 2025