How to escape message no changes to save in Oracle Forms;

There are various ways to solve this issue:-

KEY-COMMIT  trigger :-

BEGIN
  :System.Message_Level := 25;
  COMMIT;
  :System.Message_Level := 0;
END;
and second is at on-error trigger:-
DECLARE
  V_Error_Code       NUMBER;
  V_Error_Text       VARCHAR2 (2000);
 
BEGIN
  V_Error_Code      := Error_Code;
  V_Error_Text      := Error_Text;


  IF V_Error_Code IN (40401, 40405) THEN
   
    NULL;
 
  ELSE
 
    Show_Error (V_Error_Code);
  END IF;
END;

Enquiring the database block based on a text item:

For example you want to enquire a employee record by employee name .
then set the following value in the database where clause and we can enquire the employee record by employee name.

(REGEXP_LIKE (regexp_replace(employee_name,'[[:space:]]*',''),regexp_replace(:b_parameter.employee_name,'[[:space:]]*',''))
or :b_parameter.employee_name is null)

here the :b_parameter.employee_name is the field entered by user for enquiry and employee_name is the dabase column
for the block employee.While executing query it will match the enquired text with the employee name and will
bring the output based on it.

How to use edit_textitem in Oracle forms

For a LOB or CLOB fields its difficult to display the full content in small text box. So in that case
we can always define an EDITOR under editors section of forms and
attach this editor to the text item which is CLOB or LOB.

To call this editor we can write below code:-

Go_Item('block_name.textitem');
Edit_TextItem;

Calling a loop inside loop for two non-database blocks in form:-

Here the block1 and block2 are non database items and they are not related to each other.
And we have to consider the block1 as master and block2 as detail block. so while reading the data from both the blocks we have to read all the records of block2 for each record of block1 and to achieve that we should write the below loop statement:-

go_block('block1');
first_record;
loop
rec_num:=:system.cursor_record;
if :rnum is not null then
go_block('block2');
first_record;
loop

exit when :block.2from_amt is null;
cnt_case:=GET_TOTAL_COUNT(:block1.rnum,:block2.FROM_AMT,:block2.TO_AMT);

next_record;
end loop;
go_block('block1');
go_record(rec_num);
exit when :system.last_record='TRUE';
next_record;


end if;
end loop;

How to import data from excel-sheet to a oracle table using toad

How to import data from excel-sheet to a oracle table using toad:-

Connect to the database and create a table where you want to import the data.

go to the table and right-click ,you will get an option for import data from file.go to execute wizard.

select the file.

select the delimited -character.

see the list of fields.

map the excel fields to database-table columns.

 Atlast execute,and commit. you will find the data will be transferred to the table.

Oracle report sub query selection based on parameter value

Oracle report sub query selection based on parameter value:-

minus
IF parameter_value = 'E'THEN
(RUN THIS SELECT QUERY
union
another select query)
ELSE
(RUN THIS SELECT QUERY)
END IF

I tried to use case when but it also gives error in this case

minus
SELECT CASE WHEN parameter_value = 'E' THEN (RUN THIS SELECT QUERY
union
another select query)
ELSE (RUN THIS SELECT QUERY)
END X


so, I found another way for this

minus
(RUN THIS SELECT QUERY
union
another select query where parameter_value='E')

and it works.

Oracle Tuning :Comparison of "Not in" and "minus" in Oracle select query

We have two queries :-

select * from t1
where a.t1 not in (select b from t2)

and other is,

select a from t1
minus
select b from t2


the second query will run very fast while the first one will take longer time.With MINUS, a full scan is done on both tables and the results for t2 are removed from the results for t1.

With NOT IN, a full table scan is done on t1. For each t1 row, a lookup is then done in t2. If no row is found in t2, the t1 row is returned .