Feed aggregator

Extract only "executable" SELECT code block within a very long SQL Text string that contains comments

Tom Kyte - Wed, 2017-03-15 11:46
Hello, I have a column of type CLOB in a table, where a user could copy-paste their entire "SQL Query Text" string so that a procedure will later "process" the entries in this table and use DBMS_SQL to parse out the query string to see if it is va...
Categories: DBA Blogs

datafile, tablespace

Tom Kyte - Wed, 2017-03-15 11:46
Hi Tom, I have create a tablespace test01 with datafile as '/u01/app/oracle/oradata/test01.dbf', also create one table test01 with tablespace test01, insert data into table test01, and commit. Than without alter tablespace, from os level i have mo...
Categories: DBA Blogs

Confessions of a PostgreSQL DBA

Chris Foot - Wed, 2017-03-15 10:46

Circa 1988.  I entered the technology space, writing SAS/JCL on an IBM 3270 for the Federal Government.  There was something missing though. Countless merges, duplicate data filtering and large data sets all seemed like a horrible waste of resources and, more importantly, time. Not only that, SAS user guides filled my entire cubicle, making it nearly impossible to evolve the skill set quickly.

The Django Fandango Farrago – Looking at Django’s Physical Data Model Design

The Anti-Kyte - Wed, 2017-03-15 08:40

I’m sure I’m not the only Oracle Developer who, over the years, has conjured a similar mental image during a planning meeting for a new web-based application…

wibble

…and we’re going to use an ORM

If you want the full gory details as to why this is so troubling from an Oracle database perspective, it is a topic I have covered at length previously.

This time, however, things are different.
Yes, I am somewhat limited in my choice of database due to the hardware my application will run on (Raspberry Pi).
Yes, Django is a logical choice for a framework as I’m developing in Python.
But, here’s the thing, I plan to do a bit of an audit of the database code that Django spits out.
< obligatory-Monty-Python-reference >That’s right Django, No-one expects the Spanish Inquisition ! < obligatory-Monty-Python-reference / >

torturer

Donde esta el Base de datos ?!

I know, this is a character from Blackadder and not Monty Python, but I’ve often regretted the fact that there never seems to be a vat of warm marmalade around (or some kind of gardening implement for that matter), when you enter those all important application architecture discussions at the start of a project.

As a result, one or two further Blackadder references may have crept in to the remainder of this post…

What we’re looking at

The Application I’m developing is as described in my previous post and we’ll be using SQLite as the database for our application.

What I’ll be covering here is :

  • The physical data model we want to implement for our DVD Library Application
  • Using Django to generate the data Model
  • Installation and use of the SQLite command line
  • Tweaking our code to improve the model

We’re not too concerned about performance at this point. The application is low-volume in terms of both data and traffic.
I’ll point out aspects of the code that have a potential performance impact as and when they come up (and I notice them), but performance optimisation is not really the objective here.
The main aim is to ensure that we maximise the benefits of using a relational database by ensuring data integrity.

The target model

By default, Django applies a synthetic key to each table it creates. I have indulged this proclivity in the model that follows, although it’s something I will return to later on.

The application I’m building is a simple catalogue of DVDs and Blu-Rays we have lying around the house.
The main table in this application will be TITLE, which will hold details of each Title we have on Disk.
Note that the Unique Key for this table is the combination of TITLE_NAME, YEAR_RELEASED and MEDIA_FORMAT. Yes I do have some films on both DVD and Blu-Ray.
As for the relationships :

  • a film/tv SERIES may have one, or more than one TITLE
  • a TITLE may belong to one or more CATEGORY
  • a CATEGORY may apply to one or more TITLE

So, in addition to our main data table, TITLE, we need two reference tables – SERIES and CATEGORY. We also need a join table between CATEGORY and TITLE to resolve the many-to-many relationship between them.
Each of the tables will have a Synthetic Key, which makes storing of Foreign Key values simple. However, Synthetic Key values alone are no guarantee of the uniqueness of a record (beyond that of the key itself), so these tables will also require unique constraints on their Natural Keys to prevent duplicate records being added.

The final data model should ideally look something like this :

dvds_data_model

Fun with Synthetic Keys

The first tables we’re going to generate are the CATEGORY and SERIES reference tables.
As we’re using Django, we don’t need to type any SQL for this.
Instead, we need to go to the project directory and create a file called models.py.

So, if we’re using the installation I setup previously…

cd ~/dvds/dvds
nano models.py

…and now we can define the CATEGORY object like this :

from django.db import models

