OraDep home
     


I suggest to load some files which probably can be necessary for you.

If you have written any PL/SQL programs, PLL or OLB libraries which can be used by others, send them to me I shall lay out them on a site.

sa_utility_pg.zip - database package for program stack support and send mail via utl_tcp system package.
I use this package for 3 purposes:
  • The set of functions which return constants and it enables me to write a clear code, for example: 
    if samle_function(pi_param1=>'dummy') = sa_utility_pg.yes then
    
        ...
  • A sending of mail from database, you for certain and it are able.
  • The most interesting:
    Support of program stack. If in the beginning of the program (and in all calling prcedures) to write a sa_utility_pg.prolog, and at all returns from the program to write a sa_utility_pg.epilog, in sys.v_$session view you will always see, that program now works.
    This is sample of select from sys.v_$session 
    select t.username, t.module,t.action from sys.v_$session t where (t.username='OPS$USER')
and (t.module like 'cl_%' or t.module like 'sa_%' );
    Usually, if the sa_utility_pg.epilog is used in exception section it is possible to take advantage of that that the mail is sent(if p_MailTo parameter is not null).
    Usage: 
      --     Sample
  procedure proc_sample(Name varchar2) is
    l_Msg      varchar2(2000);
  begin
    sa_utility_pg.prolog('proc_sample');
  --


  -- Program unit epilog
    sa_utility_pg.epilog(p_Msg    => '',
                         p_From   => ''),
                         p_MailTo => '');
  exception
    when others then
      l_Msg := sqlerrm;
      sa_utility_pg.epilog(p_Msg    => 'Error: '||l_Msg,
                           p_From   => 'proc_sample',
                           p_MailTo => 'user@domain.com');
      sa_utility_pg.put_err_to_output(v_Msg);
  end proc_sample;
    • gnlib.pll - PLL for debug messages and trace message writing into file.
    The code added for tracing can be left in programs units and triggers after transfer to production environment, there is enough to comment t.traceon call at one place in form or use :parameter.trace
    Usage:
    1. Attache library
    2. Create alert with name Stop_1
    For trace on/off you use procedures t.traceon/t.traceoff.
    I usually use a code in WHEN-NEW-FORM-INSTANCE trigger: 
      if :parameter.trace is not null and upper(:parameter.trace) = 'YES' then
    t.traceon;
  end if;
    The trace file has a name trace.log and is in a Windows temp directory.
    I receive a temp directory name from WinApi function Win_Api_Environment.Get_Temp_Directory
    Usage sample: 
      PROCEDURE check_unit_in_unit (p_ParentId     integer ) IS
  BEGIN
    t.ProgStart('check_unit_in_unit');
    t.p('p_ParentId: '||p_ParentId);
    set_application_property(CURSOR_STYLE,'BUSY');
    show_text_working;
    set_application_property(CURSOR_STYLE,'DEFAULT');
    hide_text_working;
    go_item('SA_OBJECTS.OBJECT_NAME');
    t.ProgEnd('check_unit_in_unit');
  exception
    when form_trigger_failure then
      set_application_property(CURSOR_STYLE,'DEFAULT');
      hide_text_working;
      go_item('SA_OBJECTS.OBJECT_NAME');
      t.ProgEnd('check_unit_in_unit');
      raise form_trigger_failure;
    when others then
      set_application_property(CURSOR_STYLE,'DEFAULT');
      hide_text_working;
      go_item('SA_OBJECTS.OBJECT_NAME');
      d.p(sqlerrm);
      t.ProgEnd('check_unit_in_unit');
      raise form_trigger_failure;
  END;
    Results(here it is possible to see result of work of FormsAPI addtrace.p2s script which has added trace functions of trace in all programs of the form): 
      Logon: Y/...@db
  Current Form: C:\my_projects\form_debug\debug.fmx
  Calling Form:
  Start : Blocks.SA_OBJECTS.Triggers.WHEN-NEW-RECORD-INSTANCE
    Start : Program Units.IS_FORM
      P_TYPE = PACKAGE BODY
      Return value:
      FALSE
    End   : Program Units.IS_FORM
    Start : Program Units.IS_STORED_PROCEDURES
      P_TYPE = PACKAGE BODY
      Return value:
      TRUE
    End   : Program Units.IS_STORED_PROCEDURES
    select status from dba_objects where object_id =532357
  End   : Blocks.SA_OBJECTS.Triggers.WHEN-NEW-RECORD-INSTANCE
  Start : Triggers.WHEN-TIMER-EXPIRED
    Start : Program Units.POPULATE_FILE_DETAILS
      Start : Program Units.POPULATE_NOTE_BLOCK
        P_DEPENDENCYBLOCK = SA_OBJECT_DEPENDENCIES
        P_NOTEBLOCKNAME = NOTE_BLOCK
        Start : Blocks.NOTE_BLOCK.Triggers.POST-BLOCK
        End   : Blocks.NOTE_BLOCK.Triggers.POST-BLOCK
      End   : Program Units.POPULATE_NOTE_BLOCK
    End   : Program Units.POPULATE_FILE_DETAILS
  End   : Triggers.WHEN-TIMER-EXPIRED
    dynamic_picklist.olb - Object library with dynamic picklist object group.
    Usage:
    1. In any form to copy/subclass PICKLIST_AND_LOV object group from OLB;
    2. m_multi_lov_pg.initiate_picklist procedure 
    m_multi_lov_pg.block_return := 'EMP';
