Let’s revisit the ways to programmatically load data into an on-premise or Cloud Oracle database.  Many applications need to load data from external sources as some part of the workflow. What is the best data load option for your environment and your requirements?  Is your spreadsheet data CSV or true XLSX? Are you loading CLOBs or LOBs? What are the APEX data load options? Which data load methods work in a Cloud database? What methods make most sense for your users, for your environment, for your budget? This presentation reviews a collection of data load options, including the APEX Data Load wizard, external tables, ORDS bulk data load, open source interfaces, plugins and APEX APIs helpful for custom coding.

Demonstrations and examples are geared toward APEX applications, yet most of the techniques described can be used in any PL/SQL application. Attendees gain sufficient knowledge to make an intelligent choice for their application data loading needs.

Make the database smarter? Isn't it already smart enough? Well...no it isn't.  The Oracle Database provides a rich environment for the expression and incorporation of business logic within the data-layer. As powerful as PL/SQL is, it still hits a limit that forces logic to be implemented in the middle or client tier.  This presentation will…

Proper design of an application includes security plans, which ensure that specific data is available only to specific user groups. Application design should also include security plans for guarding against data or application tampering. These two aspects can easily be set aside while developers are caught up in the process of developing PL/SQL code to…

This talk covers a real-world solution, that recreates database links using ORDS, REST and JSON. It breaks the limits of database links by loosely coupling two databases. The complexity is completely hidden from users and developers. Modern technologies such as REST and JSON offer elegant ways to couple databases without fixed dependencies. The protocol http(s)…

The only group of Oracle specialists that continues to stay outside of the cloud hype are server-side developers. Everybody else (from CFOs to DBAs) are frantically trying to figure out what is happening and how to survive in the rapidly changing environment. Nevertheless, developers still hope that either their DBAs are good enough or their CFOs are charitable enough to make the system work. They are wrong – both in the technical and in the political/corporate sense!

Politically, developers are missing tectonic shifts: (1) hardware resources are no longer static and (2) expense model is “pay-per-use”. Previously, as long as your current servers were surviving the workload, no one cared whether they were 50% or 70% utilized. Now, this difference can be immediately converted to real money because the resource elasticity means that you can give it back. As a result, the total quality of the code base (+performance tuning efforts) has a direct impact on cost. This presentation will share some of the corresponding best practices:

Code instrumentation – to solve any performance problem you first need to detect it! You can place markers yourself or by using DBMS_APPLICATION_INFO, but this task must be done.
Profiling – even if you can detect a performance problem, you need to find its exact location.  The Hierarchical Profiler is your best friend in that search.
Code management – proper versioning helps to ensure that new fixes don’t introduce new problems. Edition-based redefinition (EBR) could streamline such efforts. 

Technically, the shift is a bit more subtle. For years, the whole industry “knew” that for any large system the most limited resource was I/O bandwidth. In reality, that was mostly due to the fact that CPU resources were over-allocated (Black Friday!). In the cloud, you cannot afford such over-allocation all the time. Don't forget that the introduction of solid-state drives decreased storage latency. Together, these two factors caused cloud-based systems to suddenly become CPU-bound! Fortunately, you can do a lot with your code to make it less expensive:

Avoid context switches between SQL and PL/SQL - using PRAGMA UDF or WITH-clause can help a lot. 
Don’t reinvent the wheel – There are some very powerful SQL features (for example: analytical functions, PIVOT/UNPIVOT) that are often ignored by the majority of developers.
Don’t do things multiple times - various caching techniques can help reuse what’s already known.
Collections in-memory processing are still the fastest way of calculating anything. Wide usage of SET operations decreases the number of context switches as well.

Overall, it is possible to make your system cloud-friendly, but doing so takes explicit efforts and serious thinking. This presentation will help you by utilizing real-world examples based on years of experience.

Original Broadcast: June 8, 2017 Version management of the PL/SQL source code has always been challenging. Database programmers lack the tools and utilities that make version control easy for developers who code with file based programming languages such as Java and JavaScript. In fact, this is one of the primary reasons why large software teams…

Version management of the PL/SQL source code in the Oracle Database has always been challenging. Database programmers lack the tools and utilities that make version control easy for developers who code with file based programming languages such as Java and JavaScript. In fact, this is one of the primary reasons why large software teams avoid…

Version management of the PL/SQL source code in the Oracle Database has always been challenging. Database programmers lack the tools and utilities that make version control easy for developers who code with file based programming languages such as Java and JavaScript. In fact, this is one of the primary reasons why large software teams avoid writing code in PL/SQL

In this presentation you will learn how you can use Git, the prominent open source version control software built for distributed teams, to manage your PL/SQL source code in the Oracle Database. Using these techniques you can manage your source code in the Oracle Database just like any other development team manages their file based code base written in languages like Java, JavaScript or C#.

Most Oracle professionals are aware of the importance of keeping database statistics up to date. However, contrary to common knowledge, these statistics are not limited to tables, columns and indexes! PL/SQL functions also have a number of associated statistics, namely costs (CPU, I/O, network), selectivity, and cardinality (for functions that return collections). These statistics have…

Each individual sql statement submitted from a client (even sqlplus running directly on the database server) generates a pair of sqlnet waits. That is no big deal if you are submitting a single large set operation transaction and the sqlnet waits are a tiny fraction of the time of executing the statement. But if you…