King Training Blog

APEX 5.1 is Here!

January 16th, 2017

It’s here! Without much fanfare APEX 5.1 became available for download on December 21, 2016! With all the holiday stuff going on I missed it until after the first of the year.

Here’s where you can download APEX 5.1 and find out more:

http://www.oracle.com/technetwork/developer-tools/apex/downloads/download-085147.html

APEX 5.1 is a significant release and should be pretty stable considering the long beta program and early adopter programs it went through.

Major new features include:

  1. Interactive Grid
    “Live editing” of reports/pages brought to a whole new level
  2. JET Charts
    Oracle JET brings an organized approach to JavaScript, CSS3, and HTML5 to improve data display
  3. Universal Theme
    Universal Theme has new templates, new styles, over 1100 icons, and “Live Template” options
  4. Page Designer
    Page Designer is even easier adding a component view and the ability to view two panes at once including drag-and-drop between the panes!
  5. Calendars
    Calendars now allow JavaScript customization and now INCLUDE end dates
  6. Wizards Improved
    Several wizards have been improved to reduce steps necessary to get things done
  7. New Packaged Apps
    APEX include brand-new apps for Competitive Analysis, Quick SQL, and REST Client Assistant; plus the sample and productivity apps have all been refreshed

So, download APEX 5.1 and give it a try soon!

Oracle 12c – UTL_CALL_STACK for Easier Debugging

January 7th, 2017

UTL_CALL_STACK

Oracle has provided PL/SQL debug aids for a long time; perhaps your show currently uses one or more of the following

  • dbms_utility.format_call_stack
  • dbms_utility.format_error_backtrace
  • dbms_utility.format_error_stack

Oracle 12c adds the UTL_CALL_STACK package providing greater insight into the stack.

UTL_CALL_STACK includes the following functions

  • BACKTRACE_DEPTH Number of items in backtrace
  • BACKTRACE_LINE Line number of unit in backtrace
  • BACKTRACE_UNIT Name of unit in backtrace
  • CURRENT_EDITION Current edition name for backtrace unit
  • CONCATENATE_SUBPROGRAM Concatenated unit name
  • DYNAMIC_DEPTH Number of subprograms on call stack
  • ERROR_DEPTH Number of errors on error stack
  • ERROR_MSG Error message for specified error
  • ERROR_NUMBER Error number for specified error
  • LEXICAL_DEPTH Lexical nesting level of subprogram
  • OWNER Backtrace unit owner name
  • UNIT_LINE Line number in backtrace unit
  • SUBPROGRAM Name of backtrace unit

Example Procedure using UTL_STACK_TRACE

