quantum computers – the quantum revolution begins

Quantum computers have the potential to change our society in a similar seminal way, that conventional computers have done up to this day.

What started 30 years ago as a visionary legacy of an ingenious physicist is becoming real in these days.

I have started a new website, which covers this mysterious new technology quantum computers comprehensively. All you need to bring along is a good portion of curiosity … and a good knowledge of German 😉

www.quantencomputer-info.de

You are welcome to check it out!

 

 

Advertisements
Posted in Uncategorized | Leave a comment

Set the value of a popup lov dynamically in Oracle APEX

Dynamic Actions are a great tool in Oracle APEX and you can use them very easily in an declarative way. Yet, to set the value of a popup lov dynamically, it is easier to render the link with the java script yourself.
On Apextipps I give an example how to render the link as a simple display-item. It does not need additional page items, dynamic actions or other Apex components. You can easily copy it to other pages of your Oracle APEX application.

https://apextipps.wordpress.com/2017/09/28/den-wert-fuer-eine-popup-lov-dynamisch-setzen-in-oracle-apex/
You will find my demo site on

https://apex.oracle.com/pls/apex/f?p=DEMO_JENS_MARRE:POP_LOV_SET_VALUE

(log in with: demo/demo)

Posted in Apex, Oracle | Leave a comment

A generic data model for organigrams

During my work as an IT consultant I have also implemented systems for resource planing for many years (both objects and human resources). Based on this know-how I and my colleague have generated a generic data model for organigrams which is especially optimized for this purpose.

The basic idea is to introduce so called person groups which are linked together like recursive mailing lists. This enables both arbitrary hierarchical and composite-like structures of those groups.

Attached to the person groups are persons, disposable objects and event types. These entities are linked together by events. Users of the system are also authorized by attaching privileges to person groups like „write events for objects of department Logistic“, „write events for persons and event types of department OS Administration“.

I sketch this data model further on our blog apextipps:

https://apextipps.wordpress.com/2016/01/29/ein-generisches-datenmodell-fuer-organigramme

Posted in Uncategorized | Tagged , | Leave a comment

Sending a tabular HTML report via APEX Mail

While operating a Oracle APEX application it is often necessary to send tabular SQL reports via mail. This may be an administration mail about the status of the APEX application or for  reporting data to users or stakeholder of the APEX application. Especially for the latter case the layout of this mail should be nice and handy. An easy way to accomplish this is to send the report as an HTML table in a HTML mail via APEX mail.

Recently I have posted a generic function on apextipps, which executes an arbitrary SQL select and renders the result set as an HTML table. Along I gave an example for a general CSS style sheet to do the HTML layout part of the APEX mail.

http://apextipps.wordpress.com/2015/04/23/einen-tabellarischen-html-bericht-per-apex-mail-versenden/

Posted in Apex, CSS, Mail | Leave a comment

How to generate history tables in Oracle SQL

By enabling Oracle Flashback you have an excellent option to report and search for historical data in your Oracle tables. Yet even this option might prove to be insufficient for certain requirements or situations. E.g. if Flashback is not enabled or you might need to track usernames along with the DML history changes or you might need to store the historical data more fine grained than what Oracle Flashback offers to you.

This is a Oracle PL/SQL script, that I use to generate for any table in my data models

  • a history table <table name>_HIST
  • along with a Oracle DML-trigger <table name>_TBIUD that populates the historical data
  • and a history view  <table name>_HIST_V that combines the historical and the current data

Of course you need to adapt this script to the conventions of your data model:

SET SERVEROUTPUT ON
 
var l_tablename VARCHAR2(30)
 
exec :l_tablename := UPPER('MY_TABLE');
 
DECLARE
        /**
         * Simple dbms_output.put_line
         */
        PROCEDURE sysout (p_text IN VARCHAR2)
        IS
        BEGIN
          DBMS_OUTPUT.put_line(p_text);
        END;

 
        /**
         * Returns the column list as a comma separated list.
         * Optionally a prefix will be added for each Oracle table
         */
        FUNCTION get_column_list (  p_tablename IN VARCHAR2,
                                    p_column_prefix IN VARCHAR2 
                                                       DEFAULT '')
          RETURN VARCHAR2
        IS
                l_return_str VARCHAR2(32767) :=NULL;
        BEGIN
                FOR l_tab_rec IN (
                        SELECT column_name
                        FROM  user_tab_columns
                        WHERE table_name=p_tablename            
                ) LOOP
                        IF l_return_str IS NULL THEN
                                l_return_str := '';
                        ELSE    
                                l_return_str := l_return_str || 
                                                   ',' || CHR(10);
                        END IF;
 
                        l_return_str := l_return_str || 
                                         p_column_prefix||'"'||
                                         l_tab_rec.column_name||'"';
                END LOOP;
 
                RETURN l_return_str;
        END;
