Oracle Apex - Dynamically Color Text field

It is often useful to highlight certain fields when certain (usually incorrect) values are entered as a form of on the fly validation. 

Suppose you had a fairly large form with many fields many of which only accept numbers. It would be much more user friendly if you could alert end-users to incorrect values as they enter it, rather than let them submit and have a big list of invalid entries to sift through.

1) Put the following code in page header.

<script language="JavaScript" type="text/javascript">

function setCol(pThis)

{

var vv = $v(pThis);

var cls = '#'+pThis;

if(isNaN(vv))

{

$(cls).css("background-color","red");

}

else

{

$(cls).css("background-color","white");

}

}

</script>

2) Create Dynamic Action with following configuration:

1. Type Advanced (for pre-4.2)
2. Event: change
3. Selection Type: item
4. Item(s): P1_ENAME, P1_MOB (comma seperate the list of items)
5. True Action: 
1. Action: Execute Javascript Code
2. Code: setCol(this.triggeringElement.id);
6. Affected Items:
1. Pick the items in #4 above.

What this does is basically highlight textfield when non-numeric characters are entered (isNAN). 

Here is how it should look like if implemented properly. You can take this and do more fancy stuff like validating against regex. 

http://apex.oracle.com/pls/apex/f?p=21796:1

Oracle - Move schema from one tablespace to another

Starting from 11G, Oracle offers a much simpler way to migrate schema for tablespaces with Data Pump utility. Previously you would have to export the schema and then drop the user re-import the schema and then rebuild all the indexes.

With Data Pump the process of switching tablespace is much simpler.

STEP 1: Export the schema using datapump

expdp system/system_password SCHEMAS=MY_SCHEMA DIRECTORY=DATA_PUMP_DIR DUMPFILE=MY_SCHEMA.dmp LOGFILE=expdp.log

Review the log to ensure the export is done properly.

STEP 2: Drop the user from database

 DROP USER MY_SCHEMA CASCADE;

STEP 3: Import the schema with REMAP_TABLESPACE

impdp system/my_schema SCHEMAS=MY_SCHEMA REMAP_TABLESPACE=SYSTEM:MY_SCHEMA_TBSPACE DIRECTORY=DATA_PUMP_DIR DUMPFILE=MY_SCHEMA.dmp LOGFILE=impdp.log

STEP 4 : Verify Tablespace Change & Validity of Objects

Check for default tablespace by running:

select username, default_tablespace from dba_users;

Also check if all objects are valid, if not compile them

SQL> select owner, status from dba_objects where upper(owner)='MY_SCHEMA';

Check the indexes:

select index_name, status from all_indexes where status ='UNUSABLE';

For more information, check out the white paper on Data Pump:

Quick Start Guide:
http://www.oracle.com/technetwork/issue-archive/2009/09-jul/datapump11g2009-quickstart-128718.pdf

Documentation:
http://docs.oracle.com/cd/B12037_01/server.101/b10825/dp_import.htm

Creating database backups using exp & and crontab

The following is a neat little script that will allow you to create logical backups (and remove old ones) of your schema in the same file system of the database.


ORACLE_BASE=/u01/app/oracle
export ORACLE_BASE
ORACLE_HOME=/u01/app/oracle/product/11.2.0/xe
export ORACLE_HOME
ORACLE_SID=XE
export ORACLE_SID
PATH=$ORACLE_HOME/bin:$PATH
export PATH

BKUP_DEST=/home/rode/cbackups
find $BKUP_DEST -name 'backup*.dmp' -mtime +10 -exec rm {} \;



cd /home/rode/cbackups && /u01/app/oracle/product/11.2.0/xe/bin/exp schema/password FILE=backup_`date +'%Y%m%d-%H%M'`.dmp

You will need to change the bits highlighted in orange with paths of your system. The script starts by exporting necessary paths, then removes any old backups greater than 10 days before using the exp utility to create the backup.

To set it up, follow these instructions: 

