How Do I Start Learning Oracle ADF - The 12c Edition

Shay Shmeltzer - Fri, 2017-06-30 17:42

The most popular blog entry on my blog has been the "How do I start Learning ADF" entry for years now. That entry however was last updated in 2012 (and written in 2010) - so I figured it is time to give it another update, point to more recent resources, fix broken links, and cover additional resources that appeared over the years.

So here is the ADF 12c version of that blog entry updated for 2017:

Get started with Oracle ADF in 6 steps

Step 1 - Learn Basic Java

Oracle ADF aims to reduce the amount of coding you need to do for a lot of the tasks you'll need for building an application, and if you follow some of the tutorials mentioned later you'll see how you can build advanced apps without coding. But, at the end of the day, you will write code when developing with ADF - and that code would be written in Java. You don't have to be a Java ninja to work in ADF, but you should be familiar with basic language concepts and constructs.

There are lots of resources out there that will teach you the language (by the way if you are on ADF 12.2.* you should learn the Java/JDK 8 syntax), one option is the Oracle Java Tutorials path. Searching online you'll be able to find many other resources for this task. Since Oracle ADF is based on Java EE architecture - you might want to also get a bit of understanding of that architecture - but don't worry about learning all of Java EE in details - ADF will make it much simpler for you.

While learning the language you should be practicing it with the development tool that you are going to use, if you are going to developer Oracle ADF applications then that tool will be Oracle JDeveloper. Get yourself familiar with the basic IDE features for coders by running through this IDE tutorial.

Step 2 - Get started with Oracle ADF

Now that you know the basics of the Java language (and maybe some Java EE concepts), it's time to start using the framework that will simplify your life. Start by reading the data sheet and technical paper to understand what ADF is all about.

Now get your hands dirty by completing the Overview tutorial for Oracle ADF - this will take you a couple of hours but by the end of it you'll have built a full blown application, and you will touch on most of the parts of the Oracle ADF architecture.

Two other tutorials you should do next will deepen your knowledge about the Oracle ADF Controller Layer and taskflows, and the Oracle ADF Faces UI layer. If you got more time, have a run through other tutorials from our site.

Step 3 - Getting Educated

Now that you have hands-on experience with Oracle ADF, it would be a good point to go and get some deeper knowledge about how the framework works. You can leverage the collection of free online lessons we recorded in the ADF Essentials channel. You don't have to watch all the videos, but I would definitely recommend that at a minimum you'll watch the overview, ADF business components, ADF Controller (both parts) and ADF Faces video. And then you must watch the video about the ADF bindings internal seminars (2 parts) - these are critical for you to understand the inner working of the ADF "magic layer" that makes development so simple. 

By the way if you prefer to get knowledge through live or online instructor-lead courses or by reading books - we have those too - see the list here.

Step 4 - RTFM

Ok, now you have a good grasp of the framework and how it works, it might be a good time to read the manual for Oracle ADF - "Developing Fusion Web Applications with Oracle Application Development Framework". This is the complete guide and you should read it to get more insight into the framework, best practices, and general guidelines. Note that the ADF documentation libraries has additional books about ADF Faces, ADF Desktop Integration, Administration guides and more.

Step 5 - Become an ADF Architect

Now that you know how to build ADF apps, it's time to learn how to architect more complex projects and work in a team environment. The resource to learn from is the ADF Architecture Square - where we discuss best practices, development guidelines, and most importantly how to architect a complete complex application. Here you can find docs and also a link to a set of videos on the ADF Architecture Square YouTube Channel. If you only have time to watch one video from that channel - go for the "Angels in the ADF Architecture". By the way, if you are looking for a platform for your team to collaborate on while building Oracle ADF applications - check out the Oracle Developer Cloud Service and the integration it provides with JDeveloper.

Step 6 - Join the Community

As you continue on your development road, there will be times when you'll want to know "How do I do X?" or "Why do I get this error?". The nice thing is that since many other developers are working with ADF, you can leverage their collective knowledge. Got a question - type it into google and it is likely that you'll find blog entries and youtube videos that explain how to solve your issue.

A great place to search for answers is the indexed collection of ADF and JDeveloper blog articles. Search by keywords or topics and you'll likely get great samples to help you achieve your task.

