Tuesday, 12 December 2017

Interactive grid validation

APEX and IG validation

 

Interactive grid validation


Check for duplicates


Something that come out today as a question - How to validate Interactive grid to check if your user typed primary key like EMPNO is not already in the table?  

Catch was to do this only on insert as people will not be able to delete or update it afterwards.

Adding a validation to your IG is easy enough, creating one as:

 
 
Where PLSQL Function body code is:



That is all. Notice the use of APEX$ROW_STATUS to detect if row is created.






Happy APEXing,
Lino

Thursday, 30 November 2017

APEX and Universal theme Navigation bar list

APEX and Universal theme Navigation bar list

 

Navigation bar list CSS tip


Menu enhancement


For past few weeks I worked on APEX public facing application where one of the key design points was of course the need to match the existing company website UI (or at least as closely).

Luckily all of this was possible just by lightly tweaking Universal theme adding few custom report templates and rest was result of CSS magic.

To come to the point of this post - while polishing the look and feel for mobile devices something we picked up that I wanted to share here. 

If you happen to use Navigation bar list you will notice that as you minimize your screen at some point your items turn into icons and all text only navigation bar list item disappear. 

This is great as it shows responsiveness of UT. But what if you want to keep icons and text items. I know that having a smaller icon is recommended but in my case simply client did not want to use icons. 


Notice how only item with icon stays on after reaching certain screen size. 

To adjust this all we need is a little of CSS. 


<style>

.t-NavigationBar:before{
    cursor: pointer;
    border: 1px solid black;
    border-radius: 2px;  
    marxin-top:-2px;
    padding:5px;   
    content: "\e015";
    font-size: 16px;
    font-family: apex-5-icon-font!important;
    font-style: normal!important;
    font-weight: 400!important;
    font-variant: normal!important;
    text-transform: none!important;
    speak: none;
    line-height: 1;
    -webkit-font-smoothing: antialiased;   
    display: none;
}

@media only screen and (max-width: 640px){
    .t-Button--navBar .t-Button-label {
        display: inherit;
        font-size: 1rem;
    }
    .t-NavigationBar{
        position:fixed;
        z-index: 1000;
        top:5px;
        right:0;     
        background-color: #2578cf;  /*#ffffff;*/
       
    }
    .t-NavigationBar li{
        text-align:left;
    }
    .t-NavigationBar-item {
        display:none;
    }
    .t-NavigationBar:before{
        display:inline-block;
    }
    .t-NavigationBar:hover .t-NavigationBar-item,
    .t-NavigationBar:focus .t-NavigationBar-item{
        display:inherit;
    }
}


</style>



All that is left is adjust background color and icon you want to use.

Please note that in this example demo application uses both Navigation menu and Navigation bar. 

Live demo.



Happy APEXing,
Lino

Tuesday, 24 October 2017

Oracle APEX and RESTful API standards

Oracle APEX and RESTful API standards

 

Web services guidelines

 

Consistent RESTFul APIs



For the past few month I have been involved in corporate level interoperability project and these are my few key "lessons-learned" notes. 

We know and we have all heard of RESTful services and how important they are today for any production systems that are exchanging data. 
Point here is to emphasize importance of having standards to provide guidelines for architects and developers in the design and development of RESTful APIs that are consistent, usable, max reusable and are based on current industry best practices.

Similar to your PL/SQL and Oracle APEX standards these are things that will evolve over time but key thing is to consider having them before development work begins.These guidelines are applicable regardless if your services are built with Oracle APEX, PHP or any other technology.

I will try to keep short and sweet as I am sure a lot can be said on this topic.

Overall summary would be: 
REST Guidelines Summary
A REST URI identifies a resource
REST URIs should include nouns, not verbs
Use plural nouns to represent resources for consistency (no singular nouns)
Use HTTP verbs (GET, POST, PUT, DELETE) to operate on resource collections and elements
Use HTTP headers to include meta-data where possible
Use the correct HTTP status codes for responses
Control the depth and try not to nest deeper than /resource/{id}/resource if possible
Version the API using major, minor and patch version numbers, e.g. v1.0.0
Put the major version number in the URI, prepended with a ‘v’, for example https://my-resturl.com/v1/resource
Develop services that are idempotent, i.e. (accidentally) invoking the service twice with an identical payload has no undesirable side effects
Handle concurrency for 'unsafe' methods (i.e. PUT, POST, DELETE) where required
Default on using the JSON format for request and response messages
Use lowerCamelCase JSON message formats
All incoming and outgoing dates and times comply with the ISO8601 standard
All dates and times returned by a service should use the following ISO8601 compliant UTC common format: YYYY-MM-DDThh:mm:ss.sssZ
Wrap responses and error messages using a standardised format
Use the Common Information Model (CIM) where available
Always use HTTPS to secure the API at the transport layer
< Use OAuth 2.0 for token based security
Never change service features that have been released
As a consumer of a REST service: ignore content that is not used
 

