One query or another depending on the schema the code is in

user_10689857 0
Viewed 26

I have the below 2 queries:

SELECT * from mytable; SELECT * from mytable where rownum < 100;

I would like to execute the first one whenever my schema equals 'SCHEMA1' and the second one otherwise. Is there a way to do this without having to rewrite both queries like this:

DECLARE
    myschema VARCHAR2(50);
BEGIN
    SELECT
        sys_context('userenv', 'current_schema')
        || 's'
    INTO myschema
    FROM
        dual;

    IF myschema = 'SCHEMA1' THEN
        INSERT INTO myothertable
            SELECT
                *
            FROM
                mytable;

    ELSE
        INSERT INTO myothertable
            SELECT
                *
            FROM
                mytable
            WHERE
                ROWNUM < 100;

    END IF;

END;

I would also like to avoid dynamic PLSQL.

oracle sql
1 Answers

I would write it this way:

DECLARE
  MYSCHEMA VARCHAR2(128) := SYS_CONTEXT('userenv', 'current_schema');
BEGIN
   INSERT INTO myothertable
   SELECT * 
     FROM mytable 
    WHERE ROWNUM < CASE WHEN MYSCHEMA = 'SCHEMA1'  THEN 100
                        ELSE ROWNUM + 1
                    END;
END;
/
user_48918150
Related