Monday, November 26, 2012

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 :)

    Sunday, September 23, 2012

    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.

    Monday, June 4, 2012

    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.

    Friday, April 27, 2012

    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. 

    Wednesday, April 11, 2012

    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.

    Monday, March 26, 2012

    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...

    Monday, March 5, 2012

    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... 

    Tuesday, February 21, 2012

    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..

    Sunday, February 19, 2012

    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

    Friday, February 17, 2012

    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

    Tuesday, February 7, 2012

    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. :)