I have coded in PL/SQL for around 29 years and it is one of my favourite languages along with C. I also code in Lua more and .net almost daily but PL/SQL and C are my favourite languages.
PL/SQL is well documented to be based on ADA. If you look at ADA you can easily see that PL/SQL has some ADA features missing and of course SQL added.
I was remembering CFront the first C++ compiler. This was not a compiler in the normal sense as it read the C++ user program source and converted it into native C to be complied by the C compiler available on the machine. The CFront compiler was also written in C++ so had a strange bootstrap mechanism to allow some pre-compiled C libraries to be used when CFront was first compiled from C++ on a new machine. This C++ compiler had lexers, AST and parsers etc but it output C code not assembler or native binaries for the machine. CFront died many many years ago at the start of the 90s because it was hard to add more and more new language features and syntax; well not really died as it is still available to download if you wanted to. Actual proper C++ compilers were written before CFront stopped being the de-facto C++ compiler. The C++ language at the time that CFront was used was much simpler than C++ now.
If we look at the PL/SQL DIANA nodes that are available to see in the dictionary code shipped with the database even in 23c / 23ai we can still see DIANA nodes for ADA features that are not in PL/SQL.
The idea of CFront got me thinking. I have talked about and even mentioned here a few times over the years how it would be nice to have some other features in the PL/SQL language. Oracle are not going to add new features to PL/SQL just because we ask them to but could we add new features ourselves if we wanted to.
I started coding C for Oracle a very long time ago and coding with the OCI libraries (the original OCI not the cloud!!) and I also coded in Pro*C where SQL was embedded with some extra syntax into C programs that were then pre-processed and generated a complete C program with shipped Oracle libraries that allowed the C+SQL to work. Pro*C was a good example as a C programmer could write C and embed SQL where they needed database access rather than use C APIs direct. This made the original source easier to read and understand. The original source could not be compiled direct to a binary and had to be pre-processed into C that could be compiled to a binary along with linking Oracles libraries.
APEX is not the same idea that I am talking about above but is similar. You choose, add meta data, write code snippets and a complete application is created and runs in the APEX environment
This thought experiment would allow us to add:
- language syntax: We could add a syntax "++" to a variable, i.e. instead of writing i:=i+1 we could write i++ as we can in C. I have shown an example of this in the past as a function on this blog
- Security: We could add security features to the PL/SQL code that is written as a kind of place holder. This means the developer can say "create or replace procedure dummy is begin --++DOSEC" where we say make this a secure procedure BUT the security code is added after development but before deployment. This means the developer can identity procedures that are important and need to be secured BUT even the developer doesn't get to see the security code and its added and managed by the security team. We do this now for customers
- New Features: Imagine that want multi-threaded PL/SQL and rather than write complex code to use jobs and implement mechanisms to do sync/lock, semaphores and more it would be better to use simple language features to define and add threads rather than use a complex library directly. We can do this now for instance by automatically adding license code
- Productivity: If we write pattern code, i.e. we have 600 database tables and each table needs red, write, update, delete PL/SQL code. We can just add syntax to PL/SQL that states this, i.e. "create or replace procedure dummy as {{uses access 'where clause' table schema.table}}...". The code would be managed and generated and can then be used in the procedure without the developer writing it specifically
- Template: We can also use templates in a similar way like inheritance in C++ or maybe as interfaces in vb.net for repeated code or slightly modified code
- Insecure or not supported : If for instance your code uses DBMS_JOB you should change to DBMS_SCHEDULER. This change is not just a find and replace in an editor - and this is just one example - the change may require some code rewrite as well. We help customers do this now
All of these examples are possible to do by taking new syntax and creating valid PL/SQL in the same way Pro*C creates valid C or CFront creates valid C from C++
The CFront or Pro*C approach could easily be used in PL/SQL using any simple method to process PL/SQL with extra syntax to produce real PL/SQL to compile
We could also change the language so that {} becomes begin/end or as we said i++ becomes i:=i+1 and so on. For fun!!
We are also not limited to PL/SQL, we could do the same ideas on Lua, VB.NET, c#.NET and many more. We do all of these things above now for PL/SQL except adding new syntax but we could do that now as well if needed using our same methods. We do these things for security reasons in most cases in our PL/SQL code using tools we have developed.
#oracleace #sym_42 #oracle #database #plsql #code #securecode #apex #23ai #security