Global guidelines

Design before build - The desired behavior of the API, including endpoints and message examples, must be designed, documented and reviewed before implementation starts.

OpenAPI specification - APIs must be specified using the OpenAPI Specification which is a well-established and widely adopted standard that allows both humans and computers to understand the capabilities of a service, without having to access the source code.

Include the major version number in the URI - The major version should be prepended with the letter ‘v’ to form the URI as it improves 'explorability' via a browser address bar:
https://my-resturl.com/v1/projects

Use plural nouns for resource names - This makes interfaces more intuitive to use and avoids having to deal with unusual pluralisation, such as diagnosis/diagnoses
/employee
/diagnoses/1234
  
Use hyphens in URIs for resource names that consist of multiple words
/v1/employee-profile

Use lowerCamelCase for URI query parameters - This is consistent with the default notation for JSON messages
/projects/1234?select=name,startDate,finishDate

Avoid using verbs in resource names - it may be convenient to use a verb to indicate a status change of a resource. The following URI may be implemented to activate a project:
POST /projects/1234/activate

Don’t use verbs in the URI for CRUD-type operations, e.g.:
Incorrect:
GET /GetAllProjects


Correct:
Resource
Expected Behaviour
GET /projects
Retrieve a list of projects
POST /projects
Create a new project
PUT /projects
Bulk update complete project records
PATCH /projects
Bulk update partial project records
DELETE /projects
Delete all projects
GET /projects/1234
Retrieve a specific project
POST /projects/1234
Usually invalid, but may be used when the consumer is allowed to provide the primary ID in a create resource request
PUT /projects/1234
Update all attributes of a specific project
PATCH /projects/1234
Update one or more attributes of a specific project
DELETE /projects/1234
Delete a specific project



Use the appropriate HTTP status codes to indicate Success and Failure

Implemented services in such a way that they are idempotent -
multiple requests to a service with an identical payload have no adverse effects to the overall state of the resource.

Detect concurrent modifications - Unsafe methods (i.e. methods changing resources) must consider the need to detect concurrent modifications. A common way to implement this is to have a read (GET) operation on the resource include an HTTP ETag header containing a digest (hash, e.g. MD5) representing the state of the resource.

Message format defaults to JSON - JSON is more lightweight than XML and just as easy for humans to understand. In addition, no parsing is required by JavaScript clients to use JSON messages.

Do not store values in keys of a JSON object
Correct:
{
  "gender": [
    { "code": "M", "description": "Male" }
  ]
}

Empty fields - Empty fields in response messages should be included as null instead of being omitted. 


Timestamps - All timestamps accepted and returned by the service ideally must be in ISO 8601 format [5] with time zone information.

Validate all input fields - basic check like i.e. required and unique parameter, string, number types or follows certain pattern

Restrict usage of wildcards  - for performance; check if at least one parameter is supplied

 

Collection of data

Enable pagination of collections by using the standard keywords ‘limit’ and ‘offset’.

Define an appropriate default limit that makes to prevent unnecessary use of bandwidth and resources.
Use the standard keyword ‘sort’ to indicate sorting of collections. Use ‘+’ to indicate ascending sort direction, use ‘-‘ to indicate descending sort direction. If no direction is specified, the default sort direction should be ascending.

 

Error messages 

Must include the following information:
·         a HTTP status code;
·         a link to the resource causing the error;
·         a short error message;
·         a longer description explaining the cause of the error.

Avoid exposing detailed system log messages to the consumers on production systems.

Security

All REST services should be exposed through the HTTPS protocol to provide security at the transport layer.

Services should use OAuth 2.0 to support token based authentication.  

API designers and developers should avoid exposing sensitive information like user names, API keys, security tokens or other credentials on the URI.

It is not enough to rely on consumers of the service to validate the input of end-users. The API is responsible for checking that the data provided in the service request conforms to business rules and does not pose a threat from a security perspective (e.g. SQL and NoSQL injection, Cross Site Scripting). 

REST services, particularly services that are exposed to the public, must be monitored using an API Management Tool
 

Conclusion:

Luckily once we have all of this in place APEX makes it easy to consume your newly created APIs. To make your life easier check out REST Client assistant packaged app and start from here. :)


Happy APEXing
SLino