Feed aggregator

NO_DATA_FOUND exception when passing a empty row in associative array to procedure

Tom Kyte - Thu, 2018-02-01 00:26
I am trying to pass a procedure a record that is, or will be rather a new row in an associative array but am getting a a NO_DATA_FOUND exception. When I set to position in the collection to NULL, it works fine. Example simplified down: -- spec...
Categories: DBA Blogs

Keep archived logs for 5 days

Tom Kyte - Thu, 2018-02-01 00:26
Hi, I want to keep archived logs for 5 days in physical location on windows. can you please suggest on this ?
Categories: DBA Blogs

Invalid Views in PeopleSoft and the Oracle Database

David Kurtz - Wed, 2018-01-31 14:33
I was listening to the section on Invalid Views in PSADMIN Podcast #117 (@19:00). Essentially, when you drop and recreate a view that is referenced by a second view, the status on the second view in the database goes invalid. This is not a huge problem because as soon as you query the second view it is compiled. However, you would like to know whether any change to a view prevents any dependent views from compiling, although you would expect have teased these errors out before migration to production.
The PeopleSoft solution to this is to include all the dependent views in the Application Designer package. However, as pointed out, in the podcast you are now releasing code, possibly unintentionally releasing code changes and certainly updating last change dates on record definitions, when really you just need to compile the database objects.   PeopleSoft does this because it is a platform generic solution, but really it is using the PeopleSoft Application Designer to solve a database management issue.
A similar problem also occurs in the Oracle database with dependent PL/SQL procedures and packages where you sometimes get referential loops. Oracle provides a procedure DBMS_UTILITY.COMPILE_SCHEMA that recompiles all invalid objects in a schema and report any errors to the ALL_ERRORS view.  I think this is a much safer option.

Here is a very simple (non-PeopleSoft) example
drop table t purge;
drop view a;
drop view b;

create table t (a number);
insert into t values (1);
create view a as select a from t;
create view b as select a from a;

column object_name format a12
select object_type, object_name, status
from user_objects where object_name IN('T','A','B')
/

OBJECT_TYPE OBJECT_NAME STATUS
----------------------- ------------ -------
TABLE T VALID
VIEW B VALID
VIEW A VALID
Dropping and recreating view A renders view B invalid.
drop view a;
create view a as select a from t;

select object_type, object_name, status
from user_objects
where object_name IN('T','A','B')
/

OBJECT_TYPE OBJECT_NAME STATUS
----------------------- ------------ -------
TABLE T VALID
VIEW B INVALID
VIEW A VALID

select * from b;
select object_type, object_name, status
from user_objects where object_name IN('T','A','B')
/
Just querying B has made it valid again.
OBJECT_TYPE             OBJECT_NAME  STATUS
----------------------- ------------ -------
TABLE T VALID
VIEW B VALID
VIEW A VALID
Let's make B invalid again by rebuild A, but this time I will change the name of the column in view A from A to T so that view B cannot compile without an error.
drop view a;
create view a (t) as select a from t;

select object_type, object_name, status
from user_objects where object_name IN('T','A','B')
/
EXEC DBMS_UTILITY.compile_schema(schema => 'SCOTT');
select object_type, object_name, status
from user_objects where object_name IN('T','A','B')
/

OBJECT_TYPE OBJECT_NAME STATUS
----------------------- ------------ -------
TABLE T VALID
VIEW B INVALID
VIEW A VALID
And I can query the errors from user_errors
NAME
---------------------------------------------------
TYPE SEQUENCE LINE POSITION
------------ ---------- ---------- ----------
TEXT
---------------------------------------------------
ATTRIBUTE MESSAGE_NUMBER
--------- --------------
B
VIEW 1 0 0
ORA-00904: "A": invalid identifier
ERROR 0
N.B.: if you use CREATE OR REPLACE VIEW is not left invalid unless there is an error. Unfortunately, Application Designer always drops and recreates views.

Assemble Systems and Oracle Construction and Engineering Offer Model-based Scheduling for Construction

Oracle Press Releases - Wed, 2018-01-31 11:00
Press Release
Assemble Systems and Oracle Construction and Engineering Offer Model-based Scheduling for Construction Construction industry to benefit from timeline simulation and BIM data integration with Oracle’s Primavera P6 schedules

Redwood Shores, Calif.—Jan 31, 2018

Assemble Systems, a Gold level member of Oracle Partner Network (OPN), today announced a model-based scheduling integration with Oracle’s Primavera P6 Enterprise Project Portfolio Management scheduling software. Oracle’s Primavera P6 is the de facto standard and a leader in planning and scheduling for the Commercial Building and the Engineering, Procurement and Construction industry. Oracle customers will be able to enhance the value of their Primavera P6 implementation and improve their construction projects by leveraging Building Information Model (BIM) data through this integration.

Assemble’s integration with Primavera P6 enables construction managers to combine the baseline schedule and the BIM data to communicate construction sequencing to owners and other partners. Market research indicates that 4D schedule simulation is helpful with its ability to marry highly detailed scheduling to design and construction models. However, it is often viewed as too complex and difficult to share across project teams, due to the constantly changing nature of the models, activities, and other schedule data used. The new integration will allow users to easily create simulations, cost-loaded schedules, micro-schedules, and manage constant project changes.

“BIM has been playing an increasingly vital role in the evolution of the construction industry, however there has been a need to bring the same efficiencies from BIM to scheduling processes. We are pleased to work with Assemble to bring this combination of scope and schedule to our Primavera P6 customers,” said Andy Verone, Vice President Product Strategy, Oracle Construction and Engineering.

“I’m proud of our innovative development team who built this integration to provide our customers with another powerful tool from the Assemble Cloud,” said Donald Henrich, Assemble CEO. “Assemble focuses on combining data from models, drawings, and point clouds and making connections to existing customer systems including Oracle’s Primavera P6.  We are very happy to team with Oracle Construction and Engineering to drive the construction industry forward.”

The integration was unveiled at the Construction CPM Conference in New Orleans this week.  If you would like to learn more about the Assemble and Oracle integration, contact the Assemble team today here.

Contact Info
Judi Palmer
Oracle
650.506.0266
judi.palmer@oracle.com
About Oracle Construction and Engineering

Oracle Construction and Engineering delivers best-in-class project management solutions that empower organizations to proactively manage projects, gain complete visibility, improve collaboration, and manage change. Its cloud-based solutions for global project planning and execution help improve strategy execution, operations, and financial performance. For more information, please visit www.oracle.com/construction-and-engineering.

About Assemble

Assemble Systems provides a SaaS solution that acts as a HUB consuming Building Information Models, drawings, and point clouds. This solution enables construction professionals to condition, query, and connect the data to key workflows. These workflows include bid management, estimating, project management, scheduling, site management, and finance. Today, Assemble is used by over 195 companies including one fourth of the ENR 400 and at over 1,000 construction offices and sites around the world. To learn more about Assemble Systems, visit www.assemblesystems.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

Judi Palmer

  • 650.506.0266

Announcing the Oracle WebLogic Server Kuberentes Operator

OTN TechBlog - Wed, 2018-01-31 08:00

We are very excited to announce the Oracle WebLogic Server Kubernetes Operator, which is available today as a Technology Preview and which is delivered in open source at https://oracle.github.io/weblogic-kubernetes-operator.  The operator can manage any number of WebLogic domains running in a Kubernetes environment.  It provides a mechanism to create domains, automates domain startup, allows scaling WebLogic clusters up and down either manually (on-demand) or through integration with the WebLogic Diagnostics Framework or Prometheus, manages load balancing for web applications deployed in WebLogic clusters, and provides integration with ElasticSearch, logstash and Kibana.

The operator uses the standard Oracle WebLogic Server 12.2.1.3 Docker image, which can be found in the Docker Store or in the Oracle Container Registry.  It treats this image as immutable, and all of the state is persisted in a Kubernetes persistent volume.  This allows us to treat all of the pods as throwaway and replaceable, and it completely eliminates the need to manage state written into Docker containers at runtime (because there is none).

