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;

Creating The Oracle Cursor For Update

create or replace procedure cursorforupdate as
  cursor crec is 
    select emp_code,emp_name from emp where emp_name='xyz' for update;
    vempcode emp.emp_code%type;
    vempname emp.emp_name%type;
begin
  open crec;
  loop
    fetch crec into vempcode, vempname;
    exit when crec%notfound;
    update emp set emp_number=vempcode where current of crec;
  end loop;
commit;
  close crec;
end; 

you cannot write commit inside the loop of for update cursor.

dynamically generate the list in oracle

FIRST CREATE A LIST_ITEM EMPLOYEE_CODE IN THE EMPLOYEE_MASTER BLOCK.TO FILL THE LIST WITH VALUES RUNTIME USE THE FOLLOWING CODE:-

declare
 
  r_dname varchar2(40) := 'divn_rec';
  status number;
  groupidd recordgroup;
begin

/*First dynamically create a query*/

groupidd := create_group_from_query(R_dname,
  'select employee_NAME,employee_code  from emp');

/* tHEN POPULATE THE FROUP*/
  status := populate_group(groupidd);
  clear_list('EMPLOYEE_MASTER.EMPLOYEE_CODE');
/** populates the list with values from record group*/
  populate_list('EMPLOYEE_MASTER.EMPLOYEE_CODE',groupidd);
END ;

suppress frm-40208 form running in query-only mode message

In the On-ERROR trigger write:-

  al_id     Alert;
al_button Number;

BEGIN
if errcode=40208 then
--do nothing;
else
     
      al_button := Show_Alert( 'Error_Alert' );
      RAISE FORM_TRIGGER_fAILURE;
   end if;  
end;