apex_public_user, flows_020200 and the missing schema user

As you know Oracle Application Express comes with a handy sql command window that allows you to enter queries, create and drop objects as if you were connected with a traditional client.

Whenever you specify an oracle object like a table, a procedure and so on, unless you indicated a different owner, Apex "translates" object names into the equivalent "schema.object" notation, where schema is the current schema of your choosing among those attached to your workspace.

I believe it achieves this simply by issuing

ALTER SESSION SET CURRENT_SCHEMA=schema

just before executing the statement you typed. May be there are other methods to do this, i don't know, but that one should work fairly well as it is common practice.

However i found a little quirk in this approach when it comes to executing certain packaged procedures like CTX_THES.CREATE_THESAURUS because they "inherit" the current user from the actual session and the ALTER SESSION SET CURRENT_SCHEMA does not affect them.

So, if you happen to ask yourself why the thesaurus you've just created has been registered against user FLOWS_020200 instead of your *real* user, now you know why.
How do you know that?
Well, you might easily get the following error in an attempt to drop the thesaurus if you try the operation from a traditional client.

DRG-10016: you must be the owner to modify this object

or this one:

ORA-29902: error in executing ODCIIndexStart() routine
ORA-20000: Oracle Text error: DRG-11702:
thesaurus sample_thes does not exist

The tricky bit is that probably your code will work as long as it gets executed within the scope of Oracle Application Express, but it will fail as soon as you try to execute queries from sqlplus or another traditional client if you authenticated as the schema user for instance.

So, you can easily find out against which user the thesaurus was created by executing the following query:

SELECT * FROM CTX_THESAURI;

Now that we know why, the next task is to find a workaround.

At first i thought to hack the system by submitting a batch job, because i had the impression that batch jobs were run under the actual schema user, but i found out soon that instead of FLOWS_020200 i was finding the thesaurus under APEX_PUBLIC_USER (or HTMLDB_PUBLIC_USER for older versions). So i had just shifted the problem.

Then i had an idea. A very simple one indeed:

CREATE PROCEDURE execute_as_definer (p_ddl in varchar2) AS
BEGIN
EXECUTE IMMEDIATE p_ddl;
END;

Then i ran the following pl/sql block:

begin
EXECUTE_AS_DEFINER('begin CTX_THES.CREATE_THESAURUS(''sample_thes''); end;');
end;

And finally i got the desired result.

Happy hacking.