Weblog for the AMIS Technology corner
Posts tagged plsql
Jouw toekomst als Oracle professional – AMIS !!EXTRA!! informatieavond over jouw ontwikkelingspad
Jan 24th
Als je dit leest en je bent Oracle professional – ontwikkelaar of administrator – is de kans groot dat je jouw carriere bent gestart in de jaren ‘90, in het tijdperk van Oracle7, Oracle Forms en Client/Server applicaties – net voor de opkomst van Java, internet, mobiel en SOA. Misschien ligt je start daar nog wel voor en misschien ben je van een tikje later. Maar in elk geval: je hebt een solide fundament en al vele jaren ervaring in Oracle technologie.
Nu is het 2012. En de afgelopen jaren is de technologie in hoog tempo ontwikkeld. Web 2.0 applicaties en multi-tier architecturen dienden zich aan en SOA, Service Bus en BPM zijn opgekomen. Java is overal – en HTML 5 staat op het punt om Flash en Silverlight te verdringen, in desktop browsers en in mobiele devices. Eindgebruikers worden mondiger en veel-eisender en real-time, push-enabled, BI-gedreven dashboards en multi-channel takenlijsten worden in snel itererende agile-projecten gerealiseerd.
Oracle 11g Database doet alle simpele DBA taken zelf – en de uitdaging voor de Database Administrator is verschoven naar groter en interessanter, zeker als 24/7 beschikbaarheid, virtualisatie en de cloud als infrastructuur opduiken. Middleware administratie en integrale security zijn zomaar twee nieuwe uitdagingen op de weg van de administrator, naast een verdergaande consolidatie en professionalisering van deployment en monitoring.
De uitdaging voor de Oracle professional lijkt duidelijk: hoe kies je je weg naar de toekomst? Om interessante klussen te blijven (gaan?) doen is het nodig om aansluiting te vinden bij de ontwikkelingen in de markt. Eerst moet je natuurlijk goed inzicht hebben in wat die ontwikkelingen zijn – in de markt in het algemeen en binnen de Oracle technology stack in het bijzonder. Dan moet je uitvinden hoe je vanuit je huidige kennis en ervaring de aansluiting vindt en jezelf prepareert voor het werken met de huidige en komende technologie. En tenslotte moet je het gaan doen – kennis vergaren, ervaring opdoen en je profileren om de passende klussen op te kunnen pakken.
Publishing to CometD Bayeux Channel from inside the Oracle Database – PL/SQL based push to CometD Web Client
Jan 19th
In recent articles, I have introduced CometD as framework for Server to Client and Client to Client Push: Running CometD 2 examples – locally on Tomcat using Maven and NetBeans http://technology.amis.nl/blog/14709/running-cometd-2-examples-locally-on-tomcat-using-maven-and-netbeans, CometD 2 Java Client Sample – open project in NetBeans based on Maven pom file, modify sources and run Java Based Comet Client (http://technology.amis.nl/blog/14720/cometd-2-java-client-sample-open-project-in-netbeans-based-on-maven-pom-file-modify-sources-and-run-java-based-comet-client) and Push based synchronized Slideshow demo application implemented using CometD and jQuery running on Tomcat (http://technology.amis.nl/blog/14870/push-based-synchronized-slideshow-demo-application-implemented-using-cometd-and-jquery-running-on-tomcat).
CometD allows web clients (or stand alone Java clients) to subscribe to Bayeux channels maintained by the CometD Server (Servlet). These clients can publish messages to the channels and CometD will deliver the messages to all or to selected clients – in a push-fashion. This makes it possible to push messages from the Java server side of a web application to web clients and indirectly to do the same between web clients. One example I have described of applying this technology is the synchronized slideshow: a web page is opened in two or more browsers. When a slide is selected in one of the browsers, the slide is selected in all browsers (because they all subscribed to the channel that a slide selection event was published to).
This article adds another push channel to the overall picture: it describes how the Oracle Database can publish directly to the Bayeux Channels (by posting to the CometD servlet over HTTP) and thereby perform push to the subscribed web clients. Database to browser push – how does that sound?
Absolutely Typical – The whole story on Types and how they power PL/SQL Interoperability (UKOUG, 2011)
Dec 12th
This presentation will hopefully convince database developers that types in the Oracle Database are worth their salt – and more. With the recent improvements in 11gR2, the pieces are available to complete the puzzle of structured and modern programming with a touch of OO and more importantly to create a decoupled, reusable API that exposes services based on tables and views to clients that speak SQL, AQ, PL/SQL, Types, XML or RESTful, through SQL*Net, JDBC or HTTP.
This session shows through many demonstrations how types and collections are defined, how they are used between SQL and PL/SQL and how they can be converted to and from XML and JSON and how they drive Native WebServices as well as RESTful services based on the Embedded PL/SQL Gateway. Everyone doing PL/SQL programming will benefit immediately from this session. Every Database Developer should be aware of Types and Collections. For structured programming, for optimal SQL to PL/SQL integration and for interoperability to client application. This session introduces Types and Collections, their OO capabilities, the conversion to XML and JSON, their use in Native and RESTful WebServices and the pivotal role they can play in encapsulation and decoupling.
The slides can be reviewed here:
View more presentations from Lucas Jellema.
Resources
Download Slides plus Demoscripts here: AbsolutelyTypical_UKOUG2011_jellema.zip.
Implementing Web Services backed by a Database PL/SQL API using the Oracle Service Bus
Dec 1st
This article accompanies an article on the Architecture section of Oracle Technology Network (OTN): Implementing the Enterprise Service Bus Pattern to Expose Database Backed Services. It provides a detailed description of the implementation of the ESB architecture design pattern – the same that is introduced in the article on OTN – using Oracle Service Bus. This OSB based implementation is in terms of structure, architecture design and functionality the replica of the pure Java based implementation described in the OTN-article. OSB adds productivity, agility and many run time administrative benefits over the pure Java solution.
The challenge
The challenge the organization – let us call them Stuff Inc. – is facing is simple and common: external parties have requested access to information through Web Services. This information is held in the enterprise (Oracle) database at Stuff Inc.
The architecture team decides that the implementation of the service should be done according to the ESB architecture pattern.
Their initial implementation is based on custom Java Classes, XSLT stylesheets and JDBC calls.
After some time having worked with this initial set up, they decide to introduce the Oracle Service Bus to replace much of their generic classes and ESB plumbing. This article tells the story of how the OSB is used to implement a simple Web Service based on a PL/SQL API in an Oracle Database, using two transformations and a JCA Database Adapter service. It demonstrates the essential steps in implementing any OSB service.
2 dagen seminar door Steven Feuerstein: Best of Oracle PL/SQL (8 en 9 december)
Oct 13th
Steven Feuerstein
In dit tweedaagse seminar neemt Steven Feuerstein je mee ver voorbij de basismogelijkheden van PL/SQL. Steven zal tijdens dit seminar de best practices behandelen die hij op tientallen plekken in de wereld heeft verzameld en die hij ook mede door zijn nauwe samenwerking met het PL/SQL product team van Oracle kan verifiëren en aanscherpen. Hij laat via code-voorbeelden en interactieve sessies zien hoe je als ontwikkelaar in staat bent om snel, hoogwaardige, goed onderhoudbare en uitbreidbare applicaties kunt maken.
Er is tijdens dit seminar voldoende ruimte om vragen te stellen en ideeën met Steven Feurestein uit te wisselen.
Bij dit seminar zijn de volgende zaken inbegrepen:
- Een exemplaar van het boek Oracle PL/SQL Programming (5th edition) van Steven Feuerstein (twv €71,-).
- Een PL/SQL quiz onder leiding van Steven Feuerstein met leuke prijzen.
- Ook ontvang je een gratis jaarabonnement op het PL/SQL Channel. Hiermee heb je toegang tot 27 uur videotraining (normaal $395).
Dit is de kans om in Nederland in een select gezelschap met één van de experts op PL/SQL gebied kennis te maken en je expertise in Oracle PL/SQL verder te vergroten. Meer informatie of inschrijven kan via deze link.
(NB: Deelname voor deze twee dagen kost 1050 euro)
Business Rule: Only One per Day, but keep the time
Aug 24th
The business rule states:
Only one entry is allowed per ID and per day and the time should be recorded.
The table involved (simplified for the blog post)
SQL> create table test 2 (id number 3 ,inspection_dt date 4 );
Wouldn’t it be nice if it was possible to do it like this?
SQL> create table test
2 (id number
3 ,inspection_dt date
4 ,constraint one_per_day unique (id, trunc (inspection_dt))
5 );
,constraint one_per_day unique (id, trunc (inspection_dt))
*
ERROR at line 4:
ORA-00904: : invalid identifier
This way you still have the complete date information (time is a component of the date column), and only use the TRUNC (inspection_dt) to constrain the data entry.
As you can tell from the error message, this is not allowed.
Oracle 11g Release 1 introduced Virtual Columns which can implement this requirement declaratively.
Creating JSON document straight from SQL query – using LISTAGG and With Clause
Jun 14th
This very brief article demonstrates how a fairly complex, nested JSON document can be created using a single SQL query. The main features used in the SQL statement are the WITH clause to define the inline views per object (Department, Manager, Employee), Scalar Subquery to retrieve the result from an inline view as string into the overall JSON string and LISTAGG to collect multiple elements into a JSON list.
The article shows a very simple application of this JSON creation through a simple HTML page – served by a PL/SQL package. This page does not contain any data. Right after loading, the onLoad event triggers a listener that invokes – through AJAX – the PL/SQL package’s other procedure. This procedure returns the JSON object – courtesy of that fabulous SQL query – and uses it to write the company details on the fly into the page.
How to use an outerjoin to synchronise data
Apr 11th
There is often a need to synchronize data from a query. For example reading external data and update the tables. Sometimes it can be achieved by a single merge statement, but this is not always possible. Mind, for example, the need to update more than one table, or updating the data using an api.
As an example we want to grant and revoke privileges on database objects. Tables needing select, insert, update and delete privileges, packages execute and views select privileges. The table and packages also get the grant option for the select and execute privilege.
Read the rest of this entry »
No JDBC based data retrieval in Java applications – RESTstyle, JSON formatted HTTP based interaction from Java to Database
Jan 30th
This article demonstrates how Java application can query data from relational databases (well, in this case more specifically the Oracle RDBMS) without the use of plain old JDBC but instead through a simple HTTP interaction with a RESTful service that returns the data in JSON format.
In this way, issues with JDBC drivers, database connections, data sources and firewall and other network complications are avoided and the Java application does not contain a single bit of SQL.
This article will demonstrate how easy it is to set up the RESTful API from the database – using Oracle’s dbms_epg package – and how this API can be accessed from Java using JAX-RS (Jersey) and the json-lib utility library.
This topic is one of many to be discussed in Friday’s (4th February) Masterclass on Leveraging the Oracle Database in Java Application (some seats are available – go to http://www.amis.nl/trainingen/oracle-database-in-java-applications for registration and details).
APEX: Creating a form based on a procedure
Nov 12th
Introduction
Within ApEx for a while it has been possible to create a page (more specifically, a form) based on a procedure. A procedure enables you to build in more logic than a simple DML statement or otherwise. My example is relatively simple in that when I want to book an appointment with somebody, I do not want this appointment to overlap with somebody else’s. Maybe I even don’t want my appointment to be directly after or before another one, and want a 15 minute break.
Scenario
Build the procedure
This kind of scenario is not easy to realise if you don’t have a plan. The plan is therefore to build an INSERT statement (into a procedure, not a function!) that is only actually executed when there is enough room for my new appointment – in terms of time – in my agenda. Building this procedure is done in the Object Browser area of Apex (Home –> SQL Workshop –> Object Browser; press Create).
To check that there are actually no overlaps you need to know when the appointment starts and how long it will take. (Loads more additional features could be added, I am trying to keep the example simple for now). This justifies at least 2 IN-parameters for a procedure to be built, starting moment and duration of the appointment. Another parameter will be used to return the answer. This answer must be of a textual character (!), saying something along the lines of “Your appointment was successfully entered into your agenda”, or “Your planned appointment is overlapping with another”. Allow for at least enough characters to hold this message, as an OUT-parameter in your procedure. The procedure could look something like this…
Note the use of the parameters at the top and what purpose they serve. The OUT parameter is populated with an informative message that will appear in the form that we still have to build.
Build the page
The next step would be to build the page. It is obvious that you need to test in some way that the procedure works. Therefore relevant tables and reference data must be available.
- Move to the relevant application and create your page. Therefore press the yellow Create page button.
- Then select Form and press Next.
- Then select Form on a procedure and press Next.
- You are prompted to select the schema owner of the procedure to be used. Press Next.
- The next screen enables you to select the relevant procedure from a list of values. What is slightly misleading is that the list of values also displays functions, which in this context are of no use, sadly. Select the required procedure and press Next.
- Then a page displays with 7 entries, of which the compulsory entries are depicted with an asterisk:
- A default page number is displayed. You can modify it, if you have thought up your own page numbering system. It is a compulsory entry.
Define a page name, equally compulsory. - Define a region template. You can leave this as is for now.
- The next entry, region name, is also compulsory. Make this an explanatory piece of text, as it will appear on top of the region.
- The next two entries enable you to enter Submit and Cancel button labels. You may prefer to display labels in another language.
- The final entry enables you to choose to have a breadcrumb displayed at the top of your page. Leave this value as is.
- Press Next.
- You will be presented with a screen that enables you to select a Tab (existent or now) or no tab at all. For now leave this to the default setting, and press Next.
- Next you will see a screen quoting an optional invoking button. This is an extra button that will be created somewhere else in your collection of pages within the application, that enables you to move to the page that we are currently making. The page where you want this button to be created can be defined in the first entry, for which a list of valid values is available.
In the second entry you can simply type the label for this button. Should you leave these 2 entries empty then no such button will be created.
For now we will not do any such thing yet. - Press Next.
- The next screen enables you to define which pages to move to when you either press the Submit or Cancel buttons. Please define two pages, making use of the offered list of values. You may even decide to use the same page for the two different buttons. Press Next.
- Now you are presented with a screen that makes use of the parameters that were defined for the procedure involved.
- Here you can see quoted the 3 names of the parameters, for which you can modify the labels (the names that appear in the form yet to be generated). Names in the example start with ‘D’, like the names of the parameters themselves. The 3 settings Include should be left set to Yes. In the Default entry you can set default values. The values to be entered depend on the technical functionality of the procedure. If the procedure takes care of default values then the application does not need to do so, and vice versa. Note that in this scenario dates of data type Date are involved, being D_START_DATUM. For all 3 items the Display Type may be kept to Text.
- Please note that the D_BERICHT parameter according to the procedure will contain a message that can be used in the application.
Press Next. - You will be presented with a Form on Procedure confirmation. Press Finish.
- You will be given the options of Running or Editing the page. We are so anxious now, so press Run Page.
Something like this will be presented to you:
A few more points need to be addressed before the form will actually work properly:
- This is hardly a form that you can present to a user. To at least make it a bit more presentable give the user a calendar display type (this would have to include the time, not just the date) when dates need to be entered.
- A default value for the minutes (this is the number of minutes that an appointment would take) could be set at 60 or 90.
- The D_MESSAGE can be left empty. The procedure should use this to fill in an appropriate message.
- The previous warning in this text saying that we are dealing with data type DATE (namely D_START_DATUM) we will hit upon now. A change in one of the processes is mandatory for this form to work.
- Finally the names of the fields and the forms need to be modified.
To address these issues we need to do the following:
-
Press Edit Page xx, in my particular case it was page 13. A screen very similar to this will appear:
- In the Page Rendering box: Right click on P13_START_DATUM.
- Press Edit.
- In the Name box, set Display as to Date Picker.
- In the Label box, adjust the Label, by removing the trailing D.
- In the Settings box, set Format Mask to DD-MON-YYYY HH24:MI (use the list of values).
- Press Apply changes in the top right of the screen.
- Similar steps for the Minutes entry can be executed to check that there is a default value defined.
- When you are back in the screen where Page Rendering is at the top, right click on Run Stored Procedure underneath the Page Processing header and select Edit.
- Modify the code as follows, noting that it is a call to the previously defined procedure, which needs to be adjusted so that the data (namely data of type DATE)is dealt with properly:
- Press Apply Changes.
- Press Run Page, and check that the functionality is correct.
#OWNER#.P_ENTER_PLANDATE(
D_START_DATUM => TO_DATE(:P13_START_DATUM, 'DD-MON-YYYY HH24:MI'),
D_MINUTEN => :P13_MINUTEN,
D_BERICHT => :P13_BERICHT);
Conclusion
We can conclude that there are many things in Apex that can be realised, but sometimes Apex needs some help from us. The modification in the code that Apex made for us was not sufficient for it to work properly. When building code you will find more often that you are reminded of the fact that you are actually trying to build an application for the internet, and that browsers don’t really take data types of type date (and probably others also) into consideration.
The text in this blog is by no means complete. It is merely trying to inspire people to use – in this case – forms for a procedure. Please do not hesitate to leave any comments, feedback or additions to the text offered to you. Thank you for your attention.
