Shoebox 2001/2000

Shoebox 1999/1998

Dear Aunt Augusta,

We are considering using stored PL/SQL procedures combined with external C procedures or perhaps Java code to execute from a single place through SQL*Net some operating system scripts to do some Oracle-related work, such as renaming or compressing log files and the like.
Our problem is that we have a number of different operating systems, and that to execute what is functionally the same task we may have to run different scripts. How can we find out from a PL/SQL procedure which is the underlying operating system ?

J.C.

Dear J.C.,

Ruling the world from a single tiny place, what a noble ambition! I can only approve of it. That said, it is not particularly easy to unmask the operating system from PL/SQL - bar perhaps from a Java procedure, but I must confess that Java is not quite my cup of tea.
Using purely PL/SQL, one way to do it is to have a look at the file names into say DBA_DATA_FILES. Something as unsubtle as:

SQL> select file_name from dba_data_files
  2  where rownum = 1;

FILE_NAME
--------------------------------------------
/u01/ORACLE/ORIOLE1/system01.dbf

will tell you without the shadow of a doubt that I am running on a Unix machine - You would probably have had no trouble either with say something like:

cD:\ORACLE\ORIOLE1\SYSTEM01.DBF

to guess the type of platform and anybody nearing my age would find:

DISK$ORA:[ORIOLE1]SYSTEM01.DBF;1 

totally unambiguous.

However, this may not be precise enough for you. Well, just with Unix scripts, it's not always obvious whether you should call 'awk' or 'gawk' if you want to pass a parameter using a '-v' flag (just an example). To the best of my knowledge, the most precise information you may find is in V$VERSION, which contains the banners telling you which version of the various components (RDBMS, PL/SQL ...) you are using. One of those banners contains a reference to operating system - not the row about the server version, but the one which refers to SQL*Net (I guess the various ports musn't have been very easy ...).
Just try:

select substr(substr(banner, 1, instr(banner, ':') - 1), 9) OS
from v$version
where banner like 'TNS%';

It will tell you which is your operating system (not, unfortunately, its version - and I fear that the various avatars of Windows go pretty undifferentiated).

Sincerely yours,

~~~~~~~~~~~~

February 8 2002

G'day Auntie,

An old application has recently been totally rewritten and we're going to put it into production soon. Quite obviously, the schema has been substantially altered and we have to move all the data from the old format to the new format. Our scripts have been successfully tested and are ready but they're slow.
Since we want to minimize the impact of migration for our users, we have to keep the old application open during the process of migration but in read-only mode. This is where my problem lies.
How can I ensure that nobody will try to modify the data?
I can't play with grants, because the old application handles accesses and everybody is logged-in as the schema owner.
I've thought of setting the tablespaces in READ-ONLY mode but the tables of other, unrelated applications are also stored in the same tablespaces (we are changing this too) and, moreover, we may have to change some data before moving it.
Got any ideas?
Thanks.

Rick

My dear antipodean friend,

If I understand you right, you want updates to some tables to generate errors, except for the happy few. Methinks that triggers can prove quite handy at this. Have I already told you about the DBMS_APPLICATION_INFO package? Wonderful little package, which allows you neatly to make a difference between users, a bit like those welcoming signs 'UK passports' and 'Johnny Foreigners' (or something similar) which grace our borders and which you will no doubt notice the next time you enter the home country, whether you come on holiday or on parole.

The idea is to create a little procedure which sets in your environment something which says that YOU CAN update; something like this :

create or replace procedure allow_update
as
begin
	dbms_application_info.set_module('UK PASSPORT', NULL);
end;
/
create public synonym allow_update for allow_update
/

Quite obviously, you should not grant EXECUTE on this to anybody and be unabashedly elitist.
The second step is to create on all tables you do not want to be updated a trigger which fails whatever you do, unless, that is, Oracle finds the suitable credentials in your environment :

create trigger ‹substr(table_name, 1, 16)›_READONLY_TRG
before insert or delete or update on 
declare
   v_module varchar2(48);
   v_action varchar2(32);
begin
   dbms_application_info.read_module(v_module, v_action);
   if ((v_module != 'UK PASSPORT') or (v_module is null))
   then
     --  Scum of the earth
     raise_application_error(-20000,
             'You can only read from this table now');
   end if;
end;
/

Yours truly,

~~~~~~~~~~~~