The diagram below gives a high level overview of the layout of a domain in Kubernetes when using the operator:

The operator can expose the WebLogic Server Administration Console to external users (if desired), and can also allow external T3 access; for example for WLST.  Domains can talk to each other, allowing distributed transactions, and so on. All of the pods are configured with Kubernetes liveness and readiness probes, so that Kubernetes can automatically restart failing pods, and the load balancer configuration can include only those Managed Servers in the cluster that are actually ready to service user requests.

We have a lot of documentation available on the project pages on GitHub including details about our design philosophy and architecture, as well as instructions on how to use the operator, video demonstrations of the operator in action, and a developer page for people who are interested in contributing to the operator.

We hope you take the opportunity to play with the Technology Preview and we look forward to getting your feedback.

Getting Started

The Oracle WebLogic Server Kubernetes Operator has the following requirements:

  • Kubernetes 1.7.5+, 1.8.0+ (check with kubectl version)
  • Flannel networking v0.9.1-amd64 (check with docker images | grep flannel)
  • Docker 17.03.1.ce (check with docker version)
  • Oracle WebLogic Server 12.2.1.3.0

For more details on the certification and support statement of WebLogic Server on Kubernetes, refer to My Oracle Support Doc Id 2349228.1.

A series of video demonstrations of the operator are available here:

The overall process of installing and configuring the operator and using it to manage WebLogic domains consists of the following steps. The provided scripts will perform most of these steps, but some must be performed manually:

  • Registering for access to the Oracle Container Registry
  • Setting up secrets to access the Oracle Container Registry
  • Customizing the operator parameters file
  • Deploying the operator to a Kubernetes cluster
  • Setting up secrets for the Administration Server credentials
  • Creating a persistent volume for a WebLogic domain
  • Customizing the domain parameters file
  • Creating a WebLogic domain

Complete up-to-date instructions are available at https://github.com/oracle/weblogic-kubernetes-operator/blob/master/site/installation.md or read on for an abbreviated version:

Build the Docker image for the operator

To run the operator in a Kubernetes cluster, you need to build the Docker image and then deploy it to your cluster.

First run the build using this command:

mvn clean install

Then create the Docker image as follows:

docker build -t weblogic-kubernetes-operator:developer --no-cache=true

We recommend that you use a tag other than latest to make it easy to distinguish your image. In the example above, the tag could be the GitHub ID of the developer.

Next, upload your image to your Kubernetes server as follows:

# on your build machine docker save weblogic-kubernetes-operator:developer > operator.tar scp operator.tar YOUR_USER@YOUR_SERVER:/some/path/operator.tar # on the Kubernetes server docker load < /some/path/operator.tar

Verify that you have the right image by running docker images | grep webloogic-kubernetes-operator on both machines and comparing the image ID.

We will be publishing the image in Oracle Container Registry and the instructions will be updated when it is available there.  After it is published, you will not need to build the image yourself, you will have the option to pull it from the registry instead.

Customizing the operator parameters file

The operator is deployed with the provided installation script, create-weblogic-operator.sh. The input to this script is the file create-operator-inputs.yaml, which needs to updated to reflect the target environment.

The following parameters must be provided in the input file:

CONFIGURATION PARAMETERS FOR THE OPERATOR Parameter Definition Default externalOperatorCert A base64 encoded string containing the X.509 certificate that the operator will present to clients accessing its REST endpoints. This value is only used when externalRestOption is set to custom-cert.   externalOperatorKey A base64 encoded string containing the private key ask tom This value is only used when externalRestOption is set to custom-cert.   externalRestOption Write me. Allowed values:
- none Write me
- self-signed-cert The operator will use a self-signed certificate for its REST server. If this value is specified, then the externalSans parameter must also be set.
- custom-cert Write me. If this value is specified, then the externalOperatorCert and externalOperatorKey must also be provided. none externalSans A comma-separated list of Subject Alternative Names that should be included in the X.509 Certificate. This list should include ...
Example: DNS:myhost,DNS:localhost,IP:127.0.0.1 . namespace The Kubernetes namespace that the operator will be deployed in. It is recommended that a namespace be created for the operator rather than using the default namespace. weblogic-operator targetNamespaces A list of the Kubernetes namespaces that may contain WebLogic domains that the operator will manage. The operator will not take any action against a domain that is in a namespace not listed here. default remoteDebugNodePort Tom is adding a debug on/off parameter
If the debug parameter if set to on, then the operator will start a Java remote debug server on the provided port and will suspend execution until a remote debugger has attached. 30999 restHttpsNodePort The NodePort number that should be allocated for the operator REST server on which it should listen for HTTPS requests on. 31001 serviceAccount The name of the service account that the operator will use to make requests to the Kubernetes API server. weblogic-operator loadBalancer The load balancer that is installed to provide load balancing for WebLogic clusters. Allowed values are:
- none – do not configure a load balancer
- traefik – configure the Traefik Ingress provider
- nginx – reserved for future use
- ohs – reserved for future use traefik loadBalancerWebPort The NodePort for the load balancer to accept user traffic. 30305 enableELKintegration Determines whether the ELK integration will be enabled. If set to true, then ElasticSearch, Logstash and Kibana will be installed, and Logstash will be configured to export the operator’s logs to ElasticSearch. false Decide which REST configuration to use

The operator provides three REST certificate options:

  • none will disable the REST server.
  • self-signed-cert will generate self-signed certificates.
  • custom-cert provides a mechanism to provide certificates that were created and signed by some other means.
Decide which optional features to enable

The operator provides some optional features that can be enabled in the configuration file.

Load Balancing

The operator can install the Traefik Ingress provider to provide load balancing for web applications running in WebLogic clusters. If enabled, an instance of Traefik and an Ingress will be created for each WebLogic cluster. Additional configuration is performed when creating the domain.

Note that the Technology Preview release provides only basic load balancing:

  • Only HTTP(S) is supported. Other protocols are not supported.
  • A root path rule is created for each cluster. Rules based on the DNS name, or on URL paths other than ‘/’, are not supported.
  • No non-default configuration of the load balancer is performed in this release. The default configuration gives round robin routing and WebLogic Server will provide cookie-based session affinity.

Note that Ingresses are not created for servers that are not part of a WebLogic cluster, including the Administration Server. Such servers are exposed externally using NodePort services.

Log integration with ELK

The operator can install the ELK stack and publish its logs into ELK. If enabled, ElasticSearch and Kibana will be installed in the default namespace, and a logstash pod will be created in the operator’s namespace. Logstash will be configured to publish the operator’s logs into Elasticsearch, and the log data will be available for visualization and analysis in Kibana.

To enable the ELK integration, set the enableELKintegration option to true.

Deploying the operator to a Kubernetes cluster

To deploy the operator, run the deployment script and give it the location of your inputs file:

./create-weblogic-operator.sh –i /path/to/create-operator-inputs.yaml What the script does

The script will carry out the following actions:

  • A set of Kubernetes YAML files will be created from the inputs provided.
  • A namespace will be created for the operator.
  • A service account will be created in that namespace.
  • If ELK integration was enabled, a persistent volume for ELK will be created.
  • A set of RBAC roles and bindings will be created.
  • The operator will be deployed.
  • If requested, the load balancer will be deployed.
  • If requested, ELK will be deployed and logstash will be configured for the operator’s logs.

The script will validate each action before it proceeds.

This will deploy the operator in your Kubernetes cluster.  Please refer to the documentation for next steps, including using the REST services, creating a WebLogic domain, starting a domain, and so on.

Philippine Nonprofits Amplify Social Impact with NetSuite

Oracle Press Releases - Wed, 2018-01-31 08:00
Press Release
Philippine Nonprofits Amplify Social Impact with NetSuite Zuellig Family Foundation, the Philippine Council for NGO Certification and Grupo Kalinangan, Inc. Improve Efficiency and Transparency with Oracle NetSuite Social Impact

