Posts tagged plsql
How to use an outerjoin to synchronise data
0There 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. (more…)
Create an Excel-file with PL/SQL
78For 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.
Anton
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
No JDBC based data retrieval in Java applications – RESTstyle, JSON formatted HTTP based interaction from Java to Database
1This 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
3Introduction 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 More >
AS_PDF, generating a PDF-document with some plsql
87I’ve written a small package (1500 lines). But with this package you can generate a PDF-document with a few lines of PL/SQL code. It’s small because it lacks some functionality. It can only use the standard PDF fonts, and that means that it can only use the WINDOWS-1252 encoding/characterset. But besides that it’s fairly complete. (more…)
PL/SQL (and APEX) highlight of the year: OPP Conference in Brussels, 27-28th October
0PL/SQL Developer Tools Session Brussels (Belgium) will be the PL/SQL Hotspot of the world during the two-day OPP 2010 (ODTUG’s Oracle PL/SQL Programming) conference – that is co-located with the APEXposed 2010 conference. These two conferences offer a wonderful opportunity for both intermediate and advanced PL/SQL and APEX programmers to learn, get inspired and meet with fellow enthousiasts and peers from all over Europe. Many of the best known names – forum-gurus, authors of books, blogs and articles and presenters – gather in Brussels to share their experiences, their ventures into uncharted territory, their best practices and most of all, their passion for the technology. More information on these two conferences is found at: http://www.odtugopp.com/agendabrussels.html . Note: the early bird registration ends on October 15th, so hurry up to get the best deal!
On the program for the OPP conference: AMIS will contribute six presentations delivered by four different presenters (three of whom happen to be Oracle ACE Directors):
Patrick Barel:
PL/SQL Developer Tools Session
Marco Gralike:
XML Programming in PL/SQL (Part 1) XML Programming in PL/SQL (Part 2)
Lucas Jellema:
Recent Comments