m_multi_lov_pg.initiate_picklist(
                 p_DisplayColNumber   =>2,
                 p_From         => 'from emp e,dept d where e.deptno=d.deptno order by ename',
                 p_SelectColumn => 'empno ,ename " Family              ",'||
                                    'sal,d.dname,e.deptno, d.deptno',
                 p_Title        => 'Select employes'
                ,p_IdColumnNumberList => '1'
                ,p_IdColumnNameList => 'empno'
                                );
          
              -- initiate_picklist parameters:
p_DisplayColNumber integer := 1 - Column number from  column list
p_From             varchar2    - Part of select begin from "from" keyword
p_SelectColumn     varchar2    - Column list, available aliases
p_Title            varchar2 := 'Our Choice' - Multi select window title
p_DateFormat       varchar2 := 'dd/mm/rr' - Display format for date fields
p_isReturnTbls     boolean := true - if to return lists selected values in 10 PL/SQL tables
p_ColumnWidthLimit integer := 100 - Width limit for long fields
p_IdColumnNameList varchar2 := null - Column names list per ","
           for "where clause" bulding in calling block m_multi_lov_pg.block_return
           by default where clause for m_multi_lov_pg.block_return not setup
p_IdColumnNumberList varchar2 := null - Column number list per ","
           from  for "where clause" bulding
           by default where clause for m_multi_lov_pg.block_return not setup
            else use "where clause" in form:
   'column_name in ()' where  is list selected values

          Limits:

    Max Column count in select list = 10;
    Max Column length = 255;
    Support Column datatypes: Varchar2,
                              Char,
                              Number,
                              Date;
    Support Coordinate System property  - real centimeter only

          Call Sample:

     m_multi_lov_pg.initiate_picklist(
                  p_DisplayColNumber   => 2,
                  p_From         => 'from emp order by ename',
                  p_SelectColumn => 'empno,ename "  Family              ",ename " family_2         ",'||
                                     'sal,comm,job "           Job   ",deptno,hiredate " hiredate   ",'||
                                     'hiredate "hiredate2        "',
                  p_Title        => 'Select files'
                 ,p_IdColumnNumberList => '1,8'
                 ,p_IdColumnNameList => 'empno,hiredate'
  	                                 );

     m_multi_lov_pg.initiate_picklist(
                  p_DisplayColNumber   => 2,
                  p_From         => 'from emp e,dept d where e.deptno=d.deptno order by ename',
                  p_SelectColumn => 'empno,ename "Family              ",sal,d.dname,e.deptno, d.deptno',
                  p_Title        => 'Select files'
                 ,p_IdColumnNumberList => '1'
                 ,p_IdColumnNameList => 'empno'
                                     );

     m_multi_lov_pg.initiate_picklist(
                  p_DisplayColNumber   => 2,
                  p_From         => 'from emp order by ename',
                  p_SelectColumn => '*',
                  p_Title        => 'Select files'
                 ,p_IdColumnNumberList => '1'
                 ,p_IdColumnNameList => 'empno'
                                      );

     m_multi_lov_pg.initiate_picklist(
                  p_DisplayColNumber   => 1,
                  p_From         => 'from sa_units  order by 1',
                  p_SelectColumn => 'unit_name "Unit Name",unit_type "Unit type",'||
                                     'note',
                  p_Title        => 'Select units',
                  p_isReturnTbls => false
                                     );
    OraDep home