APEX: Let’s Talk About Charts Attributes (Inverted Scale)

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.

More“APEX: Let’s Talk About Charts Attributes (Inverted Scale)”

Understanding Apex URL

An basic step into Apex development is to understand URL syntax.
I keep this note in my favorites folder, to check anytime.

http://apex.oracle.com/ords/f?p=4350:1:220883407765693447

or

f?p=App:Page:Session:Request:Debug:ClearCache:itemNames:itemValues:PrinterFriendly

where

  • 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 🙂
Cassiano.

javascript:apex.confirm

The most simple way to ask for your user attention, is to popup a javascript browser question. Something like “Do you really wanna proceed?”

In the APEX world, just remember You do not need to reinvent the wheel!
Let’s use the native apex javascript Api, that comes with the function named Confirm, which ask user for a confirmation, before to submit page or before run some process.

Easy Example

First, select the button you want this behavior, then set the property Target to URL.
Second, set the target url to below javascript code, and don’t forget to adapt the message for your need’s.

   javascript:apex.confirm('Delete the user &APP_USER. Really?', 'DELETE');

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”.

Complex Example

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.

   javascript:apex.confirm("Save Department?", {
       request:"SAVE",
       set:{ "P1_DEPTNO":10, "P1_EMPNO":5433 } } );

 
Cassiano.

 

 

Shellscript: Using eval and SQLPlus

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:

      OK:DBNAME=xpto; INST_NAME=xpto_1;

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

Cassiano.

Say Hello to Oracle Apex and for the new Blog member too!

Hi people!

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.

Oracle Apex is a development tool that enables you to build applications using only your web browser, using basically PL-SQL, this familiarity helps to create departmental applications. Even DBA’s can create good web applications easily. Apex is not a new tool, was first released on 2006, and previously it was called HTML DB. After 10 years of development, offers a modern IDE, and with more dedication, you can use to build complex solutions using CSS and JavaScript.

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.

get_workspace

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.

Cassiano.