How to disable PL/SQL in Oracle queries

Question

Here's an example of the sort of query I would like to prevent from running on my server:

begin
  While True
  LOOP
     dbms_output.put_line('tst');
  END LOOP;
end

This query (or others like it) may be hitting my Oracle server via the Oracle JDBC thin driver. I would be open to preventing this query from running either at the JDBC configuration level, the database server configuration level, or via user permissions within the schema. I would like the user to continue to be able to run normal select/insert/update/delete queries. Honestly I'd be quite happy if none of the PL/SQL type commands were available, and instead only standard SQL.

Update

I should also mention that I want users to continue to be able to use standard functions in their SQL queries. I just really don't want them doing anything that looks like procedural programming (and having to worry about the pitfalls of such things, as seen above).

Answer 1

You can't prevent people from writing procedural PL/SQL code against your server. Depending on the exact nature of the problem you're trying to solve, however, you may have other options. Two options that spring to mind...

You can create a profile associated with the database user that enforces various resource limits. So you can limit the amount of CPU a single call can consume or the number of reads it can do. That lets you automatically kill sessions that do something like coding an infinite loop. Note that the RESOURCE_LIMIT initialization parameter needs to be set to TRUE in order for Oracle to enforce resource limits in profiles.

You can use Oracle Resource Manager to prioritize access to resources to reduce the risk that a developer's mistake will take all the resources available on the server and starve the important production processes.