class Category(models.Model) :
    # Object properties defined here map directly to database columns.
    category_name = models.CharField(max_length = 50)

    def __str__(self):
        return self.category_name

We now need to tell Django to implement (migrate) this definition to the database so…

cd ~/dvds
./manage.py makemigrations dvds
./manage.py migrate

Now, if this were a common or garden Python article, we’d be heading over to the Python interactive command line (possibly via another Monty Python reference). The fact is though that I’m getting withdrawal symptoms from not writing any SQL so, we’re going to install a CLI for SQLite.
Incidentally, if hacking around on the command line is not your idea of “a big party”, you can always go down the route of obtaining an IDE for SQLite – SQLite Studio seems as good as any for this purpose.

If like me however, you regard the command line as an opportunity for “a wizard-jolly time”…

sudo apt-get install sqlite3

…and to access the CLI, we can now simply run the following :

cd ~/dvds
sqlite3 db.sqlite3

Django will have created the table using the application name as a prefix. So, in SQLite, we can see the DDL used to generate the table by running …

.schema dvds_category

The output (reformatted for clarity) is :

CREATE TABLE "dvds_category"
(
    "id" integer NOT NULL PRIMARY KEY AUTOINCREMENT,
    "category_name" varchar(50) NOT NULL
);

The problem with this particular table can be demonstrated easily enough (incidentally, a Blokebuster is the opposite of a Chick Flick, in case you’re wondering)…

insert into dvds_category(category_name) values ('BLOKEBUSTER');

insert into dvds_category(category_name) values ('BLOKEBUSTER');

select *
from dvds_category;

1|BLOKEBUSTER
2|BLOKEBUSTER

As is evident, the Unique Key on category_name has not been implemented. Without this, the Synthetic Key on the table (the ID column) does nothing to prevent the addition of what are, in effect, duplicate records.

After tidying up…

delete from dvds_category;
.quit

…we need to re-visit the Category class in models.py…

from django.db import models

class Category(models.Model) :
    # Object properties defined here map directly to database columns.
    # Note that Django creates a synthetic key by default so no need to
    # specify one here
    category_name = models.CharField(max_length = 50, unique = True)

    def __str__(self):
        return self.category_name

This time, we’ve told Django that category_name has to be unique as well. So, when we migrate our change…

cd ~/dvds
./manage.py makemigrations dvds
./manage.py migrate

…and check the DDL that Django has used this time…

sqlite3 db.sqlite3
.schema dvds_category

…we can see that Django has added a Unique Constraint on the category_name…

CREATE TABLE "dvds_category"
(
    "id" integer NOT NULL PRIMARY KEY AUTOINCREMENT,
    "category_name" varchar(50) NOT NULL UNIQUE
);

…meaning that we now no longer get duplicate category_names in the table…

insert into dvds_category(category_name) values('BLOKEBUSTER');
insert into dvds_category(category_name) values('BLOKEBUSTER');
Error: UNIQUE constraint failed: dvds_category.category_name

It’s worth noting here that some RDBMS engines create a Unique Index to enforce a Primary Key. Were this the case for this table, you’d end up with two indexes on a two-column table. This is would not be the most efficient approach in terms of performance or storage.
Assuming that’s not a problem, we can move on and add the Series object to models.py as it’s structure is similar to that of Category…

from django.db import models

class Category(models.Model) :
    # Object properties defined here map directly to database columns.
    # Note that Django creates a synthetic key by default so no need to
    # specify one here
    category_name = models.CharField(max_length = 50, unique = True)

    def __str__(self):
        return self.category_name

class Series(models.Model) :
    series_name = models.CharField( max_length = 50, unique = True)

    def __str__(self) :
        return self.series_name

…and deploy it…

cd ~/dvds
./manage.py makemigrations dvds
./manage.py migrate

…which should result in a table that looks like this in SQLite :

CREATE TABLE "dvds_series"
(
    "id" integer NOT NULL PRIMARY KEY AUTOINCREMENT,
    "series_name" varchar(50) NOT NULL UNIQUE
);

Fun as it is messing around in the database on a command line, it’s not very frameworky…

DML using the Admin Interface

It’s a fairly simple matter to persuade Django to provide an interface that allows us to manage the data in our tables.
Step forward admin.py. This file lives in the same directory as models.py and, for our application as it stands at the moment, contains :

from django.contrib import admin
from .models import Category, Series

#Tables where DML is to be managed via admin
admin.site.register(Category)
admin.site.register(Series)

Save this and then just run :

./manage.py migrations

Now, if we run the server…

./manage.py runserver

