Home » Developer & Programmer » Forms » frm-40735 error occur with ora-01403
frm-40735 error occur with ora-01403 [message #662529] Fri, 05 May 2017 03:53 Go to next message
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;
Re: frm-40735 error occur with ora-01403 [message #662545 is a reply to message #662529] Fri, 05 May 2017 04:44 Go to previous messageGo to next message
cookiemonster
Messages: 13894
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.
Re: frm-40735 error occur with ora-01403 [message #662568 is a reply to message #662545] Fri, 05 May 2017 12:02 Go to previous messageGo to next message
Littlefoot
Messages: 21561
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 Go to previous messageGo to next message
mashhoodnasir
Messages: 26
Registered: May 2017
Junior Member
@littlefoot: well i simply arrange them in a single item.
Re: frm-40735 error occur with ora-01403 [message #662584 is a reply to message #662545] Sat, 06 May 2017 04:11 Go to previous messageGo to next message
mashhoodnasir
Messages: 26
Registered: May 2017
Junior Member
@cookiemonster:
do you have any other way to make such thing possible?
Re: frm-40735 error occur with ora-01403 [message #662587 is a reply to message #662584] Sat, 06 May 2017 05:36 Go to previous messageGo to next message
Littlefoot
Messages: 21561
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
CM and me were hoping that your grandmother would use

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

What other way would you want to use and why?
Re: frm-40735 error occur with ora-01403 [message #662616 is a reply to message #662545] Mon, 08 May 2017 02:56 Go to previous messageGo to next message
cookiemonster
Messages: 13894
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.
Re: frm-40735 error occur with ora-01403 [message #662629 is a reply to message #662616] Mon, 08 May 2017 05:44 Go to previous messageGo to next message
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));
Re: frm-40735 error occur with ora-01403 [message #662632 is a reply to message #662629] Mon, 08 May 2017 06:29 Go to previous messageGo to next message
Littlefoot
Messages: 21561
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Your best option is to simply try it. Doesn't cost much.
Re: frm-40735 error occur with ora-01403 [message #662700 is a reply to message #662632] Tue, 09 May 2017 23:40 Go to previous messageGo to next message
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 710 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 Go to previous messageGo to next message
Littlefoot
Messages: 21561
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.
Re: frm-40735 error occur with ora-01403 [message #662708 is a reply to message #662701] Wed, 10 May 2017 02:27 Go to previous messageGo to next message
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;
Re: frm-40735 error occur with ora-01403 [message #662722 is a reply to message #662708] Wed, 10 May 2017 03:39 Go to previous message
cookiemonster
Messages: 13894
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.
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:
  


Current Time: Wed May 27 16:26:45 CDT 2020