Monday, June 19, 2017

Notification_Msg and checksum, and undocumented Oracle APEX tricks

I am sharing an Oracle APEX trick that I have just found because of a project requirement. It is not documented and took a while for me to figure it out.

If you want to pass a notification message or success message to a next page by using URL redirect, you just need to concatenate following strings to the end of your URL ( &notification_msg=[your message]/[message checksum]/ )
(If you use branch it simply happens if you click “include process success message”.)

Example PL/SQL code:
v_NOT_FOUND_TICKET_MSG       varchar2(512) := 'App+can+not+get+this+ticket+information+from+XXXX!+This+information+may+not+be+up+to+date.';
v_URL  := v_URL || '&notification_msg=' || v_NOT_FOUND_TICKET_MSG || '/' ||  WWV_FLOW_UTILITIES.LOV_CHECKSUM(v_NOT_FOUND_TICKET_MSG) || '/';

Extra info:
WWV_FLOW_UTILITIES.LOV_CHECKSUM is an internal function that generates an MD5 after removing special characters and adding application Salt. (APEX for some usage create Salt from IP by HEXTORAW(L_SESSION_REC.IP_ADDRESS) and some time from Application Alias). GET_PAGE_CHECKSUM also is quite similar with some additional Salt concatenated like page number and the request.

Example URL: (%2F is equal to / which is a URL encode APEX automatically does):!+This+information+may+not+be+up+to+date.%2FC7E9A908F8ED9DD46FF05BE88E2C405C%2F

Note: in URL encoding we replace space with +

Friday, June 3, 2016

How to convert a string to selectable table by a function to use in APEX or ORACLE SQL