…we can navigate to the admin site (appending /admin to the development server URL…

admin

You can then connect using the credentials of the super user you created when you setup Django initially.

Once connected, you’ll notice that Django admin has a bit of an issue with pluralising our table names

admin_wrong_plural

We’ll come back to this in a mo. First though, let’s add some Category records…

Click the Add icon next to “Categorys” and you’ll see …add_cat

Once we’ve added a few records, we can see a list of Categories just by clicking on the name of the table in the Admin UI :

cat_list

This list appears to be sorted by most recently added Category first. It may well be that we would prefer this listing to be sorted in alphabetical order.

We can persuade Django to implement this ordering for our tables, as well as correctly pluralizing our table names by adding a Meta class for each of the corresponding classes in models.py :

from django.db import models

class Category(models.Model) :
    # Object properties defined here map directly to database columns.
    # Note that Django creates a synthetic key by default so no need to
    # specify one here
    category_name = models.CharField(max_length = 50, unique = True)

    def __str__(self):
        return self.category_name

    class Meta :
        # set the default behaviour to be returning categories in alphabetical order by category_name
        ordering = ["category_name"]
        # Define the correct plural of "Category". Among other places, this is referenced in the Admin application
        verbose_name_plural = "categories"

class Series(models.Model) :
    series_name = models.CharField( max_length = 50, unique = True)

    def __str__(self) :
        return self.series_name

    class Meta :
        ordering = ["series_name"]
        verbose_name_plural = "series"

Once we migrate these changes :

./manage.py makemigrations dvds
./manage.py migrate

…and restart the dev server…

./manage.py runserver

…we can see that we’ve managed to cure the Admin app of it’s speech impediment…

correct_plural

…and that the Category records are now ordered alphabetically…

cat_order

It’s worth noting that specifying the ordering of records in this way will cause an additional sort operation whenever Django goes to the database to select from this table.
For our purposes the overhead is negligible. However, this may not be the case for larger tables.

So far, we’ve looked at a couple of fairly simple reference data tables. Now however, things are about to get rather more interesting…

Foreign Keys and other exotic database constructs

The Title object (and it’s corresponding table) are at the core of our application.
Unsurprisingly therefore, it’s the most complex class in our models.py.

In addition to the Referential Integrity constraints that we need to implement, there are also the media_type and bbfc_certificate fields, which can contain one of a small number of static values.
We also need to account for the fact that Django doesn’t really do composite Primary Keys.
I’m going to go through elements of the code for Title a bit at a time before presenting the final models.py file in it’s entirety.

To start with then, we’ll want to create a couple of choices lists for Django to use to validate values for some of the columns in the Title table…

# Allowable values Lists
MEDIA_FORMAT_CHOICES = (
    ('BR', 'Blu Ray'),
    ('DVD', 'DVD'),
)

# British Board of Film Classification Certificates
# as per the official BBFC site - http://www.bbfc.co.uk
BBFC_CHOICES = (
    ('U', 'U'),
    ('PG', 'PG'),
    ('12A', '12A'),
    ('15', '15'),
    ('18', '18'),
    ('R18', 'R18'),
)

In a database, you would expect these valid values to be implemented by check constraints. Django however, goes it’s own way on this. I’d infer from the lack of resulting database constraints that the Choices Lists will work so long as you always populate/update your underlying tables via the Django application itself.
Incidentally, it is possible to reference these name/value pairs in Django templates should the need arise, something I will cover in a future post. It is for this reason that I’ve declared them outside of the classes in which they’re used here.

As with choices, the same appears to apply to the check we’ve added to ensure that we don’t get a silly value for the year a film was released, which necessitates …

from django.core.validators import MinValueValidator
...
year_released = models.IntegerField(validators=[MinValueValidator(1878)]) # Movies invented around 1878.

Our first attempt at the Title class looks like this :

class Title( models.Model) :

    # For optional fields, blank = True means you can leave the field blank when entering the record details
    # null = True means that the column is nullable in the database
    title_name = models.CharField( max_length = 250)
    year_released = models.IntegerField(validators=[MinValueValidator(1878)]) # Movies invented around 1878.
    bbfc_certificate = models.CharField("BBFC Certificate", max_length = 3, choices = BBFC_CHOICES)
    media_format = models.CharField("Format", max_length = 3, choices = MEDIA_FORMAT_CHOICES)
    director = models.CharField(max_length = 100, null=True, blank=True)
    synopsis = models.CharField( max_length = 4000, null = True, blank = True)
    series = models.ForeignKey( Series, on_delete = models.CASCADE, null = True, blank = True)
    number_in_series = models.IntegerField(null = True, blank = True)
    categories = models.ManyToManyField(Category, blank = True)

    class Meta :
        ordering = ["series", "number_in_series", "title_name"]

    def __str__(self) :
        return self.title_name

Hang on, haven’t I forgotten something here ? Surely I need some way of implementing the Natural Key on this table ?
You’re right. However this omission is deliberate at this stage, for reasons that will become apparent shortly.
Yes, this is part of a plan “so cunning you could brush your teeth with it”.

Even without this key element, there’s quite a lot going on here. In the main class :

  • the year_released cannot be before 1878
  • the bbfc_certificate and media_format columns are associated with their choices lists using the choices option
  • we’ve specified that series as type models.ForeignKey
  • we’ve specified categories as the somewhat intriguing type models.ManyToManyField

In the Meta class, we’ve stipulated a multi-column ordering clause. Note that the default ordering appears to put nulls last. Therefore Title records that have null series and number_in_series values will appear first.

When we plug this into our models.py and apply the changes…

./manage.py makemigrations dvds
./manage.py migrate

…then check in the database…

sqlite3 db.sqlite3
.tables dvds_title%
dvds_title             dvds_title_categories

…we can see that Django has created not one, but two new tables.

In addition to the DVDS_TITLE table, which we may have expected and which looks like this :

CREATE TABLE dvds_title (
    id               INTEGER        NOT NULL
                                    PRIMARY KEY AUTOINCREMENT,
    title_name       VARCHAR (250)  NOT NULL,
    year_released    INTEGER        NOT NULL,
    bbfc_certificate VARCHAR (3)    NOT NULL,
    media_format     VARCHAR (3)    NOT NULL,
    director         VARCHAR (100),
    synopsis         VARCHAR (4000),
    number_in_series INTEGER,
    series_id        INTEGER        REFERENCES dvds_series (id)
);

…Django has been smart enough to create a join table to resolve the many-to-many relationship between TITLE and CATEGORY :

CREATE TABLE dvds_title_categories (
    id          INTEGER NOT NULL
                        PRIMARY KEY AUTOINCREMENT,
    title_id    INTEGER NOT NULL
                        REFERENCES dvds_title (id),
    category_id INTEGER NOT NULL
                        REFERENCES dvds_category (id)
);

Whilst Django can’t resist slapping on a gratuitous Synthetic Key, it is at least clever enough to realise that a composite key is also required. To this end, it also creates an Unique Index on DVDS_TITLE_CATEGORIES :

CREATE UNIQUE INDEX dvds_title_categories_title_id_96178db6_uniq ON dvds_title_categories (
    title_id,
    category_id
);

So, it seems that Django can handle composite keys after all. Well, not quite.

Remember that we still need to add a unique key to TITLE as we’ve modelled it to have a Natural Key consisting of TITLE_NAME, YEAR_RELEASED and MEDIA_FORMAT.

We can do that easily enough, simply by adding a unique_together clause to Title’s Meta class in models.py :

class Meta :
    ordering = ["series", "number_in_series", "title_name"]
    # Natural Key for a Title record is title_name, year_released and media_format - we have some films on DVD AND Blu-Ray.
    unique_together = ('title_name', 'year_released', 'media_format',)

If we now apply this change…

./manage.py makemigrations dvds
./manage.py migrate

…we can see that Django has added the appropriate index…

CREATE UNIQUE INDEX dvds_title_title_name_ae9b05c4_uniq ON dvds_title (
    title_name,
    year_released,
    media_format
);

The really wacky thing about all this is that, if we had used the unique_together function in the first place, Django would not have created the Unique Key on the DVDS_TITLE_CATEGORIES table. However, as we’ve added Title’s Natural Key in a separate migration, Django leaves the Unique Key on DVDS_TITLE_CATEGORIES in place.
Irrespective of how practical the Synthetic Key on DVDS_TITLE may be, the fact is, it is defined as the Primary Key for that table. As DVDS_TITLE_CATEGORIES is a Join Table then, in relational terms, it should itself have a Natural Key consisting of the Primary Keys of the two tables it’s joining.

Anyway, our final models.py looks like this :

from django.db import models
from django.core.validators import MinValueValidator

# Allowable values Lists
MEDIA_FORMAT_CHOICES = (
    ('BR', 'Blu Ray'),
    ('DVD', 'DVD'),
)

# British Board of Film Classification Certificates
# as per the official BBFC site - http://www.bbfc.co.uk
BBFC_CHOICES = (
    ('U', 'U'),
    ('PG', 'PG'),
    ('12A', '12A'),
    ('15', '15'),
    ('18', '18'),
    ('R18', 'R18'),
)

class Category(models.Model) :
    # Object properties defined here map directly to database columns.
    # Note that Django creates a synthetic key by default so no need to
    # specify one here
    category_name = models.CharField(max_length = 50, unique = True)

    def __str__(self):
        return self.category_name

    class Meta :
        # set the default behaviour to be returning categories in alphabetical order by category_name
        ordering = ["category_name"]
        # Define the correct plural of "Category". Among other places, this is referenced in the Admin application
        verbose_name_plural = "categories"

class Series(models.Model) :
    series_name = models.CharField( max_length = 50, unique = True)

    def __str__(self) :
        return self.series_name

    class Meta :
        ordering = ["series_name"]
        verbose_name_plural = "series"

class Title( models.Model) :

    # For optional fields, blank = True means you can leave the field blank when entering the record details
    # null = True means that the column is nullable in the database
    title_name = models.CharField( max_length = 250)
    year_released = models.IntegerField(validators=[MinValueValidator(1878)]) # Movies invented around 1878.
    bbfc_certificate = models.CharField("BBFC Certificate", max_length = 3, choices = BBFC_CHOICES)
    media_format = models.CharField("Format", max_length = 3, choices = MEDIA_FORMAT_CHOICES)
    director = models.CharField(max_length = 100, null=True, blank=True)
    synopsis = models.CharField( max_length = 4000, null = True, blank = True)
    series = models.ForeignKey( Series, on_delete = models.CASCADE, null = True, blank = True)
    number_in_series = models.IntegerField(null = True, blank = True)
    categories = models.ManyToManyField(Category, blank = True)

    class Meta :
        ordering = ["series", "number_in_series", "title_name"]
        # Natural Key for a Title record is title_name, year_released and media_format - we have some films on DVD AND Blu-Ray.
        unique_together = ('title_name', 'year_released', 'media_format',)

    def __str__(self) :
        return self.title_name

We also want to add Title to admin.py so that we can perform DML on the table in the admin application. Hence our final admin.py looks like this :

from django.contrib import admin
from .models import Category, Series, Title

#Tables where DML is to be managed via admin
admin.site.register(Category)
admin.site.register(Series)
admin.site.register(Title)
Conclusion

Django makes a pretty decent fist of implementing and maintaining a Relational Data Model without the developer having to write a single line of SQL.
Of course, as with any code generator, some of it’s design decisions may not be those that you might make if you were writing the code by hand.
So, if the data model and it’s physical implementation is important to your application, then it’s probably worth just checking up on what Django is up to in the database.


Filed under: python, SQL Tagged: admin.py, Django, foreign key, makemigrations, manage.py, migrate, models.py, Natural Key, runserver, SQLite, synthetic key, unique_together

Introducing NoSQL and MongoDB to Relational Database professionals

Amis Blog - Wed, 2017-03-15 06:25

Most enterprises have a lot of variety in the data they deal with. Some data is highly structured and other is very unstructured, some data is bound by strict integrity rules and quality constraints and other is free of any restrictions, some data is “hot” – currently very much in demand – and other data can be stone cold. Some data needs to extremely accurate, down to a prescribed number of fractional digits and other is only approximate. Some is highly confidential and other publicly accessible. Some is around in small quantities and other in huge volumes.

Over the years many IT professionals and companies have come to the realization that all this differentiation in data justifies or even mandates a differentiation in how the data is stored and processed. It does not make sense to treat the hottest transactional data in the same way as the archived records from 30 years. Yet many organizations have been doing exactly that: store it all in the enterprise relational database. It works, keeps all data accessible for those rare instances where that really old data is required and most importantly: keeps all data accessible in the same way – through straightforward SQL queries.

On March 14th, we organized a SIG session at AMIS around NoSQL in general and MongoDB in particular. We presented on the history of NoSQL, how it complements relational databases and a pure SQL approach and what types of NoSQL databases are available. Subsequently we focused on MongoDB, introducing the product and its architecture and discussing how to interact with MongoDB from JavaScript/NodeJS and from Java.

The slides presented by the speakers – Pom Bleeksma and Lucas Jellema – are shown here (from SlideShare):

 

The handson workshop is completely available from GitHub: https://github.com/lucasjellema/sig-nosql-mongodb.

image

An additional slide deck was discussed – to demonstrate 30 queries side by side, against MongoDB vs Oracle Database SQL. This slide deck includes the MongoDB operations:

•Filter & Sort (find, sort)

•Aggregation ($group, $project, $match, $sort)

•Lookup & Outer Join ($lookup, $arrayElemAt)

•Facet Search ($facet, $bucket, $sortByCount)

•Update (findAndModify, forEach, save, update, upsert, $set, $unset)

•Date and Time operations

•Materialized View ($out)

•Nested documents/tables ($unwind, $reduce)

•Geospatial (ensureIndex, 2dsphere, $near, $geoNear)

•Text Search (createIndex, text, $text, $search)

•Stored Procedures (db.system.js.save, $where)

 

The post Introducing NoSQL and MongoDB to Relational Database professionals appeared first on AMIS Oracle and Java Blog.

Oracle Public Cloud – Invoking ICS endpoints from SOA CS – configure SSL certificate and basic authentication

Amis Blog - Wed, 2017-03-15 06:16

As part of the Soaring through the Clouds demo of 17 Oracle Public Cloud services, I had to integrate SOA CS with both ACCS (Application Container Cloud) and ICS (Integration Cloud Service).

image

Calls from Service Bus and SOA Composites running in SOA Suite 12c on SOA CS to endpoints on ACCS (Node.js Express applications) and ICS (REST connector endpoint) were required in this demo. These calls are over SSL (to https endpoints) and for ICS also require basic authentication (at present, ICS endpoints cannot be invoked anonymously).

This article shows the steps for taking care of these two aspects:

  • ensure that the JVM under SOA Suite on SOA CS knows and trusts the SSL certificate for ACCS or ICS
  • ensure that the call from SOA CS to ICS carries basic authentication details

The starting point is a SOA Composite that corresponds with the preceding figure – with external references to DBaaS (through Database Adapter), ICS (to call an integration that talks to Twitter) and ACCS (to invoke a REST API on NodeJS that calls out to the Spotify API):

image

Configure SSL Certificate on JVM under SOA Suite on SOA CS

I have tried to deploy the SOA composite (successful) and invoke the TweetServiceSOAP endpoint (that invokes ICS) (not successful). The first error I run into is:

env:Serverjavax.ws.rs.ProcessingException: javax.net.ssl.SSLHandshakeException: sun.security.validator.ValidatorException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested targetoracle.sysman.emInternalSDK.webservices.util.SoapTestException: Client received SOAP Fault from server : javax.ws.rs.ProcessingException: javax.net.ssl.SSLHandshakeException: sun.security.validator.ValidatorException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target

image

This may sound a little cryptic, but is actually quite simple: the endpoint for the ICS service I am trying to invoke is: https://ics4emeapartner-partnercloud17.integration.us2.oraclecloud.com/integration/flowapi/rest/ACEDEM_RESTME_… The essential is right at the beginning: https. The communication with the endpoint is secure, over SSL. This requires the certificate of the ICS server to be used by SOA CS (in particular the JVM under WebLogic running SOA Suite on the SOA CS instance). For this to happen, the certificate needs to be configured with the JVM as a trusted certificate.

With WebLogic 12c it has become a lot easier to register certificates with the server – going through the Enterprise Manager Fusion Middleware Control. These are the steps:

1. Paste the endpoint for the ICS service in the browser’s location bar and try to access it; this will not result in a meaningful response. It will however initiate an SSL connection between browser and server, as you can tell from the padlock icon displayed to the left of the location bar

image

2. Click on the padlock icon, to open the details for the SSL certificate

SNAGHTML1005017

Open the Security tab and click on View Certificate

SNAGHTML100be83

3. Open the Details tab and Export the Certificate

SNAGHTML101da3d

Save the certificate to a file:

SNAGHTML10236cd

4. Open the Enterprise Manager Fusion Middleware Control for the WebLogic Domain under the SOA CS instance. Navigate to Security | Keystore:

image

5. Select Stripe system | trust and click on the Manage button

image

6. Click on Import to import a new certificate:

image

Select Trusted Certificate as the Certificate Type. Provide an alias to identify the certificate.

Click browse and select the file that was saved when exporting the certificate in step 3:

image

Click OK.

The Certificate is imported and added to the keystore:

image

7. Restart the WebLogic Domain (admin server and all managed servers)

Unfortunately for the new certificate to become truly available, a restart is (still) required. (or at least, that is my understanding, perhaps you can try without because it seems like a very heavy step)

This blog by Adam DesJardin from our REAL partner AVIO Consulting provided much of the answer: http://www.avioconsulting.com/blog/soa-suite-12c-and-opss-keystore-service

 

Add basic authentication to the call from SOA CS to ICS

When I again tested my call to the TweetServiceSOAP endpoint (that invokes ICS), I was again not successful. This time, a different exception occurred:

env:ServerAuthorization Requiredoracle.sysman.emInternalSDK.webservices.util.SoapTestException: Client received SOAP Fault from server : Authorization Required

This is not really a surprise: all calls to ICS endpoints require basic authentication (because at present, ICS endpoints cannot be invoked anonymously). These are the steps to make this successful:

1. Create an Oracle Public Cloud user account with one permission: call ICS services: johndoe

Now we need to a credential for jonhdoe in a credential map in the credential store in WebLogic, and refer to that credential in a OWMS Security Policy that we add to the Reference in the SOA Composite that makes the call to ICS.

2. Open the Enterprise Manager Fusion Middleware Control for the WebLogic Domain under the SOA CS instance. Navigate to Security | Credentials:

image

3. If the map oracle.wsm.security does not yet exist, click on Create Map. Enter the name oracle.wsm.security in the Map Name field and click on OK.

image

4. Select the map oracle.wsm.security and click on Create Key

image

Set the Key for this credential; the key is used to refer to the credential in the security policy. Here I use ICSJohnDoe.

image

Set the type of Password and the username and password to the correct values for the ICS user. Click on OK to create.

image

5. Add a security policy to the Reference in the SOA Composite.

In JDeveloper open the SOA Composite. Right click on the Reference. Select Configure SOA WS Policies from the context menu.

image

Click on the plus icon in the category Security. Select oracle/http_basic_auth_over_ssl_client_policy.

image

Set the value of property csf-key to the Key value defined for the credential in step 4, in my case ICSJohnDoe.

Click on OK.

6. Redeploy the SOA Composite to SOA CS.

 

This time when I invoke the Web Service, my Tweet gets published:

image

The flow trace for the SOA Composite:

image

Resources

A-Team Article – add certificate to JCS and invoke JCS from ICS – http://www.ateam-oracle.com/configuring-https-between-integration-cloud-service-and-java-cloud-service/

    The post Oracle Public Cloud – Invoking ICS endpoints from SOA CS – configure SSL certificate and basic authentication appeared first on AMIS Oracle and Java Blog.

    Oracle EMEA Infrastructure & IaaS Partner Community Forum 25th-26th April 2017

    The 2017 Oracle EMEA Infrastructure & IaaS Partner Community Forum will be  taking  place on 25th and 26th April in Malaga, Spain. Come learn and share your experiences to extend IT...

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

    Oracle EMEA Infrastructure & IaaS Partner Community Forum 25th-26th April 2017

    The 2017 Oracle EMEA Infrastructure & IaaS Partner Community Forum will be  taking  place on 25th and 26th April in Malaga, Spain. Come learn and share your experiences to extend IT...

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

    Building Custom EBS Mobile Apps with REST

    Steven Chan - Wed, 2017-03-15 02:04

    EBS Approval mobile app screenshotOver 20 EBS mobile apps are available today for E-Business Suite 12.1 and 12.2.  These apps are available for iOS and Android and cover functional areas such as Approvals, Expenses, Timecards, iProcurement, Sales Orders, Inventory, and much more.  These apps are documented here:

    What's New

    Our mobile apps have just gone through a refresh cycle (a.k.a. "Release 6.1") along with updates to all of the server-side libraries.  These updates are transparent to end-users and backwards-compatible with previous mobile app releases (e.g. "Release 6.0"). 

    The latest versions of all of these apps use APIs that are documented in our Oracle Integration Repository.  These APIs are newly-published as REST services.  This means that you can now use these APIs to build your own custom mobile apps for EBS.

    New guidelines for building your own mobile apps for the E-Business Suite are published here:

     

    Categories: APPS Blogs

    Building Custom EBS Mobile Apps with REST

    Steven Chan - Wed, 2017-03-15 02:04

    EBS Approval mobile app screenshotOver 20 EBS mobile apps are available today for E-Business Suite 12.1 and 12.2.  These apps are available for iOS and Android and cover functional areas such as Approvals, Expenses, Timecards, iProcurement, Sales Orders, Inventory, and much more.  These apps are documented here:

    What's New

    Our mobile apps have just gone through a refresh cycle (a.k.a. "Release 6.1") along with updates to all of the server-side libraries.  These updates are transparent to end-users and backwards-compatible with previous mobile app releases (e.g. "Release 6.0"). 

    The latest versions of all of these apps use APIs that are documented in our Oracle Integration Repository.  These APIs are newly-published as REST services.  This means that you can now use these APIs to build your own custom mobile apps for EBS.

    New guidelines for building your own mobile apps for the E-Business Suite are published here:


    Categories: APPS Blogs

    12.2 Moving Tables Online (Real Cool World)

    Richard Foote - Wed, 2017-03-15 01:41
    One of the really cool new features introduced in Oracle Database 12c Release 2 is the capability to MOVE tables ONLINE, while maintaining all the associated index structures. This was possible for table partitions in Oracle Database 12c Release 1 but only for Indexed Organized Tables before then. Rather than show a demo that just moves a table, […]
    Categories: DBA Blogs

    Best Way to Write SQL in Java

    Gerger Consulting - Wed, 2017-03-15 01:17

    Many of us struggle writing SQL in Java. ORM tools such as Hibernate often generate SQL statements that result in poor application performance. Surely, there must be a better way to write SQL in Java.



    Attend the free webinar by Java Champion Lukas Eder to learn the best way to write SQL in Java. Find out how you can use SQL as a type safe domain specific language in Java.

    Sign up for the free webinar.

    Categories: Development

    Iceland, Iceland, Baby

    Scott Spendolini - Tue, 2017-03-14 18:37

    Alright, stop!  Collaborate and listen!  Ok, I’ll be the one to take my own advice here and stop...

    Later this month, I’ll be heading to Reykjavík, Iceland to deliver our 3-day training class “Developing Desktop APEX Applications”.  This class will be open to the public and costs about $2500 per student, so anyone is welcome to sign up. You’ll have to make your way to Iceland, of course.

    Miracle logo1

    Here’s a brief overview of what we’re going to cover:

    This 3-day course is an introduction to developing web applications using Oracle Application Express, or simply APEX. The course starts out with an overview of data model of the application that student will build. It then transitions to the SQL Workshop portion of APEX, where basic database object management concepts are addressed.

    The bulk of the remainder of the class focuses on building an APEX application, starting with the core components that make up the foundation of the application. Students will then build several forms and reports, which allow user interaction with the data. Next, additional types of forms and reports will be introduced, as well as more advanced techniques used when managing them. The course will conclude with a review of the basic security attributes of an application as well as how to prepare and deploy it to a production environment.

    The course will run from March 28th through March 30th, and will be held in a location TBD in Reykjavík. More details, as well as the course outline and a link to register can be found on Miracle’s site here: http://miracle.is/en/building-apex-applications/

    Advanced Compression Options in Oracle 11g

    Tom Kyte - Tue, 2017-03-14 17:26
    Hi Chris/Connor, Can you please share any document on Advanced Compression Options in Oracle 11g. I have searching on this and getting only theoretical stuffs. Could you please share any reference, blog to study on this to understand its use on OL...
    Categories: DBA Blogs

    Umlaut characters converted to junk while running PL/SQL script

    Tom Kyte - Tue, 2017-03-14 17:26
    Hi, I have procedure with umlaut characters in it. Below is the sample. CREATE OR REPLACE PROCEDURE procPrintHelloWorld IS BEGIN DBMS_OUTPUT.PUT_LINE('? a A, O, U, a, o Hello World!'); END; / When procedure is created through sql ...
    Categories: DBA Blogs

    Expdp and impdp

    Tom Kyte - Tue, 2017-03-14 17:26
    I have a requirement to export a full database using EXPDP and import only three schemas using remap_schemas to a different schema than the original. How can I achieve that?
    Categories: DBA Blogs

    Clarification of "snapshot too old" error message

    Tom Kyte - Tue, 2017-03-14 17:26
    Hi, I was going through Tom's book and came across "snapshot too old" section. In the section small undo tablespace is created and below block is run. Below block generates error message "snapshot too old". As per my understanding,the column values...
    Categories: DBA Blogs

    Web Service

    Tom Kyte - Tue, 2017-03-14 17:26
    A client of mine asked me posted one or more plsql functions as webservice. Which oracle tool should I use? Thanks from Italy Hi Connor, "posted" is a mistake. They want to access them via web service. Sorry for my poor english. Mass...
    Categories: DBA Blogs

    Reclaim the free space by truncate/Drop partition from partition table having LOB column

    Tom Kyte - Tue, 2017-03-14 17:26
    Hi Team, I have one partition table having almost 300GB data with LOB column which contains the 90-95% data of total table size. Partition table having 130 partition created and out of it currently 20 partition having the data. Partition table hav...
    Categories: DBA Blogs

    db_link and synonym

    Tom Kyte - Tue, 2017-03-14 17:26
    Hi Tom, I have two databases <b>wombat</b> and <b>foo</b>. Womabt has two users craig and denver. I have created a private db_link with the same name in each users of <b>wombat</b> to connect to <b>foo</b> as follows : owner DB_LINK ------ ...
    Categories: DBA Blogs

    Pages

    Subscribe to Oracle FAQ aggregator