- sa_utility_pg - database package for program stack support and send mail via utl_tcp system package.
- gnlib.pll - PLL for debug messages and trace message writing into file.
- dynamic_picklist.pll - Object library with dynamic picklist object group. It is possible to set dynamically the text of select (the list of fields with aliases, join, where ), to choose one field for display in a picklist.
- picklist_and_multi_test.fmb
- Sample form used dynamic picklist object group (Scott EMP and DEPT tables used).
--->
- addtrace.p2s
- FAPI Master script( www.orcl-toolbox.com ) add trace possibility to any form. The script requires in
gnlib.pll.
Script is replacement to an awful mode debug_messages=yes.
You set the form and a script builds the form with the same name in other directory, adding in everyone trigger and program unit code in the beginning and in the end delivery message in a trace file. The button is added also, allowing to enable/disable trace mode.
- prob_html_excel.zip - Creating HTML document from form block (writing by Michael Leibin).
- write_excel_demo.zip - Writing form block rows to Excel file via OLE2.
- utilities.zip
- PLL with 3 packages(writing by
Sasha Landsberg):
- Excel$ - Reading/writing Excel file via OLE2.
- File$ - File functions.
- Word$ - Create file in Word via OLE2.
- app_mail_via_smtp.zip - Send mails with html body from ORACLE stored procedure. Package uses utl_smtp system package with 'MIME-Version','Content-type' mail commands. In mail body it possibility to use HTML commands. (writing by Sasha Landsberg):
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:
if sample_function(pi_param1=>'dummy') = sa_utility_pg.yes then
...
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_%' );
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:
- Attache library
- Create alert with name Stop_1
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;
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;
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:
- In any form to copy/subclass PICKLIST_AND_LOV object group from OLB;
- 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
);