1. Create a directory where the backups will be stored. In my case it is: /home/ai/cbackups
2. Open vi and save the scripts (after replacing bits in orange with your own setup) in your home directory as say: /home/ai/backup_script.sh
3. Next, run crontab -e to set up a new cron job.
4. Add an entry like:
   10 0 * * * /home/ai/backup_script.sh

Above the the script will run every 10 minutes past midnight. Check out this link on how to schedule crontab: http://www.adminschoice.com/crontab-quick-reference


You may opt (and better idea) to use data pump instead as it supercedes the export/import utilities and if exporting involves lots of data. Check this document on how to use data pump:
http://www.oracle.com/technetwork/issue-archive/2009/09-jul/datapump11g2009-quickstart-128718.pdf

Resolving ORA-01940 error

This post shows how to get around ORA-01940 error. You typically get this error when the schema you are trying to drop is connected to something (presumably another application).

The following script will allow you to kill the sessions and drop the schema.

set serveroutput on
DECLARE
  cursor c1 is select SID, SERIAL# from v$session where username = '<schema_name';
BEGIN
FOR rec IN c1 loop
EXECUTE IMMEDIATE 'alter system kill session '''||rec.SID||','||rec.SERIAL#||'''';
end loop;
DROP USER <SCHEMA_NAME> CASCADE;
END;

Oracle Apex: Adding Up Tabular Form Column Values

Often with tabular form we get the requirement to tally up the total of one of the columns as user add/edit/delete rows of data.

In this post I'll show an easy & efficient way to achieve this using Javascript.

Step 1

Get your Javascript function to add-up the total ready. I have created a simple function which you can customise for your application. You will need to know the ID of the tabular form element you are trying to add.

All editable APEX tabular forms that are ID'ed like f0x_000x. You can find them out by using Firebug (chrome, firefox) or IE Developer Tool by hitting F12 as below. 

In my case I need add up the total in Salary column as I add rows or edit entries and add it to the Total Salary text field above the tabular form.

So here is the script. Find out the ID of your field and update the italics part. 

<script language="JavaScript" type="text/javascript">

 function addTotal()
 {
  var items = document.getElementsByName("f07"); // Tabular form column to add up
  
  $total = 0;
  $itemValue = 0;
  for (var i = 0; i < items.length; i++)
  {  
   // if non-numeric character was entered, it will be considered as 0, 
   // isNaN returns true if anything but number was entered
   if(isNaN(items[i].value) || items[i].value == null || items[i].value == "")
    $itemValue = 0;
   else
    $itemValue = parseFloat(items[i].value); // convert to number
   
   $total =$total+ $itemValue; // add up
  } 
  
  // $x sets the text field to be updated to the column total just calculated
  $x('P5_TOTAL_SALARY').value = $total;
 }

</script>

STEP 2. Test out the javascript.

Its a good idea to make sure your code works before putting it page attribute. So open up Firebug, go to the console tab and copy & paste the body of the function (everything in between the curly braces) and hit enter. If your Total Salary textfield gets updated as below then the function is working fine. 

STEP 3. 

Go to page attribute and in HTML Header & Body Attribute enter the code above.

STEP 4.
Next in page attribute, go to Javascript tab and  and add the newly created function addTotal() to where it says Execute when Page Loads. This will ensure that columns are totaled when page is loaded.

STEP 5. 

I will wrap that tabular form with a < DIV> tag and call this addTotal() function with onkeyup event. This will update the column total on every subsequent entries. 

With these steps, you should be able to add up the total of a tabular form column.

Here is how the it should behave if you implement the above steps correctly. 

http://apex.oracle.com/pls/apex/f?p=63895:5 

Script to Get Oracle Database Size

Save the following code as SQL file and run it via SQL developer or SQLPlus as SYSDBA to get the database size (dont forget the / in the end).

SET SERVEROUTPUT ON

Declare

  ddf Number:= 0;

  dtf Number:= 0;

  log_bytes Number:= 0;

  total Number:= 0;

