Inhoud blog
  • Extend Oracle SQL DEVELOPER : show only invalid packages
  • Generating an html page based on the module network
  • Alternative for Oracle translation Hub
  • Position magic menu items with designer : avoid post-generation
    Zoeken in blog

    Beoordeel dit blog
      Zeer goed
      Goed
      Voldoende
      Nog wat bijwerken
      Nog veel werk aan
     
    Oracle development tips and tricks

    28-05-2013
    Klik hier om een link te hebben waarmee u dit artikel later terug kunt lezen.Extend Oracle SQL DEVELOPER : show only invalid packages
    I started to look into the user defined extensions when somebody was telling me about some useful stuff that exists in Toad and which was missing in Sql Developer.
    One of those things is the ability in TOAD to only show packages which are invalid. In Sqldeveloper, you can only filter packages on name, creation date and last modification date.

    Adding extra filter criteria can easily be done by adding a user defined extension. As information about the xsd's used  in Sqldeveloper can't be found anywhere, I think it is worth describing my user defined extension here.

    via Tools->Preferences->Database->user defined extensions : Add an extension of type Navigator and fill it with P:sqlSqlDeveloperextensionsplsql.xml

    You can find the code below, it adds 2 extra filters
    • Show only invalid : shows only db objects which are invalid
    • Exclude Tapi : excludes packages of which the name starts with CG$
    If you want to add your own filters, just look at the code and I'm sure you'll be able to figure out how to do this

    Here is an extraction of the modification I made.
                <column filterable="true" sortable="false" id="Show only Invalid" defaultFilter="true">
                  <type>
                    <![CDATA[COMPLEX_PREDICATE]]>
                  </type>
                  <colName>
                    <![CDATA[/**/INVALID = 'TRUE' or OBJECT_NAME IN (SELECT OBJECT_NAME FROM SYS.ALL_OBJECTS WHERE OWNER =:SCHEMA and STATUS = 'INVALID' and OBJECT_TYPE='PACKAGE BODY')]]>
                  </colName>
                </column>
                <column filterable="true" sortable="false" id="Exclude Tapi" defaultFilter="true">
                  <type>
                    <![CDATA[COMPLEX_PREDICATE]]>
                  </type>
                  <colName>
                    <![CDATA[/**/OBJECT_NAME not like 'CG%']]>
                  </colName>
                </column>
    If
    If

    Bijlagen:
    plsql.xml (14.7 KB)   

    28-05-2013 om 15:50 geschreven door Arno Hollanders  

    0 1 2 3 4 5 - Gemiddelde waardering: 0/5 - (0 Stemmen)
    12-04-2013
    Klik hier om een link te hebben waarmee u dit artikel later terug kunt lezen.Generating an html page based on the module network
    Klik op de afbeelding om de link te volgen

    Generating an html page based on the module network


    When you have an oracle forms application which was generated by designer, and you have a lot of screens, it sometimes is difficult to find the screen or report you are looking for.

    You often ask questions like
    • Where in the menu can I find this form or report?
    • Who has access to this form or report?
    • To which forms and reports has this user access
    The following script creates an html page with the following information


    Modulenetwork without Roles 
    • Generates an expandable tree with the complete module network
    Modulenetwork including Roles 
    • Generates an expandable tree with the complete module network, including the roles which have access to the module
    Module Access by Role 
    • Generates an expandable tree which shows all forms/reports to which a certain role has access
    In script generate_designer_module_network.sql, you'll need to change the following
    • the context of your workarea : 2365400638959977470080254665678787960
    • The name of your main menu module : SDP000M

    In generate_designer_module_network_procedure.sql, you'll need to change the following
    • procedure header : create a custom header for your html page
    To make sure that endless loops are avoided  (module X calls module Y, module Y calls module Z, module Z calls module X), the depth is limited to 3 (except for menus)

    Bijlagen:
    generate_designer_module_network.sql (0.7 KB)   
    generate_designer_module_network_procedure.sql (10.7 KB)   
    style_sdp11.css (3.8 KB)   

    12-04-2013 om 11:32 geschreven door Arno Hollanders  

    0 1 2 3 4 5 - Gemiddelde waardering: 0/5 - (0 Stemmen)
    11-04-2013
    Klik hier om een link te hebben waarmee u dit artikel later terug kunt lezen.Alternative for Oracle translation Hub
    Alternative for Oracle translation Hub

    For those of you that have a multilingual Oracle Forms application, you might know what Oracle translation Hub is about. In theory, it is a great tool but in practice, it can cause major headaches.
    • There exists hardly any documentation
    • It throws often exceptions which aren't clear at all
    • using the batch mode throws exceptions which in fact should be ignored
    • It causes you to translate far too many strings which can also lead to issues (eg when translation the default value in a poplist)
    Anyway, for me, more than enough reasons to look for an alternative.
    One alternative is too translate forms/reports at runtime, but this causes network traffic and can also be noticed by the end users.

    My solution is to create a custom translation tool by using a  pl/sql script for the translation.
    I'm not going into too much details but basically, this is how things are done.

    table translations contains columns for french and english string
    table translation_deails is a detail of translations  and will store a reference to the translation (this way, a translator can see where the string to be translated is used

    Basically, the following steps are done
    1. Convert the source (fmb, rdf, mmb) to xml using rwconver or frmf2xml
    2. Upload the xml in the database with sqlLoader
    3. Loop through all strings encountered in the xml and find the translation in table translations : if it does not exist, add it in table translations with status 'To be Translated'
    4. Export the translated xml to the file system
    5. Convert the xml back to the source (fmb, rdf, mmb) using rwconver or frmxml2f
    If you are interested in the code, just look at the files I attached. I didn't bother to make paths and database connections dynamic, so you will need to change some things in these scripts.
    You'll also see lot's of duplicate code, but this is intended to make all scripts modular


    Bijlagen:
    BlobHandler.sql (1.4 KB)   
    ConvertFMB2XMLRelease.bat (0.5 KB)   
    ConvertMMB2XMLRelease.bat (0.4 KB)   
    ConvertRDF2XMLRelease.bat (0.4 KB)   
    ConvertXML2FMBRelease.bat (0.5 KB)   
    ConvertXML2MMBRelease.bat (0.6 KB)   
    ConvertXML2MMBRelease.bat (0.6 KB)   
    load_and_translate_form.bat (0.2 KB)   
    load_and_translate_menu.bat (0.2 KB)   
    load_and_translate_report.bat (0.2 KB)   
    translate.bat (3 KB)   
    translateform.sql (0.1 KB)   
    translatereport.sql (0.1 KB)   
    translate_application.sql (23.1 KB)   
    translate_menu.bat (0.7 KB)   

    11-04-2013 om 09:22 geschreven door Arno Hollanders  

    0 1 2 3 4 5 - Gemiddelde waardering: 2/5 - (10 Stemmen)
    10-04-2013
    Klik hier om een link te hebben waarmee u dit artikel later terug kunt lezen.Position magic menu items with designer : avoid post-generation
    After generating a menu with designer; the pull-down menus FILE, EDIT, WINDOW, HELP are placed before all the other pull-down menus. Typically, you want to have WINDOW and HELP at the end.

    Explanation :
    Back in the days when forms was still in client server mode, the window and help submenu were put at the end when running the form because they were defined as magic menu items. Webforms does not know what a magic menu item is, and shows the menu items in the order they are in in the mmb/mmx file.
    To get this working, a post-generation modification to the menu is always necessary.

    Solution :
    To avoid the post-generation, we basically perform the following steps
    1. Convert the generated mmb to xml
    2. Reposition the Window and Help submenu in a PL/SQL procedure
    3. Convert the xml back to an mmb
    Detailed explanation of the steps needed:

    1. Creation of a custom bat-file
    In designer, change the command for menu compilation in the generate options to a bat-file you about to create
    Create a bat file with the following code
    In this file replace the following names according to your needs
    your_menu_name :
    your_utl_file_directory
    your_generation_destination
    your_script_location

    set oracle_home=C:OracleMiddlewareOracle_FRHome1
    set forms_path=Q:fmb11g;P:fmw11olb;P:fmw11tpl;P:fmw11pll
    set path=C:OracleMiddlewareOracle_FRHome1bin;C:OracleMiddlewareOracle_FRHome1forms;%path%
    set tns_admin=y:
    SET NLS_LENGTH_SEMANTICS=CHAR
    SET NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1

    REM Convert the MMb to XML
    call frmf2xml OVERWRITE=YES [your_generation_destination][your_menu_name].mmb 

    REM Move the xml to a destination which is accessible with utl_file
    del [your_utl_file_directory][your_menu_name]_orig_mmb.xml
    move [your_generation_destination][your_menu_name]_mmb.xml [your_utl_file_directory]your_menu_name]_orig_mmb.xml

    C:OracleMiddlewareOracle_FRHome1binsqlplus username/pw@db @[your_script_location]modifymenuXml.sql

    move P:Issue_Tracker[your_menu_name]_mmb.xml [your_generation_destination][your_menu_name]_mmb.xml

    call frmxml2f  OVERWRITE=YES PRINTTREE=YES USERID=username/pw@db [your_generation_destination][your_menu_name]_mmb.xml 


    SET NLS_LENGTH_SEMANTICS=CHAR
    SET NLS_LANG=AMERICAN_AMERICA.AL32UTF8
    C:OracleMiddlewareOracle_FRHome1binFRMCMP module_type=MENU batch=YES window_state=MINIMIZE %1=%2 %3=%4 %5=%6


    2. SQL-Script
    Some explanation of the script below
    The main-menu in our case is sdp000m, the last menu-item also needs to be defined, in our case ADMINISTRATION is the last submenu after which we want to place WINDOW and HELP
    temp_convert_menu_doubles : This table is user because designer sometimes generates menu items with the same name (eg when you attach the same form in a submenu with a different command) , when converting the xml back to and MMB, these duplicates would get lost
    Code about ofgnavl65 : for one reason or another, there a lots of entries of this attached library, the code removes the duplicate entries

    SET HEADING OFF
    SET PAGESIZE 500
    SET LINESIZE 240
    SET FEEDBACK OFF
    SET VERIFY OFF
    SET DEFINE OFF
    SET SERVEROUTPUT ON SIZE 1000000

    REM create table temp_convert_menu
    REM    ( string varchar2(2000)
    REM , line    number);
    REM create table temp_convert_menu_doubles
    REM    ( string varchar2(30) 
    REM    , CONSTRAINT "menu_doubles_PK" PRIMARY KEY ("STRING"));

       
    SPOOL P:Issue_Trackeradaptxml.txt
    DECLARE
       v_file_handle_in  UTL_FILE.FILE_TYPE;
       v_file_handle_out UTL_FILE.FILE_TYPE;
       v_line VARCHAR2(2000);
       v_new_line VARCHAR2(2000);
       v_name  VARCHAR2(2000);
       v_name_start_pos number; 
       v_name_end_pos number;
       v_index  VARCHAR2(2000);
       v_index_start_pos number; 
       v_index_end_pos number;
      v_counter number := 0;
      v_linenumber number := 0;
      l_within_sdp000m boolean := false;
      l_within_window_help  boolean := false;
      l_within_admin boolean := false;
      l_sdpapp_written boolean := false;
      l_ofgnavl65_written boolean := false;
      v_loopcounter number := 0;
    BEGIN

       -- We use a temporary table to store the lines
       delete from temp_convert_menu;
       delete from temp_convert_menu_doubles;
       DBMS_OUTPUT.ENABLE(1000000);

       v_file_handle_in  := UTL_FILE.Fopen('[your_dba_directory]','[your_menu_name]_orig_mmb.xml','R',32767);
       v_file_handle_out := UTL_FILE.Fopen('[your_dba_directory]','[your_menu_name]_mmb.xml','W',max_linesize => 32767);
       -- Loop through all line encountered in the original
       LOOP

           
     UTL_FILE.get_line(v_file_handle_in, v_line);
          IF instr(v_line,'<Menu Name="SDP000M"') > 0 THEN
             l_within_sdp000m := true;
     END IF;
          IF instr(v_line,'<MenuItem Name="WINDOW"')> 0
           OR instr(v_line ,'<MenuItem Name="HELP"') > 0 THEN  
        l_within_window_help := true;
     END IF;
          IF instr(v_line,'<MenuItem Name="ADMINISTRATION"') > 0 THEN
             l_within_admin := true;
     END IF;  

     IF v_line like '%</Menu>%' THEN
        delete from temp_convert_menu_doubles;
     END IF;
          v_name_start_pos := instr(v_line,'"',1,1);
          v_name_end_pos := instr(v_line,'"',1,2);
          v_name := substr(v_line,v_name_start_pos+1,v_name_end_pos-v_name_start_pos-1);
          v_name := substr(v_name,1,30);
          IF v_name is not null 
     and v_line like '%<MenuItem Name=%' 
     and v_name not like 'SEPARATOR%' 
     and instr(v_name,'______') = 0 THEN
     BEGIN
     insert into temp_convert_menu_doubles(string) values (v_name);
     EXCEPTION
        when dup_val_on_index THEN
    v_loopcounter := v_loopcounter + 1;
                v_name := substr(v_name,1,25)||lpad(v_loopcounter,5,'0');
    insert into temp_convert_menu_doubles(string) values (v_name);
     END;
          END IF;
     
     v_new_line := substr(v_line,1,v_name_start_pos)||v_name||substr(v_line,v_name_end_pos,2000);

     IF l_sdpapp_written and instr(v_line,'LibraryLocation="sdpapp.pll"') > 0 THEN
        null;
     ELSIF l_ofgnavl65_written and instr(v_line,'LibraryLocation="ofgnavl65.pll"') > 0 THEN
        null;
     ELSIF not l_within_window_help THEN
             UTL_FILE.PUT_line(v_file_handle_out, v_new_line);
          ELSE
        v_linenumber := v_linenumber + 1;
             insert into temp_convert_menu(string, line)
    values
    (v_new_line, v_linenumber);
     END IF;

     IF l_within_window_help and instr(v_line,'</MenuItem>') > 0 THEN
        l_within_window_help := false;
     END IF;

     IF instr(v_line,'LibraryLocation="sdpapp.pll"') > 0 THEN
        l_sdpapp_written := true;
     END IF;
     IF instr(v_line,'LibraryLocation="ofgnavl65.pll"') > 0 THEN
        l_ofgnavl65_written := true;
     END IF;

     
     IF l_within_admin and instr(v_line,'</MenuItem>') > 0 THEN
        l_within_admin := false;
    l_within_sdp000m := false;
             FOR REC_LOOP in ( SELECT string as text_string
                                FROM temp_convert_menu
                               order by line)
             LOOP
                UTL_FILE.PUT_line(v_file_handle_out, rec_loop.text_string);
             END LOOP;   
     END IF;
     
          IF v_line like '%</Module>%' THEN
     UTL_FILE.FCLOSE(v_file_handle_in);
           exit;
          END IF;
       END LOOP;
       -- 

       
       UTL_FILE.FCLOSE(v_file_handle_out);
       commit;
    END;
    /
    SPOOL OFF
    exit

    ...


    Bijlagen:
    https://forums.oracle.com/forums/thread.jspa?messageID=204353𱹁   
    https://forums.oracle.com/forums/thread.jspa?messageID=306147񊯣   

    10-04-2013 om 09:05 geschreven door Arno Hollanders  

    0 1 2 3 4 5 - Gemiddelde waardering: 1/5 - (3 Stemmen)
    Tags:oracle, designer, forms, magic, menu item, position


    Archief per week
  • 27/05-02/06 2013
  • 08/04-14/04 2013

    E-mail mij

    Druk op onderstaande knop om mij te e-mailen.


    Gastenboek

    Druk op onderstaande knop om een berichtje achter te laten in mijn gastenboek


    Blog als favoriet !


    Blog tegen de wet? Klik hier.
    Gratis blog op https://www.bloggen.be - Meer blogs