Tuesday, March 10, 2020

Enable webADI Debug/Tracing


Navigate to System Administrator > Profile > System

Search for profile -->  BNE%Log%

BNE Server Log Filename : Any Name (Ex: WebADI.log) 
BNE Server Log Level    : ERROR
BNE Server Log Path     : Provide the server path

If trace is not generated bounce the Apache Server.

Sunday, March 1, 2020

webADI SQL exception PL/SQL upload

 First enable debug/trace for webADI. This will help you to easily identify the problem.


Possible fixes :

  1. When Interface type is chosen as "API - Procedure" then only procedure calls should be used in the name, functions cannot be used .  
            Package              XX_ABC
            Package body     XX_ABC_PROC
                                        XX_ABC_FUNC
            
            Correct definition
            
            Interface Type --> API - Procedure
            Package Name  --> XX_ABC
            Procedure/Function Name --> XX_ABC_PROC

            Wrong definition

            Interface Type --> API - Procedure
            Package Name  --> XX_ABC
            Procedure/Function Name --> XX_ABC_FUNC

            And for "API - Function" vice versa.

        2. API Returns in the Interface definition should be set to "FND Message Code".

Monday, June 18, 2018

Oracle Merge error handling / dbms_errlog example

The DBMS_ERRLOG package provides a procedure that enables you to create an error logging table so that DML operations can continue after encountering errors rather than abort and roll back. This enables you to save time and system resources.

Works only after Oracle10g Release 2

DROP TABLE catalog1;
CREATE TABLE catalog1 (ID NUMBER(3), item VARCHAR2 (20), price number);
INSERT INTO catalog1 VALUES(1, 'laptop', 800);
INSERT INTO catalog1 VALUES(2, 'iphone', 500);
insert into catalog1 values(3, 'camera', 400);

drop TABLE catalog2;
--To explicitly produce error column price is set to VARCHAR2
CREATE TABLE catalog2 (ID NUMBER(3), item VARCHAR2 (20), price VARCHAR2 (20));
INSERT INTO catalog2 VALUES(1, 'laptop', 100);
INSERT INTO catalog2 VALUES(2, 'iphone', 200);
insert into catalog2 values(3, 'camera', 'ABCDEFG'  );

drop table errlog;

BEGIN
--  dbms_errlog.create_error_log ('catalog1', 'errlog'); -- Use this if you need to use own error table name rather then default name
  dbms_errlog.create_error_log ('catalog1'); 
END;


/* Using dbms_errlog for MERGE*/
MERGE INTO catalog1 s1 USING catalog2 s2 ON (s1.id = s2.id)
WHEN MATCHED THEN UPDATE SET s1.price = s2.price 
--WHEN NOT MATCHED THEN INSERT (ID, item, price) VALUES (s2.ID, s2.item, s2.price)
--LOG errors INTO errlog ('Bad') REJECT LIMIT UNLIMITED
--LOG errors INTO errlog ('Bad') REJECT LIMIT 100
LOG ERRORS ('put comment') REJECT LIMIT UNLIMITED
;

/* Using dbms_errlog for update*/
BEGIN
   UPDATE catalog1
      SET price = 'AAAAAAAAAA'
      where rownum  < 2
      LOG errors ('put comment') REJECT LIMIT UNLIMITED;
END;


SELECT * FROM err$_catalog1; -- use this if you have not used error table name in dbms_errlog.create_error_log




SELECT * FROM errlog; -- use this if you have used error table name in dbms_errlog.create_error_log

Tuesday, April 11, 2017

Oracle Forms Builder crashes when opened Form / Canvas

Oracle Developer Form builder might crash when tried to open a form or canvas with possible error uiw60.dll.

Both the errors should be handled differently.

Form builder crashes when tried to open a form (.FMB)

1) This could be related to version mismatch between the FMB and form builder. Forms developer version should match the Oracle Application version.
2) Make sure all the suggested Oracle developer edition patches are applied to match with desktop OS (32/64 Bit).


Form builder crashes while opening Canvas

1) Canvas physical width and height should be in the limit of run time Window.
2) Open other canvases from same form or different forms and then open the canvas that's failing.
3) Place the form in directory where TEMPLATE.fmb and other dependent forms exists along with PLL's and try opening the canvas.
4) Delete all the Graphics from canvas and open the canvas.



Friday, October 28, 2016

Create custom form with folder functionality Oracle forms



      1)      Open APPSTAND.fmb and copy STANDARD_FOLDER object group to the form.

      2)      Add the library APPFLDR.pll to the form.

      3)      Create a parameter of type number with initial value as 2. Parameter name should be    
               blockname_parameter. Create more blockname_parameters if the form has more folders.

Tip: Use Tabbed canvas Tab Pages to place/align the FIXED and STACKED canvases for visual appeal.