Converting comma separated values to a list and table has a long history. I do remember lots of time faced a scenario I need to parse a delimited string. Generally, in Oracle and writing query in APEX, converting these strings to a selectable table is a nice way to face these scenarios and simplify codes.
In APEX, it is common we use "Checkbox Item" in forms. They return a delimited string like "Option1:Option2:Option3". Even there are many ways to parse and use them in condition and query. However, a simple and fast way is to convert them to a table to be used in SQL query and avoid any PL/SQL or custom code.
An example to show what I mean is (PXX_DEP is APEX checkbox item):

 Select * from emp where DEPTNO in (  
   select to_number(str) from table(string_to_selectable_table(:PXX_DEP)) aa  

I have developed function "string_to_selectable_table" to ease this use similar to APEX original function "APEX_UTIL.STRING_TO_TABLE" but selectable. I did not use APEX array type "APEX_APPLICATION_GLOBAL.VC_ARR2" to show we could create different type and I used a regular expression that can be used to convert more complex strings to the selectable array. You can download it from my GitHub by using this URL, and it contains data type, function, and an example.

Type and function are (use GitHub to access the latest version) :

 CREATE or REPLACE TYPE t_str_row_tab IS TABLE OF VARCHAR2(4000) ;  
 CREATE or REPLACE FUNCTION string_to_selectable_table ( p_string   IN VARCHAR2,  p_delimiter  IN VARCHAR2 DEFAULT ':')  
    RETURN t_str_row_tab PIPELINED  
    n_row NUMBER := 1 ;  
    v_str VARCHAR2(4000) ;  
    v_str := trim(regexp_substr(p_string, '[^'|| p_delimiter || ']+', 1, n_row) ) ;  
    WHILE ( v_str is not null )  
      PIPE ROW ( v_str );  
      n_row := n_row + 1 ;  
      v_str := trim(regexp_substr(p_string, '[^'|| p_delimiter || ']+', 1, n_row) ) ;  
    END LOOP;  
 END string_to_selectable_table;  

To test you can run :
 select COLUMN_VALUE from table(string_to_selectable_table('Option1:Option2:Option3', ':') ) aa;  

Wednesday, March 30, 2016

Finally Auto increment ID in Oracle provided by Oracle DB 12c

Those who worked with few databases have known that Oracle Database had a lack of functionality to create an ID column which was an incremental number to be used as ID. Now, it provided with the name "Identity Columns" in Oracle 12c version. Even, now we can use sequence.nextval in default part of columns which is not advisable (using Identity is a better option).
In other DBs it looks like:
Previously in Oracle to have an ID column that starts with 1 and increments one by one for each new record we need to create a sequence and a trigger to assign a number from sequence to column for each insert query. However, with IDENTITY we can alter a column as auto increment identity. For example:
Now, by just an insert and without mentioning "id" column in the insert, it will get 1 for the first row and 2 for the second insert.
INSERT INTO ATK_test_t (name) VALUES ('First Name');
INSERT INTO ATK_test_t (name) VALUES ('Second Name'),(;Third Name');
and the result will be:
ID          NAME
---------- ------------------------------
1            First Name
2            Second Name
3            Third Name
This new Identity function has three types of:
  1. ALWAYS: It gets number, and you do not allow to assign any other number in insert or pass null to this column ( generate error ORA-32795) 
  2. DEFAULT: We can assign or update any number but cannot assign Null 
  3. DEFAULT ON NULL: We can assign Null, but Oracle will assign next number for its value.
  I have found this functionality here with good explanation and more examples.

Friday, February 26, 2016

Which programing languages Do I need to learn, invest, use or forget?

Confusion of using which programming languages (and following it which framework) has a long history. Story begins from the first day of my programming era, 20 years ago, when I just learnt Quick Basic, Foxpro. I remember when I learnt C, I was quite convinced, C is the best and I would be a C developer. In fact, officially, I stepped in IT and SE market when I learnt C in early 1999. However, When in 2001 Microsoft just released C# (.Net), I was one of the first people who started to learn it but later because of my job's conditions I moved to PHP programming in the end.

It was a very confusing time. I wanted to be an expert in one programming language, but also I loved to learn them all, and in fact, every new project moved me in learning a new one. However, when I was going to choose one between programming languages, surprisingly my filed changed to R&D on Oracle technologies. Later when I moved back to programming, I started to use Perl, Java and later on PL/SQL! In fact, I was in a small company and depend on each project; the path could change. This story of confusion became harder and harder as much as my experience grows. Now I knew, PL/SQL, SQL, Java, Perl, PHP, HTML, JavaScript, C++, C#, Shell and R. In fact, when I just used R in 2015 in a Big Data consultation project, I was so excited like when I learnt C in 1999. So, I started wondering that what programming languages are existing which I may love them and I should invest in them as well?

During one of my consultation in 2015, I found an excellent Gartner article ($2000USD) about the situation of Programming languages (here). With this Clock, we could see where Programming languages are in a high-level view, although you cannot see power and advantage of each. The article also advised when and why in IT and enterprise project we could use each one of programming languages.
For example use Dart, CoffeeScript or TypeScript instead of JavaScripts for new HTML5 projects, use portable Ruby instead of Dot NET. This article clearly support the strong and stable position of Java, PERL and PL/SQL which are my expertise and I believed in them as well.
I also found that in comparison to 2013, Gartner dropped "Oracle Forms" and replaced it with PL/SQL which it is because of Oracle Application Express (APEX) and some existing database objects. (Oracle APEX is a new Oracle web-based agile development framework that is replacing Oracle form as this replacement was expecting...).
So, now I have a big list to check to find alternatives. I hope this list helps you as well.

Friday, July 12, 2013

No SQL Database

It looks like "No SQL" also going to be famous like Cloud (if it dose not happen yet at least).
I have started to learn about "No SQL" not because Google (bigtable) or Facebook using "NO SQL", just because Oracle has announced its No SQL DB (here and here) which uses Key-Value Data model based on its "Oracle Berkeley DB Java" (here and here).

Based on "Martin Fowler" (YouTube, Brief PDF) "No SQL" have not a unique definition and we should define it with its characteristics which are:
  1. Non-Relational
  2. Cluster-Friendly 
  3. Mostly Open Source
  4. Developed in 21st Century and Web
  5. Schema-Less
And they have 5 Design types:(more example)
  1. Key-Value ( i.e. Project Voldemort, Riak, Redis)
  2. Document (i.e. mongoDB, Raven DB, CouchDB)
  3. Column-Family (i.e. Apache HBASE, Cassandra)
  4. Graph (i.e. Neo4j)

More important is, looks like different type of "No SQL" and RDBMS are useful for different businesses, contexts and purposes. For me it means, I have one "No SQL" option for analytical purpose beside Essbase, Vertica and Infobright, which is "Apache Cassandra".

Wednesday, July 10, 2013

Logic, Language and Philosophy

Reasoning relies on establishing the truth of statements, which can then be used to build up a train of thought leading to a conclusion and philosophy cover this idea of constructing a rational argument. It means, philosophy is not so much about coming up with the answers to fundamental questions as it is about the process of trying to find these answers, using reasoning rather than accepting without question conventional views or traditional authority.
Now, problem is philosophical logic unlike mathematics, is expressed in words rather than numbers or symbols which is subject to all the ambiguities and subtleties inherent in language. Thus, understanding and examine the language we use in construction of reasoned argument is important beside the logic itself. This requirement has been created "philosophy of language" in 20th century.

A brief to philosphy of logic and language which I am going to love!

Sunday, November 20, 2011

Acquisition in IT world is ongoing and Google/Oracle/IBM are on top

(Updated in Aug 2013: Looks Acquisition has been slow down in 2012-13)
Do you know acquisition market of small and medium IT companies become more interesting in these years. It seems, it is going to become a new way in business (If it is not already a business )!Therefore, which reasons are making small companies interesting for a big IT companies? Can we say, they are: Their market's threats ? Increasing share price? Going to new filed and market? Removing potential competitors?
Based on a simple counting , top companies in acquisition in 2012 are Google and Oracle and in 2011 is is Google.
Even sometimes it is not easy to count and sum values of these acquisitions,but below are the numbers of big IT companies acquisitions in (2013,2012, 2011, 2010, 2009 and 2008)[The most expensive acquire$):
It is clear, 2008 business crisis and new technologies were  main triggers of these acquisitions.  Meanwhile, next year should be more interesting, because of Euro crisis, China and USA companies may buy out some Europe companies (I think I will put Lenovo in the next year list!) which shows Acquisition in IT world still is on going.

Saturday, August 6, 2011

Do you want to web programming on Windows by PHP or Perl?

If you want to web programing with PHP/Perl or even Python, is better to use a  WAMP software bundle which include (WindowsApacheMySQLPHP/Perl/Python) instead of download and install them separately.

Currently there are around 30 of them, so which package? Best place to start is here in Wikipedia which compare these packages.
Previously, even I used PHP and Perl on Linux but for my students which was not familiar with Linux, I suggested EasyPHP which is one of first WAMP, or WampServerthat is the first in Google search.

But now, which package is the best? XAMPP or ...

Thursday, July 14, 2011

Oracle Golden Gate pushed back Oracle Streams

Yesterday, I attend Oracle GoldenGate hand-on round table. I just can say, Perfect and Expensive product. GoldenGate price is 17,500 USD per CPU (Active Data Guard 10,000 USD per CPU). In addition lack of GUI was the worst thing I saw. However, it forces DBA/SA become expert and use scripting instead of do stupid mistake with GUI.

Oracle acquire GoldenGate Software in July 2009 which was one of real-time data integration solutions leader. Now, they decided to keep this product as their strategic product that keep away Stream and Data Active Guard from Data warehousing and Integration (ODI and OWB are still much different). These tree are quite same (Active Guard is only for DR purpose and one way) but GoldenGate is not only for Oracle Database! It can join and sync different DB brand with different version.

Oracle GoldenGate can sync and integrate most RDBMS like DB2, MS SqlServer, Sybase, Teradata  (No Informix), besides, it can replicate all ODBC base DB. (For more info refer to here ). In main supported DBs, it read active redo log and write changes to a trail file as a queue and send it to target. Everything is command base and rule base which I can say it quite acceptable and simple (We should see it in production and real complex scenarios).

Oracle GoldenGate Veridata (30,000 USD) is another tools to check synchronization process which I think is much expensive than its usage even if you get 80% discount.( but not for Banking sector)

(I will Upload My screen Shots later)

But what happen to Streams (no more in store). Oracle mentioned only support.
Nice diagram of Oracle Stream Bi-Directional (Sync) Method.

I love its hub method which possible implementation IS in multi-service centers around the world or country which each of them are independent and sync to center or region.