BEGIN
 
/* Creates the Oracle create table statement
*/
sysout('PROMPT '||:l_tablename||'_HIST');

sysout('--DROP table "'||:l_tablename||'_HIST";');
sysout('
CREATE TABLE "'||:l_tablename||'_HIST"
AS 
SELECT app_log_seq.nextval AS hist_id, 
       CAST(NULL AS DATE) AS hist_operation_on, 
       CAST(NULL AS VARCHAR2(50)) AS hist_operation_by,
       CAST(NULL AS VARCHAR2(3)) AS hist_dml_operation,
       tab.*
FROM  "'||:l_tablename||'" tab
WHERE   1=2;');
 
sysout('
COMMENT ON TABLE "'||:l_tablename||'_HIST" IS ''
This Oracle table contains the full historical data of the table 
"'||:l_tablename||'", inluding the DML operation type that modified 
this data (Insert, Update, Delete). For each change of data a copy 
of the original row is copied to the history-table. 
For the INSERT-operation the row will also be copied to the 
history table, to provide the full history of it.
The current row not contained in the history table. The column names
are identical to the column names of the original table. 
Only the columns to document the DML change possess 
the prefix HIST_'';
 
COMMENT ON COLUMN "'||:l_tablename||'_HIST".hist_id IS
''The primary key of each history table'';
 
COMMENT ON COLUMN "'||:l_tablename||'_HIST".hist_operation_on IS
''Day and time, when this content was changed. The row in the 
history table is the content of the row **before** this change.'';
 
COMMENT ON COLUMN "'||:l_tablename||'_HIST".hist_operation_by IS
''The user who executed the DML operation. 
The row in the _HIST-table is the content of the row 
**before** this change.'';
 
COMMENT ON COLUMN "'||:l_tablename||'_HIST".hist_dml_operation IS
''The type of the DML operation INS, UPD, DEL.
The row in the history table is the content of the row 
**before** this change.'';
');
 
/* Creates the create Oracle-trigger statement
*/
sysout('PROMPT '||:l_tablename||'_TBIUD');
 
sysout('
CREATE OR REPLACE
TRIGGER "'||:l_tablename||'_TBIUD'||'"
BEFORE INSERT OR UPDATE OR DELETE ON "'||:l_tablename||'"
FOR EACH ROW
DECLARE
  l_dml_operation       VARCHAR2(3);
 
  PROCEDURE insert_old
  AS
  BEGIN
      INSERT INTO "'||:l_tablename||'_HIST"
      (hist_id, hist_operation_on, hist_operation_by, hist_dml_operation,
        '|| get_column_list ( p_tablename => :l_tablename,  
                              p_column_prefix => '') ||'
      )
      VALUES
      (app_log_seq.nextval, sysdate, app_util.get_current_user, 
          l_dml_operation,
        '|| get_column_list ( p_tablename => :l_tablename,  
                              p_column_prefix => ':OLD.') ||'
      );
  END;
 
 
  PROCEDURE insert_new
  AS
  BEGIN
      INSERT INTO "'||:l_tablename||'_HIST"
      (hist_id, hist_operation_on, hist_operation_by, 
        hist_dml_operation,
        '|| get_column_list ( p_tablename => :l_tablename,  
                              p_column_prefix => '') ||'
      )
      VALUES
      (app_log_seq.nextval, sysdate, app_util.get_current_user, 
        l_dml_operation,
        '|| get_column_list ( p_tablename => :l_tablename,  
                              p_column_prefix => ':NEW.') ||'
      );
  END;
 
 
 
