This is a proof of concept for a multiplayer game in Oracle APEX. Players log into an APEX application and choose a team (Blue/Red). Then the game presents each player with a page and a randomly chosen math problem. The page however is “locked” by a modal page telling the player that the game is paused.
The button the the Raspberry Pi controls the game state: Stopped, Ready, Running. When the game is stopped or ready, the player’s screens are blocked with the modal window.
A breadboard with a LED and two buttons is connected to the Raspberry Pi (the blue button doesn’t do anything).
The red button cycles through the games states. When the game is in Running state, the modal window goes away, and the players can answer the math question. When the button is pressed again, the game goes into Stopped state, and the modal window pops up, preventing the players from answering more questions.
In addition, the LED also shows the game state: Off- game stopped, Blinking – ready, On – running.
This video shows me cycling through the three game states. There are four players connected: Red team on phones, Blue team on separate browsers.
At first the game is Stopped (LED off, modal window up), the first button press puts the game in Ready state (LED blinks, modal window up). The second button press puts the game in running state (LED on, modal window disappears). The final button press puts the game back int Stopped state (LED off, modal window up).
To change the size (height/width) of a modal dialog page in APEX 5, edit the modal page’s page attributes, and edit the attributes of the Dialog section:
Here is a very helpful blog post by Andy Tulley on how to set/get CLOB values from an APEX collection.
I guess I’m a published author now. :)
Today the new edition of Expert Oracle Application Express was released. My contribution is chapter 12: Map Integration, where I show how to integrate maps, such as Google, or Leaflet maps into an APEX application. Here is the introduction from the book:
Any database application that contains location data, such as addresses or geographic coordinates, may benefit from displaying this data on a map. It may be of further benefit, to allow users to interact with this data by associating it with other data, or even perform geometric calculations against it.
In this chapter we will explore some options to free the geographic data from its confines of the database tables, and render it in useful ways on interactive maps. To accomplish this we will look at a number of tools and techniques available to us in APEX, the Oracle database, and some publicly available APIs.
We will learn how to turn address data into geographic coordinates and how to use these coordinates to display addresses on a map. Further we will take a look at various mapping APIs, and how to use Oracle’s powerful spatial features to find relationships between geographic data points.
You can get the book from Amazon. If you do, please don’t forget to leave a comment.
The login screens of some of the APEX 5 sample applications have a nice look and include a check mark when you enter text into the username/password fields. This article will demonstrate how to create your own login page to look like that of some of the sample applications.
Edit your Login page (101):
- Change login region template to Login.
- Changes to Username and Password fields:
- Change Template to Hidden.
- Change Value Placehoder to Username / Password respectively.
- Change Value Required to Yes.
- Change CSS classes:
- Username: icon-login-username
- Password: icon-login-password
- Change Post Text for username and password to
<span class=”t-Login-iconValidation a-Icon icon-check”></span>.
- Change the Login button Button Position to Next.
Save the changes and run the page. The result should look like this:
To can add one of the application icons from the the packaged applications select on of the icons my previous blog post and make the following changes:
Navigate to Edit Application Definition -> User Interface, and edit the interface for your application.
Under Cascading Style Sheets add the this CSS file: #IMAGE_PREFIX#pkgapp_ui/css/5.0#MIN#.css
On the Login page (101) click the Log In region and add the name of the icon class to Icon CSS Classes:
Save and run the log in page. It should look something like this:
Tags: apex, application express, oracle, pl/sql
I just came accross a little nuisance: During attempt to load some spreadsheet data into a new table using the APEX Data Load wizard in the SQL Workshop, I saw that the Schema select list was empty:
The issue was that the parsing schema was granted CREATE ANY TABLE. However for this list to be populated, the parsing schema needs the CREATE TABLE privilege.
Adding An Element
newColor = ‘Green’
$s(‘P1_LIST’, $v(‘P1_LIST’) + ‘:’ + newColor);
This is problematic, if P1_LIST were empty. Then you could end up with:
P1_LIST = “:Green”
To avoid this you can first split the list into an array, “push” the new color to the array, and then write it back as a delimited list:
newColor = 'Green'; arr = $v('P1_LIST').split(':'); arr.push(newColor); $s('P1_LIST', arr.join(':'));
Removing an Element:
To remove a color from our list is a little trickier. But working with arrays helps. Let’s say we want to remove Blue. We’ll pull the delimited list back into an array (arr). We’ll find the array index of Blue, then loop through arr and push all elements except Blue into a new array (arr2). Lastly we’ll write the new array back to the page item:
arr = $v('P1_LIST').split(':'); //change list into an array arr.splice(arr.indexOf('Blue'),1); //remove the color blue $s('P1_LIST', arr.join(':')); //write array back to list
Thanks to Sentinel for the simplified version using splice();
This query may be more applicable to a bill of materials type of query. I’ve added a new column to the EMP table (value number). Then I only populated a value for this column for the leaf nodes, i.e. the employees lowest in the hierarchy (ADAMS, SMITH, ALLEN, WARD, MARTIN, TURNER, JAMES, MILLER).
This query the sums up these leaf node values to their manager, and to the manager’s manager.
with totals as ( select empno ,ename ,mgr ,value ,level lev ,rownum rn ,sum(nvl(value, 0)) over( partition by connect_by_root(empno) ) tot_val from emp t connect by prior empno=mgr ) , lev1 as ( select empno ,mgr ,rn ,ename ,value ,tot_val from totals where lev = 1 ) select lpad(' ', 2*level, ' ') ||ename name ,value ,tot_val from lev1 connect by prior empno=mgr start with mgr is null
This query starts with the leaf node and climbs up the hierarchy to the top ancestor. It then displays the result in the usual top-down order:
CMR@vrep > list select lvl ,employee from (select level lvl ,lpad(' ', 2 * ( max(rownum) over () - level) , ' ') ||empno ||', ' ||ename employee ,rownum rn from emp connect by empno=prior mgr start with ename=upper('&ename')) order by rn desc CMR@vrep > / Enter value for ename: adams LVL EMPLOYEE ---------- -------------------------- 4 7839, KING 3 7566, JONES 2 7788, SCOTT 1 7876, ADAMS CMR@vrep >