create or replace procedure Print_Call_Stack 
as
 DEPTH pls_integer := UTL_CALL_STACK.dynamic_depth();
 procedure printheaders
 is
 begin
 dbms_output.put_line( 'Lexical Depth Line Name' );
 dbms_output.put_line( 'Depth Number ' );
 dbms_output.put_line( '------- ----- ---- ----' );
 end printheaders;
 procedure print
 is
 begin
 printheaders;
 for stunit in reverse 1..DEPTH loop
 dbms_output.put_line(
 rpad( UTL_CALL_STACK.lexical_depth(stunit), 10 ) 
 || rpad( stunit, 7) 
 || rpad(to_char(UTL_CALL_STACK.unit_line(stunit), 
 '99'), 9 ) 
 || UTL_CALL_STACK.concatenate_subprogram
 end loop;
 end print;
begin
 print;
end;
/

Example PL/SQL Package to Test (does not directly call UTL_CALL_STACK)

create or replace package TestPkg is
 procedure proc_a;
 end TestPkg;
 / 
 create or replace package body TestPkg is
 procedure proc_a
 is
 procedure proc_b
 is
 procedure proc_c
 is
 procedure proc_d is
 begin
 Print_Call_Stack();
 raise program_error;
 end proc_d;
 begin
 proc_d();
 end proc_c;
 begin
 proc_c();
 end proc_b;
 begin
 proc_b();
 end proc_a;
 end TestPkg;

UTL_CALL_STACK Output

Executing the package results in a stack trace; the second set of output below shows the dbms_output results from UTL_CALL_STACK. The first batch of output lines is part of the “normal” stack trace. The second set was generated using UTL_CALL_STACK and shows how the program managed to get to the point of the failure.

 begin TestPkg.proc_a; end;
 
 Error report -
 ORA-06501: PL/SQL: program error
 ORA-06512: at "JOHN.TESTPKG", line 11
 ORA-06512: at "JOHN.TESTPKG", line 14
 ORA-06512: at "JOHN.TESTPKG", line 17
 ORA-06512: at "JOHN.TESTPKG", line 20
 ORA-06512: at line 1
 06501. 00000 - "PL/SQL: program error"
 *Cause: This is an internal error message. An error has 
 been detected in a PL/SQL program.
 *Action: Contact Oracle Support Services.
Lexical Depth Line Name
Depth Number 
------- ------ --------- ----
1 6 20 TESTPKG.PROC_A
2 5 17 TESTPKG.PROC_A.PROC_B
3 4 14 TESTPKG.PROC_A.PROC_B.PROC_C
4 3 10 TESTPKG.PROC_A.PROC_B.PROC_C.
 PROC_D
0 2 26 PRINT_CALL_STACK
1 1 17 PRINT_CALL_STACK.PRINT

Conclusion

UTL_CALL_STACK is NOT a user-oriented feature; it is directly squarely at the PL/SQL Developer and the DBA who need help determining not just where in a nest of PL/SQL calls an error occurred but HOW you got there!

Clearing Clouds: Unraveling IaaS, PaaS, and SaaS

November 22nd, 2016

Cloud, Cloud, and More Cloud!

Cloud is upon us! Unless you’ve been living under a rock you must be aware that our industry is headed to the cloud; some of us are already there!

Many cloud services are available and more are coming every day. How can you make sense of the many “?aaS” acronyms? In this article I’ll explain the differences between Cloud Service Models (IaaS, PaaS, and SaaS) and Cloud Deployment Options (public, private, and hybrid).

Cloud Service Models

Many, many acronyms come along with the cloud; here are three that are common:

  • IaaS  Infrastructure as a Service
  • PaaS  Platform as a Service
  • SaaS  Software as a Service

IaaS

Infrastructure as a Service means that the cloud provider provides: Hardware, Operations. and maybe core operating systems.

Does your organization really need to be in the Data Center Operations business?

PaaS

Platform as a Service means that in addition to what comes with IaaS, the provider is responsible for some core software load perhaps including: Operating System, Backup & Recovery, Disaster Recovery, and maybe a database and/or web server.

Is the day-to-day administration of platform keeping you from work that is important and unique to your business?

SaaS

Software as a Service means that the provider has it all: Infrastructure, Platform, and Software stack  (e.g. SalesForce, Oracle Cloud Applications).

Huge portions of IT budgets are devoted to maintaining the existing code base; should your organization leverage the work of others so that you can focus on what is unique to your business?

Comparing Models

The diagram below illustrates how the load shifts from the customer (green) to the vendor (purple) as you move from on-premise, to IaaS, then PaaS, and finally SaaS.

iaaspaassaas1

Services and Transport

The diagram below shows the same progression from on-premise, to IaaS, then PaaS, and finally SaaS but uses familiar transportation options to illustrate the differences.

iaaspaassaas2

Cloud Deployment

Cloud offering offer deployment options have three basic modes:

  • Public  You have private areas in public
    resource pools
  • Private  Your resources stored in private
    resource pools
    (perhaps on-premise)
  • Hybrid  Some combination

Public Cloud

With Public Cloud, the Cloud Vendor provides services that, while specific to you, may be sharing resources with other customers. For instance. Backup/Recovery might be handled for all customers collectively rather than for the individual customer and Disaster Recover might also be handled for all customers rather than individual customer.

Data is stored on servers owned and managed by the vendor while being co-located on public cloud servers with the data from other customers.

Private Cloud

Private Cloud is similar to being on-premise; in fact, it may actually be provided on-premise for the customer. With Private Cloud the vendor manages resources dedicated to customer. Private Cloud resources usually exist within firewall of a customer and are not shared with other customers of the vendor. Private Cloud offerings are sometimes on-premise of the customer but managed by the cloud provider (e.g. Oracle’s Cloud At Customer).

What About Security?

Really? What’s your organization’s budget for security? What do you suppose the
security budget is at Oracle ? (or for that matter Amazon or Microsoft?)bandit

If a cloud providers slip once; public embarrassment and exit of customers follows.

Your data is probably more secure on the cloud!

That said, security is one of our most important concerns. Some things to consider include Security of data in Public Cloud versus off-premise Private/Hybrid Cloud environments. This has particular relevance when working with financial or personal information. Sometimes regulations dictate that data be located locally; that might be difficult with public cloud and even some off-premise private cloud offerings.

When working in public cloud; segregation of “secure” data must be concerned for: normal processing, backup/recovery procesing, and disaster recovery. Does your contract with the Cloud Provider provide guaranteed wiping of data should you part ways with vendor?

An important bottom line: Responsibility for regulated data lies with customer, not vendor no matter what your contract might say.

Oracle and the Cloud

In case you missed it <grin> Oracle’s into the Cloud in a big way; see Oracle’s cloud website for the complete scoop:

https://cloud.oracle.com/home

Oracle’s Cloud Servcie offerings include:

  • SaaS  Cloud Applications (formerly Fusion Applications) finally goes big?
  • PaaS  Reduces your administration load?
  • IaaS  Takes you out of data center biz?

Oracle SaaS

Oracle has exposed their redesigned and reimagined applications stack as SaaS including:

  • Customer Experience
  • Human Capital Management (HCM)
  • Enterprise Resource Planning (ERP)
  • Supply Chain Management (SCM)
  • Enterprise Performance Management (EPM)
  • Analytics
  • Data
  • Social Media
  • More…

Oracle PaaS

Oracle is ready to provide infrastructure and management with several PaaS Cloud Services  including:

  • Database and Big Data
  • Middleware, Integration, and SOA
  • Application Development (Java, Developer, etc.)
  • Content and Collaboration
  • Business Analytics
  • More…

Oracle IaaS

Oracle provides three families of IaaS: Compute, Storage, and Network.

Oracle’s Larry Ellison announced at Open World 2016 that Oracle intends to be a major player in IaaS; in fact, he announced Oracle’s intention to surpass Amazon.

Oracle DBaaS

Oracle has been king of the database hill for many years. With Oracle DBaaS, Oracle extends that dominance to the cloud as Oracle DataBase as a Service (DBaaS).

DBaas uses the Platform as a Service (PaaS) model to enable deployment and management of Oracle database instances in the cloud. Oracle DBaaS is easy to use and includes instances using per-configured VM images. Each DBaaS instance is built upon Oracle’s IaaS Compute & Storage services. Some important things to remember about Oracle DBaaS:

  • Customer has full administrative control via SQL*Plus, SQL Developer, OEM, or Oracle’s Cloud Management Service
  • Creation and deployment may be performed via wizards or manually if desired
  • Oracle DBaaS works just like on-premise database for any applications

Wrapping it all Up

Cloud is everywhere, cloud is here to stay. Choosing IaaS, PaaS, or SaaS will be dependent upon your organization’s needs. Deciding to use Public, Private, or Hybrid cloud will be determined on a case by case basis too. Oracle’s IaaS, PaaS, and DBaaS provide strong offerings for customers to choose from. Oracle’s DBaaS provides a safe and simple way to start using the cloud.

Oracle 12.2 DBaaS Availability!

November 21st, 2016

At Oracle Open World 2016 Larry Ellison introduced Oracle Database 12c Release 2 (12.2) and shared that it would be available first to Oracle Cloud customers.

Immediately after Open World 12.2 became available to people who subscribed to the Oracle Exadata Express Cloud; those of us on DBaaS needed to wait for a little while longer.

In the second week of November, Oracle released Oracle Database 12c Release 2 (12.2) to DBaaS (DataBase as a Service) customers. Here’s how it looks when creating an instance:

oracledbaas12cr2

Maybe more exciting is the news that the Oracle Database 12c Release 2 documentation is now available on docs.oracle.com:

oracledocs12cr2

It sounds like those waiting for 12cR2 “on-premise” will have to wait until sometime after the beginning of 2017 for download capability.

However, with access to the documentation and the availability to fire up test instances using DBaaS you’ll be ready to go when the software becomes available! Give it a try…

Using ORDS To Protect the Crown Jewels (your data)

October 17th, 2016

Using ORDS To Protect the Crown Jewels (your data)

Introduction

Information Technology today is fantastic. Never before have we had so much capability to collect, store, and analyze data. Never before have we had so many wonderful tools for presenting data and our analysis of data.

Today, data represents the “crown jewels” of IT. All of our wonderful systems are less useful if the data is incorrect or inaccurate.
crownjewels

Cornucopia of Available Tools

Today’s applications are built using a polyglot of tools with new tools springing up seemingly every day. Today’s developers often think of data as a resource; they have no inclination, desire, or time to learn SQL or PL/SQL; let alone the details of efficient access. In the haste to make data available to modern web and mobile applications our data gets dumped to flat files or spreadsheets and copied in various ways making it available to applications. Who knows how accurate or up-to-date that data is or who else has access to it? Efficiency and accuracy are sometimes the victims of the haste to make data easily available. Over the last decade, many organizations have adopted a “thick-database” approach by placing business logic in the database rather than in the client or middle tier; all of that work is often skipped around in the quest to make data available to web and mobile applications.

Modern Expectations

Today’s developers are not stupid or lazy; they have unreasonable deadlines like most of us and an ever-changing landscape of new tools to use; “old” skills like SQL and PL/SQL are not high on their list. So what do they want? What do they expect?

Today’s developers want and expect to access data through Web Services via RESTful APIs (more on this later) with data being passed to and from the Web Services using JSON (most of the time).  Many of today’s development tools are designed assuming data is available via RESTful APIs and that parameters and values will be passed via JSON.

What is REST?

So, what is REST? Representational State Transfer (REST) describes a different architecture than the SOA Web Service architecture used in many systems. The term REST originated in a doctoral dissertation about the web written in 200 by a fellow named Roy Fielding; here’s a link to the paper, I think you may find it sleep-inducing <grin>.

What most people mean by RESTful APIs loosely describes asynchronous transmission of domain-specific data via HTTP/HTTPS using simple HTTP-style requests without SOAP, WSDL, or any type of session tracking.

How RESTful APIs Work

RESTful APIs work with resources, not procedures. Resources are identified uniquely by URI and HTTP verbs are used to act upon them in a standard way:
•    GET           Query
•    PUT           Create (sometimes Update)
•    POST         Update (sometimes Create)
•    PATCH     Partial Update
•    DELETE    Delete

You Already Know REST

One of the strengths of (what we call) REST is that you already use it everyday:

•    You use a browser to access a “resource” –
http://my.website.com/zebra/index.html
o    Each URI includes a website’s domain or IP address
o    The URI’s “resource” (zebra above) describes the context of the request
o    The URI’s “resource” web page (index.html above) identifies the individual resource involved
o    You “GET” (or “POST” or “PUT” etc.) the data corresponding to the URI

REST in Action

URLs use nouns for resources (not verbs for actions); note in the examples below who the URI names the resource; the HTTP verb describes the type of action.

GET /ords/hr/employees/                         Preferred
GET /ords/hr/GetAllEmployees/             Not Preferred

As with many good things in IT; standards are the key here. Uniform APIs for operations: GET, POST, PUT, DELETE make it easy for people to read,  understand, and create code. Requests are stateless; any necessary state information is contained in URI’s and the REST API’s parameters (as Oracle’s Jeff Smith says, “it’s all about the hyperlinks”).

Standard error code families help developers properly handle exceptions in code:

100’s – Informational
200’s – Success
300’s – Redirection
400’s – Client Error
500’s – Server Error

Reviewing Web Services

Web Services, (often called Microservices today) are the targets of RESTful API calls. So, what is a Web Service anyway? Here’s a definition from the W3C (World-Wide Web Consortium) website: “A Web service is a software application identified by a URI, whose interfaces and binding are capable of being defined, described and discovered by XML artifacts and supports direct interactions with other software applications using XML based messages via internet-based protocols.” Note that the W3C’s definition reflects the days of SOAP and XML but the notion is the same. We want to access code without regard to the programming language it is written in or the operating system it might be running on.

Web Service interactions are performed by sending message via URI and a “payload” contained in parameters, headers, and responses. Web Services interactions were originally XML-based; today, JSON-based interactions are pretty much the standard for Mobile and becoming the standard for most other web applications too.

Why XML and/or JSON?

The plain-text of XML and JSON makes them ideal for cross-language/platform use. Every operating system and every language knows how to work with character stream data. Using XML or JSON with standard interfaces like SOAP and REST makes Web Services potentially programming language and operating system independent.

Emergence of JSON

JSON (JavaScript Object Notation) has become the mechanism of choice for sharing and passing data to-and-from mobile applications. JSON is an international, open standard governed by the ECMA; here is a link where you can learn more http://www.ecma-international.org/publications/files/ECMA-ST/ECMA-404.pdf.

JSON-XML Similarities

JSON and XML share many similarities. JSON is text only, just like XML and thus is an excellent vehicle for data interchange. JSON and XML are “human readable” and “self-describing” (sort of, I’m not sure anybody really want to read them). JSON and XML are hierarchical (data sets nested within data sets). JSON and XML both offer validation capability; XML’s is more mature and capable today.

JSON-XML Dissimilarities

XML is verbose, JSON is less-verbose. JSON has no end tags, end tags are required in XML making JSON quicker to read and to write.  Reading XML documents often requires “walking the DOM” – reading JSON does not. JSON works more easily and is faster than XML when working with AJAX.

Perhaps the biggest and most-meaningful XML-JSON difference is XML’s standard requiring a document to be well-formed XML. Each time an XML document is processed the code is required by standard to make sure that the XML document is “well-formed” (follows specific rules and is syntactically correct); further, if an XML document is passed to a subroutine it too is required to make sure the XML document is “well-formed” before it may begin processing it (and so on, and so on, and so on). This constant testing of the XML document “well-formed-ness” slows things down. JSON has no such requirement; however, some documents might be unreadable and cause errors.

XML vs JSON Verbosity

Here are two sample documents, one XML and one JSON containing EXACTLY the same data; note the actual difference in size. The principle reasons to use JSON are the speed JSON gains by not bothering to check for well-formed-ness and the ready availability of tooling that works with JSON.

XML File (300 characters without counting spaces)

 <?xml version="1.0"?>
 <myBooks>
    <book>
       <name>Learning XML</name>
       <author>Eric T. Ray</author>
       <publisher>O'Reilly</publisher>
    </book>
    <book>
       <name>XML Bible</name>
       <author>Elliotte Rusty Harold</author>
      <publisher>IDG Books</publisher>
    </book>
    <book>
       <name>XML by Example</name>
       <author>Sean McGrath</author>
    </book>
 </myBooks>

JSON File (228 characters without spaces)

 {"myBooks":
    [ {"book": {
       "name":"Learning XML",
       "author":"Eric T. Ray",
       "publisher":"O'Reilly" }
    },
    {"book": {
       "name":"XML Bible",
       "author":"Elliotte Rusty Harold",
       "publisher":"IDG Books" }
    },
    {"book": {
       "name":"XML by Example",
       "author":"Sean McGrath" }
    }
 ]}

Why ORDS?

What if you could expose your Tables, Views, and stored PL/SQL (thick database) as web services? What if you could accept input in JSON and generate JSON easily? What if you could make data available to today’s developers safely in a form they’re familiar with and want to use?

ORDS to the Rescue!ordslogo

Oracle’s REST Data Services (ORDS) provides a solution to:

  • Expose Tables and/or Views via REST APIs
  • Expose PL/SQL via REST APIs
  • Transfer data using JSON or other datatypes

Introduction to ORDS

ORDS provides RESTful access to Oracle data that modern tools expect. ORDS maps SQL to REST style HTTP: GET, POST, PUT, DELETE calls that may return results in JSON or other data types.

What is ORDS?

ORDS was originally known as APEX Listener in 2010.  Support for JSON was added in 2011. The product officially became Oracle Rest Data Services (ORDS) in 2014. ORDS works with APEX (Application Express), SQL Developer, or PL/SQL via a package named ORDS. This article primarly shows ORDS via SQL Developer; a later post will illustrate ORDS via APEX.

ords1

  • ORDS is included in Oracle DBaaS instances
  • ORDS may be installed easily in any instance
  • ORDS runs in any Java EE container (Weblogic, Glassfish, Tomcat, etc.)
  • Developers may create standalone version
  • ORDS is an enhanced version of Oracle’s Java mod_plsql Apache module

ords2

  • ORDS data is stored in Oracle (relational tables and columns)
  • ORDS defines  URI-to-SQL mapping with SQL results mapped to JSON (or other data types)
  • Applications use URIs via HTTP(S) to GET and POST data

ORDS REST APIs

ORDS provides a subset of the typical HTTP-style API calls.

Here’s an example of a typical REST “payload” returned from a query

{  "items": [
 {
 "employee_id": 100,
 "first_name": "Steven",
 "last_name": "King",
 "email": "SKING",
 "phone_number": "515.123.4567",
 "hire_date": "1987-06-17T04:00:00Z",
 "job_id": "AD_PRES",
 "salary": 24000,
 "commission_pct": null,
 "manager_id": null,
 "department_id": 90,
 "links": [
 .... More Here ....
 },

ORDS Installation

As stated before, ORDS comes “out of the box” with Oracle DBaaS. Or, your DBAs might already have installed ORDS. F not, installation is easy with SQL Developer (partially shown below) or APEX (not shown in this article).

ORDS may be installed using a simple set of wizards (not all steps shown here); here’s the first part of the process.

ordsinstall1
Once ORDS is installed a review screen describes the installation.

ordsinstalllast

ORDS Administration via SQL Developer

SQL Developer provides two tools for ORDS; one for Development and one for Administration.

ordsadmin1

ORDS Reporting in SQL Developer

SQL Developer provides many built-in reports including on for ORDS as shown here.

ordsreports1

ordsreports2

Enabling ORDS for a Schema in SQL Developer

Using SQL Developer’s Connection Navigator right-click on a connection to set up ORDS for that schema. You will provide an “alias” that will be used to describe the resource in URIs and optionally set additional security by requiring that users be part of an Oracle role.

ordsenableschema

Enabling ORDS for a Table and/or View in SQL Developer

SQL Developer will automatically set up ORDS calls for selected database objects so that you don’t have to. Using the SQL Developer Object Navigator right-click on an object to enable ORDS. You will provide an “alias” for the resource allowing a meaningful name rather than our sometimes non-obvious standards-laden Table/View names. You will also have the opportunity to require additional security requiring users to be part of an Oracle role.

ordsenable1ordsenable2aordsenable3

Manual Mapping of ORDS in SQL Developer

Developers may manually map ORDS for a query, PL/SQL procedure/function call, or event anonymous PL/SQL. This avoids the “SELECT *” of the automatically generated ORDS mappings described in the previous section.

ordsdeveloper1ordsdeveloper2

ordsdeveloper3ordsdeveloper4

PL/SQL ORDS APIs

Oracle provides a PL/SQL package that allows creation and manipulation of ORDS in code. This PL/SQL “ORDS” package is used for defining and configuring RESTful services so that ORDS mapping may be put into scripts and may be repeated quickly and easily. Complete documentation of the ORDS package may be found at this URL:

https://docs.oracle.com/cd/E56351_01/doc.30/e56293/ords_ref.htm – AELIG90180

Here’s an example of the PL/SQL API in Use:

begin
    ords.create_service(
       p_module_name    => 'samples.employees',
       p_base_path      => '/samples/employees',
       p_pattern        => '.',
       p_items_per_page => 5,
       p_source         => 'select * from hr.employees
                              order by employee_id');
    commit;
 end;

Security

Security is an important part of todays applications. ORDS provides a standardized mechanism for URIs allowing application designers to tie into Oracle Identity Management via WebGate to access Single Sign On (SSO) or to use OAuth2 (built-in).

ORDS Sample Output

Use URL to view all table rows:

ordsemployees
Use URL to view row contents:

ordsemployees200

Wrapping it all Up

With ORDS the Crown Jewels are safe! crownjewelschina2new

 

 

ORDS allows controlled access to our data by providing exactly what the developers need and want:

  • Web Services available via RESTful APIs
  • Data in and out in JSON as well as other data types

ORDS helps enable your newest applications. Give it a try today!

SQL Developer and Oracle DBaaS

October 6th, 2016

Connecting to Oracle DBaaS from SQL Developer

Introduction

So, you have access to an Oracle DBaaS instance. How do you connect so that you can run use SQL Developer’s navigator or execute SQL and PL/SQL scripts? An earlier post showed how easy it is to create an Oracle DBaaS instance. This article will show you how to connect to DBaaS from SQL Developer.

When a DBaaS instance is created it is necessary to supply a private/public key pair to enable more-secure access via SSH (Secure Shell). By adjusting the DBaaS properties, you can also expose the CDB and PDB using the IP address without the protection of SSH (probably not a good idea for production use).

SSH Public Key

When creating a DBaaS instance you are required to provide a valid SSH key to protect your cloud resource. On UNIX/Linux/Mac this means running the “ssh-keygen” line command, on Windows this is usually accomplished using PuTTYgen’s SSH-2 RSA feature. A “passphrase” along with public key and a private key are specified. You use the public key when creating the DBaaS service instance and the private key when accessing it.

Finding IP and Database Service Name Information

Use the DBaaS dashboard to display the IP address and Service name along with other useful information as shown below.

dbaassqldev01

Using DBaaS from SQL Developer (using SSH)

To access the DBaaS from SQL Developer you first define an SSH Host. You might need to open the SSH View (View->SSH from the SQL Developer menu) to see the display below.

Right-click and choose “New SSH Host” to continue.

dbaassqldev02

Creating A New SSH Host

Use the information from DBaaS instance to define a new SSH Host.

  • Name: your choice
  • Host: IP address from DBaaS service display
  • Port: 22
  • Username: oracle
  • Check “Use key file” and browse to private key matching the DBaaS public key file
  • Check “Add a Local Port Forward” and pick a name, host, and port
  • Click “OK” when done

dbaassqldev03

Create SQL Developer Connection to CDB

To create a SQL Developer connection to the CDB specify a username and password (system password specified at DBaaS creation); choose Connection Type “SSH” and specify the Port Forward name from the previous step. Use the SID from the DBaaS service; be sure to test before saving.

dbaassqldev04

Create SQL Developer Connection to PDB

To create a SQL Developer connection to the PDB specify a username and password (system password specified at DBaaS creation); choose Connection Type “SSH” and specify the Port Forward name from the previous step. Specify the Service Name connect string from the DBaaS service; be sure to test before saving.

dbaassqldev11

Can I Avoid Using SSH?

Using SSH (Secure Shell) is a good way to ensure the integrity of your database connections. However, occasionally you might want to access the database using the IP address and a “normal” (less-secure) connection. Here’s how you set it up.

First, open the “Service Console” and use the icon on the right to display a list of options. Choose “Access Rules” to continue.

dbaassqldev06

Enabling “Normal” Access via DB Listener

From the “Access Rules” panel you can enable and/or disable accesses of different types for your DBaaS service instance. To enable use of the port 1521 listener, click on “Enable” and confirm in the dialog that displays.

dbaassqldev07

 dbaassqldev08

DB Listener Enabled

Once enabled, the “Access Rules” panel shows the current status and allows you to disable the access again should you desire.

dbaassqldev10

Using SQL Developer and the Listener

Once the listener connection is enabled; you may access the CDB (via SID) or the PDB (via Service Name) to create a new SQL Developer connection (as shown below).

dbaassqldev11

Congratulations!

Congratulations, you have now accessed an Oracle DataBase as a Service (DBaas) service instance from SQL Developer.

Oracle’s DBaaS Is Easy

September 15th, 2016

Introduction

Oracle has been king of the database hill for many years. Now, they’re extending that dominance to the cloud. Oracle DataBase as a Service (DBaaS) uses the Platform as a Service (PaaS) model to enable deployment and management of Oracle database instances in the cloud.

Using Oracle’s DataBase as a Service (DBaaS) is quick and easy. In the example below I create and deploy an Oracle SE instance in about thirty minutes; about twenty of that was waiting for the system to complete provisioning. Have you ever been able to create a database and built the server space it required so simply? Once you have Oracle’s DBaaS, instance creation and deployment can be as easy as following a wizard-based process; no forms to fill out from you operations people and no hardware to purchase/allocate.

Creating a New Service

First you must have an Oracle Cloud account with DBaaS. Log in and go to “My Services” as shown below. Click on “Create Service” to begin the wizard-based process.

Once, the wizard begins you will be “walked” through a series of steps to define and deploy your database instance.

Subscription Type and Billing Frequency

The first screen in the process asks that you choose to create the database entirely with wizards (as I do in this paper), manually, or using DBCA (Database Configuration Assistant).

Software Release

The next stage in the process is to specify the database version to be used. (Rumor has it that Oracle 12c 12.2 will be available first to Oracle DBaaS cloud customers and then to on-premise shops.)

Software Edition

Part of Oracle DBaaS flexibility is allowing you to choose a Standard Edition (SE) database as shown below; or one of three “flavors” of Oracle Enterprise Edition.

Service Details (Configuration)

The Service Details panel allows detailed specification of the Database Service including Service Configuration, Backup and Recovery Configuration, and Database Configuration. For the sake of example I did not set up Backup and Recovery and went with the simplest setup possible. However, as you can see many options allow specification of a complex fully protected database.

SSH Public Key

Security is a significant concern in today’s world; Oracle’s DBaaS requires that you provide a valid SSH key to protect your cloud resource. On UNIX/Linux/Mac this means running the “ssh-keygen” line command, on Windows this is usually accomplished using PuTTYgen’s SSH-2 RSA feature. You will specify a “passphrase” and create a public key and a private key. You will use the public key when creating the DBaaS service instance and the private key when accessing it later.

Choose the “Edit” button provided to direct the wizard to your SSH Public Key file selection.

Here is the screen with the public key file in place. Next select the “shape” of the instance. In this example I selected a simple single CPU with minimal RAM; most of the instances I create are for training developers so this is more than adequate. Your applications might require significantly greater resources, so choose them!

Backup and Recovery

In this example; I opted to skip having backup and recovery. For most of my training courses this is the appropriate decision. Your production systems will probably require that you more-fully protect your data.

Database Configuration

In the final part of the screen you may choose to create the instance from an existing backup; or you may specify several options to complete the database configuration: file storage; admin password, SID, PDB name, character sets, and whether or not to enable Oracle GoldenGate.

Confirmation Page

Finally, the “Create Service Instance” wizard provides a confirmation page allowing you to review and if necessary go back and change settings.

Service Created

Once the service is created; you are returned to the Cloud Services Dashboard where the new service is listed. However, it may take a while for provisioning and deployment to complete. Clicking on the instance name “sedemo1” in the example below takes you to a page that show many specifics for the instance.

Instance Specifics – Abbreviated

The instance is not yet completely provisioned as shown by the lack of IP address and the “In Progress” message at the bottom of the page. To see more details, click the “show more” link.

Instance Specifics – Detailed

After clicking “show more” greater detail is displayed about the DBaaS service instance. Note, still “in progress” – this can sometimes take 15-20 minutes.

Create Service Completed!

Once the service is created; a message appears with the start and stop time (about 20 min below). Note that the service has now been assigned an IP address and a connect string.

All Done!

As you can see; it is remarkably easy to “spin up” an Oracle instance using DBaaS. Remember this the next time you need a test database, or to support a new application, or to build a Proof-of-Concept system. Oracle DBaaS is quick and easy to use.

Oracle 12c – Invisible Columns!

July 19th, 2016

Oracle 12c – Invisible Columns

Beginning with Oracle 12c columns may be marked “INVISIBLE” in CREATE and ALTER TABLE statements.

What the?!?!?!?!

Suppose you have code that uses “SELECT *” (no, really this sometimes occurs) and you need to add a column or columns? Oops! Code using “SELECT *” now breaks as does code using INSERT statements without a column list. (I know, ALL of your system’s code uses explicit column name lists in SELECT and INSERT <grin>; this does not apply to your shop… (please excuse the sarcasm))

Here’s how it works:

  • Invisible columns do not appear in SQL*Plus DESCRIBE
  • Using SQL*Plus SET COLINVISIBLE ON will cause invisible columns to appear when the table is DESCribed
  • Invisible columns do not appear in SQL Developer column display
    (at least, it does show in SQL Developer table column list)
  • Invisible columns do not appear in SELECT * queries
  • Invisible columns are not included in PL/SQL %ROWTYPE
  • Invisible columns that are null-capable may be omitted from INSERT statements
  • Invisible columns may be inserted into INSERT statements by name
  • When made visible, formerly-invisible columns appear to move to the end of table
    (hmmm, why?)
    The answer is best described by knowing what happens when a column is marked invisible
  • The database changes the column number to 0; if you have access (probably only if your are a DBA); you can see this with the following query (must be SYS or be GRANTed access):
SELECT c.name,c.type#,c.col#,c.intcol#, c.segcol#,
         TO_CHAR (c.property,'XXXXXXXXXXXX') AS property
FROM sys.col$ c, sys.obj$ o, sys.user$ u
WHERE c.obj# = o.obj#AND o.owner# = u.user#
  AND u.name = ‘MYUSER’
  AND o.name = ‘MYTABLE’;
  • Col# is set to 0
  • Property is set to x’40000020′

Once a column is made visible again, it takes the next available column number. So, you could intentionally “reorganize” a table (or at least the output from SELECT *) with potentially disastrous results! (let your imagination roam…)

Sample Statements

Here’s some code illustrating how invisible columns work.

First the table is created with invisible column

Invisible Column Creation

drop table invisible_test;
create table invisible_test 
 (id number,
  col1 varchar2(10),
  col2 varchar2(10) invisible,
  col3 varchar2(10));
desc invisible_test;
  • DESC (and SELECT *) do not show the column
    Screen Shot 2016-07-19 at 9.22.56 PM
  • Invisible columns do not appear in SQL Developer table display
    Screen Shot 2016-07-19 at 9.24.55 PM Screen Shot 2016-07-19 at 9.24.33 PM
  • Invisible columns DO appear in SQL Developer navigator
    Screen Shot 2016-07-19 at 9.25.18 PM
  • Hidden columns may be SELECTED by name or used elsewhere in SQL

Finding Invisible Columns in the Catalog

The following select will display column names and hidden indicator using the Oracle catalog.

select owner,table_name,column_name,
  hidden_column,column_id
from all_tab_cols
where owner = user
  and table_name = 'INVISIBLE_TEST';

Inserting Rows With Invisible Columns

The following statements INSERT rows into the table with invisible columns:

  • Invisible columns may be omitted from column lists
  • Invisible columns may be named in column lists
  • If invisible columns are included in values; they MUST be named in column list
SQL> insert into invisible_test (id,col1,col3) values (1,'a','a');
1 row inserted.
SQL> insert into invisible_test values (2,'b','b');
1 row inserted.
SQL> insert into invisible_test values (3,'c','c','c');
Error starting at line : 12 in command -
insert into invisible_test values (3,'c','c','c')
Error at Command Line : 12 Column : 13
Error report -
SQL Error: ORA-00913: too many values
SQL> -- fail
SQL> insert into invisible_test (id,col1,col2,col3) values (3,'c','c','c');
1 row inserted.
SQL> insert into invisible_test (col1,col3) values (4,'d');
1 row inserted.
SQL> insert into invisible_test values (5,'e');
Error starting at line : 15 in command -
insert into invisible_test values (5,'e')
Error at Command Line : 15 Column : 13
Error report -
SQL Error: ORA-00947: not enough values
SQL> insert into invisible_test values (5,'e','e');
1 row inserted.
  • Inserts without column specifications act as if the column is truly invisible
  • Inserts with column specifications may include invisible column

Queries and Invisible Columns

  • select * does not “see” the invisible column
  • select may name invisible columns or use invisible columns
SQL> select * from invisible_test;
ID         COL1       COL3
---------- ---------- ----------
1          a          a
2          b          b
3          c          c
4          d
5          e          e
SQL> select id,col1,col2,col3 from invisible_test;
ID         COL1       COL2       COL3
---------- ---------- ---------- ----------
1           a                    a
2           b                    b
3           c          c         c
4           d
5           e                     e

Making Columns Visible

  • Columns may be made visible or invisible using ALTER TABLE
  • Columns made visible appear at the bottom of the list due to column number reassignment
SQL> alter table invisible_test modify col2 visible;  
Table INVISIBLE_TEST altered.
SQL> desc invisible_test;
Name Null Type
---- ---- ------------
ID       NUMBER
COL1     VARCHAR2(10)
COL3     VARCHAR2(10)
COL2     VARCHAR2(10)
SQL> select * from invisible_test;
ID         COL1       COL3       COL2
---------- ---------- ---------- ----------
1          a          a
2          b          b
3          c          c          c
4          d
5          e                     e    

– Visible column shows up at end of describe and select *

Invisible Columns – Use and Maintenance

Invisible columns allow you to add columns to a table without “breaking” any code that has the misfortune to include “SELECT *”.

Making different columns invisible and then visible may cause code using SELECT * to behave erratically or fail.

An invisible column’s NOT NULL or NULL status is unchanged.

Invisible columns might be useful as a (pretty weak) kind of security.

Virtual columns can be made invisible and vice-versa.

Tables may be partitioned by an invisible column, either during or after table creation.

External, cluster and temporary tables may not have invisible columns.

User-defined types may not contain invisible attributes.

System-generated invisible columns may not be made visible.

Conclusion

Invisible columns allow us to add columns to tables without fear of breaking code that makes the unfortunate use of “SELECT *” or “INSERT” without column lists. This is particularly valuable when your code base includes purchased software that may not be modified for contractual or other reasons.

In some cases, Invisible columns will provide an excellent option for working with existing systems. Use of “invisible/visible” to reorder columns in output is probably a bad idea; you should probably recreate the tables in the desired column sequence.

Oracle 12c – PL/SQL “White List” via ACCESSIBLE BY

February 13th, 2016

Oracle 12c now provides a “white list” capability for procedure/function execution by means of a new PL/SQL ACCESSIBLE BY clause used to control access. ACCESSIBLE by specifies which objects are able to reference a PL/SQL object directly (sometimes called a “white list”). The ACCESSIBLE BY clause may be added to packages, procedures, functions, and types allowing an additional layer of security. Even if by some accident an intruder obtains permissions to use stored PL/SQL; the ACCESSIBLE BY rules will not allow any access that violates the specified rules.

Example Coding

Here’s an Example of ACCESSIBLE BY in a simple function:

create or replace function TIMES_10 (inval in number)
 return number
 accessible by (CALLER1,CALLER3)
 is
begin
 return inval * 10;
end;
/

Note: The “TIMES_10” routine may only be accessed by PL/SQL objects with the name “CALLER1” or “CALLER2” – it may not even be called from the SQL*Plus command line or SQL Developer.

Example Calls

Here are three example calls to the “times_10” function:

 create or replace procedure CALLER1 as
   anumber number := 1;
 begin
   anumber := times_10(anumber);
 end;
 /
 create or replace procedure CALLER2 as
   anumber number := 1;
 begin
   anumber := times_10(anumber);
 end;
 /
 create or replace procedure CALLER3 as
   anumber number := 1;
 begin
   anumber := times_10(anumber);
 end;
 /
  • The first execution from “CALLER1” runs fine.
  • The second execution from “CALLER2” aborts and raises an error.
    PLS-00905: object JOHN.CALLER2 is invalid
  • The third execution from “CALLER3” also runs fine.
    exec caller3; — runs fine

Clearly, ACCESSIBLE BY adds a useful layer of security to what is already in place with Oracle’s normal permission. You should consider using it where applicable in your shop.

Oracle 12c – PL/SQL in SELECT (WITH & PRAGMA UDF)

January 16th, 2016

Oracle 12c – PL/SQL in SELECT
(using WITH & PRAGMA UDF)

It is often useful to execute PL/SQL functions as part of an SQL SELECT statement (or other DML). When SQL calls PL/SQL a “context switch” occurs as execution moves between SQL execution and PL/SQL execution. In Oracle Database 12c Oracle has given us two tools to help reduce the impact of this context switching. The first is the ability to add a local function definition to the SELECT via the WITH clause, the second is to flag a function using PRAGMA UDF so that the compiler and optimizer will know it is to be used from SQL. Both options can measurably improve performance if used correctly.

To achieve maximum performance gains the PL/SQL function involved should not call other PL/SQL procedures and/or PL/SQL functions nor should PRAGMA UDF functions be called from “normal” PL/SQL (not inside SQL).
 

Normal Function Use in SQL (for comparison)

The following function definition and subsequent tests provide a baseline for comparison of WITH and PRAGMA UDF performance.

create or replace function times_42(inval number)
  return number 
as
begin
  return inval * 42;
end;
/
select channel_id,count(*) nbr_rows,sum(quantity_sold) qtysold, sum(times_42(cust_id)) cust42
  from sh.sales
  group by channel_id
  order by channel_id;

Elapsed (Wall-Clock) Execution Times

Running the above code three times (using SQL*Plus SET TIMING ON) resulted in elapsed times as follows:

1 – 2.018

2 – 1.945

3 – 1.928

 

WITH

Oracle 12c allows definition of PL/SQL Functions and Procedures using SQL’s Common Table Expression (WITH).

Defining PL/SQL locally reduces SQL-PL/SQL context-switching costs perhaps significantly improving performance.

Some points of caution:

  • Local PL/SQL overrides stored PL/SQL with the same name
  • Local PL/SQL is not stored in the database
  • Local PL/SQL is part of the same source code as the SQL that uses it
    (and would have to be copied if reused, yow!)
  • PL/SQL Result Cache does not cache Local PL/SQL

To use the feature in a sub-query SELECT, the main query must use the “/*+ WITH_PLSQL */” hint to avoid a syntax error.
 

Example Using WITH

with function times_42(inval number)
      return number
     as
     begin
        return inval * 42;
     end;
select channel_id,count(*) nbr_rows,
             sum(quantity_sold) qtysold,
             sum(times_42(cust_id)) cust42
  from sh.sales
  group by channel_id
  order by channel_id
/

 

Elapsed (Wall-Clock) Execution Times

Running the above code three times (using SQL*Plus SET TIMING ON) resulted in elapsed times as follows:

1 – .854

2 – .825

3 – .929

 

PRAGMA UDF

A related tool to reduce context switching in Oracle 12c allows functions to be defined using “PRAGMA UDF” to specify that a function will be used in SELECTS (behaving similar to function in WITH). This optimizes code for use within a SELECT or other SQL. Using PRAGMA UDF is probably not a good option for functions also used directly from PL/SQL (not nice to lie to the database).
 

Example Using PRAGMA UDF

create or replace function times_42(inval number)
  return number
as
  pragma udf;
begin
  return inval * 42;
end;
/
select channel_id,count(*) nbr_rows,
           sum(quantity_sold) qtysold, 
           sum(times_42(cust_id)) cust42
  from sh.sales
  group by channel_id
  order by channel_id;

 

Elapsed (Wall-Clock) Execution Times

Running the above code three times (using SQL*Plus SET TIMING ON) resulted in elapsed times as follows:

1 – .667

2 – .602

3 – .664

 

Performance Implications

All examples in this article use the Oracle-supplied SH.SALES table (>900k rows) running on a single-CPU laptop with Oracle 12c Standard Edition to allow you to recreate them. I was unable to find a tool to accurately measure context switching (if you know of one please send me a note); so, elapsed time is used in the comparisons.

Both WITH and PRAGMA UDF options provide a dramatic improvement in runtimes and in my testing (with other data sources and functions too) the PRAGMA UDF seemed to consistently out-perform a local function using WITH. Here is a table showing the comparative elapsed times shown in this unit:

1st Run 2nd Run 3rd Run
Compiled Function in Database 2.018 1.945 1.928
Function in WITH 0.854 0.825 0.929
Compiled UDF Function in Database 0.667 0.602 0.664

 

Conclusion

In this case my first choice is to use PRAGMA UDF because having a single copy of the function code seems so much more maintainable than having code repeated; the consistently better performance provides an added incentive.

Clearly, there are savings to be had when PL/SQL is called from SQL with a couple of caveats:

  • If the PL/SQL in question calls other PL/SQL, then, WITH and UDF might not be the best choice.
  • If a PL/SQL function will be called from PL/SQL, UDF may cause performance to be off since the optimization will be incorrect.

As always when performance is involved; don’t take my word for it (or anyone else’s). Test, test, and test again in your own environment with your own data!