On our mobile phones we are used to have very convenient widgets to ease the handling of mobile applications. One of these widgets is a flipswitch which is perfect to show "two-state" values. For this kind of widget here is my new APEX plugin. This kind of widget will come "out of the box" with APEX 5.1 but if you will have it earlier, here it is!
Solution 1: SQL Subquery
-- General SELECT (select <Blob-Column> from <Remote-Table>@<DB-Link> where ...) AS blob_column FROM DUAL;
Customers often ask, how to make sure, that a particular PL/SQL-code which runs in several sessions at the same time, process the data for the FIFO paradigm. The answer is: no problem at all. Oracle offers the dbms_lock package which is very helpful there to solve problems arround locking and synchronizing.
From time to time I will be faced the problem to move data from one table to another. Under normal circumstances this is not a big deal, but if the source table contains a long-/raw column this is impossible to manage that with plain SQL. So here is a suitable workaround to solve this problem with a few lines of PL/SQL code
Starting point is a table with a long raw column:
insert into long_table select 'new id', long_column /* LONG-Feld */ from long_table a where a.long_id = 'some old id';
This ends up with "ORA-00997: illegal use of LONG datatype error"
The workaround is:
... exactly this was the question of a customer, because he wants to join tables between two Oracle databases. Well, the idea for this solution is based upon the fact, that we can have a foreign key constraint on a view. So let's go ahead:
1. We create a view which points to a table/view on the foreign database, using a database link:
create view test_view_dblink as select * from some_table@external_oracle_database;
2. We create a foreign key constraint on that view
alter view test_view_dblink add constraint test_view_dblink_fk foreign key (column_name) references table_in_local_database(column_name) disable;
The "disable" clause at the end of the statement is the important thing because constraints on views must be disabled.
2b. If we would need a primary key we also can define one:
alter view test_view_dblink add constraint test_view_dblink_pk primary key (column_name) disable;
Last but not least: with this technique, we can create relations to all databases for which we have a oracle connector/gateway (e.g. IBM DB2, ...). Check it out!
This article describes how to tokenize a string, just with plain "out of the box" Oracle SQL. In the IT we often encounter requirements to split a string in parts. With Oracle PL/SQL we have a quite elegant solution for that.
The solution is based on the ability of oracle to use regular expressions within a SQL statement. In the first example we have comma seperated string, containing the most important crew members of the USS Enterprise:
SELECT regexp_substr(str, '[^,]+', 1, LEVEL) AS splitted_element, LEVEL AS element_no FROM (SELECT rownum AS id, 'Kirk,Spock,Scotty,McCoy,Uhura' str FROM dual) CONNECT BY instr(str, ',', 1, LEVEL - 1) > 0 AND id = PRIOR id AND PRIOR dbms_random.value IS NOT null;
This article is about how Java programs can be developed directly in the Oracle database and in particular, it shows an example how to access the file system of the operating system. Since the Oracle version 8, it is possible to write Java code direct within the database. So it would be possible, to write stored procedures with Java instead of using PL/SQL. Nevertheless, the advantages of PL/SQL are the safe and stable processing of mass data, so there is no need to change to Java in this part. But if we have issues who are located outside of the database, we will reach the limits of PL/SQL very quickly. This is the point where Java starts, because the language itself is independent of the operating system and therefore it is predestined for tasks like this. In addition, we can rely on a vast range of finished Java solutions, who can than operate within the Oracle database. Maybe there are a few slightly modifications needed but in general it is not a big deal.
However, a weak point of this is the actuality of Java! The version within the database is "lagging" behind the current versions. This is because that a stable Java version is the base of the development of a new database release. While this new release than is productive for years, there are no changes in the internal version of Java, whereas the Java world outside of the Oracle database has already seen significant developments. The result is, that a Oracle version 11.2, which is very common right now (the first release dates from 2007), has the Java Runtime version 1.5.0_10 (2005!) included.
The entire source code of the directory list example you will find here!
Stored Procedures with Java?
The most common language within the Oracle database is still PL/SQL. This is the prefered language to develop Stored Procedures, Packages or Triggers. In order to develop with Java, the Java sources must be uploaded into the database. From this, the classes are extracted and displayed in the Data Dictionary as a schema object. It allows Java sources (.java) and class files (.class) or archives (.jar, .zip) to be uploaded.
XML and JSON are perfect to manage and transfer structured data on the internet. But in a relational database like oracle the data is stored in tables who can be linked via joins. This article will show how to get a XML or JSON out of a SQL statement which contains a master-detail dependency. For easy use, I put all together in a PL/SQL Package which you will find at the end of the article and in the download area.
In our case study we have two tables to store persons and their phone numbers. Both tables are connected through a foreign key. See the following script for the definition of the tables:
create table ex01_person_tb ( id number, name varchar2(50) ); insert into ex01_person_tb values (1, 'Roger Waters'); insert into ex01_person_tb values (2, 'David Gilmour'); create table ex01_phone_tb ( id number, person_id number, phone_number varchar2(50) ); insert into ex01_phone_tb values (1, 1, '543 454433'); insert into ex01_phone_tb values (2, 1, '512 4776443'); insert into ex01_phone_tb values (3, 1, '521 6454423'); insert into ex01_phone_tb values (4, 2, '212 8332464'); insert into ex01_phone_tb values (5, 2, '312 6736423');
A perfect case study for the development of APEX Plugins is the integration of Google Maps into APEX. The Plugin we will develop here should not only show a part of a map, it should also provide the ability to place multiple markers on the map. The positions of the markers will be passed as a string which contains a JSON object. All the other information will come from settings of the Plugin itself. In the most cases, the GPS position we want to place on the map, came out of the database. Therefor we need a PL/SQL Function which converts this data into a JSON string. All the required functions are described in this article. To see a Demo of the Plugin, click here!
Since version 4.0 it is possible to extend the functionality of APEX with the so-called "Plugins". The plugins are used in the APEX Application Builder like ordinary components (eg. items, buttons, forms, etc.). According to there use, Plugins are separated in the following types:
- Region Type Plugins
- Item Type Plugins
- Dynamic Action Plugins
- Process Type Plugins
|Sometimes, things which seems to be very simple, are in fact very complicated. One of those things are the custom ordering of Featured Articles in Joomla 3.3. It was quite tricky to figure out how it works but I found the key to solve the problem:
Choose the Menu Item which contains the Featured Articles. In the "Layout" Tab there is an item called "Article Order". In the drop down list, select the entry "Featured Articles Order". This was the obvious part of the problem. Now, the tricky thing is, that there exists an item called "Category Order" which contains the value "Use Global". This setting controls the order of the Featured Articles as well. So to make sure, that the ordering of the Featured Articles is as you like, you have to switch it to "No Order". (Figure 1)