Canvas

        1)      Create a Primary content canvas for Window and 2 stacked canvas. 1 as NAME_FIXED and
            as NAME_STACKED. Set window to primary window.

a.       Set NAME_FIXED subclass as CANVAS_STACKED_FIXED_FIELD and add the primary fields from data block. (usually less than 4 non editable fields but can be many)

b.      Set NAME_STACKED subclass as CANVAS_STACKED and add all the needed fields from data block.

Block Level 

         1)      Set main data block item property Prompt to null which are used on canvas. Headings are  
                  used from prompt block mentioned below.
                           


              2)       Create FOLDER_SWITCH item on main data block with subclass property ‘Switcher’. 

                             


            3)      (Optional) CURRENT_RECORD_INDICATOR on main data block with subclass property
               CURRENT_RECORD_INDICATOR

                     
                          


         4)      Create the following 9 Triggers at Main Data Block Level  

              
    WHEN-NEW-BLOCK-INSTANCE
                 app_folder.event('WHEN-NEW-BLOCK-INSTANCE');
KEY-PREV-ITEM
               if (:parameter. blockname_parameter = 1) then
                     previous_item;
                    else
                          app_folder.event('KEY-PREV-ITEM');
                   end if;
KEY-NEXT-ITEM
               if (:parameter. blockname_parameter = 1) then
                     next_item;
                    else
                          app_folder.event('KEY-NEXT-ITEM');
                   end if;
PRE-BLOCK
           app_folder.event(‘PRE-BLOCK’);
KEY-EXEQRY
            app_folder.event(‘KEY-EXEQRY’);
POST-BLOCK
               app_folder.event(‘POST-BLOCK’);
PRE-QUERY
             app_folder.event(‘PRE-QUERY’);
POST-QUERY
             app_folder.event(‘POST-QUERY’); 


Create Prompt Block

Once the main data block item properties are set, copy items that are displayed on canvas and create a new prompt block. Don’t use FOLDER_SWITCHER and CURRENT_RECORD_INDICATOR items from main data block.

            1)      Set subclass of the prompt block to DYNAMIC_PROMPT.


2)      Set subclass of the item as below and assign items to appropriate canvas (Fixed/Stacked).

Item Type: Display Item

Subclass: FOLDER_PROMPT_MULTIROW

Initial Value: Prompt of the field name


            3)      Add the following items to the prompt block


Properties

FOLDER_OPEN

Item Type: Push Button

Subclass: FOLDER_OPEN

Canvas: Content Canvas / Tabbed canvas as per design

FOLDER_DUMMY

        Item Type: Text Item

Subclass: FOLDER_DUMMY

Canvas: TOOLBAR

FOLDER_TITLE

Item Type: Display Item

Subclass: DYNAMIC_TITLE

Canvas: Content Canvas / Tabbed canvas as per design

ORDER_BY1

Item type: Push Button

Subclass: FOLDER_ORDERBY

Canvas: Content/Fixed/Stacked Canvas

ORDER_BY2

Item type: Push Button

Subclass: FOLDER_ORDERBY

Canvas: Content/Fixed/Stacked Canvas

ORDER_BY3

Item type: Push Button

Subclass: FOLDER_ORDERBY

Canvas: Content/Fixed/Stacked Canvas



Form level triggers

1)      WHEN-NEW-FORM-INSTANCE
               APP_FOLDER.DEFINE_FOLDER_BLOCK(

                                  object_name        => 'XXXX' --NAME OF WINDOW

                                , folder_block_name  => 'XXXX'  --'FOLDER_BLOCK'

                                , prompt_block_name  => 'XXXX'              --'PROMPT_BLOCK'

                                , folder_canvas_name => 'XXXX' --'STACKED_CANVAS'

                                , folder_window_name => 'XXXXX'         --'WINDOW'

                                , disabled_functions =>  null       -'UPPER, ENABLED, lower, disabled'

                                , tab_canvas_name    => 'XXXXX'  -- TAB CANVAS

                                , fixed_canvas_name  => 'XXXX' --'STACKED_CANVAS_FIXED ) ;
                        app_folder.event( 'INSTANTIATE' ) ;
show_view('XXXX') ; --'STACKED_CANVAS'

2)      FOLDER_ACTION
                        app_folder.event(:global.folder_action);

3)      FOLDER-RETURN-ACTION (OPTIONAL)

If (:global.folder_action ='CONFIRM-HIDE-FIELD') then

                If :global.folder_field in ('FIELD1','FIELD2',etc..) then

                                :global.folder_action_allowed := 'FALSE';

                End if;

End if;

4)      WHEN-WINDOW-RESIZED

                                If :system.event_window in ('PO_REQ_INTERFACE_ALL_WINDOW') then

                                                app_folder.event('WHEN-WINDOW-RESIZED');

                                End if;