This forum is currently locked. You can't register or post questions at this time. (read more)

Exit oracle sqlplus if instance is not correct

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

Jakob
Member
Posts: 4
Joined: Mon Jun 17, 2013 1:19 pm

Exit oracle sqlplus if instance is not correct

Postby Jakob » Tue Jul 09, 2013 7:38 am

Hi Tim,

How can exit from currently logged in sqlplus session when I am running my sql scrpts?

E.g.

I have the following sql scripts

loaddata.sql

Code: Select all

@@usercheck.sql

copy data from prod/prod using insert emp using select * from emp;
...


usercheck.sql

Code: Select all

DECLARE
   v_instance    VARCHAR2 (512);
BEGIN
   SELECT   LOWER (SYS_CONTEXT ('USERENV', 'DB_NAME'))
     INTO   v_instance
     FROM   DUAL;

   IF (v_instance <> 'test)
   THEN
      -- here I would like to quit my sqlplus connection if instance is not test
   END IF;
END;
/


In the above sql script if instance is not test, then I would like to exit my sql connection and I wouldn't like to proceed with data transfer. So ideally when
usercheck.sql is being called from usercheck.sql, if instance is not test, then should not execute my other statements.

How can I do this? If any alternate better approach available, kindly suggest.

I am using Oracle 10g database R2.

Thanks

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Exit oracle sqlplus if instance is not correct

Postby Tim... » Tue Jul 09, 2013 8:26 am

Hi.

You can't leave the whole SQL*Plus session, but you can quit your current PL/SQL block. The easiest way to do this is to raise an exception, for example,

Code: Select all

   IF (v_instance <> 'test)
   THEN
      RAISE_APPLICATION_ERROR(-20000, 'This is not the TEST instance');
   END IF;


Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog


Return to “Oracle SQL and PL/SQL Development”

Who is online

Users browsing this forum: No registered users and 1 guest

cron