Find all users who have a certain role

Finding out a list of all users who posses a particular role is easy when querying against that data dictionary. dba_role_privs, or all_role_privs would give you the answer.

Here’s an example search for all users who have the role of ‘ABC’, ordered by their name.

SELECT * FROM dba_role_privs WHERE granted_role = 'ABC' ORDER BY 1

Oracle interval – adding seconds, minutes and hours to dates

Adding or subtracting time from Oracle dates is not always obvious and not always readable. Adding whole days is straightforward – 1 = one day – or with the use of the add_months function, adding months is straightforward, but if, for example you want to add 3 hours to sysdate you would have to do something like the following:

sysdate + (1/24 * 3)

Of course that could be written differently – using constants perhaps – but for the purposes of this example i’ve kept it simple.

Luckily interval can be used to produce much more readable code. The example above could be rewritten like so:

sysdate + INTERVAL '3' HOUR

Note the use of singular “hour”.

Here are a few other uses for interval, including adding seconds and minutes to a date:

 sysdate + INTERVAL '3' MINUTE,
 sysdate + INTERVAL '3' HOUR,
sysdate + INTERVAL '3' DAY,
sysdate + INTERVAL '3' MONTH,
sysdate + INTERVAL '3' YEAR FROM dual

Create a PLSQL record type

The following example will create a PLSQL variable for a record type that consists of two attributes – id and description. The attributes aren’t restricted to standard Oracle types; you could also include user created types like the record_type in the example.

type record_type is record(id number, description varchar2(1000));
record_var record_type;

Creating a variable of this type is as simple as referring a variable as something like a varchar2

variable1 record_type;

The following example assigns values to the record. := 1;
variable1.description := 'this is a test description;

Convert comma separated value into an Oracle resultset

An example for taking a comma separated string and converting it into an Oracle resultset

Sometimes it is desirable to take a comma separated string and convert it into an Oracle resultset.

The following code will take a bind variable – v_comma_separated_val – and return one row per value in the comma separated list.

SELECT TRIM (REGEXP_SUBSTR (:v_comma_separated_val, '[^,]+', 1,LEVEL)) vals
CONNECT BY REGEXP_SUBSTR (:v_comma_separated_val,'[^,]+',1, LEVEL) IS NOT NULL

The use of trim removes leading and trailing spaces.

One use of this might be to take a comma separated string and use that in a cursor loop, or perhaps for use when inserting into a table.

As always, if you can improve the above example, leave a comment below.

Display Oracle month without space padding

When attempting to display a full month in Oracle, it adds spaces by default, up to the length of the maximum month.

This occurs when using

to_char(sysdate, 'Month')

You could wrap that with a trim function call, or you could use

to_char(sysdate, 'fmMonth')


Suppress output in SQL/Plus

When running a script that contains a lot of feedback (eg dbms_output.put_line) to the screen, it can take a while to send all the feedback to the screen.

To suppress the output to the screen use:

set termout off

This command only seems to work when placed inside a script.

If you suppress the feedback to the screen, it might be useful to spool it to a file to keep a log of the output.

Alter SQL/Plus prompt

It is possible to alter the SQL/Plus prompt, replacing the default.

I tend to have many SQL/Plus windows open, so I prefer to see exactly who I am logged in as, and in which schema I am logged into.

To do this, edit your glogin.sql file to add the following:

set sqlprompt "_USER'@'_CONNECT_IDENTIFIER > "

Of course, you could alter the code above to suit your own preference.