Hello! If you had play with Apex before, you know how easy is to build a simple report to present your data. But sometimes, your boss will ask you to build something more “graphical” or with a better design. But I never thought in color themes or pictures when I developed my simple reports in Sqlplus. Those colorful themes and design things are, most of the times, not familiar for DBA’s.
An basic step into Apex development is to understand URL syntax.
I keep this note in my favorites folder, to check anytime.
- App -> Application ID or alias.
- Page -> Page number or alias.
- Session -> Identify a session ID.
- Request -> A keyword that you can use to react in your process workflow. When you press a button, request will be set to button action name, e.g. when press Submit or Next page, your Request variable should have “submit” value.
- Debug -> Set this flag to YES to increase log level (must be uppercase).
- ClearCache -> Specify the numeric page number to clear cached items on a single page, this flag set all item’s values to null. To clear cached items on multiple pages, use a comma-separated list of page numbers. Clearing a page’s cache also resets any stateful processes on the page.
- itemsNames -> Comma-delimited list of item names.
- itemsValues -> Comma-delimited list of item values.
- PrinterFriendly -> set to YES, to use a printer friendly template.
I hope this help you too 🙂
In the APEX world, just remember You do not need to reinvent the wheel!
First, select the button you want this behavior, then set the property Target to URL.
The second parameter can be used to set the value of REQUEST, when the page is submitted. You can use this value selectively run some Process point, setting the property Condition to “when request = value”.
For more complex needs, you can set Apex Items values, before to proceed with page submit. In this case, the second parameter should be an Object, with all items and values necessary for your page flow and correct process.
I always liked bash programming, and sometimes need to set Bash variables using information from Oracle tables.
To achieve that I’m using below solution, which I explain in details later.
# SQLPlus should return a string with all Bash commands $ORACLE_HOME/bin/sqlplus -S -L -R 3 / as sysdba > /tmp/sqlplus.log <<-EOF SET PAGES 0 FEEDBACK OFF TIMING OFF VERIFY OFF LINES 1000 SELECT 'OK:DBNAME='||UPPER(D.NAME)||'; INST_NAME='||I.INSTANCE_NAME AS STR FROM V\$DATABASE D, V\$INSTANCE I; EOF # Now, tests if sqlplus exit fine, and check if result string starts with OK keyword if [ $? -eq 0 ] && [ "$( cat /tmp/sqlplus.log | head -n 1 | cut -d: -f1 )" == "OK" ]; then sed -i 's/OK://g' /tmp/sqlplus.log while read r; do eval "$r"; done </tmp/sqlplus.log else echo "Failed to search local instance $ORACLE_SID" return 2 fi
In the first part, I call sqlplus, which select should return an string that contains valid bash commands, to set all variables I need. In this example, sqlplus returns Database Name and Instance Name:
The second part, exists only for consistency checks. It verify if result string starts with “OK” keywork. If all went fine, it execute the result string using the bash command eval.
eval – That is where magic happens!
The command eval, can be used to evaluate (and execute) an ordinary string, using the current bash context and environment. That is different than when you put your commands in a subshell.
The below source code, reads sqlplus.log and execute every command using eval:
while read line; do eval "$line"; done </tmp/sqlplus.log
That’s my first post and I would like introduce me and this great tool what I will talk about here, at this site, but Let’s start about Oracle Application Express, or Apex, which is probably your most intention here! You can read about my history with Apex in the end of this article.
Apex also comes with a entire system to manage your development life cycle. Using the Team Development it is possible to track your project progress from brainstorm to tracking bugs and continuous maintenances.
You can start using and testing Oracle Apex right now, just accessing apex.oracle.com and creating your own workspace. Just click Get Started and select Free Workspace. Remember that should be used for educational propose.
By the way, in the next weeks and some articles from now, I intend to write about how to create an entire application, describing most of standards options and explaining Oracle Apex in details.
I start using Apex in version 2, when the standard templates produces applications that looked like Enterprise Manager some years ago. The latest version 5.02 was released in October 2015. The Apex 5 has a revolutionary IDE, which is in the same way powerful, intuitive, clean and easy to use.
Enjoy and welcome to Apex World! There is an active community on OTN that supports mostly users needs and questions through discussion web forums.