Friday, 19 August 2011

Forms - Compilation batch routine (Windows)

Something I've used to generate forms from the command line and copy to appropriate directories.

@ECHO OFF

REM ---
REM Simple batch file for compiling forms in 10g only.
REM ---
REM - 1. Run using oracomp10 <formname>
REM - 2. A sub-directory will be generated containing 10g .fmx files.
REM - 3. .fmx files will also be copied to  %oasexe10g% 

set oasexe10g=C:\Users\lawtond\Documents\Oasis\oasexe10g
ECHO --- 10g .fmx will be copied to %oasexe10g%
IF NOT EXIST 10g\ mkdir 10g\
del *.err
copy %1.fmb 10g\
cd 10g\
del *.err
ECHO --- version 10g - compiling
frmcmp userid=djl/djl@database.server module=%1 batch=yes module_type=form compile_all=yes window_state=minimize 
copy /Y %1.fmx %oasexe10g%
del %1.fmb
cd ..
ECHO --- Done

SQL - Alphanumeric Sort

I'm sure there is an easier/less unwieldy way to do this.

A sort for mixed alphanumerics.  Integers and decimals do not mix well in this SQL!

select bed_key
from djl_temp_beds
order by nvl(rpad(ltrim(replace(
    translate(bed_key,'0123456789. ','@@@@@@@@@@@'),'@',' ')),15),' '), -- chars
  nvl(lpad(ltrim(rtrim(replace(bed_key,
    replace(
    translate(bed_key,'0123456789. ','@@@@@@@@@@@'),'@',''),''))),15,'0'),0) -- nums;

SQL - OVERLAPS (dodgy - not supported)

Something I discovered ages ago.  A way to compare two date ranges for an overlap through the use if the OVERLAPS keyword.  I've since discovered that this keyword is not actually supported by Oracle and although it still appears to work, it's usage is not recommended.


SELECT 1 FROM DUAL WHERE (DATE '2001-01-01' , DATE '2001-12-27') 
OVERLAPS
(DATE '2001-01-01' , DATE '2001-01-01');

Oracle Forms - Ref cursor in form

I've not come across that much ref cursor usage in Forms, but it is certainly useful; consider that a database routine returning a ref cursor may be utilised by non Forms applications.

DECLARE


  TYPE cursor_type IS REF CURSOR;
  TYPE record_type IS RECORD(filename VARCHAR2(255));
  
  c_list cursor_type;
  r_list record_type;
  
BEGIN
  
  oasaudit_package.get_directory_list('PATIENT_EVENTS_DIR',
                                      c_list);


  LOOP
    FETCH c_list INTO r_list;
      add_list_element('CONT.PAT_EVENT_ARCHIVE_LIST',
                       1,
                       r_list.filename,
                       r_list.filename);
    EXIT WHEN c_list%NOTFOUND;    
  END LOOP;
                        
END;

SQL - Using PARTITION BY

Advanced sorting.  Really useful.



http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:3170642805938



http://www.java2s.com/Code/Oracle/Analytical-Functions/PARTITIONBYdividethegroupsintosubgroups.htm


SELECT row_number() over(PARTITION BY referral_to_treat_pathway_id ORDER BY clock_start) dense_rank,
       rttd.referral_to_treat_period_id,
       rttd.clock_start,
       rttd.clock_stop,
       rttd.start_event_reason_code,
       rttd.stop_event_reason_code
  FROM referral_to_treat_periods rttd
 WHERE rttd.referral_to_treat_pathway_id = p_pathway_id
 ORDER BY rttd.clock_start