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 .

How to attach a calendar to the form

How to attach a calendar to Oracle Form:-

It is one of the method which is widely used to attach calendar to a form.

For this you need certain components like :-

1. CALENDAR.olb download CALENDAR.olb
2. Start the Forms Builder and create a new Forms Module.
3. Open the CALENDAR.olb and double-click on the CALENDAR node in the Object Libraries.

4. From the CALENDAR object library pane, click on CALENDAR and drag and drop it on the Object Groups node of your new form. 

5. Once copied, you can see the following objects in the Forms Object Navigator . 

               6.   Create a new Data Block .Ensure this block appears first in the list before the     DATE_CONTROL_BLOCK and DATE_BUTTON_BLOCK blocks.   

                 7. Add a date field and a button to it.

  

\
8..       Write the following code to call the calendar in the when-button-pressed trigger of button.

9.   Now compile and run the form.

Compare dates in Oracle Forms:-

Compare dates in Oracle Forms:-
Forms will automatically adjust the date format depending on your max length or your format mask. Only thing is, not sure how different NLS settings will affect your individual needs.

The best way to copmare two dates is to convert them into numbers and compare.For eg.

select Emp_code,Emp_name from employee
where to_number(to_char(join_date,'yyyymmdd'))=to_number(to_char(sysdate,'yyyymmdd'))

Sometimes we store the DAY in our database column like Absent_day='MONDAY' and the oracle give different results depending on the nls setting.For eg.
Sometimes ,

select * from employee where absent_day=to_char(sysdate,'DAY') will not work as the nls lang settings are different or other than english.
so in such situations we have to write:-

select * from employee where absent_day=(LTRIM(RTRIM(TO_CHAR(sysdate ,'DAY','NLS_DATE_LANGUAGE=ENGLISH'))

One more comparision is to compare an parameter date with two existing date columns in a table:-
select* from employee_date
where to_number(to_char(:p_e_date,'yyyymmdd'))
between to_number(to_char(employee_date_from ,'yyyymmdd'))
and  to_number(to_char(employee_date_to,'yyyymmdd'));

I hope all the date related comparision must be solved .

Display Oracle Report in Excel/SpreadSheet:-

Display Oracle Report in Excel/SpreadSheet:-

A complete syntax example to run Reports from a browser looks like this:-

http://<server>:<port>/reports/rwservlet? keyname&report=<report>.rdf&desformat=[htmlcss|pdf|xml|delimited|]&destype=cache&paramform=[no|yes]

In the cgicmd.dat file define the keyname as :-

testxls: userid=test/test@testdb destype=cache desformat=spreadsheet %*

DECLARE
REP_NAME varchar2 (2000);
rep_url varchar2 (4000);
BEGIN
                                                             
                                REP_NAME:='d:\ PROJECTS\ code\'||'MYFIRSTREPORT.rdf';                                      
                                rep_url:='http://localhost:8890'||'/reports/rwservlet?testxls&module='||REP_NAME                        
                                ||'&p_emp_code='||:BLOCK_NAME.EMP_CODE;
                                                                                                                                                                                                                             
                                WEB.SHOW_DOCUMENT(rep_url,'_blank');
END;

At runtime the program will ask you to open the excelsheet ,open it and you can see the out put in excel format.

How to update one oracle table based on condition of other two tables:-

update t1  set t1.code=
(
select t3.code  from  t2 ,t3
where t2.id=t3.id
and t2.fcod=t3.fcod
and t3.mname=t1.mname)
where  t1.code is null

How to check the duplicate records are entered in Oracle Detail block at runtime?

At runtime while doing data entry or while saving multiple records entry we need to check the duplicate records are entered or not ,if yes then display an error message for the same.

To check the duplicate records the following method can be used:-

The technique used to solve this problem comes from the Kevin D Clarke’s calculated item famous solution.

It uses two calculated items, one in the data bock and another in a control block.

The first calculated item (:DEPT.MATCH_FOUND) is added to the DEPT block. It contains the formula as follow:

Comparaison(:ctrl.charsave, :dept.deptno||:dept.dname)

Notice in this case,that we want to avoid duplicates on both DEPTNO and DNAME values.

Function COMPARAISON (val1 varchar2, val2 varchar2)

Return number

Is

   answer number := 0;

Begin

   if val1 = val2 then

      answer := 1;

   end if;

   return(answer);

End;

COMPARAISON is a program unit stored in the Forms module.

The two values are compared to each other, then the function returns 1 (a value greatest than 0) if both the values are identical.

The first value (:ctrl.charsave) contains the bakup value of the current record.

The DEPT block must have the following properties setting:

Query all records

YES

The CTRL block must have the following properties setting:

Query all records	YES
Single record	YES
Database data block	NO

The second calculated item (:CTRL.MATCH_FOUND) is added to the CTRL block.

It summarize the values contained in all the rows of the DEPT block (dept.match_found).

If the total is greater than 1, we have two duplicated data.

How to validate in detail-oracle block that atleast one record is entered?

Sometimes we have to put validation that based on some condition in master block the detail block should have at least one record or zero records.
To apply this validation ,we should first create a total_count number field in detail block.This field is the non-database summarized item calculating the total number of records in detail block.

In the key-commit we will write the following code to validate our condition:-

go_block('master-block');

if :master-block.chk_item_in_detail_blk='T' then
if :detail-block.total_count=0 then
message('Enter at least one item in detail block');
raise form_trigger_failure;
end if;
end if;