SAN MATEO, Calif. and MAKATI CITY, Philippines—Jan 31, 2018

Oracle NetSuite, one of the world’s leading providers of cloud-based financials / ERPHRProfessional Services Automation (PSA) and omnichannel commerce software suites, today announced three nonprofit customers in the Philippines that have streamlined mission-critical processes while saving significant time and costs by moving to NetSuite. The Zuellig Family Foundation (ZFF), the Philippine Council for NGO Certification (PCNC) and Grupo Kalinangan, Inc. are among a growing number of Philippine nonprofits and social enterprises that have improved operational and financial efficiency, transparency for donors, regulatory reporting and capacity for informed, data-driven decision making since graduating from entry-level business applications to NetSuite. By eliminating labor-intensive manual work, nonprofits are freeing up resources to better focus on their social missions across the Philippines in such areas as healthcare, education, business development, disaster relief and alleviation of poverty.

Zuellig Family Foundation, PCNC and Grupo Kalinangan are supported by the Oracle NetSuite Social Impact group, which makes available free and discounted software licensing to qualified nonprofits and social enterprises. The program also includes Suite Pro Bono, wherein NetSuite employees volunteer their time and expertise to help nonprofits with training and customizations to make the most of NetSuite. More than 1,000 nonprofits and social enterprises globally use Oracle NetSuite Social Impact offerings, including Philippine organizations like Coca-Cola Foundation Philippines, American Chamber Foundation Philippines, Cartwheel Foundation, Virlanie Foundation, Microventures Foundation, BagoSphere and Theo & Philo. To learn more about Oracle NetSuite Social Impact, please visit www.netsuite.com/socialimpact.

ZFF Improves Healthcare Delivery with Leadership Training

Zuellig Family Foundation (www.zuelligfoundation.org) has provided local training to improve healthcare in nearly 650 rural municipalities across 32 provinces since its founding in 2008. Established and funded by the Zuellig family, whose patriarch, Frederick Zuellig, emigrated from Switzerland in 1901 to establish trading companies in the Philippines, ZFF focuses on training local leaders to address healthcare issues. More than 2,000 leaders have participated in ZFF’s health leadership and governance training programs, applying their insights to improve healthcare at the local level. Based in Parañaque City with 80 full-time employees, ZFF partners with other organizations such as USAID, UNICEF and MSD Merck for Mothers, with funding rising 35 percent since 2013 to $196.8 million USD in 2016. NetSuite helps ZFF better manage its financials, gaining speed and visibility and eliminating time-consuming manual work the nonprofit experienced with its previous application. ZFF estimates PHP1.65 million in annual savings and cost avoidance compared to an SAP solution it evaluated before selecting NetSuite, going live in 2015. With those savings in licensing, IT infrastructure, IT personnel and bookkeeper, ZFF is better able to focus resources on improving rural healthcare.

“NetSuite has simplified and facilitated our compliance with reporting requirements by supporting internal controls and providing accurate, real-time reporting with a complete set of audit trails,” said Wesley Villanueva, Manager. “We have greater accountability and transparency and can generate real-time reports that aid in decision-making.”

PCNC Drives Effectiveness, Accountability with NGO Certifications

The Philippine Council for NGO Certification (www.pcnc.com.ph), a nonprofit that certifies NGOs as meeting organizational standards for financial management and transparency, helps improve accountability and effectiveness of nonprofits involved in education, healthcare, poverty alleviation and other missions. Created in 1997 by a consortium of NGO networks, PCNC currently certifies approximately 450 NGOs with a “Seal of Good Housekeeping,” based on evaluations conducted by more than a hundred volunteer evaluators on six criteria including mission, goals and financial management. That gives donors confidence that funding will be used for its intended purpose. Based in Manila with eight employees, PCNC also helps match NGOs with communities in need, improving local outcomes across the nation. Since going live in 2016, NetSuite has been playing a critical role by giving PCNC new capabilities to track and segment NGOs by mission, regions and other characteristics, helping bring together NGOs and beneficiaries. PCNC also uses NetSuite for automated communications, such as notification emails to NGOs six months before certification expires, and intends to use NetSuite LightCMS for a more modern website. NetSuite technical support and Suite Pro Bono volunteers have been instrumental in helping PCNC make the most of its system.

“What used to take a day of gathering and aggregating data now takes minutes,” said Luis Morales, PCNC Executive Director. “We’re saving a lot of time with the ability to generate data very easily and that means less cost and less manual work. We didn’t realize what we were missing with NetSuite. NetSuite really knows how to work with nonprofits.”

Grupo Kalinangan Advocates Preservation of Filipino Heritage

Grupo Kalinangan, Inc. (www.grupokalinangan.org), an organization founded in 2015 in Makati City, develops and deploys IT tools, services and support systems for local government and grassroots communities for improved cultural heritage management in the Philippines. Bringing together advocates and practitioners from allied fields including social science, architecture and IT, GKI is building a data platform and map of Philippine history and culture, while implementing projects and activities aligned with understanding how to better preserve and promote Philippine culture and heritage through big data and analytics. A key focus area for GKI, with five board members and about 50 volunteers, is the use of data exchange, analytics and software such as Arches, an open-source GIS tool to collect and share data, heightening awareness of the importance of Filipino heritage. GKI, being an early adopter of technology, has always embraced modern applications to streamline its own operations. Replacing a free Wave Accounting application with NetSuite, which went live in 2016, NetSuite helps GKI save time and money in managing financials, preparing annual reports, and improving transparency for donors and in government filings. Despite Grupo Kalinangan’s initial concern that NetSuite would be too complicated, the system has proven to be easy to use, especially with the training provided by Suite Pro Bono volunteers over a six-week period. As a more cost-efficient organization, GKI can put full focus on heritage without needless overhead.

“We’ve definitely improved our processes with NetSuite and its capabilities,” said Karl Aguilar, Corporate Treasurer. “We can focus on the most important things for our organization without getting bogged down by problems and complicated financial processes. NetSuite does the work for us.”

Addressing Modern Challenges with Modern Cloud Technology

Nonprofits and social enterprises in the Philippines and elsewhere see transformational improvements by using NetSuite for functions such as accounting, donor and project management, online commerce, fund-raising and marketing. At the same time, NetSuite helps nonprofits address key pain points such as tight budgets, limited IT resources and increasing competition for funding. Benefits include:

  • Efficient financials and operations. Nonprofits eliminate error-prone, paper-based processes, dramatically reducing labor and cost and channeling savings into social impact programs.
  • Real-time visibility. Management can easily track revenue vs. expenses, fund-raising effectiveness and project status, enabling informed decisions on priority focus areas and complete tracking of results.
  • Greater transparency. Grant accounting at a line-item level helps nonprofits track how, when and where a grant is spent on any given project, helping to meet exacting donor requirements.
  • Regulatory reporting. Nonprofits speed up and simplify external audits as well as reporting to regulatory bodies, publicly documenting key financial metrics used by prospective donors to assess a nonprofit’s worthiness of funding.
  • Increased fund-raising effectiveness. Organizations gain a 360-degree view of their constituents and can utilize email marketing tools for personalized outreach and building long-term relationships.
Contact Info
Michael S. Robinson
Oracle NetSuite
781-974-9401
michael.s.robinson@oracle.com
About Oracle NetSuite Social Impact

Founded in 2006, the Oracle NetSuite Social Impact group is empowering nonprofits to use NetSuite to further their mission, regardless of their ability to pay. More than 1,000 nonprofits and social enterprises around the world are supported by NetSuite Social Impact, which makes available free and discounted software licensing to qualified organizations. The program also includes Suite Pro Bono, under which NetSuite employees provide their expertise to help nonprofits with training and customizations to make the most of the platform. To learn more about NetSuite Social Impact, please visit http://www.netsuite.com/socialimpact.

About Oracle NetSuite