Still can't find the answer? Ask your question on our ADF community forum, just don't forget to follow the basic rules of asking questions on the forum.

Things keep evolving in the world of Oracle ADF, so to keep up to speed you should follow JDeveloper on Twitter for the latest news.

Over the years Oracle ADF has proven itself to be a great framework for enterprise applications, and each new release introduced further capabilities and simplifications - If you are just now joining the world of Oracle ADF you are in for a great ride. Have fun.

Categories: Development

Agile Development with PL/SQL

Gerger Consulting - Mon, 2017-06-19 13:12
Agile Development gives us the ability to work on multiple features at the same time and change which ones to ship at any point in time, quickly. This might be challenging for PL/SQL teams to accomplish, to say the least. This short video shows how Gitora, version control tool for PL/SQL, helps Oracle PL/SQL developers solve this problem.

If you prefer reading a step by step guide instead of watching a video, please click here.
Categories: Development

The river floes break in spring... take 2

Greg Pavlik - Sun, 2017-06-18 16:19
Alexander Blok
The river floes break in spring...
March 1902
translation by Greg Pavlik 

The river floes break in spring,
And for the dead I feel no sorrow -
Toward new summits I am rising,
Forgetting crevasses of past striving,
I see the blue horizon of tomorrow.

What regret, in fire and smoke,
the lament of the cross,
With each hour, with each stroke -
Or instead - the heavens’ gift stoked,
from the bush burnt without loss!


Весна в реке ломает льдины,
И милых мертвых мне не жаль:
Преодолев мои вершины,
Забыл я зимние теснины
И вижу голубую даль.

Что сожалеть в дыму пожара,
Что сокрушаться у креста,
Когда всечасно жду удара
Или божественного дара
Из Моисеева куста!
Март 1902
Translators note: I updated this after some reflection. The original translation used the allegorical imagery that would have been common in patristic writing and hence Russian Orthodoxy. For example, I used the image of Aaron's rod in lieu of the word "cross", which appears in Russian (креста). The rod of Aaron was commonly understood to be a type of the cross in traditional readings of Old Testament Scriptures. Similarly, the final line of Blok's poem "Из Моисеева куста" literally translates to "from Moses's Bush". In my original translation, I rendered the final line "from the bush of Moses, the Mother of God". Since at least the 4th century, the burning bush was interpreted as a type of Mary, the Theotokos (or God-bearer) in the patristic literature (see for example, Gregory of Nyssa, The Life of Moses). In Russian iconography there is even an icon type of the Mother of God called the Unburnt Bush. While the use of "rod" and "Mother of God" allowed me to maintain the rhyme pattern (rod/God in place of креста/куста) of the original poem, it created a awkward rhythm to the poem, especially in the final line. It also added explicit allusions to patristic images that are not explicitly present in the original poem, perhaps fundamentally altering the author's intention. A neat experiment but also one that I think ultimately failed.

The new translation returns to a more literal translation without allegory: "
креста" means simply cross and that is how the poem now reads. The final line has been abbreviated from my original translation, though somewhat less literal - "Из Моисеева куста" is now rendered as "from the bush burnt without loss" rather than the literal "from Moses's bush" or the more awkward original translation "From the Bush of Moses, the Mother of God". The new translation I believe captures more closely the original meaning and manages to maintain at least the rhyme pattern of the original (now cross/loss in place of креста/куста). Overall, this is far from a perfect translation but I think it is an improvement.
One final comment about Blok himself that perhaps illustrates why I am still conflicted about the changes to final line: Blok was a master of the Symbolist movement in Russian poetry, wherein he worked unconventional rhythms and rhyming into his poetry. On that score, I feel somewhat more at liberty to ignore the meter of the original and attempt to express something of a musical quality in English. However, Blok was also deeply influenced by the great philosopher Vladimir Soloviev, a proponent of Sophiology in the Russian intellectual tradition. This led to him writing many of his early poetic compositions about the Fair Lady, Sophia the embodiment of Wisdom. It is with this in mind that I feel some regret at removing the reference to the Mother of God, a human embodiment/enhypostatization of Divine Wisdom.

