The last few weeks I’ve made quite a few improvements to my TAPI generator which I thought I’d share. I’ve also added an Apex API generator which generates code suitable for interfacing between simple Apex applications and my TAPIs. This reduces the volume of PL/SQL required within Apex to a bare minimum.
Templates are now defined in a package spec, so they are easier to edit in a tool with syntax highlighting (more or less)
Whenever you use PL/SQL in SQL statements, the Oracle engine needs to switch from doing SQL to doing PL/SQL, and switch back after it is done. Generally, this is called a “context switch”. This is an example of that:
-- A function that uses PL/SQL
create or replace function add_one( value number ) return number is
l_value number(10):= value;
-- A SQL statement that uses the PL/SQL function
select sum(add_one(id)) (more...)
A few days ago I was introduced (or re-introduced) to USERENV(‘COMMITSCN’) by Jonathan Lewis. This is an internal function that allows limited access to the SCN of your transaction.
I was trying to find a way to get the actual commit SCN easily as it struck me that Oracle would have it to hand somewhere and it would be unique to the change and generated very efficiently. I could not find anything to do it (more...)
As I said in my earlier post about Oracle Week 2015, this is one of the biggest database related educational (non-sale oriented) convention in Israel. None of the session is designed for selling any products (but consultant use it to showcase their abilities hoping to sell some professional services :)). Yesterday was my second daylong session in this convention this year . This time we talked about advanced features in PL/SQL and some new features of (more...)
Sayan Malakshinov hat zuletzt zweiArtikel veröffentlicht, in denen er sein GitHub Package XT_HTTP vorstellt, mit dessen Hilfe man auf HTTP- bzw. HTTPS-Seiten zugreifen kann, ohne Zertifikate importieren zu müssen. Die aktuelle Version enthält einen ergänzenden Timeout-Parameter, eine Suchoption auf Basis von regulären Ausdrücken (PCRE) und den Support für plsqldoc (ein Hilfsmittel zur automatischen Generierung von Dokumentation; ähnlich wie javadoc). Sicher sehr nützlich für entsprechende Fragestellungen.
Der Artikel hat noch den zusätzlichen Pluspunkt, dass er (more...)
Timeout parameter – it would be better to control connection time;
Simple page parsing with PCRE regular expressions – to speed up and simplify page parsing, because if you want to get big number matched expressions from CLOB with regexp_xxx oracle functions, you have to call these functions many times with different [occurance] parameters, passing/accessing to the (more...)
I don’t like to import certificates, so i cannot use httpuritype for HTTPS pages and I decided to create package which will work with https as http.
It was pretty easy with java stored procedures github/XT_HTTP
java source: xt_http.jsp
create or replace and compile java source named xt_http as
Often, the biggest problem with regular expressions is that those who use them sometimes don’t use them correctly. A great example occurs in the Oracle Database with the REGEXP_LIKE function. For example, some developer use the following to validate whether a string is a number but it only validates whether the first character is a number.
Do you need to send SMS (Short Message Service) text messages from your Oracle database using PL/SQL? This is actually quite easy to do, the only hard part is deciding on which SMS gateway to use. You need to sign up with a gateway provider to get a username and password to use the gateway, and you typically pay some cents for each message you send.
Using Oracle’s Resource Manager requires you to understand the IO dynamics. The first step requires you to run the CALIBRATE_IO procedure from the DBMS_RESOURCE_MANAGER package.
Oracle provides some great examples about how to use the CALIBRATE_IO procedure of the DBMS_RESOURCE_MANAGER package in the Oracle Database Database PL/SQL Packages and Types Reference. The CALIBRATE_IO procedure returns the best answer when you provide a valid number of files, which you can capture by querying the V$ASM_DISK view.
Using an Oracle object type’s instance in a query is a powerful capability. Unfortunately, Oracle’s SQL syntax doesn’t make it immediately obvious how to do it. Most get far enough to put it in a runtime view (a subquery in the from clause), but then they get errors like this:
ERROR AT line 4:
ORA-00904: "INSTANCE"."GET_TYPE": invalid identifier
This is an alternative to if… then… else… elsif… end if when you want to use conditional statements in PL/SQL. Here is a simple example, which checks whether a given number is 1 or 2. You start with the word case. Then you add one or more conditions followed by the action to take if that condition is satisfied. Each condition is preceded by the word when. You finish with the words end case:
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.