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 .

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;

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;

An error happens just when the form opens, and then the form and the whole program closes.

SOLUTION: Show an alert in when-new-form-instance trigger (e.g. err_sys('stop')). This will prevent the form from closing. Then see the error by pression CTRL-ALT-E.

A problem related to query when using master-detail relationship-

The problem is that when records are queried it got vanished from the screen as soon as the mouse is clicked on the second, third or new record except the first one.


Reason-
Oracle Forms assigns names to relationships automatically. The name is created using the names of both the master and the detail blocks. For Example if the name of the master block is Criminal_Court_Cases and the name of the detail block is Criminal_Court_Parties then the name of the relationship will be Criminal_Court_Criminal_Court. If another detail block is added, for example Criminal_Court_Accusations the name of the new relationship will also be Criminal_Court_Criminal_Court. To avoid this conflict rename the names of the blocks.

Query to display all the dates between two dates in oracle

select level id,
  to_date('01-01-2010','dd-mm-yyyy') + rownum -1
  from   dual
  where
  rownum <= to_date('27-04-2010','dd-mm-yyyy')-to_date('01-01-2010','dd-mm-yyyy')+1
  connect by level <= to_date('27-04-2010','dd-mm-yyyy') - to_date('01-01-2010','dd-mm-yyyy');

How to use Oracle Applications

1. For the first time use

While accessing the application from your PC for the first time ensure the
following

•Contact your system administrator for entering host name details into your PC

•That your machine is loaded with jinitiator tool . This will automatically
down load the tool when the forms are opened .


2. Logging On to your computer

a. Log on to your computer
b. Start your Web Browser
c. Enter the URL for your site below

For Development use the URL below

http://almapp.alm.com:8004/OA_HTML/AppsLocalLogin.jsp

For Development use the URL below

http://almapp.alm.com:8002/OA_HTML/AppsLocalLogin.jsp

For Prouction use the URL below

http://almapp.alm.com:8008/OA_HTML/AppsLocalLogin.jsp

d. Click on ------------------------
e. Enter your User Id and Password

User ID:
Password:


3. Changing Your Password

To change your password, use the following steps:
1.Click the Edit menu.
2.Select the Preferences option.
3.Select the Change Password option to display the Password Update window.


4. Getting Started

a) When you log in to Oracle Applications your E–Business Suite Home is
displayed. E–Business Suite Home displays all the responsibilities, which is
assigned to you.

b) Select the responsibility link to view its menu of functions on the adjacent pane

c) Select the functions you want to start working on (Ex: Requests). This will
launch two more windows for oracle forms.


5. Navigation

The forms that you can navigate to are displayed in a navigation list on the left–hand
side of the Navigate window. The navigation list is organized much like the hierarchy of
a file system.

Where ever you can expand, the items begin with a plus sign (+) to further sub–levels
until you find your form of interest. Sub–levels appear indented below the items from
which they are expanded. Items that are expanded are preceded by a minus sign (–).
You can expand no further when an item displays neither a plus or minus sign.


To expand or collapse the navigation list

1. Choose one of the following methods to expand an expandable item to its next
sub–level:
• Double–click on the item.
• Select the item and choose Open.

2. To collapse an expanded item: Double–click on the item.

3. Te following Icons can also be used

Sl. No	Icon	Description
1		To expand an expandable item to its next sub–level
2		To collapse an expanded item
3		Expand All Children: expand all the sub–levels of the currently selected item
4		Expand All: expand all the sub–levels of all expandable items in the navigation list
5		Collapse all: collapses all currently expanded items in the navigation list

6. To Open a Form

Double–click directly on the form to open it.
Ø Open the to any form (Ex supplier form)
Ø Switch to query mode. Retrieve a supplier
Ø Tab through the tabs and sub-screens, including Supplier Sites
Ø Close. Ask the user to do the entire routine by themselves
Ø Do the same with the invoice form.
Ø Have them select a value from a simple LOV.
Ø Have them select a value from a LOV – using combinations. Ex. Account
code.

7. Using the Top Ten List

If there are forms that you use frequently, you can copy them over to a navigation
top ten lists located on the right–hand side of the Navigate window. The top ten lists
display your forms numerically so you can choose them instantly without having to
search for them in the navigation list. You can add a maximum of ten forms to the top
ten lists and you can create a different top ten list for each responsibility you have
access to.

To create navigation top ten list:
a. Select a frequently used form from the navigation list.

b. Choose the arrow pointing to the Top Ten List. The form now appears in the
navigation top ten list preceded by a top ten list number.

c. If you wish to remove a form from the top ten lists, select that form in the top ten

lists and choose arrow pointing away from the Top Ten List.


8. Switching your responsibility


If you have several responsibilities and you want to work in Oracle Applications under
a different responsibility, you can do so without exiting Oracle Applications.

To switch your responsibility:

1. Click in the Navigate window or choose Navigator from the Window menu to make
the Navigate window active.

2. Choose Switch Responsibility from the File menu. If there are windows still open
in your current responsibility, Oracle Applications asks you to save your changes,
if there are any, before it closes those windows. You may cancel switching
responsibilities by choosing Cancel in the save changes window.

Note: The save changes window only appears if there are any changes to save. If
there are no changes to save, all open windows other than the Navigate window close,
and Oracle Applications displays your responsibility list.

Note: If you have only one responsibility, the Switch Responsibility menu item is
disabled.

3. Select a new responsibility from the Responsibilities window and choose OK. The
Navigate window title and contents then update to reflect the new responsibility
you chose.

Calling new form from the current oracle form.

Calling another form by pressing button in main form:-

Create a button in the current form.On its when-button-pressed trigger write the below code to call another form.
One can pass parameter  also from one form to another.

declare
the_param      varchar2(10);
   pl_id          paramlist;
   the_code       varchar2(10);
 

begin

  :System.Message_Level := 25;
  POST;  
  :System.Message_Level :=0;
  The_Param:='pram1';
  pl_id := Get_Parameter_List(The_Param);
  if not Id_Null(pl_id) then
     Destroy_Parameter_List( pl_id );
  end if;
  pl_id := create_parameter_list(the_param);


      add_parameter(pl_id,'P_QUERY',text_parameter,:PARAMETER.P_QUERY);
 
call_form('new_form_name',hide,no_replace,no_query_only,pl_id);


end;

also we can open the new form in query only mode by replacing "no_query_only" value to "query_only".

How to select last two rows of an Oracle table?

select * from (
select ROW_NUMBER()
   OVER (PARTITION BY employee_name ORDER BY emp_code desc) rnum,emp_code from your_table where employee_name='Test'
)
where rnum<=2

How to initialize a date field to system date in oracle forms?

do you want initialize a date in text item?

if so,

Set initial value $$dbdate$$

or 

Set initial value to $$date$$

Convert Arabic Hijri date to Gregorian date using nls_calendar!!

--p_hijri_date is varchar2 field
select TO_CHAR(TO_DATE(p_hijri_date,
                          'dd/mm/yyyy',
                          'nls_calendar=''English Hijrah'''),
                  'dd-mm-yyyy',
                  'nls_calendar=''Gregorian''')
from dual;


select TO_CHAR(TO_DATE('06/02/1433',
                          'dd/mm/yyyy',
                          'nls_calendar=''English Hijrah'''),
                  'dd-mm-yyyy',
                  'nls_calendar=''Gregorian''')
from dual;

output- 01-01-2012