BEGIN
 
 
  IF NOT deleting THEN
 
    app_util.init_commons_tbiu(
        p_is_inserting    => INSERTING,
        p_new_id          => :NEW.ROLS_ID,
        p_new_created_on => :NEW.ROLS_CREATED_ON,
        p_new_created_by    => :NEW.ROLS_CREATED_BY,
        p_new_updated_on => :NEW.ROLS_UPDATED_ON,
        p_new_updated_by => :NEW.ROLS_UPDATED_BY,
        p_old_created_on   => :OLD.ROLS_CREATED_ON,
        p_old_created_by  => :OLD.ROLS_CREATED_BY,
        p_old_updated_on   => :OLD.ROLS_UPDATED_ON,
        p_old_updated_by  => :OLD.ROLS_UPDATED_BY);
 
  END IF;
 
 
  BEGIN  
    IF inserting THEN
      l_dml_operation :=''INS'';
      insert_new;
    ELSIF updating THEN
      l_dml_operation :=''UPD'';
      insert_old;
    ELSIF deleting THEN
      l_dml_operation :=''DEL'';
      insert_old;
    END IF;  
 
  EXCEPTION     
        WHEN OTHERS THEN
          logger.log_error(sqlerrm, '''||:l_tablename||'_TBIUD'||''');
 
  END;
END;
/');
 
 
/* Creates the create Oracle-view statement
*/

sysout('PROMPT '||:l_tablename||'_HIST_V');
 
 
sysout('
CREATE OR REPLACE FORCE VIEW "'||:l_tablename||'_HIST_V'||'"
AS
  SELECT  NULL AS HIST_ID,
          sysdate AS HIST_OPERATION_ON,
          NULL AS HIST_OPERATION_BY,
          ''CURR'' AS HIST_DML_OPERATION,
          tab.*
  FROM "'||:l_tablename||'" tab
  UNION ALL
  SELECT *
  FROM "'||:l_tablename||'_HIST"
/');
 
sysout('
COMMENT ON TABLE "'||:l_tablename||'_HIST_V'||'"  IS 
''This view contains the combined view of the historical and 
the current version of each row of the Oracle 
table "'||:l_tablename||'".
The entries with "HIST_DML_OPERATION=CURR" labels the current content.
The entries with "HIST_DML_OPERATION=INS, DEL oder UPD" labels 
the historical content, for with the DML operation was executed.
The complete history is being obtained 
sorting by HIST_OPERATION_ON.'';
/');
 
END;
/


Posted in Oracle, PL/SQL | Leave a comment

Big Bang, dark energy, multiverses and all that

Having studied theoretical physics and math one of my passions is to keep up with later developments in this kind of science. Sometimes I listen to a podcast lecture series on my way to work. Recently I stumbled upon the lecture “The Early Universe” held by Alan Guth at MIT in 2013. Alan Guth, being one of the fathers of the inflation theory, is probably one of the best addresses to learn something about cosmology.

In his first lecture he gives an deep introduction about what he is going to talk about. At this stage he uses no math at all and little physical background. So if you ever wanted to learn something about the Big Bang model and current topics such as cosmic inflation, dark energy and cosmological multiverses this is a great place to start with:

http://ocw.mit.edu/courses/physics/8-286-the-early-universe-fall-2013/video-lectures/lecture-1-inflationary-cosmology-is-our-universe-part-of-a-multiverse

Posted in Physics | Tagged , | Leave a comment

Flexible Resource-Gantt-Chart with Oracle Apex and AnyChart / AnyGantt (2)

The standard charting features in Oracle Apex provide an easy and fast way to create various simple charts. After you collect more and more requirements, you will soon get to the limits of the Apex-Wizards. Yet, the Oracle Apex environment possesses the full functionality of the AnyChart-library. Thus, you are still able to create much more powerful charts. The very detailed online documentation and XML-reference of the AnyChart – library is a very good help along the way.

I have written a post on Apex-Tipps on how to extend my base implementation for a Resource-Gantt-Chart in Oracle Apex using the AnyGantt-library, to accomplish all requirements that I have addressed for my Resource-Gantt-Chart:

  • The Resource-Gantt-Chart is controlled by search criteria
  • If a search criterion is changed, the Gantt-Chart is refreshed „the ajax way“
  • The Gantt-Chart is in German
  • Holidays are marked
  • The bars are displayed in different colors, with a nice gradient
  • The bars are labeled
  • On mouse hover a popup info is displayed
  • On mouse click on a bar, an additional dialog / page is opened.

https://apextipps.wordpress.com/2015/01/14/flexibler-resource-gantt-chart-mit-oracle-apex-und-anychart-anygantt-

You will find my demo site on

https://apex.oracle.com/pls/apex/f?p=DEMO_JENS_MARRE:ANY_GANTT

(log in with: demo/demo)

 

Posted in Uncategorized | Leave a comment