Version Control for PL/SQL

Gerger Consulting - Wed, 2017-06-07 14:23

We are hosting a live webinar for IOUG on June 8th at 10:00am PST. Attend and learn how you can manage your PL/SQL source code with Git. We have a new cool demo to show off. :-)

Sign up at this link.

The sign up page as 12 mandatory questions. :-) We are sorry for the inconvenience. We had no control over it. But please bear with us. Bite the bullet. Take a leap of faith. It'll be worth your time. We promise. :-)

Categories: Development

Extending Oracle Database DevOps with Automated PL/SQL Unit Testing

Shay Shmeltzer - Mon, 2017-06-05 14:57
Automated testing helps you locate problems earlier in the development cycle saving you precious time down the road. This is why it should be a key part of any DevOps cycle - and your database code shouldn't be an exception to this rule.This blog entry will teach you how to execute tests automatically following code changes that you do in your Oracle database.
In previous blog entires I showed you how to use Oracle Developer Cloud Service (DevCS for short) for database development including how to track and manage tasks, version code changes, conduct code reviews, and automate code deployment to the database. This blog adds one more step to this lifecycle - automated testing.
For PL/SQL testing I'm using the open-source utPLSQL unit testing solution. The team behind this project just released a completely re-written version of the framework with features that allow you to add PL/SQL testing to continuous integration processes.

A full explanation of utPLSQL is outside of the scope of this blog (They have decent documentation to get you started). But in short, the concept is that you write PL/SQL procedures that test other procedures. The framework includes functions you invoke from your test functions to evaluate results as well as annotations that deliver meaningful messages and information when reporting test results. The utPLSQL utility is comprised of a set of database objects that you install in a new schema, and then you use their procedure to execute test cases.

One nice feature built into the framework is the ability to produce test result reports in a format that is compatible with regular JUnit tests. With this functionality, I was able to get Developer Cloud Service to show me the test results nicely. Further more the built in support of DevCS for the SQLcl commands, made it simple to integrate the PL/SQL based framework as part of a generic build process without the need to install anything else on my continuous integration server.

Here is a quick video showing you the result and the configuration needed.
In the video I show how a check in of a PL/SQL script into the Git repository triggers a chain of events that ends with publishing test results. If the test fails the build is marked as failed - which can trigger an email being sent to you notifying you each time someone broke your code.
Some tips for configuration of such a chain:

My build pipeline has two jobs. The first one runs the SQL scripts in the database. This job is triggered by any change made to my Git repository. So when I update my git repository with a SQL script that has a new definition of a database object, the build immediately takes it and updates the definition in my development or QA database.
Once this build job finishes, it queues up the next job - the unit testing job.

The unit testing job is using SQLcl to run the following commands:

set serveroutput on;
set feedback off;
spool /workspace_directory/results.xml;
spool off;

I spool the results of the test run into an xml file that I keep in the workspace directory for my job. (You can find out this directory by adding a shell command build step that does echo $WORKSPACE - an environment variable on the build server). Then I execute the procedure with the parameter that tells it to output the results as XUnit/JUnit format - doc on this option here. I turn serverouput on to get the results to show, and I turn feedback off to hide the message that the procedure successfully completed.

In the post build step I archive the results.xml file, and then I indicate that I want to publish the content of this file as test results.
Post Build Step

When your build finishes you'll see your build status visually and you can then drill down to see specific tests status.
Notice that you can also ask to be notified by email on the results of the build (the CC Me button).
Click on a specific run of a job to drill down into the test results
Test Summary
And click on a specific test suite to get the details of each test

Test Results Report

That's it. You now have a complete chain that will notify you the minute that a database change someone did breaks any tests, helping you deliver better code faster.
Categories: Development

DevOps and the Agile DBA

Gerger Consulting - Wed, 2017-05-31 13:38
The esteemed ACE Director alumna Kellyn Pot'Vin Gorman is hosting our next webinar!

Attend the free webinar by Kellyn on June 13th and learn how agile best practices and virtualization can help you become better at DevOps.

About the Webinar:
This session discusses common database challenges and how they can be solved with newer DevOps practices without impacting the DBAs “safe zone”.

