How to install Oracle APEX

sqlplus / as sysdba
sql> SELECT dbms_xdb.gethttpport FROM dual;
sql> EXEC dbms_xdb.sethttpport(0);

>COL comp_name FOR A30
>SELECT comp_name, version, status FROM dba_registry WHERE comp_id='APEX';
 
If there is already installed:

cd $ORACLE_HOME/apex
$ sqlplus / as sysdba

sql> @apxremov_con.sql

If the tablespace is not there already:

CREATE TABLESPACE APEX DATAFILE '/u02/oradata/snap11g/apex_01.dbf'
SIZE 200M REUSE AUTOEXTEND ON NEXT 10M MAXSIZE 1000M LOGGING
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;

 sql> @apexins APEX APEX TEMP /i/
 admin password :

sqlplus / as sysdba
sql> @apxchpwd
Under the old apex versions you can edit the script summoned by the apxchpwd.sql (apxxepwd.sql) such as the password can be as strong or weak as you wish. Under the newer versions, you can decide this only from the admin interface.

 embedded PL/SQL Gateway configuration (EPG)

sql> @apex_epg_config.sql /u01/app/oracle/product/11.2.0.3/db_1

 Update the APEX images for the new release

sql> @apxldimg.sql /u01/app/oracle/product/11.2.0.3/db_1

ALTER USER anonymous ACCOUNT UNLOCK;
ALTER USER xdb ACCOUNT UNLOCK;
ALTER USER apex_public_user ACCOUNT UNLOCK;
ALTER USER flows_files ACCOUNT UNLOCK;
 enable the Oracle XML DB HTTP Server port (8080):

 EXEC dbms_xdb.sethttpport(8080);

Enable remote HTTP connections (optional):
EXEC dbms_xdb.setListenerLocalAccess(l_access => FALSE);

ORA-39097: Data Pump job encountered unexpected error -12801 while expdp

ORA-39097: Data Pump job encountered unexpected error -12801 
ORA-39065: unexpected master process exception in MAIN 
ORA-12801: error signaled in parallel query server P..., instance <instance_name> 
ORA-01460: unimplemented or unreasonable conversion requested Job <job_name> stopped due to fatal error at <failure_time>. 


The problem seems to be a bug and the solution can be an update to minimum 11.2.0.4 version.
For me, the workaround was:

> alter system set  PARALLEL_FORCE_LOCAL=TRUE;

Create audit triggers for all the tables of a schema and audit tables in another schema

First, create the  owner of the audit tables (in our scenario, will be "john_audit" and the audited schema will be "john"). Be aware that the audit user should mandatory name like audited name + "_audit".

CREATE OR REPLACE PROCEDURE create_audit_tables (table_owner VARCHAR2)
IS
   CURSOR c_tables (
      table_owner VARCHAR2)
   IS
SELECT ot.owner AS owner, ot.table_name AS table_name
        FROM all_tables ot
       WHERE    lower( ot.owner) = lower(table_owner)
             ;

   aud_user  varchar2(100);
   v_sql     VARCHAR2 (8000);
   v_count1  number;
   v_count2  number;
   v_count   NUMBER := 0;
   v_aud     VARCHAR2 (30);
   n_count   number;
BEGIN
   aud_user:=table_owner||'_AUDIT';
   select count(*) into v_count1  from dba_tables where  lower(owner)=lower(table_owner);
   select count(*) into v_count2  from dba_tables where  lower(owner)=lower(aud_user);
   v_count:=v_count1-v_count2;
   if v_count>0 then
   FOR r_table IN c_tables (table_owner)
   LOOP
      BEGIN
         v_aud := r_table.table_name;
         select count(*) into n_count from dba_tables where lower(table_name)=lower(v_aud) and lower(owner)=lower(aud_user);
        
         if n_count=0 then
        
         v_sql :=
               'create table '||aud_user
            ||    '.'
            || v_aud
            || ' as select * from '
            || r_table.owner
            || '.'
            || r_table.table_name
            || ' where 0 = 1';

         DBMS_OUTPUT.put_line ('Info: ' || v_sql);


         EXECUTE IMMEDIATE v_sql;

         v_sql :=
               'alter table  '||aud_user||'.'
            || v_aud
            || ' add ( AUDIT_ACTION char(1), AUDIT_BY varchar2(50), AUDIT_AT TIMESTAMP)';

         EXECUTE IMMEDIATE v_sql;

        end if;
     
        -- v_count := c_tables%ROWCOUNT;
      EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.put_line (
                  'Failed to create table '
               || v_aud
               || ' due to '
               || SQLERRM);
      END;
   END LOOP;
      end if;
   IF v_count = 0
   THEN
      DBMS_OUTPUT.put_line ('No audit tables created');
   ELSE
      DBMS_OUTPUT.put_line (v_count || ' audit tables created.');
   END IF;
