RESTful Deployment

I have been an enthusiastic APEX fan for years. And like every APEX fan, I also have a workspace on APEX apex.oracle.com. Here Oracle offers the possibility to get to know APEX and to host small demo projects. You can switch and manage within your workspace as you like. What Oracle does not offer here, however, is external access to the database / schema with an IDE or SQLPlus. Of course, otherwise Oracle would have to publish the port of the DB listener.

Recently I read an article by Peter Raganitsch. Link: http://www.oracle-and-apex.com/streaming-flat-file-data-into-database

Here he describes how to upload and evaluate log files via RESTful Service. When I read that, I had an idea. Why shouldn’t this also work with my source code?

And what shall I say? It works! With my current favorite editor, Sublime Text 3, I can now send my source code for all kinds of stored procedures (triggers, procedures, functions, and packages) to my workspace, which is somewhere in the cloud, for example, at apex.oracle.com and have it compiled. But how does it all work?

First we create a RESTful service. In the SQL-Workshop we go to RESTful Services and click on Create. Now we define a RESTful service module.

Then we define a resource template.

Finally, we define a resource handler.

This resource handler is filled with the following lines as source.

Declare v_source_code clob := rtrim(wwv_flow_utilities.blob_to_clob(:body),'/'); v_err_found boolean := false; v_obj_name varchar2(1000) := regexp_substr(lower(dbms_lob.substr(v_source_code, 32000, 1)), 'create or replace (function|procedure|package body|package|trigger) ([^ (]*)', 1, 1, null, 2); Begin -- Prepare Header / Output owa_util.mime_header('text/plain', true); htp.p(''); -- empty -- Execute / Compile Code execute immediate v_source_code; -- Everything is fine, let the user know it htp.p('Result: success'); :status := 200; Exception when others then -- Something went wrong htp.p('Result: failure'); htp.p('Object: ' || v_obj_name); htp.p('Error: ' || sqlerrm); -- Maybe we get the object out of source for cur in (select rownum idx, line, position, text from user_errors where name = upper(v_obj_name) order by sequence) loop -- FirstRow, let's print heading if cur.idx = 1 then htp.p(' LINE | POSITION | TEXT'); v_err_found := true; end if; -- Print Message htp.p(lpad(cur.line, 7, ' ')||' | '||lpad(cur.position, 8, ' ') ||' | '||cur.text); end loop; if not v_err_found then htp.p(dbms_utility.format_error_backtrace); htp.p(v_source_code); end if; :status := 400; End; 

Basically, these few lines are only about the fact that the content of the file is executed by “Execute Immediate”. If an error occurs, the system tries to determine the DB object and displays the corresponding error. Now all we have to do is upload our source code. I chose curl at this point. But I also think that wget should work. With the following command, we load the contents of the file my_stored_procedure. sql into the workspace my_workspace_name and run it there.

curl -X POST \ --header "Content-Type:text/xml;charset=UTF-8" \ --data-binary @my_stored_procedure.sql \ https://apex.oracle.com/pls/apex/my_workspace_name/deploy/compile/ 

To directly upload and compile the source code with Sublime Text 3, we create a batch file that is used by the build system of Sublime Text, for example as D: \my_rest_deploy. bat.

@echo off REM -- For information only echo File: %2 echo Path: %1 echo Url: %3 echo REM -- Change to the directory where the sublime file is cd %1 REM -- Deploy to https://apex.oracle.com/pls/apex/my_workspace_name/deploy/compile/ curl -X POST --header "Content-Type:text/xml;charset=UTF-8" --data-binary @%2 %3 

Then we create a new build system with the following content in Sublime Text under “Tools / Build System / New Build System…”

{ "cmd":["D:/my_rest_deploy.bat", // Script "$file_path", // %1 "$file_name", // %2 "https://apex.oracle.com/pls/apex/die21/deploy/compile/" // %3 = RestURL ], "selector": "source.plsql.oracle", "shell":"true" } 

And if I now edit my code and have chosen the appropriate build system, a simple Ctrl+B is enough to bring my package or whatever into the cloud. This is very useful if you want to write a skill for ALEXA, for example, but can only access the DB via a web interface.

The solution is not perfect and you should never do this on a productive environment. Whoever knows this URL is in the worst case master of your database!

At this point I would like to thank Peterand KrisRice, who brought me to this idea…