Oracle NetSuite pioneered the Cloud Computing revolution in 1998, establishing the world’s first company dedicated to delivering business applications over the internet. Today, it provides a suite of cloud-based financials / Enterprise Resource Planning (ERP), HR and omnichannel commerce software that runs the business of companies in more than 100 countries.

For more information, please visit http://www.netsuite.com.

Follow Oracle NetSuite’s Cloud blog, Facebook page and @NetSuite Twitter handle for real-time updates.

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 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

Michael S. Robinson

  • 781-974-9401

Update with in-list running long

Tom Kyte - Wed, 2018-01-31 06:26
Hello, I am fairly new with ORacle SQL and am stuck at one point. I am trying to run the following update statement on a daily fact table, and it is running for ever. Is there a way to optimise the below sql, do i need to do any preprocess...
Categories: DBA Blogs

Oracle PLSQL Writing Excel File

Tom Kyte - Wed, 2018-01-31 06:26
Hi Tom, Thanks for being with us! I have a excel file with a template, what my requirement is to copy that template from that excel file to new excel file and also append some data into the same excel file with template. when I tried to write ...
Categories: DBA Blogs

Any scenairo where Optimizer will not use an active SQL profile

Tom Kyte - Wed, 2018-01-31 06:26
Hi team, If a SQL query has an active(ENABLED) profile, will there be any scenario when Optimizer will not use it? E.g. As I know the profile is auxiliary statistics on all objects being referenced by underlying SQL query. So if we gather stat...
Categories: DBA Blogs

Cannot enable table locks - ORA-00054: resource busy

Tom Kyte - Wed, 2018-01-31 06:26
Hello, I have one user who has disabled table locks on a table, and now we can't enable lock on this table : SQL> alter table USER.SOME_TABLE enable table lock; alter table USER.SOME_TABLE enable table lock * ORA-00054: resource busy and a...
Categories: DBA Blogs

TDE Column Enablement

Tom Kyte - Wed, 2018-01-31 06:26
Hi Oracle Masters, Two questions: 1) I read the below line in "Advanced Security Guide" for TDE: "If you enable TDE column encryption on a very large table, then you may need to increase the redo log size to accommodate the operation". Ho...
Categories: DBA Blogs

Column Creation Time

Tom Kyte - Wed, 2018-01-31 06:26
Hi Tom, I want to view the time at which a particular column is created/added in the table. USER_TAB_COLUMNS does not contain the column creation/modification timestamp. USER_OBJECTS has LAST_DDL_TIME which is at the Table level. I need Colum...
Categories: DBA Blogs

how to specify block size when installing database silently with dbca

Tom Kyte - Wed, 2018-01-31 06:26
Hi Tom, I use the script below to create database, but I want to know how to specify db block size.I didnot find a parameter in dbca.rsp corresonding to this. And block size is hard to be changed once the database is created. So can you please tell ...
Categories: DBA Blogs

ORA-24263: Certificate of the remote server does not match the target address

Tom Kyte - Wed, 2018-01-31 06:26
Dear Ask TOM Team, We have upgrade our DEV Environment to the Oracle 12.2 Release and we are about to finish our upgrade tests. Now one test that we thought should be a simple one is the SEND Mail over the UTL_SMTP package. In the release 12.1 we...
Categories: DBA Blogs

GDPR - backups and database design

Tom Kyte - Wed, 2018-01-31 06:26
Really surprised to see that nobody had any queries posted on the impacts on backup strategies related to GDPR. There is a clause which says right to erase personal data with respect to the GDPR and we are thinking on the possible ways the backup ...
Categories: DBA Blogs

Oracle API Platform Cloud Service: Design-First approach and using Oracle Apiary

Amis Blog - Wed, 2018-01-31 01:10

At the Oracle Partner PaaS Summer Camps VII 2017 in Lisbon last year, at the end of august, I attended the API Cloud Platform Service & Integration Cloud Service bootcamp.

In a series of article’s I will give a high level overview of what you can do with Oracle API Platform Cloud Service. The version used of Oracle API Platform CS was Release 17.3.3 — August 2017.

See https://docs.oracle.com/en/cloud/paas/api-platform-cloud/whats-new/index.html to learn about the new and changed features of Oracle API Platform CS in the latest release.