END;
/

Run the procedure (will create audit tables in audit schema only for the tables created after the last run):
 
begin
 create_audit_tables('john'); --it's not caps sensitive
end;
/

Now, a function which will gatter the columns together:



create or replace FUNCTION get_columns_for_table (
     table_owner   VARCHAR2,
     t_name   VARCHAR2,
     prefix  VARCHAR2
  ) RETURN  CLOB
  IS
     v_text CLOB;
  BEGIN
     FOR getrec IN (SELECT column_name
                      FROM all_tab_columns
                     WHERE table_name = t_name
        AND owner = table_owner
        AND data_type<>'BLOB')
     LOOP
       v_text := v_text
          || ','
          || prefix
          || getrec.column_name
          || CHR (10)
          || '                             ';
     END LOOP;

     RETURN ltrim(v_text,',');
  END;

And a function that will compare the old and new columns values, in order to find if an update applied.



create or replace function get_column_comparison (
     table_owner   VARCHAR2,
     t_name   VARCHAR2
  ) RETURN CLOB
  IS
    v_text CLOB;
  BEGIN
    FOR getrec IN (SELECT column_name
                     FROM all_tab_columns
                    WHERE table_name = t_name
                      AND owner = table_owner
                      AND data_type<>'BLOB')
   LOOP
      v_text := v_text
         || ' or( (:old.'
         || getrec.column_name
         || ' <> :new.'
         || getrec.column_name
         || ') or (:old.'
         || getrec.column_name
         || ' IS NULL and  :new.'
         || getrec.column_name
         || ' IS NOT NULL)  or (:old.'
         || getrec.column_name
         || ' IS NOT NULL and  :new.'
         || getrec.column_name
         || ' IS NULL))'
         || CHR (10)
         || '                ';
   END LOOP;
 
   v_text := LTRIM (v_text, ' or');
   RETURN v_text;
  END; 

And now the procedure that will produce the triggers:



CREATE OR REPLACE PROCEDURE create_audit_triggers (table_owner VARCHAR2)
IS
   CURSOR c_tab_inc (
      table_owner VARCHAR2)
   IS
      SELECT ot.owner AS owner, ot.table_name AS table_name
        FROM all_tables ot
       WHERE     lower (ot.owner) =lower ( table_owner)
            ;
   aud_user  varchar2 (100);
   v_query   VARCHAR2 (32767);
   v_count   NUMBER := 0;
BEGIN
   aud_user:=table_owner||'_AUDIT';
   -- the triggers should have privileges in the audit schema
   FOR c IN (SELECT owner, table_name FROM all_tables WHERE lower(owner)=lower(aud_user))
        LOOP
            EXECUTE IMMEDIATE 'grant select,insert,update on '||c.owner||'.'||c.table_name||' to '||table_owner;
        END LOOP;
   FOR r_tab_inc IN c_tab_inc (table_owner)
   LOOP
      BEGIN


         v_query :=
               'CREATE OR REPLACE TRIGGER '
            ||table_owner
            || '.A'
            || substr(r_tab_inc.table_name,1,29)
            || ' AFTER INSERT OR UPDATE OR DELETE ON '
            || r_tab_inc.owner
            || '.'
            || r_tab_inc.table_name
            || ' FOR EACH ROW'
            || CHR (10)
            || 'DECLARE '
            || CHR (10)
            || ' v_user varchar2(30):=null;'
            || CHR (10)
            || ' v_action varchar2(15);'
            || CHR (10)
            || 'BEGIN'
            || CHR (10)
            || '   SELECT SYS_CONTEXT (''USERENV'', ''session_user'') session_user'
            || CHR (10)
            || '   INTO v_user'
            || CHR (10)
            || '   FROM DUAL;'
            || CHR (10)
            || ' if inserting then '
            || CHR (10)
            || ' v_action:=''INSERT'';'
            || CHR (10)
            || '      insert into '
            || aud_user
            || '.'
            || r_tab_inc.table_name

            || '('
            || get_columns_for_table (r_tab_inc.owner,
                                      r_tab_inc.table_name,
                                      NULL)
            || '      ,AUDIT_ACTION,AUDIT_BY,AUDIT_AT)'
            || CHR (10)
            || '      values ('
            || get_columns_for_table (r_tab_inc.owner,
                                      r_tab_inc.table_name,
                                      ':new.')
            || '      ,''I'',v_user,SYSDATE);'
            || CHR (10)
            || ' elsif updating then '
            || CHR (10)
            || ' v_action:=''UPDATE'';'
            || CHR (10)
            || '   if '
            || get_column_comparison (r_tab_inc.owner, r_tab_inc.table_name)
            || ' then '
            || CHR (10)
            || '      insert into '
            ||aud_user
            ||'.'
            || r_tab_inc.table_name
            || '('

            || get_columns_for_table (r_tab_inc.owner,
                                      r_tab_inc.table_name,
                                      NULL)
            || '      ,AUDIT_ACTION,AUDIT_BY,AUDIT_AT)'
            || CHR (10)
            || '      values ('
            || get_columns_for_table (r_tab_inc.owner,
                                      r_tab_inc.table_name,
                                      ':new.')
            || '      ,''U'',v_user,SYSDATE);'
            || CHR (10)
            || '   end if;'
            || ' elsif deleting then'
            || CHR (10)
            || ' v_action:=''DELETING'';'
            || CHR (10)
            || '      insert into '
            ||aud_user
            ||'.'
            || r_tab_inc.table_name
            || '('
            || get_columns_for_table (r_tab_inc.owner,
                                      r_tab_inc.table_name,
                                      NULL)
            || '      ,AUDIT_ACTION,AUDIT_BY,AUDIT_AT)'
            || CHR (10)
            || '      values ('
            || get_columns_for_table (r_tab_inc.owner,
                                      r_tab_inc.table_name,
                                      ':old.')
            || '      ,''D'',v_user,SYSDATE);'
            || CHR (10)
            || '   end if;'
            || CHR (10)
            || 'END;';

         DBMS_OUTPUT.put_line (
               'CREATE TRIGGER '
            || REPLACE (r_tab_inc.table_name, 'TABLE_', 'TRIGGER_'));

         EXECUTE IMMEDIATE v_query;

         DBMS_OUTPUT.put_line (
               'Audit trigger '
            || REPLACE (r_tab_inc.table_name, 'TABLE_', 'TRIGGER_')
            || ' created.');


         v_count := c_tab_inc%ROWCOUNT;
      EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.put_line (
                  'Failed to create audit trigger for '
               || r_tab_inc.owner
               || '.'
               || r_tab_inc.table_name
               || ' due to '
               || SQLERRM);
      END;
   END LOOP;

   IF v_count = 0
   THEN
      DBMS_OUTPUT.put_line ('No audit triggers created');
   END IF;
