Feed aggregator

Question: Anything Wrong With Query Performance? (Straight To You)

Richard Foote - Thu, 2018-04-05 00:21
I have a query that runs pretty darn efficiently, here’s the setup: So the query basically returns 1000 rows based on the CODE column and it does so using an index on CODE. The CBO has got the costings for this just about spot on. For 1000 rows returned, it does so with just 1006 […]
Categories: DBA Blogs

My Oracle Support Blog

Joshua Solomin - Wed, 2018-04-04 14:42
Tips for MOS. 

Virtual Workshop: Developing Microservices Using DevOps

Get Hands On with your Oracle Cloud Trial The Oracle Cloud Developing Microservices using DevOps workshop will walk you through the Software Development Lifecycle (SDLC) for a Cloud Native...

We share our skills to maximize your revenue!
Categories: DBA Blogs

Logging APEX Report Downloads

Scott Spendolini - Tue, 2018-04-03 20:42
A customer recently asked how APEX could track who clicked “download” from an Interactive Grid.  After some quick searching of the logs, I realized that APEX simply does not record this type of activity, aside from a simple page view type of “AJAX” entry.  This was not specific enough, and of course, led to the next question - can we prevent users from downloading data from a grid entirely?

I knew that any Javascript-based solution would fall short of their security requirements, since it is trivial to reconstruct the URL pattern required to initiate a download, even if the Javascript had removed the option from the menu.  Thus, I had to consider a PL/SQL-based approach - one that could not be bypassed by a malicious end user.

To solve this problem, I turned to APEX’s Initialization PL/SQL Code parameter.  Any PL/SQL code entered in this region will be executed before any other APEX-related process.  Thus, it is literally the first place that a developer can interact with an APEX page - be it a full page view or Ajax-based process.

Both IRs and Classic Reports leave enough data in the REQUEST parameter of the URL in the logs to decode which report was downloaded and what format was selected.  However, if you don’t know what the specific URL patterns look like, or don’t have the Request column selected, you’d never know it.  For my solution, I chose to incorporate all three types of reports - Classic, IG and IR - which also centralized it into a single place.

The solution is relatively simple, and requires two components: a table to track the downloads and a procedure to populate the table.  It should also work with both Oracle 11g & 12c.  I have tested it on APEX 5.1.4.  The IG portion will not work on APEX 5.0, since there is no IG component in that release.

First, create the table to store the logs:

CREATE TABLE dl_audit_log 
(
app_user VARCHAR2(255),
app_id NUMBER,
app_page_id NUMBER,
request VARCHAR2(255),
downloaded_on DATE,
report_id NUMBER,
report_name VARCHAR2(255),
report_type VARCHAR2(255),
report_format VARCHAR2(255)
)
/

Next, create the procedure that will capture any download.

CREATE OR REPLACE PROCEDURE dl_audit
 (
 p_request     IN VARCHAR2 DEFAULT v('REQUEST'),
 p_app_user    IN VARCHAR2 DEFAULT v('APP_USER'),
 p_app_page_id IN NUMBER   DEFAULT v('APP_PAGE_ID'),
 p_app_id      IN NUMBER   DEFAULT v('APP_ID'),
 p_app_session IN NUMBER   DEFAULT v('APP_SESSION')
 )
AS
 l_count NUMBER;
 l_id             NUMBER;
 l_report_name    VARCHAR2(255);
 l_report_format  VARCHAR2(255);
 l_json           VARCHAR2(10000);
BEGIN
-------------------------------------------------------------------------------------------------------------------------------
-- Capture Classic Report
-- Region ID will be embedded in the request
--------------------------------------------------------------------------------------------------------------------------------
CASE
WHEN p_request LIKE 'FLOW_EXCEL_OUTPUT%' THEN

-- Uncomment the two lines below to prevent the downloaded
--htp.prn('Download Prohibited');
--apex_application.g_unrecoverable_error := TRUE;

-- Get the ID
 SELECT SUBSTR(p_request, 20, INSTR(p_request,'_',20)-20) INTO l_id FROM dual;
 SELECT region_name INTO l_report_name FROM apex_application_page_regions WHERE region_id = l_id;

