1

There are two field formats. One is SOMENAME1234 and another is SOMENAME1234_SE. What I need to do is select all of the records where the number 1234 is less than X. The application is a test database where Jenkins generates unique schema names to keep our developer's tests from crashing into each other. The value '1234' represents a build number.

What I'm trying to write is the cleanup script. Problem is that I need to make sure that I'm not removing any schemata that are currently in use. So I found a way to pragmatically hit Jenkins for what is currently building. Such that now I have a value X that represents the lowest build number currently building. I can delete any schemata with a value lower than this. Problem is that I don't know how to write that SQL. Looking at REGEXP_LIKE, I see that there might be a regular expression involved.

Currently I have:

SELECT username
FROM dba_users
where username NOT LIKE '%"+upperBound+"%'
AND username LIKE 'BLDXYZPDQ%'

This is the best I've been able to do so far.

What I intend is to select the list and then drop each schema in that list one at a time. That way I need not worry about new schemata created during the process.

1

Since the format is not specified exactly, I will just assume the usernames follow the above pattern without any special cases. Find the number:

select to_number(replace(replace('SOMENAME1234_SE', 'SOMENAME', ''), '_SE', '')) as build_number from dual;

BUILD_NUMBER
------------
        1234

Generate commands for dropping users:

select 'drop user ' || username || ' cascade;' from dba_users where username like 'SOMENAME%' and to_number(replace(replace(username, 'SOMENAME', ''), '_SE', ''))  < :X;

Or execute them in PL/SQL:

declare
  x number := 1234;
begin
  for cmd in (select 'drop user ' || username || ' cascade' as text from dba_users where username like 'SOMENAME%' and to_number(replace(replace(username, 'SOMENAME', ''), '_SE', ''))  < x)
  loop
    execute immediate cmd.text;
  end loop;
end;
/

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.