Problem 1:
Description:Dependent fields Enabling
In my form i have user_name, region, case_type.
User can have either region or case-type, so i want to disable case-type if region is populated, and if region is skipped and user goes on to populate case-type, it should make the previou item - region - disabled/grey.
I tried to put the following code in couple of triggers, but it is not disabling, and sometimes getting FRM-40105 error.
if :rav_regions.region is not null then
SET_ITEM_PROPERTY(':rav_regions.case_type',ENABLED ,PROPERTY_FALSE);
SET_ITEM_PROPERTY(':rav_regions.case_type',VISUAL_ ATTRIBUTE,'VA_NAME');
end if ;
---
Solutions
Option 1
WHEN-VALIDATE-ITEM for region:
---
IF :REGION IS NOT NULL THEN
:CASE_TYPE := NULL;
END IF;
WHEN-VALIDATE-ITEM for case_type:
---
IF :CASE_TYPE IS NOT NULL THEN
:REGION := NULL;
END IF;
And, if you want to always have one of the two items filled, then you can add this:
Option 2
WHEN-VALIDATE-RECORD
---
IF :REGION IS NULL AND :CASE_TYPE IS NULL THEN
MESSAGE( ' You should enter a value for either REGION or CASE TYPE ' );
RAISE FORM_TRIGGER_FAILURE;
END IF;
Option 3
should always have pre-insert and pre-update triggers to make sure that the records meet your criteria before being saved. Some people like when-validate-record triggers for this, but they have two disadvantages:
1. They fire too often.
2. They can still miss a duplicate-record problem when multiple records are entered between commits.
Write "dup_check" program unit in the Form that can be called by both the pre-insert and the pre-update trigger.
And, I like the actual SQL query to be in a stored procedure (or package) in the database that the Forms program unit simply calls and
passes the key values of the current record as parameters.
Problem 2:
Handling Duplicate records in Oracle Forms
I'm using oracle form 6i and Database 10g. I have a tabular form based on emp table.In this tabular form, how to handle duplication record.
Solution:
Create a program unit in the form with a name like "dup_check" then create a "when-validate_item" trigger and a pre-insert (plus possibly a pre-update trigger)
to call your dup_check program unit. In this program unit, either write a query directly, something like Ghulam suggested (but without the "next_item" command,
just do "null;" there instead, and correct the message to be more like this: "Error:- This record already exists") or even better, create a PL\SQL function in the database, or in a PL\SQL package in the database, that contains the query, then call this function from your "dup_check" program unit in the form.
procedure dup_check as
v_count number :=0;
begin
select count(*)
into v_count
from table
where in_damage=:entry.in_damage;
if v_count =0 then
null;
else
:global.vehicle :=v_count;
end if;
end dup_check;
----
Scenario
I have two control block i.e. class_register and student_info. In which student_info is multi record block which contains stud_id, stud_name, stud_roll_no. Based on the class_register block, stud_id and stud_name is generated in the student_info table.
Now I want to generate stud_roll_no automatically until the last stud_id is present.
Can any one tell me how to do this?
Solution 1:
You can use two methods, depending upon your specific scenario. You may
use a sequence or worn trick of picking the max student id +1 like
following. I would suggest you should go ahead with a SEQUENCE
add the trigger PRE-INSERT
--if you are using MAX trick
Select nvl(max(std_id)+1, 1000<<the number what you want to start
with>>) into
:yourblock.std_id from your student_base_table;
--
Solution 2
Create a sequence like
CREATE SEQUENCE SCOTT.STD_ID_SEQ
START WITH 1000
INCREMENT BY 1
MINVALUE 1000
MAXVALUE 9999999999
CACHE 20
NOCYCLE
NOORDER
and use the following code within PRE-INSERT
select SCOTT.STD_ID_SEQ.nextval into :yourblock.std_id from dual;
If you don't want to loose sequence numbers due to a database shutdown you can create your own sequence number control function
and store the latest number in a table.
Below is a working example.
create or replace function PROC_INV_SEQ return number
AS
PRAGMA AUTONOMOUS_TRANSACTION;
v_batch_num number(6);
CURSOR C2 is
select (nvl(a.batch_num,0) + 1)
from PM_inv_seq_num a
for update of
a.batch_num,a.batch_date;
BEGIN
LOCK TABLE PM_inv_seq_num IN EXCLUSIVE MODE;
OPEN C2;
FETCH C2 into
v_batch_num;
IF(C2%FOUND) then
UPDATE PM_inv_seq_num
set batch_num = v_batch_num,
batch_date = sysdate
where current of C2;
COMMIT;
RETURN(v_batch_num);
END IF;
END;
/
-----
How can we set item property of text item on conditional basis, for few rows it should be able to update and for few rows
it should not update?
when new record instance and check
record status if it is new or not, to avoid conditions in the insertion mode
You can use the trigger when_new_record_instance/when_new_item_instance to
write below code,
As trigger depend upon the condition of functionality of
the application. You may use another one.
begin
if :deptno =10 then
set_item_instance_property('deptno', current_record, insert_allowed,
property_false);
set_item_instance_property('deptno', current_record, update_allowed,
property_false);
set_item_instance_property('deptno', current_record, delete_allowed,
property_false);
else
set_item_instance_property('deptno', current_record, insert_allowed,
property_true);
set_item_instance_property('deptno', current_record, update_allowed,
property_true);
set_item_instance_property('deptno', current_record, delete_allowed,
property_true);
end if;
end;
------------
Problem
List of Values
I have one text item with calender LOV on my custom form.
It works fine but after selecting date from calender it shows one message box with OK button after clicking OK it works
but why is this message coming and how to skip this message?
Solution
Solution :
LOV Property Validation from the list select no
Problem Description
I have a form in which there are 32rows & 20 columns.
In new-form-instance trigger, i have added 'execute_query;' ,hence when I click on to open the form, the data was getting displayed.
Now in database, I dropped the table & re-created the same table & loaded data from an excel file to a table in database. Data is present in database.
But now when I click on to open the form, the data is not getting displayed & I am getting these errors as well.
FRM-40350 : Query caused no records to be retrieved.
FRM-40301 :Query caused no records to be retrieved. Re-enter.
Help me with this please.
Possible causes:
1)Check the datablock name.It needs to be name which is there previously
2)Check the default where condition
3)Check the parameters and run the query in TOAD or sql develeoper
Problem description:
1) I have custom form on which there is a button as 'PROCESS' after clicking it ,
new form gets open which has details information
2) There I have another button as 'START APPROVAL' after clicking this i have make UPDATE_ALLOWED PROPERTY_FALSE for all text item of this block ,
3) Then I am closing this detail form then when I am pressing 'PROCESS' button
of first(master) form I am getting error message as you cannot update this record and then detail form gets open but how to avoid this message.
Solution:
You have to set system message level to higher level when pressing Process
button. And return the level again to lowest level when returning to master
Description:Dependent fields Enabling
In my form i have user_name, region, case_type.
User can have either region or case-type, so i want to disable case-type if region is populated, and if region is skipped and user goes on to populate case-type, it should make the previou item - region - disabled/grey.
I tried to put the following code in couple of triggers, but it is not disabling, and sometimes getting FRM-40105 error.
if :rav_regions.region is not null then
SET_ITEM_PROPERTY(':rav_regions.case_type',ENABLED ,PROPERTY_FALSE);
SET_ITEM_PROPERTY(':rav_regions.case_type',VISUAL_ ATTRIBUTE,'VA_NAME');
end if ;
---
Solutions
Option 1
WHEN-VALIDATE-ITEM for region:
---
IF :REGION IS NOT NULL THEN
:CASE_TYPE := NULL;
END IF;
WHEN-VALIDATE-ITEM for case_type:
---
IF :CASE_TYPE IS NOT NULL THEN
:REGION := NULL;
END IF;
And, if you want to always have one of the two items filled, then you can add this:
Option 2
WHEN-VALIDATE-RECORD
---
IF :REGION IS NULL AND :CASE_TYPE IS NULL THEN
MESSAGE( ' You should enter a value for either REGION or CASE TYPE ' );
RAISE FORM_TRIGGER_FAILURE;
END IF;
Option 3
should always have pre-insert and pre-update triggers to make sure that the records meet your criteria before being saved. Some people like when-validate-record triggers for this, but they have two disadvantages:
1. They fire too often.
2. They can still miss a duplicate-record problem when multiple records are entered between commits.
Write "dup_check" program unit in the Form that can be called by both the pre-insert and the pre-update trigger.
And, I like the actual SQL query to be in a stored procedure (or package) in the database that the Forms program unit simply calls and
passes the key values of the current record as parameters.
Problem 2:
Handling Duplicate records in Oracle Forms
I'm using oracle form 6i and Database 10g. I have a tabular form based on emp table.In this tabular form, how to handle duplication record.
Solution:
Create a program unit in the form with a name like "dup_check" then create a "when-validate_item" trigger and a pre-insert (plus possibly a pre-update trigger)
to call your dup_check program unit. In this program unit, either write a query directly, something like Ghulam suggested (but without the "next_item" command,
just do "null;" there instead, and correct the message to be more like this: "Error:- This record already exists") or even better, create a PL\SQL function in the database, or in a PL\SQL package in the database, that contains the query, then call this function from your "dup_check" program unit in the form.
procedure dup_check as
v_count number :=0;
begin
select count(*)
into v_count
from table
where in_damage=:entry.in_damage;
if v_count =0 then
null;
else
:global.vehicle :=v_count;
end if;
end dup_check;
----
Scenario
I have two control block i.e. class_register and student_info. In which student_info is multi record block which contains stud_id, stud_name, stud_roll_no. Based on the class_register block, stud_id and stud_name is generated in the student_info table.
Now I want to generate stud_roll_no automatically until the last stud_id is present.
Can any one tell me how to do this?
Solution 1:
You can use two methods, depending upon your specific scenario. You may
use a sequence or worn trick of picking the max student id +1 like
following. I would suggest you should go ahead with a SEQUENCE
add the trigger PRE-INSERT
--if you are using MAX trick
Select nvl(max(std_id)+1, 1000<<the number what you want to start
with>>) into
:yourblock.std_id from your student_base_table;
--
Solution 2
Create a sequence like
CREATE SEQUENCE SCOTT.STD_ID_SEQ
START WITH 1000
INCREMENT BY 1
MINVALUE 1000
MAXVALUE 9999999999
CACHE 20
NOCYCLE
NOORDER
and use the following code within PRE-INSERT
select SCOTT.STD_ID_SEQ.nextval into :yourblock.std_id from dual;
If you don't want to loose sequence numbers due to a database shutdown you can create your own sequence number control function
and store the latest number in a table.
Below is a working example.
create or replace function PROC_INV_SEQ return number
AS
PRAGMA AUTONOMOUS_TRANSACTION;
v_batch_num number(6);
CURSOR C2 is
select (nvl(a.batch_num,0) + 1)
from PM_inv_seq_num a
for update of
a.batch_num,a.batch_date;
BEGIN
LOCK TABLE PM_inv_seq_num IN EXCLUSIVE MODE;
OPEN C2;
FETCH C2 into
v_batch_num;
IF(C2%FOUND) then
UPDATE PM_inv_seq_num
set batch_num = v_batch_num,
batch_date = sysdate
where current of C2;
COMMIT;
RETURN(v_batch_num);
END IF;
END;
/
-----
How can we set item property of text item on conditional basis, for few rows it should be able to update and for few rows
it should not update?
when new record instance and check
record status if it is new or not, to avoid conditions in the insertion mode
You can use the trigger when_new_record_instance/when_new_item_instance to
write below code,
As trigger depend upon the condition of functionality of
the application. You may use another one.
begin
if :deptno =10 then
set_item_instance_property('deptno', current_record, insert_allowed,
property_false);
set_item_instance_property('deptno', current_record, update_allowed,
property_false);
set_item_instance_property('deptno', current_record, delete_allowed,
property_false);
else
set_item_instance_property('deptno', current_record, insert_allowed,
property_true);
set_item_instance_property('deptno', current_record, update_allowed,
property_true);
set_item_instance_property('deptno', current_record, delete_allowed,
property_true);
end if;
end;
------------
Problem
List of Values
I have one text item with calender LOV on my custom form.
It works fine but after selecting date from calender it shows one message box with OK button after clicking OK it works
but why is this message coming and how to skip this message?
Solution
Solution :
LOV Property Validation from the list select no
Problem Description
I have a form in which there are 32rows & 20 columns.
In new-form-instance trigger, i have added 'execute_query;' ,hence when I click on to open the form, the data was getting displayed.
Now in database, I dropped the table & re-created the same table & loaded data from an excel file to a table in database. Data is present in database.
But now when I click on to open the form, the data is not getting displayed & I am getting these errors as well.
FRM-40350 : Query caused no records to be retrieved.
FRM-40301 :Query caused no records to be retrieved. Re-enter.
Help me with this please.
Possible causes:
1)Check the datablock name.It needs to be name which is there previously
2)Check the default where condition
3)Check the parameters and run the query in TOAD or sql develeoper
Problem description:
1) I have custom form on which there is a button as 'PROCESS' after clicking it ,
new form gets open which has details information
2) There I have another button as 'START APPROVAL' after clicking this i have make UPDATE_ALLOWED PROPERTY_FALSE for all text item of this block ,
3) Then I am closing this detail form then when I am pressing 'PROCESS' button
of first(master) form I am getting error message as you cannot update this record and then detail form gets open but how to avoid this message.
Solution:
You have to set system message level to higher level when pressing Process
button. And return the level again to lowest level when returning to master
