SQL, PL/SQL, Oracle

Using package variables in SQL

I often want to use the constants defined in packages also in my SQL. It has in the past never worked and it for the most part still does not. If I have a a package like this to simplify my code for what department it is I’m looking at. This now allows using dept_info.c_accounting in PL/SQL instead of just hardcoding 10 and having to remember what department 10 is. This is how we usually use PL/SQL and especially with […]


Accessing a website from the database

One of the things people want more and more often is to somehow read from a web-resource using a database. It could be to just get the HTML off of a website or access REST via PL/SQL, or grab a JSON-file residing on a public website. No matter which it is we need to declare that it is a website we want the database user to reach. To have a simple example I’ll just show how to read the HTML off of […]


LUHNs algoritm – Three ways with SQL and PL/SQL

Have you encountered LUHNs algorithm? I can almost guarantee it even if you’ve never heard the name before. It is part of all of our lives every single day. It is used to check that various numbers are correctly entered. From ID numbers for persons in Sweden, Greace, and Israel to credit card numbers and IMEI numbers and misc other things. It is a very simple checksum function not intended to be cryptographically secure hash function. Due to the calculation […]


The power of using records in APEX III

In this post I’ll finish up the CRUD implementation using records, procedures and views. This series of blog posts started with this post which was followed by this. At this point we have a working report that links to a form. The report is based on a view and the form is based on a procedure. At this point the form is only loading the record in using a procedure that uses a record in its signature. In this post we’ll […]


The power of using records in APEX

Do you use forms based on procedures in APEX? If not, why not?Do you use records in the procedures you use for forms in APEX? If not, why not? I like procedures and records and with APEX I like them even more. Why you say? Should you not just point forms against tables and let the APEX magic take care of it all? No, I do not think you should. The reason is that I love that feature for prototyping, […]