Move the entire row to next page when one or more text fields data overflows to next page

Usually, Clients needs data to be displayed neatly at server UI level though it would be an excel extract or pdf extract.

This tip is useful when report text fields data overflows to next page with wired look and feel.

Solution : 
1) Remove “Detail Over flow” option for all the text elements that are kept on detail band if  the property is already applied.
2) Select the detail band and look into the properties of it.

Chose Split Type = Prevent instead Stretch




How to set the column width of classic report in Oracle Apex

  • Click the classic report column
  • Now go to HTML expression filed under column formatting and type

<span style=”display:block; width:200px”>#COLUMN_NAME_OF_REPORT#</span>

Apply changes and you are done.

This will increase the column width.. use the different width for other columns.


Oracle apex Classic report CSV download to Restrict the columns

Use the below code to restrict what are the columns download the csv download in Oracle apex Classic report.

1. if one column is hidden column, if you download the csv you want the hidden column

Check the below example

Restrict Numbers only in apex 5 input type

Use the below javascript in common js file and change input type in number the input only accept the 0-9 and dot only.

$(document).ready(function() {
    $(".number_field").keydown(function (e) {
        // Allow: backspace, delete, tab, escape, enter and .
        if ($.inArray(e.keyCode, [46, 8, 9, 27, 13, 110, 190]) !== -1 ||
             // Allow: Ctrl+A, Command+A
            (e.keyCode === 65 && (e.ctrlKey === true || e.metaKey === true)) || 
             // Allow: home, end, left, right, down, up
            (e.keyCode >= 35 && e.keyCode <= 40)) {
                 // let it happen, don't do anything
        // Ensure that it is a number and stop the keypress
        if ((e.shiftKey || (e.keyCode  57)) && (e.keyCode  105)) {


Most of the Applications includes Accounts Reports like Employee Payroll Reports, Company Purchase Reports, Invoice Reports,etc.  Now using this method user can add RUPEE symbol in your reports.
Step 1: Create Report (Interactive Report (or) Classical Report)
Step 2: Place the below SQL Query in Report Region Source
SELECT    ‘<span style=”font-weight:bold;”>₹</span>’
       || ‘ ‘
       || CASE
             WHEN NVL (SUM (amount), 0) = 0
                THEN ‘0.00’
             ELSE TO_CHAR (NVL (SUM (amount), 0), ‘99999999999.99’)
          END amount
  FROM reimbursement_dtls

oracle: unexpire and unlock accounts

oracle: unexpire and unlock accounts

Some of our customers’ applications are built around Oracle, so we have to fight the beast from time to time. Unfortunately, some of the surprizes the beast has to offer are quite random and rare, and due to this we tend to simply forget how we fixed and/or circumvented the issues previously.

So this is just an attempt of a small cheat sheat to help our overloaded brains :-)

  1. expired and locked accounts – the basics
    Now, as of version 11g, Oracle has enabled account expiration per default for many vital accounts (such as SYSMAN, SYS, …). Quite a weird idea in my view, but who knows what hyper security things some wise engineer had in mind when doing so.Beware that we run Oracle under various Linux flavours, so things might be different for you.

    • become database admin
      First log into your host running oracle and become the oracle user.
      % sqlplus /nolog
      SQL> connect / as SYSDBA
    • find out which accounts are expired
      select username, account_status from dba_users where ACCOUNT_STATUS LIKE '%EXPIRED%';
    • unexpire an account
      once an account has been expired, it can only be revived by assigning it a new password:
      ALTER USER scott IDENTIFIED BY password;
    • unlock an account
    • disable default password expiry [2]
      this all depends on the profile a user belongs to, to disable password expiry for all users assigned the default user profile do this:
  2. Enterprise Manager: unable to connect to instance
    One of the worst things that can happen in that course is if Enterprise Manager reports “Failed to connect to database instance: ORA-28001: the password has expired (DBD ERROR: OCISessionBegin)” and then simply rejects to work with the database.So far, we’ve found two accounts to be potential culprits for the problem: SYSMAN andDBSNMP

    • unexpire SYSMAN [3]
      Quite tricky, because you have to change things on two sides: First unexpire the account as explained above. And now tweak oracle configuration.
      As oracle user:

      1. unexpire and unlock the account as explained above
      2. % emctl stop dbconsole
      3. change into
        ORACLE_HOME/<HostName_SID>/sysman/config, for us this would be for example:
        % cd /opt/oracle/111/klotho_ABSDEV/sysman/config
      4. edit the file
        and change the oracle.sysman.eml.mntr.emdRepPwd property to the new password you gave the SYSMAN user.
        Then change the oracle.sysman.eml.mntr.emdRepPwdEncrypted property fromTRUE to FALSE (sidenote: Oracle will revert to TRUE automagically once it is restarted).
      5. change into
        ORACLE_HOME/<HostName_SID>/sysman/emd, for us this would be for example:
        % cd /opt/oracle/111/klotho_ABSDEV/sysman/emd
      6. edit the target.xml file
        and edit those two properties:
        <Property NAME="UserName" VALUE="SYSMAN" ENCRYPTED="FALSE"/>
        <Property NAME="password" VALUE="TheNewPassword" ENCRYPTED="FALSE"/>
      7. % emctl start dbconsole
      8. under “normal” circumstances, everything should be fine now :-)

So, as said above this is just a “small” cheat sheet for some annoyances we have met with the great oracle, of course there is much much more to know about the beast 😉