BEGIN

  select sum(bytes)/power(1024,3) into ddf from dba_data_files;

  select sum(bytes)/power(1024,3) into dtf from dba_temp_files;

  select sum(bytes)/power(1024,3) into log_bytes from v$log;

  total:= round(ddf+dtf+log_bytes, 3);

  dbms_output.put_line('TOTAL DB Size is: '||total||'GB ');

END;
/

Fixing /bin/bash^M: bad interpreter: No such file or directory

If you write a bash script in Windows and you get this error after FTP'ing and running it in Linux, try the following to fix it

perl -i -pe's/\r$//;' script_name.sh

Reverse Engineering Oracle Database Schema to ER Diagram

In this post, I'll show how to use Oracle SQL Developer to reverse engineer a schema into a relational diagram. You will need SQL Developer v3.x for this.


1) Go to File >> Import >> Data Dictionary

2) This will open up the Import Wizard. Click on Add to connect to the Database which contains the schema you wish to reverse engineer.

3) Once you have successfully tested connection click OK.

4) Select the newly created connection and click on Next

5) You will be prompted for password again, enter and click OK. 

6) Now we select the schema. In my case I selected a schema called Tutorial that comes bundled with Oracle APEX.

7) Next we select the tables. You may select all tables or a handful to see the relationships between them. Here I wanted to see the relationship between the tables prefixed by OEHR (again comes with APEX).

8) Next you will get a view summary. Verify and click Finish

9) You will be asked for your logins for 3rd time in this process. Enter and click OK.

10) Review the log and close to view your ER diagram.

11) To print your ER diagram to image/PDF. Go to Data Modeler >> Print Diagram >> To Image File.

Hiding a button in Oracle Apex

There may be a situations where you want to hide a button without removing it or making it's Condition = Never.

Simply add the following: style="display:none" to Button's attribute to accomplish this.

Hashing passwords in Oracle Database using MD5

When building applications (especially with Oracle Apex) we often have the requirement for managing users with a decent authentication system set up. A security consideration here is hashing password.

You can use the following function to hash your password using MD5.

However if you recursively hash it several times, you can secure your application and prevent dictionary attacks.

Here is a very simple function I wrote for this purpose.



create or replace Function hashPass
   ( raw_pass IN varchar2 )
   RETURN varchar2
IS
    I NUMBER;
    RES VARCHAR2(500);
BEGIN
    res:=raw_pass; -- start by putting raw value into result

    FOR I IN 1..95 LOOP -- hash it 95 times to prevent dictionary attacks
        SELECT WWV_FLOW_ITEM.MD5(res) into res FROM DUAL;
    END LOOP;

    RETURN RES; -- return hash
END;​

Note: 

1. Hashing with MD5 once is never secure as there is possibility of dictionary attacks.
2. I used 95 times for my example. You may use any arbitrary number. 

Oracle Apex: Resizing an image (BLOB) in interactive report

If you are putting an image in one of your columns in an interactive/classic report, you may want to reduce them all to same size or make the oversized images appear smaller.

Simply add this CSS to your page HTML header section:

<style type="text/css">

.apexir_WORKSHEET_DATA  td[headers="IMG_COL"] img
{

  width: 100px;
  height: 100px; 
}

</style>

Replace IMG_COL with the name of the BLOB column holding the image.

Note: This may not work with Internet Explorer.

Finding a text in files in linux

To search for a text in a bunch of files in a directory in Linux use grep command recursively as below:

$ grep -irn "text" /home/ai 

This will search for the word "text" in all files in home directory and all sub directories.

The -i asks it to ignore case.
The -r lets grep dig into the directory and all sub-directories recursively.
The -n outputs the line number when the match is found.
  

A very basic AJAX form validation in Oracle APEX

AJAX can be used efficiently with Oracle APEX to create dynamic form validation on the fly. Here is a simple and clumsy example of how it might be of use.


