Over the years, I have seen many systems that suffer from database performance problems, but also from a lack of data protection. By protection, I mean missing constraints and relationship keys, but also the lack of proper database types. A minor change to the database that causes headache is often releated to a bad design. For example, adding a column. These databases share a common problem: the absence of conceptual and logical models, which should have been developed before proceeding with the physical implementation. Doing proper modeling will not only save CPU, memory, and storage resourc ..read more

When a new version of APEX is released, we download it as soon as possible and run an upgrade of the APEX instance. All good. When you apply the new version, it will bring your existing applications up to that version. However, this process does not modify the user interface or any of the components within those applications. This means your current applications will not use the latest features. There are a few more steps to address before your applications are up-to-date. Universal Theme Within the Shared Components section of the application, APEX notifies us about the process of upgrading t ..read more

User-Defined Function (UDF) Pragma is one of those neat little pills that might actually boost your performance significantly with minimal effort. A classic problem that might cause performance degradation is calling PL/SQL functions within a SQL statement. This will always produce overhead due to context switching. Context switching occurs when the execution control shifts between the SQL and PL/SQL engines within Oracle. This process adds overhead, as each switch takes time and resources. By using the UDF Pragma, we inform the Oracle compiler that the function is primarily intended for use i ..read more

Sometimes one has to come up with a fast solution to a tricky problem. In this case, I had a SQL query with a massive amount of joins due to a poorly designed database model in the source. The query was executed on Oracle 19c Standard Edition 2, connecting to Oracle 11g Standard Edition 2, over a database link. It took about 4-5 minutes. One of the first things that was discovered was that SQL actually performed quite well if one used hardcoded literals instead of bind variables. Literals -explicit values that are directly included in the SQL statement SELECT * FROM employees WHERE departmen ..read more

Oracle External Job enables the execution of external applications, shell scripts, and similar tasks. In this post, I will demonstrate the process of creating a database job that executes a shell script responsible for running SFTP and retrieving a file. Configuring and granting appropriate permissions to execute an external job is not covered in this post. The demo is executed on Oracle 19.14.0.0.0 running on SUSE Linux Enterprise Server 12. The ‘expect’ command-line tool must be installed on the system. This demo will only use a password for connecting to the SFTP, to simplify the example. T ..read more

In version 11.2, I believe, Oracle introduced the WAIT_ON_PENDING_DML function as part of the DBMS_UTILITY package. The function seems to be a part of the Edition-based redefinition concept. According to the docs. “Waits until all transactions (other than the caller’s own) that have locks on the listed tables and began prior to the specified SCN have either committed or been rolled back” By using DBMS_UTILITY.WAIT_ON_PENDING_DML, we could potentially wait until uncommitted transactions are done (commit, rollback), and then proceed with other tasks. Example. Create a table and insert a row. CR ..read more

Choosing the correct datatype is really important. In a database, datatypes and length serve as the integrity constraints governing the data.  A number is a number… It’s also important to know which tools are available so you actually know how your application will perform in production. As a simple example to emphasize this, let’s create a table where we have the “wrong” datatype on the column lookup_id. Create and index on that column. Investigate the SQL plan in two different ways. I’m running the examples in my Oracle Cloud environment, Autonomous Database. CREATE TABLE lookup ..read more

When executing a Data Definition Language (DDL) statement, Oracle will issue two implicit commits. Prior to any syntactically correct DDL statement, even if the statement fails to execute properly. After the successful execution of any DDL statement. When one executes a DML statment. Oracle will basically do this BEGIN --Oracle does the first impicit commit COMMIT; --execute the ddl create table.. --Oracle does a second implicit commit COMMIT; exception when others then ROLLBACK; raise; end; If you are not aware of this, it might lead to ..read more

Loading data into an Oracle database, can sometimes be frustrating. Its quite common to see plenty of code for validating the input datatype. Is this a number? Is this a date? Also you might find tables which stores the data as a string, but true datatype is really a number or a date, which in turn can create all kinds of trouble. As a simple example. Let’s create a table, and insert some data as strings. Then force a conversion error with TO_NUMBER. CREATE TABLE t_validate(somedate VARCHAR2(100)); Table T_VALIDATE created. INSERT INTO t_validate (somedate) WITH data AS ( SELECT '9 ..read more

In my last post, I wrote about the importance of using Session State Protection in APEX. As with session state protection, APEX has built-in functionality for spotting potential non-secure passwords. Log on to the Workspace home page. Press the App Builder Icon. Press the Workspace Utilities Icon, on the far right we can find “Cross Applications Reports”. Here we have a report called “Password Items”. The Password Items report displays all password items in the application, indicating their encryption usage and state-saving behavior. Additionally, it flags any password that is considered “A ..read more