Posts tagged rdbms
Oracle RDBMS 10GR1: solution to avoid character encoding in XML with UPDATEXML
On a recent project, I ran into a problem with an XML document, that had to be enclosed within another XML document, generated from a database query. The problem I ran into was the character encoding of the XMLElement function, which eventually was worked around with UPDATEXML.
In this blog post I would like to share with you several attempts to solve this problem, why they failed and the final solution to the problem.
Printing of official documents happened in a separate printing module. Whenever a document needed to be printed, an XML document was generated in a database query and sent to the printing module. Most of the data came from relational database tables, but pieces of standard text were supplied as XML documents by another party… and saved in our database in an XMLType column based on CLOB storage.
Our printing module expected to find these standard text XML documents within a tag in our generated XML document. Also both XML documents, our generated XML and the supplied XML, had different namespaces, this needed to maintained this way. The Oracle database version we were working with was 10.1.0.5.0.
In this post I will use a simplified version of the real XML More >
Oracle RDBMS 11gR2 – alter or replace user defined types even when there are dependencies
In our series on the 11g R2 release of the Oracle RDBMS, AMIS staff discusses various functions, features an facilities that have been added, improved or extended in this latest release. These articles are the fruit of several months of studying this new release and projecting which new features and changes would be the most useful to us in our daily practice. This article looks at apparently relatively minor changes in functionality that may well have a substantial impact on the usability of Abstract Data Types (ADTs) or user defined (object) types.
Using ADTs or user defined types in the Oracle RDBMS is quite often very useful. Much more useful by the way than many developers realize. The integration for example between SQL and PL/SQL can be streamlined using user defined types in conjunction with TABLE and MULTISET or the BULK COLLECT operations. Presenting a service-style interface from PL/SQL packages is also much easier realized using custom type definitions – easier both for the definition of the package’s "service contract", the consumer of the package (at least the Database Adapter used in the SOA Suite) and the developers implementing the service contract. Such a More >
Oracle RDBMS 11gR2 – LISTAGG – New aggregation operator for creating (comma) delimited strings
In our series on the 11g R2 release of the Oracle RDBMS, AMIS staff discusses various functions, features an facilities that have been added, improved or extended in this latest release. These articles are the fruit of several months of studying this new release and projecting which new features and changes would be the most useful to us in our daily practice. This article introduced the LISTAGG operator, new in 11gR2.
An example usage of LISTAGG:
select deptno , avg(sal) avg_sal , listagg( ename, ',') within group (order by sal) enames from emp group by deptno
Oracle RDBMS 11gR2 – goodbye Connect By or: the end of hierarchical querying as we know it
Many years ago, Oracle basically set the standard in SQL. Whatever was Oracle SQL could be seen as the standard. This has never been absolutely true – ANSIÂ SQL was different from Oracle SQL. In some respects the differences can be traced back to lack of functional richness in the standard. However, in certain areas, Oracle has walked its own path with certain functions and features with the rest of the RDBMS pack following another route. It seems to be as if starting with Oracle RDBMS 9iR2, Oracle has made several important steps towards embracing the ANSIÂ SQL standard syntax, usually while maintaining its own specific flavor of those same functions.
Some examples of Oracle specific syntax that are also available through their ANSIÂ SQLÂ counterpart – that in most instances has even more functionality – and that is almost always the preferred approach going forward:
- outer join syntax: in addition to Oracle’s (+) notation, 9iR2 introduced the left outer join, right outer join and full outer join (like (+) on both ends of the join condition, something that Oracle does not support)
- the Decode function that with 9iR2 can (and should) be replace by the CASE expression
- the to_char More >
DBA 2.0 – how options are really not optional or: "the infinite database"
After Web 2.0, Enterprise 2.0 and Wife 2.0, Oracle introduces the term DBA 2.0. It seems to refer to the DBA of the very near future. The one who can rely on a formidable pal hiding inside the database and taking on many of the boring, repeating tasks as well as advising on some of the tougher challenges when it comes to managing the database. Manageability is one of the focus areas for Oracle in the marketing strategy for the database as well as in the development budget allocation. Lots of time and energy are invested in making the database more manageable and striking the optimal balance between automated management and human decision making.
DBA 2.0 is perhaps also the guy about whom Mark Townsend,Vice President Product Management for Server Technologies, said: "to really screw a system up, you have to be a DBA!". To which he added: (more…)
Recent Comments