Skip to content

Instantly share code, notes, and snippets.

[Unit]
Description=Oracle DB Startup-Shutdown
After=syslog.target network.target
[Service]
RemainAfterExit=yes
User=oracle
Group=oinstall
Restart=no
ExecStart=/app/oracle/ora122/bin/dbstart /app/oracle/ora122
@achampav
achampav / oratab
Last active September 22, 2018 04:48
db01:/app/oracle/ora122:Y
db02:/app/oracle/ora122:N
db03:/app/oracle/ora122:Y
db04:/app/oracle/ora122:Y
db05:/app/oracle/ora122:N
systemctl enable oracle
systemctl start oracle
systemctl stop oracle
create or replace function fet_exemple()
returns event_trigger AS
$$
declare
l_obj record;
l_rename character varying := '';
l_col character varying := '';
l_function_table character varying := '';
l_function_view character varying := '';
l_trigger_table character varying := '';
create event trigger et_exemple
on ddl_command_end
execute function fet_exemple();
declare
l_obj record;
l_table_name character varying := '';
...
begin
...
for l_obj in select * from pg_event_trigger_ddl_commands()
loop
if l_obj.command_tag = 'CREATE TABLE' then
l_table_name = substr(l_obj.object_identity,position('.' in l_obj.object_identity) + 1);
c1 cursor (c_exemple_table_name character varying) is
(select column_name from information_schema.columns
where table_name = c_exemple_table_name
order by ordinal_position);
l_view := 'create or replace view ' || l_table_schema || '.' || l_table_name || ' as select oid,' || l_view_select || ' from ' || l_exemple_table_name;
raise notice '%',l_view;
execute l_view;
/* Création de la fonction trigger qui traitera à la volée les insert, update et delete */
l_function_view := 'create or replace function ' || l_table_schema || '.tf_' || l_table_name || '() returns trigger AS $FC$
begin
if tg_op = ''INSERT'' then
insert into ' || l_exemple_table_name || l_insert_columns || ' values ' || l_insert_values || ';
return new;