CODEWITHSUNDEEP
oracle-databaseplsql
user1577314
user1577314

Reputation: 93

Oracle - conditionally execute query

I'm executing a query in a dev environment that would be catastrophic if it was run against a live environment. Because of this, I want to make sure this query can only execute against a particular database. My Oracle skills aren't too hot, so I was wondering if anyone might be able to help? Here is some pseudo-code which explains what I'm trying to do:

    select instance_name from v$instance;

    if instance_name = 'this_environment' 
    then 
        update tableA
        set column1 = 'ABC'
    else
    *break out of query*

Would anyone know how to go about doing this? Thanks for any help in advance!

Upvotes: 0

Views: 1210

Answers (2)

Srini V
Srini V

Reputation: 11375

Something like this:

DECLARE
    CURSOR C1
    IS
        SELECT
              CASE
                  WHEN INSTANCE_NAME = 'ENV_NAME'
                  THEN
                      'Y'
                  ELSE
                      'N'
              END
                  AS FLAG,        -- You can have diff values as per env
              CASE
                  WHEN INSTANCE_NAME = 'ENV_NAME'
                  THEN
                      'YOUR_QUERY'
                  WHEN INSTANCE_NAME = 'ELSE'
                  THEN
                      NULL
              END
                  AS QUERY       -- You can have diff queries as per env
        FROM
              V$INSTANCE;

    MY_FLAG VARCHAR2 ( 1 );
    MY_QUERY VARCHAR2 ( 2000 );
BEGIN
    OPEN C1;

    FETCH C1
    INTO
          MY_FLAG, MY_QUERY;

    CLOSE C1;

    IF FLAG = 'Y'
    THEN
        EXECUTE IMMEDIATE 'my_query';
    END IF;
END;

Upvotes: 0

mucio
mucio

Reputation: 7119

The best way to do it is just to avoid to have that query close to production.

Anyway, just as exercise you can do:

update tableA
   set column1 = 3
 where 'this_environment' in (select instance_name 
                                from v$instance);

You can check this code and do your tests here on SQLFiddle. I had to create a v$instance on SQLFiddle to simulate that system view.

Upvotes: 2

Related Questions