I do realize that for most of you, there may be nothing new about the dbms_sys_sql package – knowledge of it has been floating around for quite a while. I myself discovered this package a couple of years ago while playing around with HTMLDB’s (now APEX’s) internals. I’m posting this as a response to a vox populi demand: I often see people who could benefit from dbms_sys_sql, if they only knew about it.

Sometimes you need to execute SQL (or PL/SQL) as some other user. Say you need to remove a job residing in some other user’s schema. Or you need to create a database link in a different schema. You can’t do things like these without becoming the other user. If you don’t know the other user’s password then you basically have two choices: (more…)