Rounding amounts may lead to rounding-issues, I have written how this may be resolved in a previous blogpost using some analytic functions. Playing around a little bit, I also came up with a method to resolve the rounding difference with the MODEL clause.
To create an example, first let's create a table with only three records in it.
SQL> create table t 2 as 3 select rownum + 42 id 4 from dual 5 connect (more...)
Dividing a certain amount over several rows can be quite tricky, simply rounding can lead to differences. Let me try to explain what I mean. When you need to divide 100 by 3, the answer is 33.333333333333 (and a lot more threes). Money only goes to cents, so if each one gets 33.33, there is a cent missing. (3 times 33.33 equals 99.99) To solve this cent-problem, we decide that the (more...)
This is not intended as an introduction to PL/SQL, but rather an overview of features you should consider if you want to maximize your enjoyment of programming in the Oracle Database. Also, this is not an exhaustive list of all features, it's simply the ones I personally use frequently.
I ran into a problem the other day when moving some code from one database to another (both XE 11g). The code in question needs to convert an XMLType to a CLOB to do some (hacky) string manipulation on it, and then turn it back to an XMLType.
Here's the original code:
l_xml := apex_web_service.make_request(...);
-- little hack to remove bad empty namespace from result l_clob := l_xml.getClobVal(); (more...)
With Conditional Compilation you can create a single code base to handle different functionalities depending on compiler flags. The latest addition to LoggerUtil was a method to create a custom template. For this to work, LoggerUtil depends on a certain Logger Release (where (more...)
Virtual Columns are really cool. I like them a lot. If you've never heard of them, shame on you, learn about them. In short: a Virtual Column is not a real column, it's an expression that looks like a column... more or less. While using the Virtual Columns, we ran into a little oddity with them.
First of all let's start with the version of the database that I tested this on. Yes, I know (more...)
Since I have written about my pet project about a month ago, I have made some major changes to the functionality of it. If you haven't read that blog about my pet project, here's the synopsis:
I love Logger to instrument my code, I just don't like to type in all the bits and pieces to register all the input arguments when I write a new procedure or function. To solve this problem I have (more...)
The article read most often on this blog is called "Splitting a Comma Delimited String, the RegExp way". On this blog there are two articles about this technique, one is about splitting up a single string into multiple rows and the other is about multiple strings into multiple rows. Links to both articles are included at the bottom of this article. It seems like there is a need for functionality like that frequently. And just (more...)
To refresh multiple Materialized Views you can use the DBMS_MVIEW package, with the procedure aptly named Refresh. One method is to provide a comma-separated list of Materialized View names, or you can use an DBMS_UTILITY.UNCL_ARRAY to achieve the same objective. When using the latter method, I stumbled upon this oddity (which is not in the current documentation, or at least I couldn't find it).
The procedure that I initially wrote was the following:
Instrumentation of PL/SQL code is essential. My favourite tool to instrument PL/SQL is Logger. Martin Giffy D'Souza wrote a few blogs on how to speed up development with Logger and I want to chime in with my own productivity booster. What I have written is a PL/SQL package that will generate a Procedure or Function body with all the calls to Logger. This includes all the arguments, or at least the IN and IN/OUT arguments. (more...)
In this post I will try to show you how I used the Oracle Apex and the APEX_WEB_SERVICE PL/SQL package to quickly send a request to a public Internet API and how I handled the response. The code below was written during a 'Hackday' and hasn't been extensively tested.
My use case is integrating Oracle Apex with the public Mendeley REST API for Mendeley Catalog Search.
Recently someone asked me: "How did you do that? When I include an APEX page in a Page Group, the correct tab is automatically highlighted" When I setup an application, I usually use Dimitri Gielis' method, so instead of using "real tabs", I use a List and display that list as Tabs. For each of the "Tabs", I also create Page Groups, just to keep things organized.
It seems impossible to raise an exception when handling no_data_needed.
create or replace function demo return sys.odciNumberList pipelined
exception when others then
dbms_output.put_line('in exception handler, sqlcode: ' || sqlcode);
firstname.lastname@example.org > select * from table(demo());
ORA-06501: PL/SQL: program error
ORA-06512: at "SOKRATES.DEMO", line 8
ORA-01476: divisor is equal to zero
no rows selected
in exception handler, sqlcode: -1476
If you want to be able to create contemporary high-performing PL/SQL code, welcome to my training event in NYC (hosted by NYOUG) on November 7, 2014: http://nyoug.org/event/nyoug-fall-2014-training-event-high-performance-plsql/?instance_id=258 I think, it is as important as ever to follow best practices for server-side development. There are lots of options that could get the job done "now", but very few that would survive for months/years. Also, there is a constant pressure from various "alternative" solutions to (more...)
This book took me much longer to write than I initially anticipated - and to be fair, that's the reason for staying quiet at the blog. I just didn't have time/energy to write everywhere :-) Although, it was fun writing - (more...)
Sorry for staying quiet for some time, but for some good reasons. Some of these reasons will be explained in later posts, but for now I would like to share the story I wrote upon request of Steven Feuerstein, who asked to tell how PL/SQL impacted our life. Here is my story:
I started to work with databases already in college, but more from the data modelling/business analysis angle. When I moved to (more...)
I agree with Steven Feuerstein’s review that “if you write PL/SQL or are responsible for tuning the PL/SQL code written by someone else, this book will give you a broader, deeper set of tools with which to achieve PL/SQL success”.
The UTL_FILE database package is used to read from and write to operating system directories and files. By default, PUBLIC is granted execute permission on UTL_FILE. Therefore, any database account may read from and write to files in the directories specified in the UTL_FILE_DIR database initialization parameter [...] Security considerations with UTL_FILE can be mitigated by removing all directories from UTL_FILE_DIR and using the Directory functionality instead.