How to Add or Remove List Text Anywhere from List Item?

Dear Mates,
Hope your are fine. Here we will talk a trick of Oracle List Item for Forms Version 10g and 6i.

If you want to remove list text and values from a oracle forms list item. How will you do this ? When there is no remove icons and Backspace and Delete key doesn't work, both just lest blank spaces.

Don't worry. I'm here for your help. Try the following shortcut key..

"Ctrl + Shift + >" - add list element.

"Ctrl + Shift + <" - remove list element.

Leave comment and SMILTE :)

How to Check Only One Item In a Check Box ?

Hello Redars, How are you ?

Here i will share a tricky PL/SQL code with you. If you want to use Check Box in oracle forms and want to restrict end user to select only one check box, how will you handle this ?

Just customize the following code and you will get the result.

declare
  l_current_record number;
  l_last_record    number;
begin
  l_current_record := :system.trigger_record;

  last_record;
  l_last_record := :system.cursor_record;
 
  first_record;
   
  for i in 1 .. l_last_record loop
    if :system.cursor_record <> l_current_record then
       :SM_ACT_FLAG := 0;
    end if;
    next_record;
  end loop;
 
  go_record(l_current_record);
end;

If you have any question regarding this..just leave comments and i will answer.

How to Calculate Average of Date Values ?

Do you getting trouble of calculating average time of a filed, where data type is date time ?
Don't worry. You are at right place to get right solution.

I'm calculation of average time of a filed. If there is two time value in a day, i will calculate the first one means minimum one.

SELECT TO_CHAR(TRUNC(SYSDATE)+AVG(VDATE-TRUNC(VDATE)),'HH24:MI:SS')
FROM (SELECT MIN(DDATE) VDATE
FROM DEPT
GROUP BY TRUNC(DDATE))

Make changes in the script as your need and try.

Hope this helps you in work. 

Leave comments if it helps you.

How to call a report in Forms 10g ?

Calling reports from forms isn't like forms version 6i, where you can call a report via RUN_PRODUCT.
I will talk another day if i think it's needed. Because Forms version 6i isn't supported by Oracle.com and it's uses is going to decreases.

Lets talk about version 10g Developer Suite. In development environment you need to run report server manually but at Application Server it's not needed.

How can we run report server manually ?
Just go to start menu >> run and type    

rwserver SERVER=myserver

Where myserver is the server name.

Now reports server runs.

In your form under Reports Node create an report object and give name MYREPORT. 

(Select Reports Node click on Create Icon(+), select Use Existing Report File then give

 a name in the box and click OK.

Re-name it to MYREPORT go to property and delete FILENAME property value.)  

Make changes(Report Name) on following code and  try this in a Button trigger 

DECLARE
 
v_repid REPORT_OBJECT;
v_rep VARCHAR2(100);
v_rep_status VARCHAR2(100);
v_param VARCHAR2(200) := NULL;
v_valor VARCHAR2(200);
v_url VARCHAR2(2000);
v_repserver varchar2(20) := 'myserver';
 
v_report varchar2(100) := 'D:\REPORT_NAME.REP';
v_PARAMETRO varchar2(100) := '';
 
BEGIN
 
v_repid := FIND_REPORT_OBJECT('MYREPORT'); -- report is an element from object navigator report
SET_REPORT_OBJECT_PROPERTY(v_repid, REPORT_FILENAME, v_report);
SET_REPORT_OBJECT_PROPERTY(v_repid, REPORT_EXECUTION_MODE, BATCH);
SET_REPORT_OBJECT_PROPERTY(v_repid, REPORT_COMM_MODE, SYNCHRONOUS);
SET_REPORT_OBJECT_PROPERTY(v_repid, REPORT_DESTYPE, cache);
SET_REPORT_OBJECT_PROPERTY(v_repid, REPORT_DESFORMAT, 'pdf' );
SET_REPORT_OBJECT_PROPERTY(v_repid, REPORT_DESNAME, v_report);
SET_REPORT_OBJECT_PROPERTY(v_repid, REPORT_SERVER, v_repserver);
SET_REPORT_OBJECT_PROPERTY(v_repid, REPORT_OTHER, 'paramform=no '||v_PARAMETRO);
v_rep := RUN_REPORT_OBJECT(v_repid);
v_rep_status := REPORT_OBJECT_STATUS(v_rep);
WHILE v_rep_status IN ('RUNNING','OPENING_REPORT','ENQUEUED') LOOP
v_rep_status := REPORT_OBJECT_STATUS(v_rep);
END LOOP;
IF v_rep_status = 'FINISHED' THEN
message(v_rep);
message(v_rep);
WEB.SHOW_DOCUMENT(v_url||'/reports/rwservlet/getjobid'||
SUBSTR(v_rep, INSTR(v_rep,'_', -1)+1)||'?'||'server='||v_repserver, '_blank');
END IF;
 
END;
 

Updated: 20/January/2013

