trigger when new record instance doesn't work

tayshaun

good to all, currently I have oracle forms version 12.2.1.4, in oracle linux, I have a form and in that form it has a period field, I have a code in that form referring to another table, for when the period status is in status ='C' , I can not edit that form but it is not working for me, this is my code:

declare

v_period_p number(7);

v_period_st number(7);

cursor period is

select p.period,p.period_st

from period p,mov h

where p.period=h.period

and p.company=h.company

group by p.period,p.period_st;

begin

open period;

fetch period into v_period_p,v_period_st;

close period;

if :movh.period=v_period_p

and v_period_st='C'

then

set_block_property('movh',update_allowed,property_false);

end if;

end;

Hub Tijhuis

ora-06502 pl/sql numeric or value error : that helps

The error occurs before the set_block_property

1)

ORA-06502 means a problem with converting a varchar to number or not enough space to store a value.

It can occur on different places in code but zooming in I expect the problem is in the definition of v_period_st : that is a number and period_st in your table is a probably a varchar2 : you can't store a value of 'C' in a number(7).

Change the definition to a varchar2 long enough or use period.period_st%type to be sure it has the same type definition as the table item.

2) you changed the name of the argument of the cursor : now you have v_period_p defined twice in your code (as variable and as argument). It wil probably work but avoid confusion and choose a different name (and keep the argument in the where and the variable in the fetch)

3) looking again at the cursor :is there only 1 record in table movh per period or are there more for each company? If yes what company should be choosen?

4) If you get an error use the information to check your code else debug by putting temporary messages to see where the errors occurs: mostly you can find the exact line of code where the problem is. If my assumption for the error is correct you would still see such message after the open , but not see a message after the fetch.

Hub Tijhuis

What is not working? You get an error or the block property is not changed? And what should happen if the trigger fires on a second row of the block and the first row is already changed?

And if your cursor period can have more then one row as result than the value of v_period_p is uncertain because you only fetch 1 row, but did not define an order by.

Maybe adding a

message('movh:' || :movh.period || ' v_period_p:' || v_period_p || ' v_period_st:' || v_period_st ||' update allowed:' || get_block_property('movh',update_allowed));

before the "end;" gives you more information

tayshaun

The first thing is that the form has a field defining the period, and the cursor is referring to that field, when the status is ='C' then that form cannot be edited, also the cursor will bring a single line, by means of the period that the block has (:movh.period=v_period_p)

Hub Tijhuis

Your cursor is not referencing the block value :movh.period , you don't specify your problem (saying it does not work is not enough) and you don't give the results of the message i suggested.

So still guessing that your cursor gives more rows than 1 (try the same select in another environment) I think you need :

cursor period (arg_p_period number) is -- add an argument to the cursor

select p.period,p.period_st

from period p,mov h

where p_period = arg_p_period -- select only the rows satisfying the argument

and p.period=h.period

and p.company=h.company

group by p.period,p.period_st;

begin

open period(:movh.period); -- open the cursor with the argument period

fetch period into v_period_p,v_period_st;

close period;

/* -- you can skip the period test here because you did that as an argument in the cursor

if :movh.period=v_period_p

and

*/

if v_period_st='C'

tayshaun

thanks for answering

the error that it presents me when I start the form it is: frm-40735 on-error trigger raised unhandled exception ora-06502

OK I will update my cursor,

tayshaun

update my cursor: but it show me the same error: frm-40735 on-error trigger raised unhandled exception ora-06502 and allows me to edit the form

declare

v_period_p number(7);

v_period_st number(7);

cursor period(v_period_p number) is

select p.period,p.period_st

from period p,movh h

where p.period=v_period_p

and p.period=h.period

and p.company=h.company

group by p.period,p.period_st;

begin

open period(:movh.period);

fetch period into v_period_p,v_period_st;

close period;

 

if :movh.period=v_period_p

and v_period_st='C'

then

set_block_property('movh',update_allowed,property_false);

end if;

end;

Hub Tijhuis

ora-06502 pl/sql numeric or value error : that helps

The error occurs before the set_block_property

1)

ORA-06502 means a problem with converting a varchar to number or not enough space to store a value.

It can occur on different places in code but zooming in I expect the problem is in the definition of v_period_st : that is a number and period_st in your table is a probably a varchar2 : you can't store a value of 'C' in a number(7).

Change the definition to a varchar2 long enough or use period.period_st%type to be sure it has the same type definition as the table item.

2) you changed the name of the argument of the cursor : now you have v_period_p defined twice in your code (as variable and as argument). It wil probably work but avoid confusion and choose a different name (and keep the argument in the where and the variable in the fetch)

3) looking again at the cursor :is there only 1 record in table movh per period or are there more for each company? If yes what company should be choosen?

4) If you get an error use the information to check your code else debug by putting temporary messages to see where the errors occurs: mostly you can find the exact line of code where the problem is. If my assumption for the error is correct you would still see such message after the open , but not see a message after the fetch.

tayshaun

thanks for the information, you are absolutely right because the v_period_st field was declared as number and it was not number it was varchar, this solved my problem thanks for everything.