Development, testing, provisioning, patching, upgrading and managing will become easier with agile tools, virtualization and collaboration.

The take-aways from this session are to embrace DevOps basics with a “DBA twist” such as:
  • Develop, Rinse, Repeat
  • Test Until Your Five Year Old Can Do It
  • Human Intervention Creates Human Error- Automate EVERYTHING.
  • How the shorter development cycle build business success.
  • How the DBA can do more with less.
Discover the power of virtualization and agile development practices providing more value to even the most senior database expert.

We’ll demo different scenarios surrounding DevOps, virtualization and how to make the DBA pivotal to DevOps success.

Sign up for the free webinar.

About the Presenter:

Kellyn Pot’Vin-Gorman is a member of the Oak Table Network and an Oracle ACE Director alumna. She is the Technical Intelligence Manager for the Office of CTO at Delphix, a company recognized for it's impressive virtualization and data masking environment capabilities. Kellyn is known for her extensive work with multi-database platforms, cloud migrations, virtualization, the command line interface, environment optimization tuning, automation and architecture design. Her blog, and social media activity under her handle, DBAKevlar is well respected for her insight and content.

Registration is free but space is limited.
Sign up now.
Categories: Development

Automating Processes With Application Builder and Process Cloud Services

Shay Shmeltzer - Tue, 2017-05-30 18:33

Oracle Application Builder Cloud Service (ABCS) gives you a great way to build apps that track data, but what if your data is also involved in processes? What if you need to automate not just the data collection but also the human workflow interactions? The new integration between Oracle ABCS and Oracle Process Cloud Service (PCS) enables you to achieve this easily.

You can now create processes that are associated with ABCS business objects and interact with them directly from your Oracle ABCS user interfaces. This is a two way interaction patterns, where PCS processes can access information from Oracle ABCS business objects, and ABCS user interfaces can be created on top of these PCS processes to initiate and progress processes.

PCS integration in ABCS

In the video example below I'm developing a basic approval flow for travel requests. The video will show you how the integration works covering:

  • Associating a process with a business object
  • Accessing the business object values from your process
  • Setting security and connection between PCS and ABCS
  • Initiating PCS processes from an ABCS page
  • Creating custom to-do list pages in ABCS to show you your tasks
  • Creating custom task details pages in ABCS to progress tasks

As you'll see, all of these are quite simple and completely declarative to achieve with the visual development approach.

The combination of the products provide great value to the users of each one of those. PCS customers will love the ability to persist the data they use in their processes, and the ability to design even richer interfaces and reports. ABCS users will love the ability to automate and manage long running complex processes. 




Categories: Development

Smart Database Architecture for Software Development

Gerger Consulting - Wed, 2017-05-17 13:00

We are incredibly excited to announce that the amazing Toon Koppelaars from Oracle Real World Performance Team is hosting our next webinar: Smart Database Architecture for Software Development. Register at this link.

About the Webinar

Is the database a processing engine or a persistence layer? In this presentation we'll first go through a bit of history demonstrating how the database has been used in the past 30 years: at times it was a processing engine, and at other times it was just a persistence layer. 

Having witnessed many application development projects, we are convinced that the database ought to be used as a processing engine. The persistence layer approach, where all business logic is implemented outside the database has serious drawbacks in the areas of initial application development, ongoing maintenance, and most notably in the area of performance and scalability. 

We'll discuss these drawbacks, in particular the last one: We'll debunk once and for all that moving business logic out of the database benefits performance and scalability.

About the Presenter

Toon has been part of the Oracle eco-system since 1987. He is currently a member of Oracle's Real World Performance Team. The RWP-team troubleshoots application performance issues in and around the Oracle Database. The way applications currently use (or rather, abuse) the DBMS, is often at the root of these performance issues. Prior to joining the RWP team, Toon has been mainly involved in database application development. His special interests are: architecting applications for performance and scalability, database design, and business rules / constraints modeling. He is a long-time champion of using the database in a smart way, i.e. using the database as a processing engine.

Registration is free but space is limited.

Categories: Development

Leveraging Oracle JET Composite Components in Oracle application Builder Cloud Service

Shay Shmeltzer - Mon, 2017-05-15 16:17