For more Click Here

Hope this helps you all. 

How to Spell Check in Oracle Forms 6i ?

How will you feel if you able to check spelling in oracle forms?

Yea! Surely great…
Let’s try how we can do that.
This code uses the Microsoft Office spell checker, so you have installed Microsoft office suite. Now, create a procedure in forms under program units with the following code.

PROCEDURE spell_check (item_name IN VARCHAR2)
IS
   my_application   ole2.obj_type;
   my_documents     ole2.obj_type;
   my_document      ole2.obj_type;
   my_selection     ole2.obj_type;
   get_spell        ole2.obj_type;
   my_spell         ole2.obj_type;
   args             ole2.list_type;
   spell_checked    VARCHAR2 (4000);
   orig_text        VARCHAR2 (4000);
BEGIN
   orig_text := NAME_IN (item_name);
   my_application := ole2.create_obj ('WORD.APPLICATION');
   ole2.set_property (my_application, 'VISIBLE', FALSE);
   my_documents := ole2.get_obj_property (my_application, 'DOCUMENTS');
   my_document := ole2.invoke_obj (my_documents, 'ADD');
   my_selection := ole2.get_obj_property (my_application, 'SELECTION');
   ole2.set_property (my_selection, 'TEXT', orig_text);
   get_spell :=ole2.get_obj_property (my_application, 'ACTIVEDOCUMENT');
   ole2.invoke (get_spell, 'CHECKSPELLING');
   ole2.invoke (my_selection, 'WholeStory');
   ole2.invoke (my_selection, 'Copy');
   spell_checked := ole2.get_char_property (my_selection, 'TEXT');
   spell_checked :=SUBSTR (REPLACE (spell_checked, CHR (13), CHR (10)),1,LENGTH (spell_checked));
   COPY (spell_checked, item_name);
   args := ole2.create_arglist;
   ole2.add_arg (args, 0);
   ole2.invoke (my_document, 'CLOSE', args);
   ole2.destroy_arglist (args);
   ole2.RELEASE_OBJ (my_selection);
   ole2.RELEASE_OBJ (get_spell);
   ole2.RELEASE_OBJ (my_document);
   ole2.RELEASE_OBJ (my_documents);
   ole2.invoke (my_application, 'QUIT');
   ole2.RELEASE_OBJ (my_application);
END;

Now call the procedure in a Button passing the column name. Like
spell_check(‘Block_Name.Item_Name’);

Now all is yours…  :)

Say thanks, if it helps.

How to Dynamically Populate a Pop List ?

People want to know, how they dynamically populate a pop list? Means, At run time pop list will be populate. So there is no hard coded list element. Don't want to wait ?

You are at right place to learn it.

For this example code, i used SCOTT schema and DEPT table. You have to select privilege on the table.

It's a simplest example, First create a non-database block named DUMMY. create an item with type List Item named TXT_LIST. Data type CHAR, Length 30.
Now create a When-New-Form-Instance trigger at form/block level and write down the following code,

DECLARE
    group_id RecordGroup;
    group_name varchar2(10) :='abc';
    status NUMBER;
BEGIN
    group_id := find_group(group_name);
    if not id_null(group_id) then
        delete_group(group_id);
    end if;
   
    group_id := Create_Group_From_Query(group_name,'select DNAME,TO_CHAR(DEPTNO) from DEPT');
/* Select statement must have two column*/
    status := Populate_Group(group_id);
    Populate_List('DUMMY.TXT_LIST',group_id);
END;


Hope it works...

How to check, if it is first record of the block ?

At multiple-record block, you are looking for the record number, where the cursor is. Say, your at record no 5 and you want to know u'r in five. To get this follow the bellow code...

record_no:=GET_BLOCK_PROPERTY('block_name',CURRENT_RECORD);

Hope this helps... 

How to find a table being refferenced ?

You want to know does a TABLE referenced as foreign key in any other table ? Let execute the following code and find out the real truth.

select a.owner,
       a.table_name,
       a.constraint_name,
       a.status
    from   all_constraints a
    where  a.constraint_type = 'R'
    and exists(
              select 1
              from   all_constraints
              where  constraint_name=a.r_constraint_name
              and constraint_type in ('P', 'U')
              and table_name = :OBJECT_NAME        --------name of the table from which you are not able to deleting
              AND OWNER = :OBJECT_OWNER)      -------- name of the schema user name
              ORDER BY TABLE_NAME, CONSTRAINT_NAME

Hopes this helps.... and leave you comments 

How to get year,month and date from two date ?

You are in trouble getting with year, month and date ? Don't worry, just run the following script with values and you will get that.

SELECT    TRUNC (MONTHS_BETWEEN (:END_DATE, :START_DATE) / 12)  YEARS,
  MOD (TRUNC (MONTHS_BETWEEN (:END_DATE, :START_DATE)), 12)  MONTHS,
 TO_DATE (:END_DATE)- ADD_MONTHS (:START_DATE,TRUNC (MONTHS_BETWEEN (:END_DATE, :START_DATE)) ) DAYS

