Oracle Forms
Trigger auf Modulebene
WHEN-NEW-FORM-INSTANCE auf Modul Ebene
declare
instanz_name varchar2(64);
begin
select global_name into instanz_name from global_name;
instanz_name := user ||'@'|| instanz_name;
SET_WINDOW_PROPERTY(forms_mdi_window, window_state, maximize);
SET_WINDOW_PROPERTY('window1', window_state, maximize);
-- Setzt den Fenstertitel wie folgt:
-- :Programmtitel Oracleuserid@instanzname
SET_WINDOW_PROPERTY('window1', title, '<'||Get_Application_Property(current_form)||
'>:b e s c h r e i b u n g '||instanz_name);
-- Dynamisch Tabellen- Viewname setzen
-- SET_BLOCK_PROPERTY ('kna_kuli_data',query_data_source_name,'edv.kna_kuli_data_kr');
-- Groß- Kleinschreibung bei exequte_query nicht unterscheiden
set_item_property('block.item' , case_insensitive_query , property_true);
execute_query;
end;
WHEN-TAB-PAGE-CHANGED auf Modul Ebene
declare
curr_tab varchar2(200);
begin
curr_tab := GET_CANVAS_PROPERTY('canvas-name', topmost_tab_page);
commit_io;
if curr_tab = 'tab_page-1' then
go_block('data-block-1');
execute_query;
elsif curr_tab = 'tab_page-2' then
go_block('data-block-2');
execute_query;
elsif curr_tab = 'tab_page-3' then
go_block('data-block-3');
execute_query;
end if;
end;
PRE-TEXT-ITEM auf Modul Ebene
-- feld_focus_an muß als Visuelles Attribut definiert sein
DISPLAY_ITEM(:SYSTEM.TRIGGER_ITEM,'feld_focus_an');
POST-TEXT-ITEM auf Modul Ebene
DISPLAY_ITEM(:SYSTEM.TRIGGER_ITEM,'feld_focus_aus');
ON-LOGON auf Modul Ebene
logon('passwort','user@instanz');
Trigger auf Block- Item Ebene
WHEN-BUTTON-PRESSED auf Block Ebene
call_form('FORMNAME');
exit_form(DO_COMMIT);
pre-query auf Block Ebene
-- WHERE Bedingung für Block setzen
set_block_property('KNA_ORA_MSG',default_where,'where id='||:global.v_id);
-- Sortierung für Block setzen
set_block_property('KNA_ORA_MSG',order_by,'id');
post-query auf Block Ebene
-- Auf dieser Ebene konnen Non Database Items in Abhängigkeit von Database Items gesetzt werden
select bezeichnung into :artikel_bezeichnung from aktikel where artikelnr = :artikelnr;
-- Farbe des Items "ereignis" im Datenblock "fus_ereignis" verändern
-- das Visualle Attribut "vattr_normal" muß definiert sein.
Set_Item_Instance_Property( 'block.Feld', CURRENT_RECORD, VISUAL_ATTRIBUTE,'vattr_normal');
Alerts
Alert
declare
v_alert number(3);
begin
SET_ALERT_BUTTON_PROPERTY('achtung',
ALERT_BUTTON1,
LABEL,
'ja sicher!'); -- ok
SET_ALERT_BUTTON_PROPERTY('achtung',
ALERT_BUTTON2,
LABEL,
'lieber nicht!'); -- abbrechen
set_alert_property('achtung',
title,
'Achtung:');
set_alert_property('achtung',
alert_message_text,
'Alertmeldung');
v_alert := show_alert('achtung');
if v_alert = 88 then
... ok Verarbeitung ...
else
... abbrechen Verarbeitung ...
end if;
end;
Client Dateiname auswählen
:variable := get_file_name('l:\neveling\hpe',null,'mit Semikolon getrennt (*.CSV)|*.csv|');
Datei mit Text-i-o einlesen
PROCEDURE read_file_per_textio ( p_filename in varchar2)
IS
v_imp_file text_io.file_type;
v_imp_record varchar2(2000);
v_rec_cnt number(13);
BEGIN
SET_APPLICATION_PROPERTY(CURSOR_STYLE, 'BUSY'); -- Sanduhr ein
v_rec_cnt := 0;
v_imp_file :=text_io.fopen(p_filename,'r'); -- csv-Datei zum Lesen öffnen
text_io.get_line(v_imp_file, v_imp_record); -- Kopfsatz lesen
loop
v_rec_cnt := v_rec_cnt + 1;
-- insert into edv.hpe_imp_dat(ID, LINKCODE, DATENSTROM)
-- values ('', v_linkcode, v_imp_record);
text_io.get_line(v_imp_file, v_imp_record);
end loop;
EXCEPTION
WHEN no_data_found then -- EOF bei text_io.get_line
SET_APPLICATION_PROPERTY(CURSOR_STYLE, 'DEFAULT'); -- Sanduhr wieder aus
text_io.FCLOSE(v_imp_file);
WHEN others then
text_io.FCLOSE(v_imp_file);
SET_APPLICATION_PROPERTY(CURSOR_STYLE, 'DEFAULT'); -- Sanduhr wieder aus
END read_file_per_textio;
Aufruf LOV per Button
1. Möglichkeit:
-- Auf Feld mit LOV per go_item positionieren
go_item('datenblock.feld');
-- F9 aktivieren
Do_Key('LIST_VALUES');
2.Möglichkeit:
declare a boolean;
begin
a:= show_lov('lov_name');
end;
Dynamisches Sql unter Forms
-- Tabelle anlegen
forms_ddl('create table test2 (id number(13) primary key)');
Package auf Gültigkeit prüfen
PROCEDURE check_package_status IS
/* Die Package Bodys und Heads von 'KKK_EXT_IMPORT',
'FIBU_BUCHUNGEN','FIBU_INPUT','FIBU_OUTPUT','FIBU_TOOLS'
werden auf Gültigkeit über die Tabelle sys.dba_objects
überprüft. Das erste ungültige Package wird per Alert
'alert_inv_pack' (muss in Forms definiert werden) angezeigt. */
v_alert number(3);
v_object_name dba_objects.object_name%type;
v_status dba_objects.status%type;
cursor c_dba_objects is
select object_name, status from dba_objects
where owner = 'EDV'
and substr(object_type,1,7) = 'PACKAGE'
and status <> 'VALID'
and object_name in ('KKK_EXT_IMPORT', 'FIBU_BUCHUNGEN','FIBU_INPUT','FIBU_OUTPUT','FIBU_TOOLS');
begin
open c_dba_objects;
fetch c_dba_objects into v_object_name, v_status;
if c_dba_objects%found then
set_alert_property('alert_inv_pack',alert_message_text,'Package '|| v_object_name||
' ist nicht gültig!');
v_alert := show_alert('alert_inv_pack');
close c_dba_objects;
else
close c_dba_objects;
message('ok');
end if;
END;
Oracle PL/SQL
leerer Block
begin
-- Anweisung
exception
when others then
null;
end;
Funktion (innerhalb eins Packages)
function xxx
( p_linkcode varchar2,
p_wert2 number
)
return number
is
begin
return 0;
exception
when others then
return null;
end xxx;
-- deterministic Function für Indexfelder
CREATE OR REPLACE FUNCTION XXXF_pro_date(p_date date) return date deterministic
is
begin
if to_char(p_date,'HH24MI') >= '2230' then
return p_date + 1;
end if;
return p_date;
exception
when others then
return p_date;
end;
/
Null Wert auf 0 setzen
numvar := nvl(numvar, 0);
aktuelle DB ermitteln
select global_name from global_name
Monats letzten ermitteln
-- Eingabe 01.10.2002 als '011002'
-- Ergebnis : Datefeld 31.10.2002
select last_day(to_date('01'||'1002','DDMMYY')) from dual;
Heute vor einem Jahr
select add_months(sysdate, -12) from dual;
einen Monat zurück
select add_months(sysdate, -1) from dual;
Die Kalenderwoche zu eimem Datum ermitteln
select to_char(sysdate,'iw') from dual;
Den Wochentag zu eimem Datum ermitteln
-- Wochentag
-- das Ergebnis hängt von den nlslang Einträgen in der Regitry ab!
-- Mit trim werden evtl. rechtsstehende Leerzeichen gelöscht!
select trim(to_char(sysdate,'day')) from dual;
1.1. des laufenden Jahres
select trunc(sysdate,'YY') from dual
1. des laufenden Monats
select trunc(sysdate,'MM') from dual;
Anfang des Quartals
select trunc(sysdate,'Q') from dual;
Anfang der Woche (Montag)
select trunc(sysdate,'D') from dual;
Cursor
cursor c_lieferant (p_lieferant NUMBER) IS
select kund_id
from ku
where kund_id = p_lieferant
order by xxx;
r_lieferant c_lieferant%rowtype;
b_lieferant boolean;
begin
open c_lieferant (:block3.lieferant);
fetch c_lieferant into r_lieferant;
b_lieferant := c_lieferant%found;
close c_lieferant;
end;
Prozentanzeige im Cursorloop bei Abarbeiten einer Tabelle
rec_num number(13);
proz number(13,2);
proz_z number(13,2);
rec_gesamt number(13);
BEGIN
rec_num := 0;
select count(*) into rec_gesamt from &tabelle where &where&;
proz_z := 0;
-- open cursor;
-- fetch cursor into ...
-- while cursor%found
loop
rec_num := rec_num + 1;
proz := rec_num * 100 / rec_gesamt;
if proz - proz_z >= 5 or proz >= 99.5 then
SYNCHRONIZE;
message(proz||' % Daten ('||rec_num||' Sätze) importiert',NO_ACKNOWLEDGE);
SYNCHRONIZE;
proz_z := proz;
end if;
-- Fetch cursor into ...
end loop;
-- close cursor;
end;
Linkcode (eindeutige Laufkennung z.B. für Schnittstellen) erzeugen
declare
v_linkcode varchar2(13);
begin
-- Linkcode aus Datum Uhrzeit erzeugen
v_linkcode := to_char(sysdate,'YYMMDDHH24MISS');
end;
genauer geht's wie folgt
declare
v_linkcode varchar2(17);
begin
-- Linkcode aus Datum Uhrzeit bis auf 1/1000 sec. erzeugen erzeugen
v_linkcode := to_char(systimestamp,'YYYYMMDDHH24MISSff3');
end;
nocht genauer mit sessionid:
declare
v_linkcode varchar2(24);
begin
v_linkcode := to_char(systimestamp,'YYYYMMDDHH24MISSff3')||'_'||sys_context('USERENV', 'SESSIONID');
end;
select to_char(systimestamp,'YYYYMMDDHH24MISSff3')||'_'||sys_context('USERENV', 'SESSIONID') from dual;
20080917185009351_920534
select to_char(systimestamp,'YYYYMMDDHH24MISSff3')||'_'||sys_context('USERENV', 'OS_USER')||'_'||sys_context('USERENV', 'SESSIONID') from dual;
20080917184945343_gregor.neveling_920534
Foreign Key auf Mastertabelle (Referenzielle Integrität)
-- Detailtabelle anlegen
CREATE TABLE TDETAIL (ID NUMBER (13), MASTERID NUMBER (13), FELD3 VARCHAR2 (100))
TABLESPACE KNA_EDV STORAGE (INITIAL 1m NEXT 1m MAXEXTENTS unlimited);
ALTER TABLE TDETAIL ADD CONSTRAINT FK_TMASTER
FOREIGN KEY (MASTERID) -- Key der Detailtabelle
REFERENCES TMASTER (ID) ; -- Referenzkey in der Master Tabelle (ID muß Primary Key von TMaster sein)
Rechte für Delete/Insert/Select & Update für anderen User (hope) zulassen
GRANT DELETE ON KNA_IMPHEAD TO HOPE;
GRANT INSERT ON KNA_IMPHEAD TO HOPE;
GRANT UPDATE ON KNA_IMPHEAD TO HOPE;
GRANT SELECT ON KNA_IMPHEAD TO HOPE;
oder noch einfacher:
grant all on {tabelle} to {user/Rolle} [with grantoption]
Kommentar für eine Tabellenspalte setzen
COMMENT ON COLUMN KNA_IMPHEAD.ANZAHL IS 'Anzahl Datensätze aus dem Loader';
Kommentar für eine Tabelle setzen
COMMENT ON TABLE KNA_IMP_REFERENZ IS 'Feldzuordnung mit Typengerechter Konvertierung';
Public Synonym KNA_IMPHEAD für die Tabelle EDV.KNA_IMPHEAD
CREATE PUBLIC SYNONYM KNA_IMPHEAD FOR EDV.KNA_IMPHEAD;
Logfile per utl_file schreiben
create procedure write_logfile_demo
is
diskfile utl_file.file_type;
v_path varchar2(2000) := '/knauberT/dta/CK8T/export';
begin
-- Datei "logfile_YYYYMMDD" wird append geöffnet und schreibt des Satz "Hello World" hinein
diskfile := utl_file.fopen(rtrim(v_path),'logfile_'||to_char(sysdate,'YYYYMMDD'),'a',32767);
utl_file.put_line (diskfile,'Hello World');
utl_file.fclose(diskfile);
exception
when utl_file.invalid_path then (...)
when utl_file.invalid_mode then (...)
when utl_file.invalid_filehandle then (...)
when utl_file.invalid_operation then (...)
when utl_file.read_error then (...)
when utl_file.write_error then (...)
when utl_file.internal_error then (...)
when no_data_found then (...)
when value_error then (...)
when others then (...)
end;
Datei vom Server per utl_file einlesen
/*****************************************************************************************
Das Verzeichnis p_dir muß für utl_file freigegeben (Datei init.ora bis Version 8) sein.
z.B. Verzeichis c:\utlfile auf dem Oracle Server anlegen und
utl_file_dir=c:\utlfile
in die Datei init.ora einfügen
Für 9i unter Unix gilt:
SQL> CREATE DIRECTORY log_dir AS '/appl/gl/log';
Für 9i unter Windows gilt:
SQL> CREATE DIRECTORY log_dir AS 'c:\temp';
Das Verzeichnis muß existieren und entsprechende Rechte für den User 'ORACLE' haben.
SQL> GRANT READ ON DIRECTORY log_dir TO DBA;
SQL> GRANT WRITE ON DIRECTORY log_dir TO DBA;
SQL> GRANT ALL ON DIRECTORY log_dir TO userid;
Achtung!
Auf Verzeichnisse die unter dem benannten Verzeichnis liegen kann nicht zugegriffen werden.
*******************************************************************************************/
procedure arc_protokolle (p_dir varchar2,
p_file varchar2,
)
is
diskfile utl_file.file_type;
v_rec varchar2 (32767);
v_lfd number (8);
begin
diskfile := utl_file.fopen (p_dir, p_file, 'r', 32767);
v_lfd := 0;
loop
begin
utl_file.get_line (diskfile, v_rec);
v_lfd := v_lfd + 1;
(... v_rec verarbeiten ...)
exception
when no_data_found
then
exit;
end;
end loop;
utl_file.fclose (diskfile);
exception
when utl_file.invalid_path then (...)
when utl_file.invalid_mode then (...)
when utl_file.invalid_filehandle then (...)
when utl_file.invalid_operation then (...)
when utl_file.read_error then (...)
when utl_file.write_error then (...)
when utl_file.internal_error then (...)
when no_data_found then (...)
when value_error then (...)
when others then (...)
end;
Autonome Transkation PROCEDURE put_line(p_text varchar2)
--
-- alternative zu dbms_output.put_line - schreibt Text als autonome Transaktion in die Tabelle kna_msg
--
is
PRAGMA AUTONOMOUS_TRANSACTION;
begin
insert into kna_msg(TEXT, TIMESTAMP) values (p_text, sysdate);
commit;
END;
Forms Zeilenweise bearbeiten
declare
v_alert number(3);
begin
set_alert_property('loesch_abfrage',alert_message_text,'Wollen Sie alle markierten Schnittstelle wirklich löschen?');
v_alert := show_alert('loesch_abfrage');
if v_alert = 88 then
SET_APPLICATION_PROPERTY(CURSOR_STYLE, 'BUSY');
first_record;
while :system.last_record = 'FALSE'
loop
...
next_record;
end loop;
last_record;
...
SET_APPLICATION_PROPERTY(CURSOR_STYLE, 'DEFAULT'); -- Sanduhr aus
execute_query;
end if;
end;
Non Database Felder füllen
im Trigger WHEN-NEW-RECORD-INSTANCE auf Datenblock Ebene:
first_record;
while :system.last_record = 'FALSE'
loop
if nvl(:filiale, 0) <> 0 then
:filial_bez := F_KNA_FILIAL_BEZ(:filiale);
else
:filial_bez := '';
end if;
next_record;
end loop;
last_record;
if nvl(:filiale, 0) <> 0 then
:filial_bez := F_KNA_FILIAL_BEZ(:filiale);
else
:filial_bez := '';
end if;
first_record;
Generierung eines SQL Statements für alle Tabellen eines Schemas
-- Die Tabelle/View all_tables enthält in der Spalte TABLE_NAME die Tabellennamen
-- Hier wird für alle Tabellen des Owners EDV, die im Namen mit FUS_ beginnen ein
-- public Synonym gebildet.
-- Els Ergebnis wird der Text eines SQL zum erzeugen der Synonyme erzeugt.
select 'CREATE PUBLIC SYNONYM '||TABLE_NAME
||' for '||TABLE_NAME from all_tables where owner = 'EDV' and table_name like 'FUS_%';
Systemmeldung bei commit unterdrücken
PROCEDURE commit_io IS
oldmsg VARCHAR2(2);
BEGIN
oldmsg := :System.Message_Level;
:System.Message_Level := '10';
commit;
:System.Message_Level := oldmsg;
EXCEPTION
WHEN Form_Trigger_Failure THEN
:System.Message_Level := oldmsg;
END commit_io;
Dynamische Befehl aus PL/SQL per EXECUTE IMMEDIATE
declare
sql_stat varchar2(2000);
emp_rec kna_centralvoucher.voucherno%rowtype;
v_voucherno kna_centralvoucher.voucherno%type;
begin
sql_stat := 'truncate table kna_centralvoucher';
EXECUTE IMMEDIATE (sql_stat);
sql_stat := 'insert into kna_centralvoucher (select voucherno - 99000000000000000000 from ads_centralvoucher)';
EXECUTE IMMEDIATE (sql_stat);
EXECUTE IMMEDIATE 'CREATE TABLE bonus (id NUMBER, amt NUMBER)';
sql_stat := 'INSERT INTO kna_centralvoucher (voucherno) VALUES (:1)';
EXECUTE IMMEDIATE sql_stat USING v_voucherno;
sql_stat := 'SELECT * FROM kna_centralvoucher WHERE voucherno = :1';
EXECUTE IMMEDIATE sql_stat INTO emp_rec USING v_voucherno;
commit;
end;
CREATE OR REPLACE function dyn_sel(p_tab varchar2
, p_feld varchar2
, p_where varchar2
, p_groupby varchar2
, p_orderby varchar2)
return varchar2
-- select dyn_sel('unitrade_kon','konf_nr||'';''||konf_wert','nvl(konf_nr,999)<87',null,'konf_nr desc') erg from dual;
is
sql_stat varchar2(2000);
v_val varchar2(2000);
begin
sql_stat := 'SELECT '||p_feld||' FROM '||p_tab||' WHERE rownum = 1';
if p_where is not null then
sql_stat := sql_stat || ' and '||p_where;
end if;
if p_groupby is not null then
sql_stat := sql_stat || ' group by '||p_groupby;
end if;
if p_orderby is not null then
sql_stat := sql_stat || ' order by '||p_orderby;
end if;
EXECUTE IMMEDIATE sql_stat INTO v_val;
return v_val;
exception
--Fehlerausgabe wenn SQL-Fehler:
--error function dyn_sel "SELECT koxnf_wert FROM unitrade_kon WHERE rownum = 1 and konf_nr=87"
--ORA-00904: "KOXNF_WERT": invalid identifier
when others then return 'error function dyn_sel "'||sql_stat||'"'||chr(13)||chr(10)||sqlerrm;
end;
/
Dynamische Select per Ref Cursor
Testfunktion:
CREATE OR REPLACE FUNCTION test_dyn_select(p_select IN VARCHAR2)
RETURN varchar2
IS
TYPE cv_type IS REF CURSOR;
cv cv_type;
v_wert VARCHAR2 (32767);
BEGIN
OPEN cv FOR p_select;
FETCH cv INTO v_wert;
CLOSE cv;
RETURN v_wert;
EXCEPTION
WHEN OTHERS
THEN
return('*** Fehler ***'||sqlerrm);
end;
Test:
select test_dyn_select('select count(*) from kna_imphead') from dual;
--------------------------------------------------------------------------------
10170
1 row selected
Variable mit fester Länge und links- oder rechsbündiger Ausrichtung
CREATE OR REPLACE function fixStr(p_ausr char, p_len number, p_str varchar2) return varchar2 is
--
-- Funktion zur Ausrichtung von Feldern für Ausgabedateien
-- Parameter:
-- p_ausr: R: Rechtsbündige Ausrichtung, links wird mit 0 aufgefüllt
-- L: Linksbündige Ausrichtung, rechts werden Leerzeichen aufgefüllt
-- p_len Länge des ausgabefeldes in Bytes
-- p_str Feldinhalt der ausgerichtet werden soll
-- Beispiel:
-- select fixstr('L',10, '1')||'*' from dual; --> "1 *"
-- select fixstr('R',10, '1')||'*' from dual; --> '0000000001*'
v_str varchar2(2000);
begin
if upper(nvl(p_ausr,' ')) <> 'R' then
if length(p_str) < p_len then
v_str := rpad(p_str,p_len);
else
v_str := substr(p_str, 1, p_len);
end if;
else
if length(p_str) < p_len then
v_str := lpad(p_str,p_len,'0');
else
v_str := substr(p_str, 1, p_len);
end if;
end if;
return v_str;
end;
/
Umwandlung Varchar2 in Zahl mit NLS_NUMERIC_CHARACTERS
v_rab_proz := to_number(substr('02,50 xx', 1, 5), '99D99', 'NLS_NUMERIC_CHARACTERS = '',.''');