1. Create your Region and form items. For every form item, we would create a corresponding Display Only item as well to hold validation messages.


2. The Javascript/AJAX code to go in Edit Page >> HTML Header and Body Attributes

Here is the full code that is supposed to go above:


<script language="JavaScript" type="text/javascript">


 function chck()
 { 


var txtItem = $v('P1_NUMBER');

if (txtItem != null && !isNaN(txtItem))
{
var get = new htmldb_Get(null,null,'APPLICATION_PROCESS=VALIDATE',0);
get.add('MSG',txtItem);
gReturn = get.get();

if(gReturn)
{
$x('P1_MSG').innerHTML= gReturn;
}
else
{
$x('P1_MSG').innerHTML='nothing to show';
}
get = null;


} 
else if (isNaN(txtItem))
{
$x('P1_MSG').innerHTML='You must enter a number in text box';
}
else
{
$x('P1_MSG').innerHTML='';
}
}

</script>

3. The code makes a call to an Application Process called VALIDATE which I have created (below) in shared Components. It extracts value from the page item with $v( ) and passes it to the application Item MSG.




4. We now create the application process VALIDATE. This will simply run a query to check if the number entered exists in database and output (via htp.p) the appropriate message.

5.  As you can see above, the process makes use of an item called MSG. This is an application Item we create from Shared Components of the application.

6. Lastly we go to the form Item (P1_NUMBER) which will trigger the process with onBlur event. 

You can test it out here: http://apex.oracle.com/pls/apex/f?p=56259:1

Valid Entries: 0123456789

SQL Query to Find out Oracle Database Version

To find out your Oracle Database version any of these 2 queries:


SELECT * FROM V$VERSION;


which yields the following result:



BANNER                                                                           
-----------------------------------------------------------------
Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production              
PL/SQL Release 11.2.0.2.0 - Production                                           
CORE 11.2.0.2.0 Production                                                         
TNS for 32-bit Windows: Version 11.2.0.2.0 - Production                          
NLSRTL Version 11.2.0.2.0 - Production               





You can also run: 


SELECT * FROM PRODUCT_COMPONENT_VERSION;

Increase JVM Free Memory for Apache Tomcat 6

1) Go to $CATALINA_HOME/bin

2) Create a file setenv.sh (in Linux) or setenv.bat (in Windows)

3) Enter the line:

JAVA_OPTS="-Xms256m -Xmx512m -XX:MaxPermSize=256m"

4) Restart the server

5) Go to your Tomcat manager >> Complete Server Status and verify if the memory has increased or not.

This will increase free memory available and avoid having the Out of Memory error.

EXP-00091: Exporting questionable statistics.

If you receive this warning message when doing database export, you can turn it off by setting STATISTICS=NONE avoid exporting these statistics during export.

Allowing VNC connection in Oracle Enterprise Linux

If you cant remote connect to your database or to VNC it may be due to firewall restrictions.

1) Start VNC server and check which port its connected on by running: netstat -tlnp
2) Edit the iptables file to allow that port.

$ vim /etc/sysconfig/iptables
3) add this line:
RH-Firewall-1-INPUT -p tcp -m state --state NEW -m tcp --dport xxxx -j ACCEPT
BEFORE
RH-Firewall-1-INPUT -j REJECT --reject-with icmp-host-prohibited

Replace the xxxx with the port number you found in step 1.

4) Restart your iptables

$ service iptables restart
If you can't connect to database via SQL developer (ie you are getting a network adaptor error) then try this step as well to allow outside connection.


5) Start VNC Server

$ service vncserver restart

Linux: Find out who is listening on ports

 If you suspect the port you are trying to connect to is being blocked, you can check who is listening on that port using netstat -tnlp.

Better to connect as root for a more comprehensive listing. 

bash-3.2$ netstat -tnlp 