FROM DUAL;

Hopes this helps..

Oracle Forms 10g Browser Crashes

I'm able to open and compile forms in Developer Suit. However, when I try to execute a 10g form, I don't get very far. The browser that gets launched from Forms Designer throws an error and closes right away (after prompting to send error report). I tried IE7, IE8, and Firefox with the same result. 


What can i do ?
Do you want to get rid from this problem ? Just follow the following guideline.

1. Open Internet Explorer 8
2. Go to Tools, Internet Options
3. Click on the Advanced tab.
4. Scroll down into the Security section and find “Enable memory protection to help mitigate online attacks”.
5. Uncheck “Enable memory protection to help mitigate online attacks”.
6. Click Ok and Ok again.
7. Reboot your computer.

and also replace the JVM.DLL(2.2Mb) in the <Program Files>\Oracle\JInitiator 1.3.1.22\bin\hotspot folder.

You can download. JVM.DLL 

Hopes this helps.

ma as'salama

Oracle Searching at Forms, With Individual LOV

Do you want to search in every field in form ? Don't want to memorize each filed value ? Want to select the value from LOV (List of Values) ?

Ok. I will discuss, how can you do this.
For example, We have three filed named, department_id, department_name,location. We want to search in form in Enter Query mode. Instead of writing the value, we want to select it from LOV.

First, you need to create LOV for each and every filed for our form  filed and assign to the item.
Write Key-Listval trigger for each item and write the following code

if :system.mode='ENTER-QUERY' THEN
LIST_VALUES;
end if;

This will prevent the item to show LOV in any other mode and show only Enter-Query mode.

Now click on Enter-Query, Show your LOV(forms 6i= F9, 10g=Ctrl+L),select value and hit Execute-Query.

You are done.

If you don't like this try another way...Oracle Searching at Forms, Dynamic Way

Ma as'salama

How to Read Image File at Oracle Forms 10g ?

I heard and answered lot's of question. How i read image file from file system to Database in oracle forms 10g ?
Here is the solution.
First of all you have to configure webutil correctly. You need to configure formsweb.cfg, webutil.cfg
Attach the webutil.pll library at your forms. Make object group and subclass of webutil.pll
Now add a puss button to your form and write the following code at When-Button-Pressed trigger
--------------------------------------------

declare
 vfilename varchar2(300);
begin
 vfilename := client_get_file_name(file_filter => 'jpg(*.jpg,*.gif)|*.*');
 if vfilename is not null then
 client_image.read_image_file(vfilename,substr( vfilename,instr( vfilename, -1)),'COMPANY_INFO_MST.CIM_LOGO');
 client_image.write_image_file(vfilename,'','COMPANY_INFO_MST.CIM_LOGO'); 
 end if;
 
end;

-----------------------------------

set the following property of the image item.

image format: TIFF
sizing style: Adjust

You are done...  

Oracle Searching at Forms, Dynamic Way

We have 20 or more or less then 20 filed in our form. User like to search in the form based on all fields or frequently uses field. If user doesn't want to use "Query Mode" of Forms. How can we achieve this ?

1. Do we create a form with text field for referencing database filed ?

2. Write a complex Query using like operator or lexical parameter ? Or any other way ?

My choice is LOV (List of Values) and a Puss Button.

Create a LOV selecting column as you need. LOV return item should be one and better to be ID column. Don't assign it to any column.
Now at your search button create two trigger.

1. When-Button-Pressed and write something like to post the block state to enter query.

-------------------------------------------------------------------------------------------

BEGIN
 IF :SYSTEM.MODE = 'NORMAL' THEN 
  GO_BLOCK('ITEM_MST');
  CLEAR_BLOCK(NO_COMMIT);
  ENTER_QUERY;
 ELSIF :SYSTEM.MODE = 'ENTER-QUERY' THEN
  ENTER_QUERY;
 ELSIF :SYSTEM.MODE = 'QUERY' THEN 
  GO_BLOCK('ITEM_MST');
  CLEAR_BLOCK(NO_COMMIT);
  ENTER_QUERY;
 END IF;
 EXCEPTION 
  WHEN OTHERS THEN NULL;
END;

-----------------------------------------------------------------------------------------------------

2. When-Mouse-Click and call the LOV and execute query.

------------------------------------------------------------------------------------------------------

BEGIN 
 IF :SYSTEM.MODE = 'ENTER-QUERY' THEN 
  IF SHOW_LOV('CATEGORY_SEARCH') THEN 
   EXECUTE_QUERY;
  END IF;
 END IF;
 IF :System.Record_Status = 'NEW' THEN 
      Exit_Form(No_Validate); 
  END IF; 
END;

-------------------------------------------------------------------------------

You have done. 

Need more clarification ? Post your comments. :)