END;
/
Notice the sequence which grant the audited user to modify any table from the audit schema, created before with the create_audit_tables procedure.
Now, all you have to do is to run the procedure and you will create as many triggers as tables they are. The triggers will be either created or replaced, so the procedure will run for every single table in the schema, no mother if it was created after the last run of the
create_audit_tables procedure or not.


begin
CREATE_AUDIT_TRIGGERS('john');
end;
/

Create incrementing triggers for all the PKs from all the tables of a schema

This is the procedure:

CREATE OR REPLACE PROCEDURE create_pk_triggers (table_owner VARCHAR2)
IS
   CURSOR c_tables (
      table_owner VARCHAR2)
   IS

        select * FROM ALL_CONS_COLUMNS
                WHERE
                    CONSTRAINT_NAME IN ( SELECT CONSTRAINT_NAME FROM ALL_CONSTRAINTS where CONSTRAINT_TYPE = 'P') and
                    lower(owner)=lower(table_owner);

   v_sql     VARCHAR2 (8000);
   v_count   NUMBER := 0;
   v_aud     VARCHAR2 (30);
   v_col     varchar2(50);

  
BEGIN
   FOR r_table IN c_tables (table_owner)
   LOOP
      BEGIN
         v_aud := r_table.table_name;
         v_col := r_table.column_name;
         v_sql :=
               'CREATE OR REPLACE TRIGGER inspk_'
            ||substr(v_aud,1,24)
            || CHR (10)
            ||' before insert on '
            ||table_owner
            ||'.'
            || v_aud
            || CHR (10)
            || ' for each row begin '
            || CHR (10)
            || ' if inserting and :new.'
            || v_col
            || ' is null then '
            || CHR (10)
            || ':new.'
            || v_col
            || ' :='
            || ' call4prop_seq.nextval;'
            || CHR (10)
            || '  end if;'
            || CHR (10)
            || '   end;'
            ;

         DBMS_OUTPUT.put_line ('Info: ' || v_sql);

         EXECUTE IMMEDIATE v_sql;


         v_count := c_tables%ROWCOUNT;
      EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.put_line (
                  'Failed to create table '
               || v_aud
               || ' due to '
               || SQLERRM);
      END;
   END LOOP;

   IF v_count = 0
   THEN
      DBMS_OUTPUT.put_line ('No audit tables created');
   ELSE
      DBMS_OUTPUT.put_line (v_count || ' audit tables created.');
   END IF;
END;
/

And here is how to run the procedure, using as a parameter the name of the schema owner (the capitals rule will not apply):

 
begin
 create_pk_triggers('username');
end;
/