tcp     0      0 0.0.0.0:22              0.0.0.0:*               LISTEN      545/sshd       
tcp        0      0 127.0.0.1:631           0.0.0.0:*               LISTEN      705/cupsd      
tcp6       0      0 :::22                   :::*                    LISTEN      545/sshd       
tcp6       0      0 ::1:631                 :::*                    LISTEN      705/cupsd      

Now you have list of ports that are being used. If you want to find out more about the process that's running on 705, try running:

bash-3.2$ ps -aux | grep 705
 root       705  0.0  0.2   6792  2436 ?        Ss   09:33   0:00 /usr/sbin/cupsd -C /etc/cups/cupsd.conf

Creating a Database Link in Oracle

CREATE DATABASE LINK "TESTLINK"
   CONNECT TO "schema_name" IDENTIFIED BY VALUES 'schema_password'
   USING '(DESCRIPTION =
       (ADDRESS_LIST =
         (ADDRESS = (PROTOCOL = TCP)(HOST = 101.102.103.104)(PORT = 1521))
       )
       (CONNECT_DATA =
         (SID = XE)
       )
     )';
 


1) Replace the schema_name, schema_password, Host and port with your setup.

2) Login to your schema, and run the query above.

Test it by querying "Select * from dual@TESTLINK".

Resolving SP2-0750: You may need to set ORACLE_HOME to your Oracle software directory

If you set up Oracle Database XE 11G in Linux and you encounter the following error when starting up SQL Plus:

$ sqlplus
SP2-0667: Message file sp1<lang>.msb not found
SP2-0750: You may need to set ORACLE_HOME to your Oracle software directory

To resolve it try the following. It's assumed Oracle XE 11G is installed at:

/u01/app/oracle/product/11.2.0/xe/

If you have a different set up, adjust your directory path accordingly.

1) Find out the path to sqlplus
    bash-3.2$ which sqlplus
   /u01/app/oracle/product/11.2.0/xe/bin/sqlplus

2) Set your Oracle_HOME (upto xe directory)

   bash-3.2$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0/xe/

3) Set the path to the directory where sqlplus is located to your PATH variable:

    bash-3.2$ export PATH=/u01/app/oracle/product/11.2.0/xe/bin:$PATH

 
You should now be able to use sqlplus. 

4) Now you need to edit you bash_profile so all these takes effect everytime you log in.

bash-3.2$ cd ~/
bash-3.2$ vim .bash_profile

5) In the .bash_profile you will need to add those export lines so every time you log in the same settings take effect.

bash-3.2$ vim .bash_profile
# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
        . ~/.bashrc
fi

# User specific environment and startup programs
ORACLE_SID=XE
export ORACLE_SID
ORACLE_HOME=/u01/app/oracle/product/11.2.0/xe
export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$PATH
export PATH
unset USERNAME


6) Run the following:
bash-3.2$ . .bash_profile
 
After following these 6 steps you should be able to connect to sqlplus any time.

Starting and Stopping Apache HTTPD Server in Linux

To stop HTTPD Service:

$ /sbin/service httpd stop

To Start HTTPD Service:

$ /sbin/service httpd start

ORA-01031: insufficient privileges when importing dump files in Oracle Database

If you create a new user/schema having trouble importing a database dump file (.dmp) because you are encountering:

ORA-01031: insufficient privileges

OR, simple getting this error when creating tables etc.

Just connect so SQLPLUS or SQL Developer as SYSDBA and run the command:

GRANT RESOURCE to schema_name;

You can then run import statements or connect as the new user/schema without encountering 0RA-01031.

PRE-REQUISITE: Must run the grant command from SYSDBA

Hashing values in Oracle Database

ORA_HASH( ) function allows you hash a value. This is one way hash meaning you cannot decrypt it back to its original value.

Example,




SELECT ORA_HASH(column_name) FROM table_name;




If you want to hash a particular string (not column value) then do it this way: 




SELECT ora_hash('some text') FROM DUAL;


This will hash the text 'some text' for you. The value you will get is: 2387593664 which is the hash of 'some text'.