Posts tagged plsql
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.
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.
For this project I took an Apex-plugin I have written, (IR) Report to Excel (xlsx), and turned it into a PL/SQL package. With this package it’s very easy to create an Excel 2007 file with only a few lines of PL/SQL code.begin as_xlsx.query2sheet( 'select * from dual' ); as_xlsx.save( 'MY_DIR', 'my.xlsx' ); end;
The main purpose for this package is getting data from the database into an Excel file, so I deliberate did not include some Excel functionality, such as formulas, into the package. Excel itself is a far better tool for such things.
The source code for the package: as_xlsx P.S. I have added the possibility to add Comments and MergedCells to the Excel-file P.S.2 And bold/italic fonts P.S.3 Fixed issue with timezones with a regionname P.S.4 Fixed issue with XML-escaping from text P.S.5 Fixed NLS-issue with column width P.S.6 Added p_rgb to get_font P.S.7 Fixed bug in add_string P.S.8 Fixed set_autofilter (only one autofilter per sheet, added _xlnm._FilterDatabase) Added list_validation = drop-down P.S.9 Added freeze_pane
No JDBC based data retrieval in Java applications – RESTstyle, JSON formatted HTTP based interaction from Java to Database1
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).
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.
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 More >