I was looking at the Apex 4.2.2 installation script (coreins.sql), and noticed the mention of a new package, wwv_flow_json, which is apparently being worked on but was removed at the last minute "as no longer required for 4.2.2".
Even though the package is not installed
(more...)
The
Thoth Gateway is a gateway written in C# and ASP.NET that allows you to run PL/SQL web applications (including Oracle Application Express) on Microsoft's Internet Information Server (IIS). You can read
more about the gateway here. It is an alternative to Apache/mod_plsql and the Java-based Apex Listener.
For
(more...)
The latest version of
Alexandria, the utility library for PL/SQL, is now
available for download. The previous version has been downloaded more than 2,000 times in the last 8 months.
The
docs/readme.txt file contains a fairly detailed list of additions, enhancements and bug fixes. Below are some of the more noteworthy changes:
Improved installation script
You can now choose between installing the full library (close to 50 packages as of this version), by running the main
/setup/install.sql script, or you can install just the core set of packages via
setup/install_core.sql and then choose additional sets of
(more...)
Many companies tend to have mixed environments, with Oracle databases (and PL/SQL) running business-critical back-office systems, as well as Microsoft products for front-office applications such as email, word processing and collaboration/file sharing (ie Outlook, Office and Sharepoint), and of course the desktop operating system itself (Windows, with Internet Explorer, Internet Information Server, all integrated via Active Directory).
Working in these mixed environments means that you, as an Oracle PL/SQL developer, frequently need to work/integrate with various Microsoft technologies from PL/SQL.
Over the last couple of years, I've written a number of blog posts on this topic. This post is just
(more...)
Here is a Dynamic Action plugin for Oracle Application Express (Apex) that lets you execute PL/SQL code in the database, and then send output generated on the server (via calls to the "HTP.P" procedure) back to any element on the web page.
A more specific use case would be to respond to the clicking of a button by generating a fragment of HTML using PL/SQL, and then refresh a DIV on the page with that dynamically generated content. See the screenshot below for an example; notice that part of the first region has been updated with content generated on the server
(more...)
In
my last blog post I described using the DBMS_DATAPUMP and DBMS_JOB packages to automate database schema backups from the database itself, rather than using batch files to do so. I also mentioned that
"since the files are written to the database server itself, you need some kind of process to move the files to another server or storage location, so the backups don't disappear along with the database if the database server itself is somehow lost or corrupted. [One] option is to upload the backup file from the database to some online ("cloud") storage service, such as Amazon S3. (more...)
Usually, database backups are the responsibility of DBAs, and not something that developers really care or think too much about. However, if you are a lone developer, or part of a small team that doesn't have the luxury of having a dedicated DBA, then you might have to deal with backups yourself.
There are several ways to make Oracle backups; what I will be concentrating on here is the "Data Pump Export" method. You may already be familiar with the command-line "expdp" command which allows you to create a dump (.dmp) file containing your database objects (schemas, tables, procedures,
(more...)
As mentioned in
my earlier post, I've been working on a PL/SQL wrapper for the Microsoft Exchange Web Services (EWS) API. The code is now ready for an initial release!
Features
Using this pure PL/SQL package, you will be able not just to search for and retrieve emails and download attachments, but you will also be able to create emails and upload attachments to existing emails. You can move emails between folders, and delete emails. You can read and create calendar items. You can get the email addresses of the people in a distribution (mailing) list, and more.
Prerequisites
"It is sayd, that at the nede the frende is knowen."
- William Caxton: "Sonnes of Aymon" (1489)
"I say, that when you screw up, Flashback Query is a friend indeed!"
- Morten Braten: "SQLs of Oracle" (2012)
The
Flashback Query feature of Oracle (10g) can be really helpful when you run that UPDATE or DELETE statement and then,
after the COMMIT, you discover that you made a mistake!
Wouldn't it be nice to be able to look back and see what the data looked like before you messed things up? With a Flashback Query you
(more...)
It's been over two years since I released
the initial version of the jQGrid Integration Kit for PL/SQL and Apex.
Since then, I've added some additional features and fixed a few bugs:
Version 1.1
- Procedure include_static_files_once will make sure required JS and CSS files are correctly included
- Support for "popup_lov" column edit type
- Procedure render_grid supports p_read_only parameter that can be used to set read-only dynamically
- Support for some jQGrid built-in formatters based on Oracle column data type
- Lists of values (select list variety, not Popup LOV) now support bind variables and Static LOVs
- Date parser handles dates and (more...)
... or how to read your (Microsoft Exchange) email using SQL and PL/SQL.
Introduction
You are probably familiar with
sending email using PL/SQL; there's the UTL_SMTP package, the APEX_MAIL package, and various other utilities that do this.
But what about
reading email using PL/SQL? You need to investigate the POP and IMAP protocols. A quick search on Google turns up
some sample code and even a
feature-rich package, although the latter requires Java in the database (which I try to avoid whenever possible, and which is not available in Oracle XE in any case).
Approaching this from a different angle,
(more...)
Here's a bunch of interesting stuff related to PL/SQL and Apex that I've come across lately, I'm posting it both as a reminder to myself, and to help spread the word:
- First mention of PL/SQL enhancements for Oracle Database 12c: In his PL/SQL newsletter for March 2012, Steven Feuerstein revealed that "I've also started beta testing Oracle Database 12c PL/SQL. I can't yet share with you any of the new features, but I can tell you that PL/SQL will certainly be a richer, more usable language when 12c goes production!".
- On that topic, a couple of things I (more...)
Today I'm taking a look at the core packages of the PL/SQL language, namely the
STANDARD and
DBMS_STANDARD packages in the SYS schema. These packages contain a lot of the language features we use every day in PL/SQL programming.
These two packages are special in that you do not need to qualify the name of functions, procedures and constants in these packages with the package name. For example, SYSDATE and USER and DECODE and so on are declared in the STANDARD package, but you can use it without having to specify STANDARD.SYSDATE or STANDARD.USER or STANDARD.DECODE (which
(more...)
I see this question being asked now and then:
Is it possible to set up my Oracle Application Express (Apex) application with Single Signon (SSO) based on Windows Integrated Security (NTLM), so that the end-users are automatically logged into the Apex application with their Windows (Active Directory) account without entering their username and password?
The answer is yes, but the implementation depends on your infrastructure, and specifically your web server:
- If you are using Apache (Oracle HTTP Server), the Embedded PL/SQL Gateway (DBMS_EPG) or the Apex Listener, then this post is not for you. There are various options such as (more...)
There is a new version of the
Thoth Gateway, a mod_plsql replacement for IIS, available for download. The latest version is 1.3.6.
The previous version available for download was 1.3.0, so there are a number of enhancements and bug fixes rolled up into this release, including:
Version 1.3.6Bug fix: Value of the CGI environment variable LOGON_USER was empty on IIS 7 running in Integrated pipeline mode: This was due to a
breaking change on IIS 7. The Thoth Gateway now hooks into the PostAuthenticate event, rather than
(more...)
NTLM, or more properly
NTLMSSP is a protocol used on Microsoft Windows system as part of the so-called
Integrated Windows Authentication.
Integrated Windows Authentication is also known as HTTP Negotiate authentication, NT Authentication, NTLM Authentication, Domain authentication, Windows Integrated Authentication, Windows NT Challenge/Response authentication, or simply Windows Authentication.
In Microsoft Internet Information Server (IIS), the system administrator can protect a website or folder with "Integrated Windows Authentication". When you browse to this website or folder, you must enter your Windows (domain) username and password to get access (although Internet Explorer will, depending on your security settings, send your credentials
(more...)
I've just uploaded a
new version of the
Alexandria Utility Library for PL/SQL.
Updates include both small bug fixes and some major new features (which I'll return to in another post).
Among the improvements are:
- Additional functions in OOXML_UTIL_PKG for working with Excel 2007 and Powerpoint 2007 files.
- Kris Scorup has contributed improved CSV parsing to the CSV_UTIL_PKG. It now handles double quotes and separator characters inside strings.
- Anton Scheffer's packages for building PDF and XLSX files have been included in the library.
- The PL_FPDF library by Pierre-Gilles Levallois is a port of the FPDF library for PHP. Pierre-Gilles Levallois (more...)
Not really Oracle-related, but I'm posting this as a reminder to myself and possibly useful to others.
To count the number of lines in a given set of files using the Windows command prompt, do the following:
for %G in (*.sql) do find /c /v "_+_" %GThis invokes the "find" command once for each file, counting the lines that do NOT contain the string "_+_" (the string has no special significance, any weird string that would not occur "naturally" in the files can be used).
There are probably more sophisticated ways of doing this, perhaps using PowerShell and
(more...)
These days, the use of database stored procedures is
regarded by many as a bad practice.
Those that dislike stored procedures tend to regard them as incompatible with
the three-tier architecture:
By breaking up an application into tiers, developers only have to modify or add a specific layer, rather than have to rewrite the entire application over. There should be a presentation tier, a business or data access tier, and a data tier.
This is illustrated as follows:
Note that the "tiers" in the figure should actually be labelled "layers", for as the accompanying Wikipedia article says:
The concepts (more...)
The current Apex
Statement of Direction for Apex 4.1 states that it will "
include themes and HTML templates suitable for smart phones and mobile devices".
If you are wondering what that means, then check out
this thread on the Apex OTN Forum where Marc Sewtz, one of the developers on the Apex team, provides more details about this new feature.
Interestingly, some of these "mobile-enabling" features are also relevant for regular applications, such as the ability to render a form without a table grid, enhanced label templates, and dynamic (SQL-based) lists.
RSS