Oracle Database Discussions

Hi Gurus,

Oracle 10.2.0.4.0 - 64bit on Solaris

I would like to check the instance name and get out if the sql script is run against wrong instance. To do so I have devised the following code:

var  v_instance varchar2(16);
var  v_this_instance varchar2(16);
exec :v_instance := 'The_correct_instance'
DECLARE
  st varchar2(255);
  instance_error_exception     exception;
BEGIN
  st := 'select instance_name from v$instance';
  EXECUTE IMMEDIATE st into :v_this_instance;
  if :v_this_instance <> :v_instance then
    RAISE instance_error_exception;
   end if;
EXCEPTION
   WHEN instance_error_exception THEN
     dbms_output.new_line;
     dbms_output.put_line('WARNING! you are running this script against '||:v_this_instance ||', you need to run it against '||:v_instance||'! exiting');
     dbms_output.new_line;
   WHEN OTHERS THEN
     DBMS_OUTPUT.PUT_LINE(to_char(SQLCODE) || ' - ' || substr(SQLERRM,1,200));
END;
/
create table a ( c1 varchar2(30) ,....);
exit

Now the approach is that if the operator runs this script in an incorrect instance I want the login to exit Oracle and terminate the process. I want all this from one script (as opposed to calling another one from the first one etc.

In short I want to exit as soon as the instance is identified as incorrect one and follow up sql code outside of the pl/sql block is ignored.

Many thanks

Edited by: 902986 on 27-Mar-2013 14:37

Edited by: 902986 on 28-Mar-2013 07:43