In preparation of creating an API Blueprint document, I took a closer look at Oracle REST Data Services and used the RESTful Services feature in Oracle SQL Developer, to generate example JSON payload’s based on some tables in the “HR’ schema. For more information about this, see my article “Oracle REST Data Services (ORDS)”.
[https://technology.amis.nl/2018/01/22/oracle-rest-data-services-ords/]

In this first article in the series about Oracle API Platform CS, the focus will be on the Design-First approach and using Oracle Apiary.

Short overview of Oracle API Platform Cloud Service

Oracle API Platform Cloud Service enables companies to thrive in the digital economy by comprehensively managing the full API lifecycle from design and standardization to documenting, publishing, testing and managing APIs. These tools provide API developers, managers, and users an end-to-end platform for designing, prototyping. Through the platform, users gain the agility needed to support changing business demands and opportunities, while having clear visibility into who is using APIs for better control, security and monetization of digital assets.
[https://cloud.oracle.com/en_US/api-platform/datasheets]

Architecture

Management Portal:
APIs are managed, secured, and published using the Management Portal. The Management Portal is hosted on the Oracle Cloud, managed by Oracle, and users granted API Manager privileges have access.

Gateways:
API Gateways are the runtime components that enforce all policies, but also help in collecting data for analytics. The gateways can be deployed anywhere – on premise, on Oracle Cloud or to any third party cloud providers.

Developer Portal:
After an API is published, Application Developers use the Developer Portal to discover, register, and consume APIs. The Developer Portal can be customized to run either on the Oracle Cloud or directly in the customer environment on premises.
[https://cloud.oracle.com/opc/paas/datasheets/APIPCSDataSheet_Oct2017.pdf]

More about this follows in another article in the series.

Design-First approach

API-First Development is a fundamental paradigm shift in the process of API design where APIs are built before applications and mirror the goals and objectives of the company. API-First Development is also commonly referred to as Design-First Development.

Before a developer builds a web, mobile, or other application, they develop the API first, then start defining the channels that the API will be available on. Developers kick off the development process discussing the API with their potential customers, generate use cases, and mock up the API before even developing the application.
[https://cloud.oracle.com/opc/paas/datasheets/Apiary-Datasheet-Oracle.pdf]

API-First Development” experience is one of the Key Design Principles of Oracle API Platform Cloud Service. The Developer Portal, tightly linked with Apiary, allows application developers to search for, learn about, test and register to use APIs, and then track their own usage.
[https://cloud.oracle.com/opc/paas/datasheets/APIPCSDataSheet_Oct2017.pdf]

Oracle Apiary provides you with the ability to design APIs using either API Blueprint or Swagger 2.0. From these description files, Oracle Apiary generates interactive documentation and a console for making calls to the APIs from the UI.
[https://docs.oracle.com/en/cloud/paas/api-platform-cloud/apfad/oracle-apiary-integration.html]

API Blueprint

API Blueprint is a documentation-oriented web API description language. The API Blueprint is essentially a set of semantic assumptions laid on top of the Markdown syntax used to describe a web API.

An API Blueprint document – a blueprint – is a plain text Markdown document describing a Web API in whole or in part. The document is structured into logical sections. Each section has its distinctive meaning, content and position in the document.
[https://github.com/apiaryio/api-blueprint/blob/master/API%20Blueprint%20Specification.md]

For the Full Language Specification of API Blueprint see for example:
https://github.com/apiaryio/api-blueprint/blob/master/API%20Blueprint%20Specification.md

For an API Blueprint tutorial see for example:
https://docs.oracle.com/cloud/apiary/api_101/api_blueprint_tutorial/index.html

The recommended file extension for API Blueprint is .apib

Oracle Apiary

Apiary provides the world’s first platform, API Flow, specifically designed to help companies accelerate and control the design, development, and documentation of their APIs and microservices. This allows its users to create products and services that customers, business partners, and even machines love to use.

Apiary API Flow is an open platform supporting both API Blueprint and OpenAPI (Swagger). The platform provides tools to developers for every step of an API-First Development Lifecycle and delivers several significant benefits to a team of developers.
[https://cloud.oracle.com/opc/paas/datasheets/Apiary-Datasheet-Oracle.pdf]

In this article I will discuss some of the functionality of Oracle Apiary, but not all. Please see the available documentation for more details.

Go to https://apiary.io/ to start Oracle Apiary.

You can Sign Up for free with a GitHub, Twitter or email account. Once you have done that, you can Sign In to start working with Oracle Apiary.

Via menu | Create New API Project, the New API wizard is started, where you can choose between creating a Personal API or Team API and in the field “New API name” you can fill in your API name, for example: HumanResourceService.

Then click on button “Create API”.

Apiary Editor

The “HumanResourceService” API automatically shows up in the Apiary Editor. The header item “Editor” is high-lighted.

The Apiary Editor, contains 3 panes, with an API Blueprint tutorial (a polls service, which allows consumers to view polls and vote in them) already in place. For more information about that tutorial, see: https://help.apiary.io/api_101/api_blueprint_tutorial/

The Apiary Editor is the foundation of your API design. Apiary Editor supports API Blueprint and Swagger API Description languages.
[https://help.apiary.io/tools/apiary-editor/]

In the left pane (Editor), an API Blueprint document, structured into logical sections, is shown.
The center pane (Documentation preview) shows what your API document will look like when rendered as documentation. It also lets you to try out your API as you build. The documentation preview is dynamically updated as you type in the Editor.
In the right pane the code examples/console is shown.

You can modify the API Blueprint tutorial document to fulfill your needs, or use some other text editor and copy and paste it into the Editor.

As described in my previous article, mentioned above, in the table below I summarized the requests that I created:

Request name Method Request URL GetAllEmployeesRequest GET http://localhost:9090/ords/hr/demo1/employees/ CreateEmployeeRequest POST http://localhost:9090/ords/hr/demo1/employees/ GetEmployeeRequest GET http://localhost:9090/ords/hr/demo1/employees/100 UpdateEmployeeRequest PUT http://localhost:9090/ords/hr/demo1/employees/219 GetDepartmentRequest GET http://localhost:9090/ords/hr/demo1/departments/30 GetDepartmentEmployeeRequest GET http://localhost:9090/ords/hr/demo1/departments/30/employees/119

The first step for creating an API Blueprint document is to specify the Metadata, API name and description.

Metadata section:
The API Blueprint document starts with a Metadata section, which consists of Key-Value pairs. Each Key is separated from its Value by a colon (:). One pair per line. The FORMAT keyword is required and denotes that document is API Blueprint.

FORMAT: 1A

API name & overview section:
In the API name & overview section, the API name is defined by the first Markdown header in the API Blueprint document.

# HumanResourceService

Human Resource Service is an API to manage Human Resources.

Resource section:
An API consists of resources specified by their URIs.

In line with the API Blueprint tutorial (a polls service, which allows consumers to view polls and vote in them), I used a Resource section with as format:
## <identifier> [<URI template>]

For example:
## Employees Collection [/employees]

Action section:
You should specify each action you may make on a resource. An action is specified with a sub-heading with the name of the action followed by the HTTP method.

Within the Resource section I created multiple Action sections, with as format:
### <identifier> [<HTTP request method> <URI template>]

For example:
### Get all employees [GET /employees]

As you can see, in my API, some Action sections contain an URI parameters section and/or a Request section and a Response section.

URI parameters section:
The URI parameters section describes any URI parameters specific to the action, with as format:
+ <parameter name>: `<example value>` (<type> | enum[<type>], required | optional) – <description>

For example:
+ id: `220` (number, required) – Id of an employee.

Request section:
For the Request sections the following format was used:
+ Request <identifier> (<Media Type>)

For example:
+ Request (application/json)

        {“LAST_NAME”:”TESTUPDATE”, “JOB_ID”:”SA_REP”, “SALARY”:8000, “DEPARTMENT_ID”:80}

Remark:
Specifying the media type generates a HTTP Content-Type header.

Response section:
For the Response sections the following format was used:
+ Response <HTTP status code> (<Media Type>)

For example:
+ Response 200 (application/json)

{
“employee_id”: 220,
“first_name”: “TESTFIRST”,
“last_name”: “TESTUPDATE”,
“email”: “TESTMAIL”,
“phone_number”: null,
“hire_date”: “2015-06-25T04:00:00Z”,
“job_id”: “SA_REP”,
“salary”: 8000,
“commission_pct”: null,
“manager_id”: 103,
“department_id”: 80
}

Remark:
Specifying the media type generates a HTTP Content-Type header.

My HumanResourceService.apib therefor looks like:

FORMAT: 1A

# HumanResourceService

Human Resource Service is an API to manage Human Resources.

## Employees Collection [/employees]

### Get all employees [GET /employees]

Get all employees.

+ Response 200 (application/json)

        {
            "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": [
                        {
                            "rel": "self",
                            "href": "http://localhost:9090/ords/hr/employees/100"
                        }
                    ]
                },
                {
                    "employee_id": 101,
                    "first_name": "Neena",
                    "last_name": "Kochhar",
                    "email": "NKOCHHAR",
                    "phone_number": "515.123.4568",
                    "hire_date": "1989-09-21T04:00:00Z",
                    "job_id": "AD_VP",
                    "salary": 17000,
                    "commission_pct": null,
                    "manager_id": 100,
                    "department_id": 90,
                    "links": [
                        {
                            "rel": "self",
                            "href": "http://localhost:9090/ords/hr/employees/101"
                        }
                    ]
                },
                {
                    "employee_id": 102,
                    "first_name": "Lex",
                    "last_name": "De Haan",
                    "email": "LDEHAAN",
                    "phone_number": "515.123.4569",
                    "hire_date": "1993-01-13T05:00:00Z",
                    "job_id": "AD_VP",
                    "salary": 17000,
                    "commission_pct": null,
                    "manager_id": 100,
                    "department_id": 90,
                    "links": [
                        {
                            "rel": "self",
                            "href": "http://localhost:9090/ords/hr/employees/102"
                        }
                    ]
                },
                {
                    "employee_id": 103,
                    "first_name": "Alexander",
                    "last_name": "Hunold",
                    "email": "AHUNOLD",
                    "phone_number": "590.423.4567",
                    "hire_date": "1990-01-03T05:00:00Z",
                    "job_id": "IT_PROG",
                    "salary": 9000,
                    "commission_pct": null,
                    "manager_id": 102,
                    "department_id": 60,
                    "links": [
                        {
                            "rel": "self",
                            "href": "http://localhost:9090/ords/hr/employees/103"
                        }
                    ]
                },
                {
                    "employee_id": 104,
                    "first_name": "Bruce",
                    "last_name": "Ernst",
                    "email": "BERNST",
                    "phone_number": "590.423.4568",
                    "hire_date": "1991-05-21T04:00:00Z",
                    "job_id": "IT_PROG",
                    "salary": 6000,
                    "commission_pct": null,
                    "manager_id": 103,
                    "department_id": 60,
                    "links": [
                        {
                            "rel": "self",
                            "href": "http://localhost:9090/ords/hr/employees/104"
                        }
                    ]
                },
                {
                    "employee_id": 105,
                    "first_name": "David",
                    "last_name": "Austin",
                    "email": "DAUSTIN",
                    "phone_number": "590.423.4569",
                    "hire_date": "1997-06-25T04:00:00Z",
                    "job_id": "IT_PROG",
                    "salary": 4800,
                    "commission_pct": null,
                    "manager_id": 103,
                    "department_id": 60,
                    "links": [
                        {
                            "rel": "self",
                            "href": "http://localhost:9090/ords/hr/employees/105"
                        }
                    ]
                },
                {
                    "employee_id": 106,
                    "first_name": "Valli",
                    "last_name": "Pataballa",
                    "email": "VPATABAL",
                    "phone_number": "590.423.4560",
                    "hire_date": "1998-02-05T05:00:00Z",
                    "job_id": "IT_PROG",
                    "salary": 4800,
                    "commission_pct": null,
                    "manager_id": 103,
                    "department_id": 60,
                    "links": [
                        {
                            "rel": "self",
                            "href": "http://localhost:9090/ords/hr/employees/106"
                        }
                    ]
                },
                {
                    "employee_id": 107,
                    "first_name": "Diana",
                    "last_name": "Lorentz",
                    "email": "DLORENTZ",
                    "phone_number": "590.423.5567",
                    "hire_date": "1999-02-07T05:00:00Z",
                    "job_id": "IT_PROG",
                    "salary": 4200,
                    "commission_pct": null,
                    "manager_id": 103,
                    "department_id": 60,
                    "links": [
                        {
                            "rel": "self",
                            "href": "http://localhost:9090/ords/hr/employees/107"
                        }
                    ]
                },
                {
                    "employee_id": 108,
                    "first_name": "Nancy",
                    "last_name": "Greenberg",
                    "email": "NGREENBE",
                    "phone_number": "515.124.4569",
                    "hire_date": "1994-08-17T04:00:00Z",
                    "job_id": "FI_MGR",
                    "salary": 12000,
                    "commission_pct": null,
                    "manager_id": 101,
                    "department_id": 100,
                    "links": [
                        {
                            "rel": "self",
                            "href": "http://localhost:9090/ords/hr/employees/108"
                        }
                    ]
                },
                {
                    "employee_id": 109,
                    "first_name": "Daniel",
                    "last_name": "Faviet",
                    "email": "DFAVIET",
                    "phone_number": "515.124.4169",
                    "hire_date": "1994-08-16T04:00:00Z",
                    "job_id": "FI_ACCOUNT",
                    "salary": 9000,
                    "commission_pct": null,
                    "manager_id": 108,
                    "department_id": 100,
                    "links": [
                        {
                            "rel": "self",
                            "href": "http://localhost:9090/ords/hr/employees/109"
                        }
                    ]
                },
                {
                    "employee_id": 110,
                    "first_name": "John",
                    "last_name": "Chen",
                    "email": "JCHEN",
                    "phone_number": "515.124.4269",
                    "hire_date": "1997-09-28T04:00:00Z",
                    "job_id": "FI_ACCOUNT",
                    "salary": 8200,
                    "commission_pct": null,
                    "manager_id": 108,
                    "department_id": 100,
                    "links": [
                        {
                            "rel": "self",
                            "href": "http://localhost:9090/ords/hr/employees/110"
                        }
                    ]
                },
                {
                    "employee_id": 111,
                    "first_name": "Ismael",
                    "last_name": "Sciarra",
                    "email": "ISCIARRA",
                    "phone_number": "515.124.4369",
                    "hire_date": "1997-09-30T04:00:00Z",
                    "job_id": "FI_ACCOUNT",
                    "salary": 7700,
                    "commission_pct": null,
                    "manager_id": 108,
                    "department_id": 100,
                    "links": [
                        {
                            "rel": "self",
                            "href": "http://localhost:9090/ords/hr/employees/111"
                        }
                    ]
                },
                {
                    "employee_id": 112,
                    "first_name": "Jose Manuel",
                    "last_name": "Urman",
                    "email": "JMURMAN",
                    "phone_number": "515.124.4469",
                    "hire_date": "1998-03-07T05:00:00Z",
                    "job_id": "FI_ACCOUNT",
                    "salary": 7800,
                    "commission_pct": null,
                    "manager_id": 108,
                    "department_id": 100,
                    "links": [
                        {
                            "rel": "self",
                            "href": "http://localhost:9090/ords/hr/employees/112"
                        }
                    ]
                },
                {
                    "employee_id": 113,
                    "first_name": "Luis",
                    "last_name": "Popp",
                    "email": "LPOPP",
                    "phone_number": "515.124.4567",
                    "hire_date": "1999-12-07T05:00:00Z",
                    "job_id": "FI_ACCOUNT",
                    "salary": 6900,
                    "commission_pct": null,
                    "manager_id": 108,
                    "department_id": 100,
                    "links": [
                        {
                            "rel": "self",
                            "href": "http://localhost:9090/ords/hr/employees/113"
                        }
                    ]
                },
                {
                    "employee_id": 114,
                    "first_name": "Den",
                    "last_name": "Raphaely",
                    "email": "DRAPHEAL",
                    "phone_number": "515.127.4561",
                    "hire_date": "1994-12-07T05:00:00Z",
                    "job_id": "PU_MAN",
                    "salary": 11000,
                    "commission_pct": null,
                    "manager_id": 100,
                    "department_id": 30,
                    "links": [
                        {
                            "rel": "self",
                            "href": "http://localhost:9090/ords/hr/employees/114"
                        }
                    ]
                },
                {
                    "employee_id": 115,
                    "first_name": "Alexander",
                    "last_name": "Khoo",
                    "email": "AKHOO",
                    "phone_number": "515.127.4562",
                    "hire_date": "1995-05-18T04:00:00Z",
                    "job_id": "PU_CLERK",
                    "salary": 3100,
                    "commission_pct": null,
                    "manager_id": 114,
                    "department_id": 30,
                    "links": [
                        {
                            "rel": "self",
                            "href": "http://localhost:9090/ords/hr/employees/115"
                        }
                    ]
                },
                {
                    "employee_id": 116,
                    "first_name": "Shelli",
                    "last_name": "Baida",
                    "email": "SBAIDA",
                    "phone_number": "515.127.4563",
                    "hire_date": "1997-12-24T05:00:00Z",
                    "job_id": "PU_CLERK",
                    "salary": 2900,
                    "commission_pct": null,
                    "manager_id": 114,
                    "department_id": 30,
                    "links": [
                        {
                            "rel": "self",
                            "href": "http://localhost:9090/ords/hr/employees/116"
                        }
                    ]
                },
                {
                    "employee_id": 117,
                    "first_name": "Sigal",
                    "last_name": "Tobias",
                    "email": "STOBIAS",
                    "phone_number": "515.127.4564",
                    "hire_date": "1997-07-24T04:00:00Z",
                    "job_id": "PU_CLERK",
                    "salary": 2800,
                    "commission_pct": null,
                    "manager_id": 114,
                    "department_id": 30,
                    "links": [
                        {
                            "rel": "self",
                            "href": "http://localhost:9090/ords/hr/employees/117"
                        }
                    ]
                },
                {
                    "employee_id": 118,
                    "first_name": "Guy",
                    "last_name": "Himuro",
                    "email": "GHIMURO",
                    "phone_number": "515.127.4565",
                    "hire_date": "1998-11-15T05:00:00Z",
                    "job_id": "PU_CLERK",
                    "salary": 2600,
                    "commission_pct": null,
                    "manager_id": 114,
                    "department_id": 30,
                    "links": [
                        {
                            "rel": "self",
                            "href": "http://localhost:9090/ords/hr/employees/118"
                        }
                    ]
                },
                {
                    "employee_id": 119,
                    "first_name": "Karen",
                    "last_name": "Colmenares",
                    "email": "KCOLMENA",
                    "phone_number": "515.127.4566",
                    "hire_date": "1999-08-10T04:00:00Z",
                    "job_id": "PU_CLERK",
                    "salary": 2500,
                    "commission_pct": null,
                    "manager_id": 114,
                    "department_id": 30,
                    "links": [
                        {
                            "rel": "self",
                            "href": "http://localhost:9090/ords/hr/employees/119"
                        }
                    ]
                },
                {
                    "employee_id": 120,
                    "first_name": "Matthew",
                    "last_name": "Weiss",
                    "email": "MWEISS",
                    "phone_number": "650.123.1234",
                    "hire_date": "1996-07-18T04:00:00Z",
                    "job_id": "ST_MAN",
                    "salary": 8000,
                    "commission_pct": null,
                    "manager_id": 100,
                    "department_id": 50,
                    "links": [
                        {
                            "rel": "self",
                            "href": "http://localhost:9090/ords/hr/employees/120"
                        }
                    ]
                },
                {
                    "employee_id": 121,
                    "first_name": "Adam",
                    "last_name": "Fripp",
                    "email": "AFRIPP",
                    "phone_number": "650.123.2234",
                    "hire_date": "1997-04-10T04:00:00Z",
                    "job_id": "ST_MAN",
                    "salary": 8200,
                    "commission_pct": null,
                    "manager_id": 100,
                    "department_id": 50,
                    "links": [
                        {
                            "rel": "self",
                            "href": "http://localhost:9090/ords/hr/employees/121"
                        }
                    ]
                },
                {
                    "employee_id": 122,
                    "first_name": "Payam",
                    "last_name": "Kaufling",
                    "email": "PKAUFLIN",
                    "phone_number": "650.123.3234",
                    "hire_date": "1995-05-01T04:00:00Z",
                    "job_id": "ST_MAN",
                    "salary": 7900,
                    "commission_pct": null,
                    "manager_id": 100,
                    "department_id": 50,
                    "links": [
                        {
                            "rel": "self",
                            "href": "http://localhost:9090/ords/hr/employees/122"
                        }
                    ]
                },
                {
                    "employee_id": 123,
                    "first_name": "Shanta",
                    "last_name": "Vollman",
                    "email": "SVOLLMAN",
                    "phone_number": "650.123.4234",
                    "hire_date": "1997-10-10T04:00:00Z",
                    "job_id": "ST_MAN",
                    "salary": 6500,
                    "commission_pct": null,
                    "manager_id": 100,
                    "department_id": 50,
                    "links": [
                        {
                            "rel": "self",
                            "href": "http://localhost:9090/ords/hr/employees/123"
                        }
                    ]
                },
                {
                    "employee_id": 124,
                    "first_name": "Kevin",
                    "last_name": "Mourgos",
                    "email": "KMOURGOS",
                    "phone_number": "650.123.5234",
                    "hire_date": "1999-11-16T05:00:00Z",
                    "job_id": "ST_MAN",
                    "salary": 5800,
                    "commission_pct": null,
                    "manager_id": 100,
                    "department_id": 50,
                    "links": [
                        {
                            "rel": "self",
                            "href": "http://localhost:9090/ords/hr/employees/124"
                        }
                    ]
                }
            ],
            "hasMore": true,
            "limit": 25,
            "offset": 0,
            "count": 25,
            "links": [
                {
                    "rel": "self",
                    "href": "http://localhost:9090/ords/hr/employees/"
                },
                {
                    "rel": "edit",
                    "href": "http://localhost:9090/ords/hr/employees/"
                },
                {
                    "rel": "describedby",
                    "href": "http://localhost:9090/ords/hr/metadata-catalog/employees/"
                },
                {
                    "rel": "first",
                    "href": "http://localhost:9090/ords/hr/employees/"
                },
                {
                    "rel": "next",
                    "href": "http://localhost:9090/ords/hr/employees/?offset=25"
                }
            ]
        }

### Get an employee [GET /employees/{id}]

Get a particular employee by providing an identifier.

+ Response 200 (application/json)

        {
            "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": [
                {
                    "rel": "self",
                    "href": "http://localhost:9090/ords/hr/employees/100"
                },
                {
                    "rel": "edit",
                    "href": "http://localhost:9090/ords/hr/employees/100"
                },
                {
                    "rel": "describedby",
                    "href": "http://localhost:9090/ords/hr/metadata-catalog/employees/item"
                },
                {
                    "rel": "collection",
                    "href": "http://localhost:9090/ords/hr/employees/"
                }
            ]
        }

### Create an employee [POST /employees]

Create an employee, by using post with the complete payload

+ Request (application/json)

        {"LAST_NAME":"TESTINSERT", "FIRST_NAME":"TESTFIRST", "EMAIL":"TESTMAIL", "HIRE_DATE":"25-JUN-15", "JOB_ID":"IT_PROG", "SALARY":6000, "MANAGER_ID":103, "DEPARTMENT_ID":60}

+ Response 201 (application/json)

        {
            "employee_id": 220,
            "first_name": "TESTFIRST",
            "last_name": "TESTINSERT",
            "email": "TESTMAIL",
            "phone_number": null,
            "hire_date": "2015-06-25T04:00:00Z",
            "job_id": "IT_PROG",
            "salary": 6000,
            "commission_pct": null,
            "manager_id": 103,
            "department_id": 60
        }

### Update an employee [PUT /employees/{id}]

Update an employee, by using put with the a payload containing: last_name, job_id, salary and department_id.

+ Parameters
    + id: `220` (number, required) - Id of an employee.

+ Request (application/json)

        {"LAST_NAME":"TESTUPDATE", "JOB_ID":"SA_REP", "SALARY":8000, "DEPARTMENT_ID":80}

+ Response 200 (application/json)

        {
            "employee_id": 220,
            "first_name": "TESTFIRST",
            "last_name": "TESTUPDATE",
            "email": "TESTMAIL",
            "phone_number": null,
            "hire_date": "2015-06-25T04:00:00Z",
            "job_id": "SA_REP",
            "salary": 8000,
            "commission_pct": null,
            "manager_id": 103,
            "department_id": 80
        }

## Departments Collection [/departments]

### Get a department [GET /department/{id}]

Get a particular department by providing an identifier.

+ Parameters
    + id: `30` (number, required) - Id of a department.

+ Response 200 (application/json)

        {
            "department_id": 30,
            "department_name": "Purchasing",
            "manager_id": 114,
            "location_id": 1700
        }

### Get a department and employee [GET /departments/{department_id}/employees/{employee_id}]

Get a particular department by providing a department identifier and a particular employee within that department by providing an employee identifier.

+ Parameters
    + department_id: `30` (number, required) - Id of a department.
    + employee_id: `119` (number, required) - Id of an employee.

+ Response 200 (application/json)

        {
            "department_id": 30,
            "department_name": "Purchasing",
            "employee_id": 119,
            "first_name": "Karen",
            "last_name": "Colmenares"
        }
Apiary Editor, instant feedback

The Apiary Editor gives you instant feedback on any warnings or errors in your document as you type. The feedback will include line numbers and explanations for the warnings and errors, which will take you to the corresponding line in the editor when clicked.
[https://help.apiary.io/tools/apiary-editor/]

For example:

The semantic issues shown above where solved by, changing the URI template to:

### Get a department and employee [GET /departments/{department_id}/employees/{employee_id}]

After changing the content of the API Blueprint tutorial (a polls service, which allows consumers to view polls and vote in them) in order to describe the functionality of the HumanResourceService, the Apiary Editor looks like:

Mock Server

The Mock Server allows you to try out your API as you design it, giving immediate feedback along the way in how it may be used.

The Mock Server accomplishes this by listening for requests as you’ve defined them in your blueprint. When a request is received to your Mock Server for a URL you’ve defined, the corresponding response for that request will be returned.
[https://help.apiary.io/tools/mock-server/]

The Mock Server for the “HumanResourceService” API is listening at:
http://private-b4874b1-humanresourceservice.apiary-mock.com

You will have your own private URL for the Mock Server. This is to ensure that other users do not see the traffic you’re sending to the server.
[https://help.apiary.io/tools/mock-server/]

Interacting with the Mock Server can be done:

  • Directly
  • By using code examples
  • By using the console

Interacting with the Mock Server directly
This URL may be used to interact directly with the server. You can make requests with applications like curl or Paw to that URL and will get responses defined in the API Description.
[https://help.apiary.io/tools/mock-server/]

Interacting with the Mock Server by using code examples
Apiary provides code examples that you may use to interact with the Mock Server. You can get to these examples by clicking on any action in the documentation.
[https://help.apiary.io/tools/mock-server/]

Interacting with the Mock Server by using the console
The console in the documentation is where you can send requests to the Mock Server directly from the documentation (along with the Debugging Proxy and your production server). You can get to this by clicking on an action in the documentation, then clicking “Switch to Console” in the machine column.
[https://help.apiary.io/tools/mock-server/]

Interactive Documentation

When you don’t need to see the left pane (Editor) anymore, you can switch in the header to “Documentation”.

The interactive documentation contains two main columns: the human and machine columns. These two columns provide the separation that is important for reading the documentation and actually using tooling to interact with it.
[https://help.apiary.io/tools/interactive-documentation/]

After clicking on an action (for example: “Get all employees”) in the documentation (human column) the right pane (machine column) shows code examples.

Default the “Raw” type is selected, but there is a drop down that has a list of the available languages for the code examples.

When choosing for example “Java” as language for the code example, the following is shown:

// Maven : Add these dependecies to your pom.xml (java6+)
// <dependency>
//     <groupId>org.glassfish.jersey.core</groupId>
//     <artifactId>jersey-client</artifactId>
//     <version>2.8</version>
// </dependency>
// <dependency>
//     <groupId>org.glassfish.jersey.media</groupId>
//     <artifactId>jersey-media-json-jackson</artifactId>
//     <version>2.8</version>
// </dependency>

import javax.ws.rs.client.Client;
import javax.ws.rs.client.ClientBuilder;
import javax.ws.rs.client.Entity;
import javax.ws.rs.core.Response;
import javax.ws.rs.core.MediaType;

Client client = ClientBuilder.newClient();
Response response = client.target("https://private-b4874b1-humanresourceservice.apiary-mock.com/employees")
  .request(MediaType.TEXT_PLAIN_TYPE)
  .get();

System.out.println("status: " + response.getStatus());
System.out.println("headers: " + response.getHeaders());
System.out.println("body:" + response.readEntity(String.class));

This “Java” code example can be used to interact with the Mock Server.

When choosing for example “Python” as language for the code example, the following is shown:

from urllib2 import Request, urlopen

request = Request('https://private-b4874b1-humanresourceservice.apiary-mock.com/employees')

response_body = urlopen(request).read()
print response_body

This “Python” code example can be used to interact with the Mock Server.

After clicking on an action (for example: “Get all employees”) in the documentation (human column) and then in the right pane (machine column) clicking on button “Switch to Console” (or on the button “Try” in the code example), the Console is shown.

For the action “Get all employees”, in line with the specifications, the URI Parameters, Headers and Body parts are empty.

When you click on the button “Call Resource”, the response is shown:

After clicking on the action “Create an employee” in the documentation (human column) and then in the right pane (machine column) clicking on button “Switch to Console”, the following is shown.

For the action “Create an employee”, in line with the specifications, the Headers part is filled.

For the action “Create an employee”, in line with the specifications, the Body part is filled.

After clicking on the action “Get a department and employee” in the documentation (human column) and then in the right pane (machine column) clicking on button “Switch to Console”, the following is shown.

For the action “Get a department and employee”, in line with the specifications, the URI Parameters part is filled.

API Inspector

Each request and response from the Mock Server is logged in the API Inspector, which can be found by clicking “Inspector” in the Apiary header. There you will see each request received, each response given, and any validation errors that were found.
[https://help.apiary.io/tools/api-inspector/]

For example:

For each request (by clicking on it) more details are available:

API Test

When you click on the header item “Test”, the following is shown.

For more information about testing your API , please see the available documentation.

Summary

In this first article in the series about Oracle API Platform Cloud Service, the focus is on the Design-First approach and using Oracle Apiary.

“API-First Development” experience (commonly referred to as Design-First Development) is one of the Key Design Principles of Oracle API Platform Cloud Service. Developers kick off the development process discussing the API with their potential customers, generate use cases, and mock up the API before even developing the application.

The Developer Portal of API Platform CS is tightly linked with Oracle Apiary.

Oracle Apiary provides you with the ability to design APIs using either API Blueprint or Swagger 2.0. From these description files, Oracle Apiary generates interactive documentation and a console for making calls to the APIs from the UI.

This article shows you how, with the help of Oracle Apiary, a “HumanResourceService” API is created, based on API Blueprint and example JSON payload’s (based on some tables in the “HR’ schema).

The post Oracle API Platform Cloud Service: Design-First approach and using Oracle Apiary appeared first on AMIS Oracle and Java Blog.

Goldengate Tracing Network Ports

Michael Dinh - Tue, 2018-01-30 22:30
--- Find mgr process
$ ps -ef|grep ./mgr
ggs  11823     1  0  2017 ?        00:29:13 ./mgr PARAMFILE /u01/app/ggs/dirprm/mgr.prm REPORTFILE /u01/app/ggs/dirrpt/MGR.rpt PROCESSID MGR
ggs  45054 30127  0 14:15 pts/0    00:00:00 grep --color=auto ./mgr

--- Find extract process
$ ps -ef|grep ./extract
ggs  17604 30127  0 14:15 pts/0    00:00:00 grep --color=auto ./extract
ggs  44306 11823  0 03:33 ?        00:01:28 /u01/app/ggs/extract PARAMFILE /u01/app/ggs/dirprm/e_hawk.prm REPORTFILE /u01/app/ggs/dirrpt/e_hawk.rpt PROCESSID e_hawk
ggs  44354 11823  0 03:33 ?        00:00:48 /u01/app/ggs/extract PARAMFILE /u01/app/ggs/dirprm/p_hawk.prm REPORTFILE /u01/app/ggs/dirrpt/p_hawk.rpt PROCESSID p_hawk

--- Find mgr port
$ lsof -i -P |grep 11823
mgr     11823 ggs    6u  IPv4 3492119103      0t0  TCP *:7809 (LISTEN)

--- Find extract port
$ lsof -i -P |grep 44306
extract 44306 ggs    6u  IPv4 2827659844      0t0  TCP *:7840 (LISTEN)

$ lsof -i -P |grep 44354
extract 44354 ggs    6u  IPv4 2827649786      0t0  TCP *:7841 (LISTEN)
extract 44354 ggs   10u  IPv4 2827610899      0t0  TCP hawk.local:21252->eagle.local:7822 (ESTABLISHED)

--- Use Goldengate to find the same info. However, it does only provides local port.
$ ./ggsci 

Oracle GoldenGate Command Interpreter for Oracle
Version 12.3.0.1.0 OGGCORE_12.3.0.1.0_PLATFORMS_170721.0154_FBO
Linux, x64, 64bit (optimized), Oracle 12c on Jul 21 2017 23:31:13
Operating system character set identified as UTF-8.

Copyright (C) 1995, 2017, Oracle and/or its affiliates. All rights reserved.

GGSCI> send manager childstatus debug

Sending CHILDSTATUS request to MANAGER ...

Child Process Status - 2 Entries

ID      Group   Process Retry Retry Time          Start Time          Port
---- -------- --------- ----- ------------------- ------------------- ----
   0   e_hawk     44306     0 None                2017/11/20 19:49:14 7840
   1   p_hawk     44354     0 None                2017/11/20 19:55:43 7841

GGSCI>

Oracle Solaris 11.4 Beta publicly available on Oracle Technology Network (OTN)

Wim Coekaerts - Tue, 2018-01-30 13:49

Oracle Solaris 11.4 Beta is downloadable from OTN as of right now.  This is a very exciting milestone. Go and download it and play with it!

 

For more information see:

https://blogs.oracle.com/solaris/oracle-solaris-114-open-beta-released

http://www.oracle.com/technetwork/server-storage/solaris11/114beta/solaris114beta-4257760.html

https://docs.oracle.com/cd/E37838_01/

Open database read only

Tom Kyte - Tue, 2018-01-30 12:06
What is a command to change DB open mode from read write to read only without restarting the instance
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator