PL/SQL can be used to write a compiler for an existing language such as C, BASIC or even PL/SQL itself. That compiler would produce output (a binary) that needs to be executed. This could be by generating Assembler or an actual binary for an Intel or AMD process or indeed any other processor.
That binary could also be executed by the host or in a VM (written in PL/SQL). That VM could run any machine language we make up as it’s a VM. So, we could with a lot of effort use PL/SQL to achieve any of the following:
- Write an interpreter for a script or other language (C, BASIC, PL/SQL) in PL/SQL
- Write a compiler in PL/SQL to generate ASM (native or custom ASM)
- Write a compiler in PL/SQL to generate a binary for a native CPU (Intel, AMD) or a custom VM
- Write an assembler in PL/SQL to convert ASM to binary either native or for a custom VM
- Write a VM in PL/SQL to run the binary generated above
A lot of the ideas that are listed above fall into the compiler or interpreter or run time category. All of these could be written in PL/SQL if needed.
Why would we want to add a scripting language to an application written in PL/SQL? There are a number of possible reasons. The first reason is always “because we can, because it's fun!!”.
We do exactly this though and add scripting languages in our products that are written in C and .NET. We have added many scripting languages to PFCLScan (PFCLScript, Lua) and also added Lua to PFCLObfuscate, PFCLCode, PFCLUserRights and more. The reason we do this is to allow the end user to extend our applications and in our case this means writing checks and policies and rules in Lua. We extended Lua to allow access to our internal functions so that the script language is more useful in our environment. This means our customers can program our products using Lua without us needing to do anything to the core source code of course.
We have to ask the question; why would you simply not extend with a scripting language a PL/SQL application with PL/SQL?
Obviously, we can use PL/SQL as a dynamic script language in an application and that would be the simplest solution. We can pass in dynamic PL/SQL to our PL/SQL application and then have an interface that executes it. This is dangerous as we would also need an interface to limit syntax or objects that are not allowed. We also we would need to lock down permissions to ensure that if stray PL/SQL is entered then it cannot escape and cause security violations. We also would need to prevent possible SQL injection or any other security issue.
One of the biggest issues is that an access point that allows any dynamic PL/SQL or SQL can be abused and its hard to limit it properly. One design would be to create a separate schema to allow the access and then limit severely what this schema can do to application data and objects BUT allowing the kind of dynamic customization that you need. One issue is access to any other database objects outside of the schema. If any other application schemas, DBA’s developers or of course default schemas expose objects via PUBLIC then the writer of the dynamic PL/SQL could attempt to access these features/functions/objects via your dynamic PL/SQL script interface.
In simple terms if we can limit these problems of using PL/SQL in a customization / script interface then we are good to go. Of course, adding a completely different scripting language that is written in PL/SQL would solve the issue as we can limit the access from the scripting language to the host PL/SQL interface.
This is what we will do.
To add a scripting language, we need to decide a few things:
- What language will the script language use (an existing one such as PL/SQL or BASIC or a custom simple language)
- Do we write an interpreter or a compiler (compiler, assembler, VM)
- What do we expose via the language of the host application
If we use an interpreter, then we are still too close to the PL/SQL host. But its simpler to implement as there are less layers.
If we write a compiler, then it is more complex as there are more layers BUT the separation from the host PL/SQL application is much greater. Writing a compiler, Assembler and VM is more fun though so we will look at that.
The steps needed are:
Simple high-level language ==>
Compile high-level language into assembler ==>
Assemble the ASM into binary ==>
Execute the binary in a virtual machine
All of the above steps can be written in PL/SQL of course and this is what we will look at in part 2 in more details.
Watch out for Part 2 coming soon, subscribe to our social channels to make sure that you do not miss it