One of the new features of Oracle Application Builder Cloud Service (ABCS) in the May 2017 release is integration with Oracle JET's Composite Components Architecture (JET CCA).

Based on the Web Components standard, JET CCA provides a way to define reusable UI components (with logic) that can easily be incorporated into multiple applications.

The new feature in ABCS allows you to pick such components as extensions to the ABCS design time, providing an easy standard way to extend your UI capabilities. For example in the screenshot below you see a new slider component in the common components section, and how it looks like when added to the visual designer.

ABCS Design Time

In this blog entry we'll start by creating a very simple JET CCA component and then see how to add it to Oracle ABCS. (Thanks goes to John Brock who helped get this sample working).

To learn more about JET CCA have a look at their developer guide, and the sample in the Oracle JET Cookbook. We are going to start from that sample and build a very basic component. (For a deep guided tour of Oracle JET CCA check out Duncan's series of JET CCA blogs)

There are 5 files needed to define a component.

5 files in a directory

We'll start with the loader.js file - this file provides info on the other files involved in the component. Note that in the sample we are registering "slider" as the name of the component, in this file we are also indicating which jet components we are going to use and including them in the define section. Specifically we are adding the ojs/ojslider component here.

define(['ojs/ojcore', 'text!./demo-cca.html', './demo-cca', 'text!./component.json', 'css!./demo-cca','ojs/ojcomposite', 'ojs/ojslider'], function(oj, view, viewModel, metadata) { oj.Composite.register('slider', { view: {inline: view}, viewModel: {inline: viewModel}, metadata: {inline: JSON.parse(metadata)} }); } );

The next file we'll create is the component.json file. This file describes the meta data about our component. One of the key things you can define here is a set of properties that users of the components can set when they add it to their application. The nice thing in the ABCS integration is that these will show up at design time as properties in the visual editor.

In our component we are defining four properties that control the title, minimum, maximum, and actual value of a slider. Note that right now ABCS is using Oracle JET 2.3 and we need to specify this in the file.

{ "name": "Slider", "description": "A sample Oracle JET Slider CCA", "version": "1.0.0", "jetVersion": ">=2.3.0", "properties": { "title": { "description": "Name of slider", "type": "string" }, "min": { "description": "Numeric minimum", "type": "number" }, "max": { "description": "Numeric maximum", "type": "number" }, "value": { "description": "Slider value", "type": "number" } } }

Next we'll define the html file (demo-cca.html) that includes our UI. We are using regular HTML code here along with knockout.js binding of properties to values. You can use the $props prefix to refer to values of attributes we defined in the components.json file.

<div data-bind="text: 'Title: '+$props.title"></div>
  <input id="slider-id"
     data-bind="ojComponent: {
            component: 'ojSlider',

Next there is a css file - controlling the look and feel of the component. Since we are not doing any customization on the look and feel we'll create an empty file called demo-cca.css.

Next is the model file (demo-cca.js) - this file contains data and logic that can be accessed from the component. We'll create a basic file without any logic code in it.

define(['knockout'], function (ko) { function model(context) { var self = this; return model; } } )

Now that you have created the 5 files - simply zip them into a single zip file. This zip file is the file you'll give to your component users. In this case to the ABCS developer.

Go into your Application Settings -> Extensions in Oracle ABCS and choose to create a new UI component from zip file. Upload the zip file you just created. Then make sure to enable the component using the boolean control on the page.

Component extension

Switch over to the UI Designer and you'll see that there is a new component in the component palette.
Drag and drop it into your page - and you'll see the HTML code. Set the properties in the property inspector and you'll see them influencing the content of your page.
You can also bind the properties to the values of fields in your custom business objects.

Here is a quick video showing the integration.


Categories: Development

Getting Data from REST Services into Oracle Application Builder Cloud Service

Shay Shmeltzer - Fri, 2017-05-12 11:09

In the latest version of Oracle Application Builder Cloud Service (ABCS) that we rolled out at the beginning of May we introduced a set of new code templates for creating Business Object Providers.

BOP templates screen

Business Object Providers - or BOPs for short - are a mechanism that allow you to extend ABCS and have it access external REST sources of data. In the video below I'm going to show you how to use the most basic template provided for BOPs - which allows you to create a read only BOP.

The template has 2 files that you need to change - one (RESTOperationProvider.js) that has the code for accessing the REST service and reading the results, and the other (RESTEntityProvider.js) has the code that defines the structure of the object you are creating.

In the video I'm using this URL - - that gets you a list of projects/repositories that Oracle owns on Github:

Once you created a BOP you can add a new "external service" to your application in the data designer, and then you can use that object like you would any other.

Check it out:


Categories: Development

Passing Business Object Values to Custom UI Components in ABCS

Shay Shmeltzer - Mon, 2017-05-08 18:54

This quick one is based on a customer question about Oracle Application Builder Cloud Service. The scenario is that we have a business object that has a field that contains the URL to an image. We want to be able to show that image on a page in Oracle Application Builder Cloud Service.

Image showing up

To do that I add a custom UI component object to the details (or edit) page of a record - then I switched the HTML of that object to be: <img id="logoimg"/>

custom code


I then added a button to the page and added a bit of custom JavaScript code in its action as follow:

var img = document.getElementById('logoimg'); img.src=$Company.getValue('Logo'); resolve();

This code simply locates the custom object on the page using the object id and then sets the src property of the img html tag to match the value of the field in the business object.

Code in Button



Categories: Development

I'm On a New Blog Platform!

Shay Shmeltzer - Fri, 2017-05-05 12:15

This happens every several years, our blogging platform at Oracle is switching to a new environment, and my blog is one of those moving. In the next few days I'll be testing to see if content migration did its magic and everything works.

If you run into any broken entries/links/samples please drop me a line or just comment on the specific blog entry, and I'll try to fix things.

Categories: Development

Getting the Database Backup Report for all Monitored Targets in Enterprise Manager

Arun Bavera - Fri, 2017-05-05 09:05
Use the following query in BIP:

SELECT DATABASE_NAME                                  AS CONFIGURATION_TARGET_NAME    ,
 host                                                                                                                                            AS HOST_NAME                              ,
'DB Backup Status'                                                                                                                        AS CONFIGURATION_STANDARD                ,
status                                                                                                                                                AS ACTUAL_VALUE             ,
    CASE    WHEN ( mgmt$ha_backup.status = 'COMPLETED' AND round(sysdate-end_time)<=1 ) THEN 'YES'
            ELSE 'NO'
    output_device_type ,
TO_CHAR(end_time,'YYYY-MM-DD HH24:MI:SS')                                                                            AS COLLECTION_DATE                                ,
    TO_CHAR(start_time,'YYYY-MM-DD HH24:MI:SS') START_TIME,
    TO_CHAR(end_time,'YYYY-MM-DD HH24:MI:SS') END_TIME,
    round(sysdate-end_time) LATEST,
FROM   mgmt$ha_backup
                              from   mgmt$target_flat_members,
                              where  member_target_type in ( 'rac_database' ,'oracle_database')
                              and    aggregate_target_name = 'Prod-Grp'
                              AND   mgmt$target_flat_members.MEMBER_TARGET_NAME = mgmt$target_properties.target_name
                              AND   mgmt$target_flat_members.member_target_type = mgmt$target_properties.target_type
                              AND   mgmt$target_properties.property_name = 'OpenMode'
                              AND   mgmt$target_properties.property_value= 'READ WRITE'
                              SELECT   s1.target_name
                                      MGMT$TARGET s5                 ,
                                      GC$ASSOC_INST_WITH_INVERSE s5a1,
                                      MGMT$TARGET s1
                              WHERE  (   s5a1.SOURCE_ME_GUID = s1.TARGET_GUID
                                          AND s5a1.DEST_ME_GUID   = s5.TARGET_GUID
                                          AND s5.TARGET_TYPE      = 'rac_database'
                                          AND s5a1.ASSOC_TYPE     = 'member_of_cluster'
                                      AND s1.TARGET_TYPE = 'oracle_database')

Categories: Development

Arup Nanda on Big Data

Gerger Consulting - Thu, 2017-05-04 14:45
Attend our free webinar and learn from the legendary Oracle DBA Arup Nanda about the Big Data ecosystem and how it compares to Oracle solutions.

Register at this link.

About the Webinar

Big Data is the future of database computing and knowing about it not just a luxury you can avoid anymore; it's necessary for survival and growth. But Hadoop, Spark, Hive, Map/Reduce, Flume - if these terms hit you more often than you have the time to pause and think about them, look no further. Learn about these and more in this informative session on this Big Data components explained from the perspective of an Oracle professional, how they are same or different and how they compare with RAC or parallel query.

Register today.
Categories: Development

SQL validation during PL/SQL compilation

XTended Oracle SQL - Sun, 2017-04-30 18:27

A recent posting on SQL.RU asked why Oracle doesn’t raise such errors like “ORA-00979 not a group by expression” during PL/SQL compilation. Since I couldn’t find a link to the answer (though I read about it many years ago, but I don’t remember where…), I’ve decided to post short answer:

During PL/SQL compilation Oracle checks static SQL using only:

  1. Syntactic analysis – Oracle verifies that keywords, object names, operators, delimiters, and so on are placed correctly in your SQL statement. So such queries like “select * foRm dual” will fail during this validation. For example, we can get here such errors like:
    ORA-00900: invalid SQL statement
    ORA-00923: FROM keyword not found where expected
    ORA-00924: missing BY keyword
    ORA-00933: SQL command not properly ended
  2. Semantic analysis – it verifies that references to host variables and database objects are valid(including their grants) and that host-variable datatypes are correct. For example, “select * from nonexisting_table” will fail this validation.

And since Oracle doesn’t validate all other types of errors during Syntactic and Semantic analysis, we can detect them only during optimization* or execution*.
For example, Oracle detects “ORA-00979 not a group by expression” during optimization phase.

* Read “Chapter Chapter 7: Parsing and Optimizing” in Oracle Core by Jonathan Lewis

NB. It doesn’t not apply to CREATE or ALTER VIEW, because Oracle executes optimization step for them. You can check it using trace 10053.

Simple example:

-- fORm instead of "from" - syntactic validation fails:
SQL> create table t(a int, b int, c int);
SQL> create or replace procedure p_syntactic is
  2     cursor c is select a,b,sum(c) sum_c fORm t group by a;
  3  begin
  4     null;
  5  end;
  6  /

Warning: Procedure created with compilation errors.

SQL> sho error;

-------- -----------------------------------------------------------------
2/16     PL/SQL: SQL Statement ignored
2/40     PL/SQL: ORA-00923: FROM keyword not found where expected

-- semantic validation fails:
SQL> create or replace procedure p_semantic is
  2     cursor c is select a,b,sum(blabla) sum_c from t group by a;
  3  begin
  4     null;
  5  end;
  6  /

Warning: Procedure created with compilation errors.

SQL> sho error;

-------- -----------------------------------------------------------------
2/16     PL/SQL: SQL Statement ignored
2/31     PL/SQL: ORA-00904: "BLABLA": invalid identifier

-- As you can see this procedure passes successfully both syntactic and semantic analysis,
-- though query is not valid: it should raise "ORA-00979: not a GROUP BY expression"
SQL> create or replace procedure p_valid is
  2     cursor c is select a,b,sum(c) sum_c from t group by a;
  3  begin
  4     null;
  5  end;
  6  /

SQL> sho error;
No errors.

-- Oracle checks such errors for "CREATE VIEW", because it runs optimization for the query text:
SQL> create view v_cursor as select a,b,sum(c) sum_c from t group by a order by a;
create view v_cursor as select a,b,sum(c) sum_c from t group by a order by a
ERROR at line 1:
ORA-00979: not a GROUP BY expression
Categories: Development

Custom UI Components in Oracle ABCS for Dynamic Image Display

Shay Shmeltzer - Mon, 2017-04-24 17:29

This quick one is based on a customer question about Oracle Application Builder Cloud Service. The scenario is that we have a business object that has a field that contains the URL to an image. We want to be able to show that image on a page in Oracle Application Builder Cloud Service.

animated GIF

To do that I add a custom UI component object to the details (or edit) page of a record - then I switched the HTML of that object to be: <img id="logoimg"/>

custom code

I then added a button to the page and added a bit of custom JavaScript code in its action as follow:

var img = document.getElementById('logoimg');



This code simply locates the custom object on the page using the object id and then sets the src property of the img html tag to match the value of the field in the business object.

Code in Button

Categories: Development

Passing Business Object Values to Custom UI Components in ABCS

Shay Shmeltzer - Mon, 2017-04-24 17:29

This quick one is based on a customer question about Oracle Application Builder Cloud Service. The scenario is that we have a business object that has a field that contains the URL to an image. We want to be able to show that image on a page in Oracle Application Builder Cloud Service.

animated GIF

To do that I add a custom UI component object to the details (or edit) page of a record - then I switched the HTML of that object to be: <img id="logoimg"/>

custom code

I then added a button to the page and added a bit of custom JavaScript code in its action as follow:

var img = document.getElementById('logoimg');



This code simply locates the custom object on the page using the object id and then sets the src property of the img html tag to match the value of the field in the business object.

Code in Button

Categories: Development

We're All In This Thing Together

Greg Pavlik - Fri, 2017-04-14 17:26
This song pretty much summarizes everything I've learned to be true about life after nearly five decades of living...

Well my friend, well I see your face so clear
Little bit tired, a little worn through the years
You sound nervous, you seem alone
I hardly recognize your voice on the telephone

In between I remember
Just before we wound up broken down
We'd drive out to the edge of the highway
Follow that lonesome dead-end roadside sound

We're all in this thing together
Walkin' the line between faith and fear
This life don't last forever
When you cry I taste the salt in your tears

Well my friend let's put this thing together
And walk the path that worn out feet have trod
If you wanted we can go home forever
Give up your jaded ways, spell your name to God

We're all in this thing together
Walkin' the line between faith and fear
This life don't last forever
When you cry I taste the salt in your tears

All we are is a picture in a mirror
Fancy shoes to grace our feet
All that there is is a slow road to freedom
Heaven above and the devil beneath

We're all in this thing together
Walkin' the line between faith and fear
This life don't last forever
When you cry I taste the salt in your tears

Apache Spark for Oracle Developers

Gerger Consulting - Tue, 2017-04-11 01:27
In the last few years, business problems that have been traditionally solved using databases are now being solved using other technologies. Apache Spark is one of the emerging technologies which is used for large scale data processing.

Attend the free webinar by Ivan Lokic and learn how you can use Apache Spark in Big Data applications.  

About the Webinar:
Apache Spark is a general computing engine designed for large-scale data processing. Many well-known companies use it to process petabytes of data on 8000+ nodes with long running jobs measured in weeks.

In this webinar, you will learn about:
  • Apache Spark and how Spark relates to (traditional) Hadoop MapReduce technology
  • What makes Spark so fast
  • How to use the rich set of Spark API’s to design and run your ETL jobs
  • Apache Spark streaming capabilities for near real-time updates and its role in Big Data processing scenarios
  • Structured Streaming, a scalable and fault tolerant stream processing engine which makes near real-time processing scenarios even easier
The webinar is free but space is limited.

Categories: Development

Take a few minutes to patch Oracle APEX 5.1

Dimitri Gielis - Wed, 2017-03-29 08:00
Yesterday a first patch set of Oracle Application Express (APEX) 5.1 has been made available to download.

one-of patches

If you encounter issues, you can ask for support and most likely a bit later a patch is made available through The APEX team is doing a great job with this.

For example some people using APEX Office Print had an issue which was caused by a bug in APEX_JSON (which we heavily use behind the scenes). The next day the APEX Dev Team already made a patch available (PSE 25650850).

patch set

Instead of applying those one-off patches, you can wait for a patch set which includes those one-off patches and more. If you didn't move to Oracle APEX 5.1 yet, you can just download the latest version which includes 5.1.1 immediately. 

There're many fixes for the Interactive Grid features, but next to that, many others as well, like for example login issues.

applying the patch set

If you're on Oracle APEX 5.1, search for patch 25341386. Unzip the file, stop the webserver, run @apxpatch, copy the images folder and start the webserver again.
About 2 minutes later you're on the latest version. 

Happy patching...
Categories: Development


Subscribe to Oracle FAQ aggregator - Development