-- Log the download
 INSERT INTO dl_audit_log (app_user, app_id, app_page_id, request, downloaded_on, report_id, report_name, report_type, report_format
   VALUES (p_app_user, p_app_id, p_app_page_id, p_request, SYSDATE, l_id, l_report_name, 'CLASSIC', 'CSV');
-------------------------------------------------------------------------------------------------------------------------------
-- Capture IR download
-- Region ID embedded in request only when there is more than 1 IR on the page
-------------------------------------------------------------------------------------------------------------------------------
WHEN p_request LIKE '%CSV' OR p_request LIKE '%HTMLD' OR p_request LIKE '%PDF' THEN

-- Uncomment the two lines below to prevent the downloaded
--htp.prn('Download Prohibited');
--apex_application.g_unrecoverable_error := TRUE;

-- Determine how many IRs are on the page
 SELECT COUNT(*) INTO l_count FROM apex_application_page_ir where page_id = p_app_page_id AND application_id = p_app_id;

-- If there is 1, then get the ID from the view
 IF l_count = 1 THEN
   SELECT interactive_report_id, region_name INTO l_id, l_report_name
     FROM apex_application_page_ir where page_id = p_app_page_id AND application_id = p_app_id; ELSE

   -- Otherwise, get the ID from the REQUEST
   SELECT SUBSTR(p_request,5, INSTR(p_request,']')-5) INTO l_id FROM dual;
   SELECT region_name INTO l_report_name FROM apex_application_page_ir where region_id = TRIM(l_id);

 END IF;

-- Log the download
 INSERT INTO dl_audit_log (app_user, app_id, app_page_id, request, downloaded_on, report_id, report_name, report_type, report_format
   VALUES (p_app_user, p_app_id, p_app_page_id, p_request, SYSDATE, l_id, l_report_name, 'IR'
   CASE WHEN p_request LIKE '%CSV' THEN 'CSV' WHEN p_request LIKE '%HTMLD' THEN 'HTML' WHEN p_request LIKE '%PDF' THEN 'PDF' ELSE 'OTHER' END);

-------------------------------------------------------------------------------------------------------------------------------
-- Capture IG download
--------------------------------------------------------------------------------------------------------------------------------
WHEN LOWER(owa_util.get_cgi_env('QUERY_STRING')) LIKE 'p_flow_id=' || p_app_id || '&p_flow_step_id=
 || p_app_page_id || '&p_instance=' || p_app_session || '%&p_json%download%' THEN

-- Uncomment the two lines below to prevent the downloaded
 --htp.prn('Download Prohibited');
 --apex_application.g_unrecoverable_error := TRUE;

-- Extract the JSON
 SELECT utl_url.unescape(substr(owa_util.get_cgi_env('QUERY_STRING'),
 INSTR(owa_util.get_cgi_env('QUERY_STRING'), 'p_json=') + 7)) INTO l_json FROM dual;
 apex_json.parse(l_json);

-- Get the report ID
 l_id := apex_json.get_varchar2(p_path => 'regions[%d].id', p0 => 1);
 l_report_format := apex_json.get_varchar2(p_path => 'regions[%d].download.format', p0 => 1);

 -- Lookup the name
 SELECT region_name INTO l_report_name FROM apex_application_page_regions where region_id = l_id;

-- Log the download
 INSERT INTO dl_audit_log (app_user, app_id, app_page_id, request, downloaded_on, report_id, report_name, report_type, report_format
   VALUES (p_app_user, p_app_id, p_app_page_id, p_request, SYSDATE, l_id, l_report_name, 'GRID', l_report_format);

-- No auditing needed, as the user did not download a report
ELSE NULL;

END CASE;

END;
/

Lastly, add a reference to the procedure to the Initialization PL/SQL Code.  This can be found under your Shared Components > Security.


Once these three steps are completed, then any download of any report will be logged automatically.  There’s no need to adjust any specific report or add any parameters - it will just work as reports are downloaded.

Also, uncommenting the referenced lines in each section will also prevent that kind of report from being downloaded entirely.  The message could be changed as needed or even re-directed to an error page instead.

Running VirtualBox inside a VM instance in Oracle Cloud Infrastructure

Wim Coekaerts - Tue, 2018-04-03 16:15

OK - So don't ask "Why?"... Because... I can! :) would be the answer for the most part.

Oracle Cloud Infrastructure supports nested virtualization. When you create a VM instance in OCI, and you run Oracle Linux 7 with our kernel, you can create KVM or (soon you see how...) VirtualBox VMs inside. If you create a BM instance, you can install VirtualBox or use kvm as you normally would on a local server. Since, well, it's a bare metal server - full access to the hardware and its features.

VirtualBox has some very interesting built-in features which might make it useful to run remote (even when virtualized). One example would be the embedded vRDP server. It can do great remote audio and video (enable/tune videochannel), it makes it easy to take your local VirtualBox images and run them unmodified remotely, it lets you create smaller VMs that you constantly start/stop... you can use vagrant boxes, and it opens up the whole vagrant VirtualBox environment to a remote cloud. So aside from "Because I can"... there are actual good use cases for this!

How do you go about doing this. For the most part it's pretty trivial, installation of VirtualBox in a VM in OCI is no different than how you would install it on your local desktop or server. Configuring a guest VM in VirtualBox should be done using the command line (vboxmanage) instead of installing a full remote desktop and run vnc and such. It's a lot faster to do it using the command line. And then also, if you want to run VirtualBox in Bridged mode so that you have full access to the OCI native cloud network facilities (VCN/Subnet/IP addresses, even public IPs - without NAT) there are a few minor things you need to do.

Here are some of the steps to get going: I'm not a big screenshot guy so bear with me in text for the most part.

Step 1: Create an OCI VM and create/assign an extra VNIC to pass through to your VirtualBox VM.

If you don't already have an OCI account, you can go sign up and get a $300 credit trial account here. That should give you enough to get started.

Set up your account, create a Virtual Cloud Network (VCN) with its subnets and create a VM instance in one of the availability domains/regions. To test this out I created a VM.Standard2.2 shape instance with Oracle Linux 7. Once this instance is created, you can log in with user opc and get going.

When you log into your VM instance, and from the OCI web console you will see that you have a primary VNIC attached. This might show up as ens3 or so inside your VM. In the OCI web console the VNIC has a name (typically the primary VNIC's name is the same as your instance name), it has a private IP and if you decided to have it on a public network, a public ip address as well. All this stuff will be configured out of the box for you as part of your instance creation.

Since I want to show how to use a bridged network in VirtualBox, you will need a second VNIC. You can create that at this point, or you can come back later and do it once you are ready to start your VirtualBox VM. Just go to Attached VNICs in the webconsole (or use the OCI cli) and create a VNIC on a given VCN/Subnet.

create vnic

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

The important information to jot down are the mac address and the private ip address of this newly created vnic. In the example 10.0.0.2 and 00:00:17:02:EB:EA  this info is needed later.

Step 2: Install and configure VirtualBox

With Oracle Linux 7 - this is a very easy process. Use yum to install VirtualBox and the dependencies for building the VirtualBox kernel modules and quickly download and install the Extension Pack and you're done:

# yum install -y kernel-uek-devel-`uname -r` gcc # yum install -y VirtualBox-5.2 # wget https://download.virtualbox.org/virtualbox/5.2.8/Oracle_VM_VirtualBox_Extension_Pack-5.2.8.vbox-extpack # vboxmanage extpack install Oracle_VM_VirtualBox_Extension_Pack-5.2.8.vbox-extpack

That's it - you now have a fully functioning VirtualBox hypervisor installed on top of Oracle Linux 7 in an OCI VM instance.

Step 3: Create your first VirtualBox guest VM

The following instructions show you how to create a VM from the command line. The nice thing with using the command line is that you can clearly see what it takes for a VM to be configured and you can easily tweak the values (memory, disk,...).

First, you likely want to create a new VM from an install ISO. So upload your installation media to your OCI VM. I uploaded my Oracle Linux 7.5 preview image which you can get here.

Create your VirtualBox VM

# vboxmanage createvm --name oci-test --ostype oracle_64 --register # vboxmanage modifyvm oci-test --memory 4096 --vram 128 --ioapic on # vboxmanage modifyvm oci-test --boot1 dvd --boot2 disk --boot3 none --boot4 none # vboxmanage modifyvm oci-test --vrde on

Configure the Virtual Disk and Storage controllers (Feel free to attach an OCI Block Volume to your VM and put the VirtualBox virtual disks on that volume, of course). The example below creates a 40G virtual disk image and attaches the OL7.5 ISO as a DVD image.

# vboxmanage createhd --filename oci-test.vdi --size 40960 # vboxmanage storagectl oci-test --name "SATA Controller" --add sata --controller IntelAHCI # vboxmanage storageattach oci-test --storagectl "SATA Controller" --port 0 --device 0 --type hdd --medium oci-test.vdi # vboxmanage storagectl oci-test --name "IDE Controller" --add ide # vboxmanage storageattach oci-test --storagectl "IDE Controller" --port 0 --device 0 --type dvddrive --medium /home/opc/OracleLinux-R7-U5-BETA-Server-x86_64-dvd.iso

Configure the Bridged Network Adapter to directly connect to the OCI VNIC

This is a little more involved. You have to find out which network device was created on the VM host for this secondary VNIC.

# ip addr 1: lo: mtu 65536 qdisc noqueue state UNKNOWN link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00 inet 127.0.0.1/8 scope host lo valid_lft forever preferred_lft forever 2: ens3: mtu 9000 qdisc mq state UP qlen 1000 link/ether 00:00:17:02:3a:29 brd ff:ff:ff:ff:ff:ff inet 192.168.1.8/24 brd 192.168.1.255 scope global dynamic ens3 valid_lft 73962sec preferred_lft 73962sec 3: ens4: mtu 1500 qdisc noop state DOWN qlen 1000 link/ether 00:00:17:02:eb:ea brd ff:ff:ff:ff:ff:ff

Bring up this network adapter without an IP address and configure the MTU to 9000 (default mtu settings for VNICs in OCI)

# ip link set dev ens4 up # ip link set ens4 mtu 9000

Almost there... Now just create the NIC in VirtualBox and assign the mac address you recorded earlier to this NIC. It is very important to make sure you use that mac address, otherwise the networking will not allow traffic over the network. Note: don't use : for the mac address on the command line.

# vboxmanage modifyvm oci-test --nic1 bridged --bridgeadapter1 ens4 --macaddress1 00001702ebea

That's it. You now have a VirtualBox VM that can be started, will boot from install media, and be directly connected to the hosts network in OCI. There is no DHCP running on this network, so when you create your VirtualBox VM, you have to assign a static IP (use the one that was assigned as Private IP address (10.0.02 in the example above)).

Before you start your VM, open up the firewall on the host for remote RDP connections and do the same in the OCI console, modify the security list for your host primary VNIC to allow for port 3389 (RDP) traffic ingress.

# firewall-cmd --permanent --add-port=3389/tcp # firewall-cmd --reload

Start your VM in headless mode and use your favorite RDP client on your desktop or laptop to connect to the remote VirtualBox console.

# vboxmanage startvm oci-test --type headless

If you want to experiment with remote video/audio (for instance, play a youtube video inside your VM or play a movie file), enable the vrde video channel. Use the quality parameter to modify the compression/lossy ratio (improves performance) of the mjpeg stream.

# vboxmanage modifyvm oci-test --vrdevideochannel on # vboxmanage modifyvm oci-test --vrdevideochannelquality 70

Comparing Intent Classification in TensorFlow and Oracle Chatbot

Andrejus Baranovski - Tue, 2018-04-03 10:46
I have created sample set of intents with phrases (five phrases per intent, and ten intents). Using this set of data to train and build classification model with TensorFlow and Oracle Chatbot machine learning. Once model is trained, classifying identical sample phrases with both TensorFlow and Oracle Chatbot to compare results. Using Oracle Chatbot with both Linguistic and Machine Learning models.

Summary:

1. Overall TensorFlow model performs better. The main reason for this - I was training TensorFlow model multiple times, until good learning output (minimized learning loss) was produced.

2. Oracle Chatbot doesn't return information about learning loss after training, this makes it hard to decide if training was efficient or no. As consequence - worse classification results, can be related to slightly less efficient training, simply because you don't get information about training efficiency

3. Classification results score: 93% TensorFlow, 87% Oracle Chatbot Linguistic model, 67% Oracle Chatbot Machine Learning. TensorFlow is better, but Oracle Chatbot Linguistic model is very close. Oracle Chatbot Machine Learning model can be improved, see point 2

Results table (click on it, to see maximized):


TensorFlow

List of intents for TensorFlow is provided in JSON file. Same intents are used to train model in Oracle Chatbot:


TensorFlow classification model is created by training 2-layer neural network. Once training is completed, it prints out total loss for the training. This allows to repeat training, until model is produced with optimal loss (as close as possible to 0): 0.00924 in this case:


TensorFlow classification result is good, it failed to classify only one sentence - "How you work?" This sentence is not directly related to any intent, although I should mention Oracle Chatbot Linguistic model is able to classify it. TensorFlow offers correct classification intent as second option, coming very close to correct answer:



Oracle Chatbot

Oracle Chatbot provides UI to enter intents and sample phrases - same set of intents with phrases is used as for TensorFlow:


Oracle Chatbot offers two training models - linguistic and machine learning based.


Once model is trained, there is no feedback about training loss. We can enter phrase and check intent classification result. Below is sample for Linguistic model classification failure - it fails to classify one of the intents, where sentence topic is not perfectly clear, however same intent is classified well by Oracle Chatbot Machine Learning model:


Oracle Chatbot Machine Learning model fails on another intent, where we want to check for hospital (hospital search) to monitor blood pressure. I'm sure if it would be possible to review training quality loss (may be in the next release?), we could decide to re-train model and get results close to TensorFlow. Classification with Oracle Chatbot Machine Learning model:

Raspberry Pi 3 B Oracle Linux 7.4 ARM64 with UEK5 preview image available for download

Wim Coekaerts - Tue, 2018-04-03 10:07

A few weeks ago we released an Oracle Linux 7 Update 4 for ARM64 preview update on OTN. This updated ISO installs on Ampere X-Gene 3 (emag) and Cavium ThunderX / ThunderX2 -based systems (and it's also known to work on Qualcomm Centriq 2400-based servers).

Today we added the RPI3 (Raspberry Pi 3 Model B) disk image as well. The previous RPI3 image was still using Oracle Linux 7.3 as a base along with a 4.9 Linux kernel. The newly released image makes it current. It is the same Oracle Linux 7.4 package set as we released on the ISO and it uses the same UEK5 preview kernel (based on 4.14.30 right now).

The current image uses uboot and boots the kernel directly. We will do another update in the near future where we switch to uboot+efi and grub2, so that updating kernels will work the same way as we can do on the regular ARM server installs (where we boot with EFI -> grub2).

A few things to point out:

- OL7/ARM64 is a 64-bit only build. That makes binaries pretty large and the RPI3 only has 1GB of RAM so it's a bit of a stretch.

- X/gnome-shell doesn't work in this release, this is a known issue, when we move to 7.5 this will be resolved but our focus is mostly server and per the above, running a heavy GUI stack is hard on a 1GB system.

- We do not yet support the latest RPI3 Model B+.  Only the RPI3 Model B. We don't have a device tree/dtb file yet for the RPI3 Model B+.

Since it has all the same packages as the server one, you can run docker on the RPI3:

# cat /etc/oracle-release Oracle Linux Server release 7.4 # uname -a Linux rpi3 4.14.30-1.el7uek.aarch64 #1 SMP Mon Mar 26 23:11:30 PDT 2018 aarch64 aarch64 aarch64 GNU/Linux # yum install docker-engine # systemctl enable docker # systemctl start docker # docker pull oraclelinux:7-slim

And there you go a small Oracle Linux 7 for ARM image right on your rpi - directly from docker hub.

# docker pull oraclelinux:7-slim 7-slim: Pulling from library/oraclelinux eefac02db809: Pull complete Digest: sha256:fc684f5bbd1e46cfa28f56a0340026bca640d6188ee79ef36ab2d58d41636131 Status: Downloaded newer image for oraclelinux:7-slim

Oracle VM Server: supported guest systems

Dietrich Schroff - Tue, 2018-04-03 04:36
After the installation of


and

the next step is to install a guest. But which operating systems are supported on Oracle VM Server?
Let's look into the official Oracle documentation:
The list of supported operating systems can be found here.


Table 5.1 HVM-Supported Linux Guest Operating Systems
Guest Operating System
HVM 32-bit
HVM 64-bit
Oracle Linux Release 7.x
N/A
Yes
Oracle Linux Release 6.x
Yes
Yes
Oracle Linux Release 5.x
Yes
Yes
Oracle Linux Release 4.x
Yes
Yes
Red Hat Enterprise Linux 7.x
N/A
Yes
Red Hat Enterprise Linux 6.x
Yes
Yes
Red Hat Enterprise Linux 5.x
Yes
Yes
Red Hat Enterprise Linux 4.x
Yes
Yes
CentOS 7.x
N/A
Yes
CentOS 6.x
Yes
Yes
CentOS 5.x
Yes
Yes
CentOS 4.x
Yes
Yes
SUSE Linux Enterprise Server 11.x
No
Yes
SUSE Linux Enterprise Server 12 SP2 or later
No
Yes

Table 5.2 PVHVM-Supported Linux Guest Operating Systems
Guest Operating System
PVHVM 32-bit
PVHVM 64-bit
Oracle Linux Release 7.x
N/A
Yes
Oracle Linux Release 6.x
Yes
Yes
Oracle Linux Release 5.x
Yes
Yes
Oracle Linux Release 4.x
Yes
Yes
Red Hat Enterprise Linux 7.x
N/A
Yes
Red Hat Enterprise Linux 6.x
Yes
Yes
Red Hat Enterprise Linux 5.x
Yes
Yes
Red Hat Enterprise Linux 4.x
Yes
Yes
CentOS 7.x
N/A
Yes
CentOS 6.x
Yes
Yes
CentOS 5.x
Yes
Yes
CentOS 4.x
Yes
Yes
SUSE Linux Enterprise Server 11.x
No
Yes
SUSE Linux Enterprise Server 12 SP2 or later
No
Yes

Table 5.4 CPU Paravirtualized Supported Guest Operating Systems
Guest Operating System
Paravirtualized 32-bit
Paravirtualized 64-bit
Oracle Linux Release 7.x
No
No
Oracle Linux Release 6.x
Yes
Yes
Oracle Linux Release 5.x
Yes
Yes
Oracle Linux Release 4.x
Yes
Yes
Red Hat Enterprise Linux 7.x
No
No
Red Hat Enterprise Linux 6.x
Yes
Yes
Red Hat Enterprise Linux 5.x
Yes
Yes
Red Hat Enterprise Linux 4.x
Yes
Yes
CentOS 7.x
No
No
CentOS 6.x
Yes
Yes
CentOS 5.x
Yes
Yes
CentOS 4.x
Yes
Yes
SUSE Linux Enterprise Server 11.x
No
Yes
SUSE Linux Enterprise Server 12 SP2 or later
No
No

Table 5.5 Microsoft Windows Supported Guest Operating Systems
Guest Operating Systems
64-bit
32-bit
HVM
HVM with Oracle VM Paravirtual Drivers for Microsoft Windows
Microsoft Windows Server 2016
Yes
N/A
Supported
Supported
Microsoft Windows Server 2012 R2
Yes
N/A
Supported
Supported
Microsoft Windows Server 2012
Yes
N/A
Supported
Supported
Microsoft Windows Server 2008 R2 SP1
Yes
N/A
Supported
Supported
Microsoft Windows Server 2008 SP2
Yes
Yes
Supported
Supported
Microsoft Windows Server 2003 R2 SP2
Yes
Yes
Supported
Supported
Microsoft Windows 10
Yes
Yes
Supported
Supported
Microsoft Windows 8.1
Yes
Yes
Supported
Supported
Microsoft Windows 8
Yes
Yes
Supported
Supported
Microsoft Windows 7 SP1
Yes
Yes
Supported
Supported
Microsoft Windows Vista SP2
Yes
Yes
Supported
Supported

These tables are valid for version 3.4 - that means you have to check the support matrix for each version seperatly.

ODPI-C 2.3 is now on GitHub

Christopher Jones - Mon, 2018-04-02 21:33
ODPI-C logo

Release 2.3 of Oracle Database Programming Interface for C (ODPI-C) is now available on GitHub

ODPI-C is an open source library of C code that simplifies access to Oracle Database for applications written in C or C++.

Top features: Improve Batch Statement Execution

 

ODPI-C 2.3 improves support for Batch Statement execution with dpiStmt_executeMany(). To support DML RETURNING producing multiple rows for each iteration, a new function dpiVar_getReturnedData() was added, replacing the function dpiVar_getData() which will be deprecated in a future release. A fix for binding LONG data in dpiStmt_executeMany() also landed.

If you haven't heard of Batch Statement Executation (sometimes referred to as Array DML), check out this Python cx_Oracle example or this Node.js node-oracledb example.

A number of other issues were addressed in ODPI-C 2.3. See the release notes for more information.

ODPI-C References

Home page: https://oracle.github.io/odpi/

Code: https://github.com/oracle/odpi

Documentation: https://oracle.github.io/odpi/doc/index.html

Release Notes: https://oracle.github.io/odpi/doc/releasenotes.html

Installation Instructions: oracle.github.io/odpi/doc/installation.html

Report issues and discuss: https://github.com/oracle/odpi/issues

Node-oracledb 2.2 with Batch Statement Execution (and more) is out on npm

Christopher Jones - Mon, 2018-04-02 17:16

Release announcement: Node-oracledb 2.2, the Node.js module for accessing Oracle Database, is on npm.

Top features: Batch Statement Execution

In the six-or-so weeks since 2.1 was released, a bunch of new functionality landed in node-oracledb 2.2. This shows how much engineering went into the refactored lower abstraction layer we introduced in 2.0, just to make it easy to expose Oracle features to languages like Node.js.

The top features in node-oracledb 2.2 are:

  • Added oracledb.edition to support Edition-Based Redefinition (EBR). The EBR feature of Oracle Database allows multiple versions of views, synonyms, PL/SQL objects and SQL Translation profiles to be used concurrently. This lets database logic be updated and tested while production users are still accessing the original version.

    The new edition property can be set at the global level, when creating a pool, or when creating a standalone connection. This removes the need to use an ALTER SESSION command or ORA_EDITION environment variable.

  • Added oracledb.events to allow the Oracle client library to receive Oracle Database service events, such as for Fast Application Notification (FAN) and Runtime Load Balancing (RLB).

    The new events property can be set at the global level, when creating a pool, or when creating a standalone connection. This removes the need to use an oraaccess.xml file to enable event handling, making it easier to use Oracle high availablility features, and makes it available for the first time to users who are linking node-oracledb with version 11.2 Oracle client libraries.

  • Added connection.changePassword() for changing passwords. Passwords can also be changed when calling oracledb.getConnection(), which is the only way to connect when a password has expired.

  • Added connection.executeMany() for efficient batch execution of DML (e.g. INSERT, UPDATE and DELETE) and PL/SQL execution with multiple records. See the example below.

  • Added connection.getStatementInfo() to find information about a SQL statement without executing it. This is most useful for finding column types of queries and for finding bind variables names. It does require a 'round-trip' to the database, so don't use it without reason. Also there are one or two quirks because the library underneath that provides the implementation has some 'historic' behavior. Check the manual for details.

  • Added connection.ping() to support system health checks. This verifies that a connection is usable and that the database service or network have not gone down. This requires a round-trip to the database so you wouldn't use it without reason. Although it doesn't replace error handling in execute(), sometimes you don't want to be running a SQL statement just to check the connection status, so it is useful in the arsenal of features for keeping systems running reliably.

See the CHANGELOG for all changes.

One infrastructure change we recently made was to move the canonical home for documentation to GitHub 'pages'. This will be kept in sync with the current production version of node-oracledb. If you update your bookmarks to the new locations, it will allow us to update the source code repository documentation mid-release without confusing anyone about available functionality.

Batch Statement Execution

The new connection.executeMany() method allows many sets of data values to be bound to one DML or PL/SQL statement for execution. It is like calling connection.execute() multiple times for one statement but requires fewer round-trips overall. This is an efficient way to handle batch changes, for example when inserting or updating multiple rows, because the reduced cost of round-trips has a significant affect on performance and scalability. Depending on the number of records, their sizes, and on the network speed to the database, the performance of executeMany() can be significantly faster than the equivalent use of execute().

In one little test I did between Node.js on my laptop and a database running on my adjacent desktop, I saw that executeMany() took 16 milliseconds whereas execute() took 2.3 seconds to insert 1000 rows, each consisting of a number and a very short string. With larger data sizes and slower (or faster!) networks the performance characteristics will vary, but the overall benefit is widespread.

The executeMany() method supports IN, IN OUT and OUT variables. Binds from RETURNING INTO clauses are supported, making it easy to insert a number of rows and find, for example, the ROWIDs of each.

With an optional batchErrors mode, you can insert 'noisy' data easily. Batch Errors allows valid rows to be inserted and invalid rows to be rejected. A transaction will be started but not committed, even if autocommit mode is enabled. The application can examine the errors, find the bad data, take action, and explicitly commit or rollback as desired.

To give one example, let's look at the use of batchErrors when inserting data:

var sql = "INSERT INTO childtab VALUES (:1, :2, :3)"; // There are three value in each nested array since there are // three bind variables in the SQL statement. // Each nested array will be inserted as a new row. var binds = [ [1016, 10, "apples"], [1017, 10, "bananas"], [1018, 20, "cherries"], [1018, 20, "damson plums"], // duplicate key [1019, 30, "elderberry"], [1020, 40, "fig"], [1021, 75, "golden kiwifruit"], // parent does not exist [1022, 40, "honeydew melon"] ]; var options = { autoCommit: true, // autocommit if there are no batch errors batchErrors: true, // identify invalid records; start a transaction for valid ones bindDefs: [ // describes the data in 'binds' { type: oracledb.NUMBER }, { type: oracledb.NUMBER }, { type: oracledb.STRING, maxSize: 16 } // size of the largest string, or as close as possible ] }; connection.executeMany(sql, binds, options, function (err, result) { if (err) consol.error(err); else { console.log("Result is:", result); } });

Assuming appropriate data exists in the parent table, the output might be like:

Result is: { rowsAffected: 6, batchErrors: [ { Error: ORA-00001: unique constraint (CJ.CHILDTAB_PK) violated errorNum: 1, offset: 3 }, { Error: ORA-02291: integrity constraint (CJ.CHILDTAB_FK) violated - parent key not found errorNum: 2291, offset: 6 } ] }

This shows that 6 records were inserted but the records at offset 3 and 6 (using a 0-based index into the 'binds' variable array) were problematic. Because of these batch errors, the other records were not committed, despite autoCommit being true. However they were inserted and the transaction could be committed or rolled back.

We know some users are inserting very large data sets so executeMany() will be very welcome. At the very huge end of the data spectrum you may want to call executeMany() with batches of data to avoid size limitations in various layers of the Oracle and operating system stack. Your own testing will determine the best approach.

See Batch Execution in the manual for more information about the modes of executeMany() and how to use it in various cases. There are runnable examples in the GitHub examples directory. Look for the files prefixed 'em_'. There are two variants of each sample: one uses call-back style, and the other uses the Async/Await interface available with Node.js 8.

Resources

Node-oracledb installation instructions are here.

Node-oracledb documentation is here.

Node-oracledb change log is here.

Issues and questions about node-oracledb can be posted on GitHub.

Finally, contributions to node-oracledb are more than welcome, see CONTRIBUTING.

New OA Framework 12.2.6 Update 11 Now Available

Steven Chan - Mon, 2018-04-02 17:03

Web-based content in Oracle E-Business Suite Release 12 runs on the Oracle Application Framework (also known as OA Framework, OAF, or FWK) user interface libraries and infrastructure.

We periodically release updates to Oracle Application Framework to fix performance, security, and stability issues.

These updates are provided in cumulative Release Update Packs, and cumulative Bundle Patches that can be applied on top of the Release Update Packs. In this context, cumulative means that the latest RUP or Bundle Patch contains everything released earlier.

The latest OAF update for Oracle E-Business Suite Release 12.2.6 is now available:

Oracle Application Framework (FWK) Release 12.2.6 Bundle 11 (Patch 27529582:R12.FWK.C)

Where is this update documented?

Instructions for installing this OAF Release Update Pack are in the following My Oracle Support knowledge document:

Who should apply this patch?

All Oracle E-Business Suite Release 12.2.6 users should apply this patch. Future OAF patches for EBS Release 12.2.6 will require this patch as a prerequisite. 

What's new in this update?

This bundle patch is cumulative: it includes all fixes released in previous EBS Release 12.2.6 bundle patches.

In addition, this latest bundle patch includes fixes for the following issues:

  • TO BE SUPPLIED

Related Articles

Categories: APPS Blogs

To generate list of unique Random Numbers based on the number count required

Tom Kyte - Mon, 2018-04-02 09:46
Hi, My requirement is to generate the list of random numbers based on the total count provided. for instance, if total count is 100, i have to generate 100 unique random numbers. Below is the sample code i used. Could you please check and let me ...
Categories: DBA Blogs

Group by is failing on remote database query

Tom Kyte - Mon, 2018-04-02 09:46
Hello, My issue is the good old ORA02070 and I've found different workarounds but none of them fits my particular problem. It happens when I try to do a group by on remote database query. I need to compare data between the local and remote dat...
Categories: DBA Blogs

DB stuck at check point not complete.

Tom Kyte - Mon, 2018-04-02 09:46
Our DB stuck and check poin not complete. As per the logs DBA says.. ELECT for update running on the db causing the high number of block changes and caused the issue After this issue we are unable to generate ASH or AWR for that time. How to iden...
Categories: DBA Blogs

Not all standby Redo logs are being used in Physical standby database

Tom Kyte - Mon, 2018-04-02 09:46
Hi I wanted to know how does Oracle physical standby database decides how many standby Redo to be used. As We have a Standby which have multiple Standby Logs but its is only using 2 of them for each thread. We tried to switch the logs on both...
Categories: DBA Blogs

character set us7ascii missing from dbca

Tom Kyte - Mon, 2018-04-02 09:46
I am trying to create a database in oracle 12.1.0.2 using dbca. THe character set us7ascii is not in the list of character sets.
Categories: DBA Blogs

Oracle Industry Connect 2018 Draws Top Business Leaders to Share Insights and Strategies for Digital Transformation

Oracle Press Releases - Mon, 2018-04-02 07:00
Press Release
Oracle Industry Connect 2018 Draws Top Business Leaders to Share Insights and Strategies for Digital Transformation

Redwood Shores, Calif.—Apr 2, 2018

Oracle today announced its 5th-annual Oracle Industry Connect conference. The event brings together business leaders and experts from across industries to share deep sector expertise and strategies for innovation and organizational transformation. In addition to leading customer executives, the conference will feature keynotes from Oracle CEO Mark Hurd and Bob Weiler, executive vice president of Oracle’s Global Business Units. The event takes place April 10-11, 2018 at the Hilton Midtown Manhattan Hotel in New York City.

“Oracle delivers the widest range of industry-specific applications in the cloud,” said Oracle CEO Mark Hurd. “From creating smarter construction sites with IoT, to empowering retailers to deliver better customer experiences with inventory management, our unmatched industry expertise and solutions are driving our customers’ success.”

At Oracle Industry Connect, industry peers join together to share deep-domain expertise and best practices on the mission-critical applications that are core to their businesses and their success. Ninety percent of the breakout session content is comprised of Oracle customers providing insights on identifying and overcoming complex industry challenges.

The keynotes will be followed by seven industry-specific tracks for business leaders in communications, construction and engineering, energy and utilities, financial services and insurance, hospitality, life sciences and healthcare and retail. Speakers include:

  • Amy Brady, Chief Information Officer, Executive Vice President, KeyBank
  • Connie Santilli, Vice President Enterprise Systems and Strategy, GAP
  • David Sipes, Chief Operations Officer, RingCentral
  • David Wilson, Chief Innovation Officer, Bechtel Corporation
  • Michael Webber, Deputy Director of the Energy Institute, Co-Director of the Clean Energy Incubator
  • Ray Bennett, Chief Global Officer, Global Operations, Marriott International
  • Sandy Tremps, Vice President, MRL IT, Merck
  • William Pratt, Chief Information Officer, SiriusXM Radio Inc.

“Cloud computing is the foundation of how businesses can truly transform themselves,” said Bob Weiler, executive vice president, Oracle’s Global Business Units. “Smart companies will leverage powerful industry-specific solutions to innovate, delight their customers and drive new revenue streams.”

For more information about how Oracle is committed to empowering organizations through best-in-class, industry-specific business solutions, visit oracle.com/industries. To learn more about Oracle Industry Connect 2018, go to oracle.com/oracleindustryconnect.

Contact Info
Katie Barron
Oracle
+1.202.904.1138
katie.barron@oracle.com
About Oracle

The Oracle Cloud offers complete SaaS application suites for ERP, HCM and CX, plus best-in-class database Platform as a Service (PaaS) and Infrastructure as a Service (IaaS) from data centers throughout the Americas, Europe and Asia. For more information about Oracle (NYSE:ORCL), please visit us at www.oracle.com.

Trademarks

Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

Talk to a Press Contact

Katie Barron

  • +1.202.904.1138

Oracle Named a Leader in the 2018 Gartner Magic Quadrant for Identity Governance and Administration

Oracle Press Releases - Mon, 2018-04-02 07:00
Press Release
Oracle Named a Leader in the 2018 Gartner Magic Quadrant for Identity Governance and Administration Oracle positioned as a Leader based on completeness of vision and ability to execute

Redwood Shores, Calif.—Apr 2, 2018

Oracle today announced that it has been named a Leader in Gartner’s 2018 “Magic Quadrant for Identity Governance and Administration” report for the fifth consecutive time. Oracle believes this recognition further validates the strength and innovation of cloud security services Oracle has introduced over the past year and its ability to help enterprises better integrate security solutions to manage their business.

“Security and Identity has quickly become one of the most critical areas businesses must address in order to be successful and maintain regular operations, and identity governance is a critical foundational step any enterprise should take to strengthen its security posture,” said Eric Olden, senior vice president and general manager, security and identity, Oracle. “Over the last year, Oracle has significantly enhanced its solutions’ capabilities to help enterprises manage, analyze and remediate security incidents with Oracle’s autonomous security capabilities. We are continuing our commitment to offering a trusted identity fabric and portfolio to help enterprises secure their businesses.”

According to Gartner, “IGA Leaders deliver a comprehensive toolset for governance and administration of identity and access. These vendors have successfully built a significant installed customer base and revenue stream, and have high viability ratings and robust revenue growth. Leaders also show evidence of superior vision and execution for anticipated requirements related to technology, methodology or means of delivery. Leaders typically demonstrate customer satisfaction with IGA capabilities and/or related service and support.”

In December, Oracle announced the first cloud-native identity governance service for hybrid cloud environments, which will be fully integrated and native to Oracle’s SaaS applications, Oracle Identity Security Operations Center (Identity SOC) portfolio (including Oracle Identity Cloud Service and Oracle CASB Cloud Service), as well as Oracle Management Cloud. In addition, Oracle expanded its consumer identity management capabilities in Oracle Identity Cloud Service through integrations with Oracle Marketing Cloud and Oracle Data Cloud.

Oracle’s integrated security suite of the Oracle Identity SOC portfolio and Oracle Management Cloud are designed to help enterprises forecast, reduce, detect, and resolve security threats and assist in efforts to remediate application and infrastructure performance issues.  Leveraging artificial intelligence to analyze a unified data set consisting of the full breadth of security and operational telemetry, as well as provide automated remediation, Oracle’s integrated suite is designed to enable customers to quickly adapt their security and operational posture as their risk landscape changes. This application of machine learning can potentially help thwart attacks, reduce the detection window from months to minutes, and more quickly address security breaches and performance outages.

Download a complimentary copy of Gartner’s 2018 “Magic Quadrant for Identity Governance and Administration” here.

Source: Gartner, “Magic Quadrant for Identity Governance and Administration,” Felix Gaehtgens, Kevin Kampman, Brian Iverson, 21 February 2018.

Gartner does not endorse any vendor, product or service depicted in its research publications, and does not advise technology users to select only those vendors with the highest ratings or other designation. Gartner research publications consist of the opinions of Gartner’s research organization and should not be construed as statements of fact. Gartner disclaims all warranties, expressed or implied, with respect to this research, including any warranties of merchantability or fitness for a particular purpose.

Contact Info
Jesse Caputo
Oracle
+1.650.506.5967
jesse.caputo@oracle.com
Kristin Reeves
Blanc & Otus
+1.415.856.5145
kristin.reeves@blancandotus.com
About Oracle

The Oracle Cloud offers complete SaaS application suites for ERP, HCM and CX, plus best-in-class database Platform as a Service (PaaS) and Infrastructure as a Service (IaaS) from data centers throughout the Americas, Europe and Asia. For more information about Oracle (NYSE:ORCL), please visit us at www.oracle.com.

Trademarks

Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

Talk to a Press Contact

Jesse Caputo

  • +1.650.506.5967

Kristin Reeves

  • +1.415.856.5145

Adding Native Pivot Charts and Tables to your Excel Reports!!

Tim Dexter - Sun, 2018-04-01 23:59

A report in Excel format is a very common requirement and BI Publisher can generate excel output using RTF, XSL or Excel Template. Excel template is recommended when the requirement is to create pixel perfect column width, to use built in excel functions, to create multi-sheet output, to handle preceding zeroes in data, to maintain data formatting, to manage high number of columns of data, etc.

How about adding native charts and pivot tables in the excel report ? Well, excel templates can handle that too.  

There is no wizard in the Excel Template Builder to create charts or pivot table, but you can certainly include Excel Pivot Charts and Pivot Tables in your report using MS Excel features. Here is a step-by-step guide:

 

Step 1: Create Excel Template to build data for Pivot Chart & Pivot Table

Use Excel Template Builder to create Excel Template

Load a sample XML data. Add data column header names.

Use "Insert Field" option from BI Publisher Ribbon Menu and create data place holders as shown below.

You will see an interim dialog box from the Template Builder that a metadata sheet will be created. Click OK on it.

 

Add looping of data using Insert Repeating Group. Select the For Each entry at the repeating node level

 

Preview the output. This will bring all records in the excel sheet in a separate .xls output file.

 

Step 2: Create Pivot Chart & Pivot Table

You can close the output .xls file and stay in the Excel Template. Now select all the data columns to be used in the Pivot Chart and table. You can click on column headers and select the entire column to be included or you can just select the table with column headers and single row of data placeholders. From Excel Menu Insert, select Pivot Chart & Pivot Table option.

 

In the dialog box "Create PivotTable", you can keep selected the option "Select a table or range" and leave the Table/Range that appears by default based on the selection.

You can choose to create the Pivot Chart and Pivot Table in a new work sheet (recommended).Click OK.

This will add a new Sheet in the Excel file and insert a Pivot Table and Chart place holder, with Pivot Table fields on the right panel

Here you can select the fields for the Pivot table and chart, to be depicted as Axis, Legend and Values. In this example we have included Product Type, Product, LOB and Brand as Axis and Revenue as Values.

Please note that by default the function selected under Values is Count. Therefore, select the drop down next to Count function and choose Value Field Settings, where you can change this to Sum function.

 

 

One more thing to note is the presence of Field Buttons in the chart. You can hide these Field Buttons. With Pivot Chart selected, go to Analyze Menu in the Ribbon style Menu, and under Show/Hide section choose "Hide all Field Buttons".

Finally the template will look like this

 

Step 3: Include dynamic data generated by BI Publisher for Pivot Chart & Pivot Table

Right click on Pivot Chart, select PivotChart Options, select Data tab. Here select the option "Refresh data when opening the file". This will bring the data dynamically into the PIvot Chart and Pivot Table.

 

 

You can run preview of the excel output and you will see the pivot table and chart displaying dynamic data.

You will notice blank data appearing in the Pivot Table and Chart. This is due to the way the looping works against the dynamic data. You can hide this blank data by filtering the blank data from the parent field in the pivot table of the output excel file. In this example, we will remove the blank data from Product field and the complete blank section will be removed without affecting rest of the data. To do this, just hover over Product in the right side pane under Pivot Chart Fields and click on the down arrow. This will open the filter options for Product field. Uncheck the Blank value from filter list. 

 

So, this completes the template design and the final output will look as shown below

You can further include excel functions and formula within these pivot table and charts as necessary for your requirement. You can even change the chart type, style etc. to create the most appropriate visual representation of the data. You can upload the excel template on BI Publisher server and run it against live data. You can include as many sheets with different pivot charts and tables, as required for your report. 

Also note that excel template can be run against any data source type in BI Publisher Data Model. Therefore you can use BI Analysis or even run a BIJDBC SQL query against RPD layer, and bring complex calculations, aggregations as a part of your data. 

 

Hope this was helpful. If you want to check the sample template and data, download it from here.

Have a great day !!

Categories: BI & Warehousing

New functions not yet documented - TO_DOG_YEAR

Tom Kyte - Sun, 2018-04-01 15:26
Over the past few months I've been working on a project using oracle advanced analytics to detect IBS in Dogs. I've written a short blog post about this here (http://www.oralytics.com/2018/04/predicting-ibs-in-dogs-using-oracle-18c.html) I've noti...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator