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:

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

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
FROM DUAL
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')

 

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.

Convert XML to a table

Querying XML can be difficult if you are not used to Oracle’s subtle techniques for handling XML. One workaround is to convert the XML to a standard resultset as quickly as possible and then query that using techniques you are comfortable with. This is a slow way of doing things, but sometimes time is not critical.

select  
  xml.name, xml.price
from 
  XMLTABLE('/items/item'
  PASSING xmltype('
            
                juice
                1.00
            
            
                chicken
                6.00
            
            
                crisps
                1.00
            ')
            COLUMNS
            name varchar2(1000) PATH '/item/name',
            price varchar2(1000) PATH '/item/price') xml

Create an Oracle table

Here is an example on how to create a table in Oracle.

The table consists of two columns, an ID and a description.

Both columns are mandatory and the ID column has been set as the primary key.

CREATE TABLE NEW_TABLE
(
  id           NUMBER                           NOT NULL,
  description  VARCHAR2(100)                    NOT NULL
);

ALTER TABLE ONLINEMASTER.NEW_TABLE ADD (
  CONSTRAINT NEW_TABLE_PK
 PRIMARY KEY
 (id));

The next step might be to grant certain privileges on the table.

Revoke privileges on a specific table from a user or role

I have described how to grant privileges to a user or role. This is the opposite and will show how to revoke privileges.

This example revokes the ability to select on table called test_table from the user called JSMITH.

REVOKE SELECT ON test_table FROM JSMITH;

Here is the full list of possible privileges for an Oracle table.

Privilege Description
Select Ability to query the table.
Insert Ability to add new rows to the table.
Update Ability to update rows in the table.
Delete Ability to delete rows from the table.
References Ability to create a constraint that refers to the table.
Alter Ability to change the table definition.
Index Ability to create an index on the table.

Grant privileges on an Oracle table to a specific user or role

When you create an object, you have full privileges over that object. If you want to grant specific privileges to another user, or role, then this should explain how to do it.

This example grants the ability to select on a table called TEST_TABLE to the user called JSMITH:

GRANT SELECT ON TEST_TABLE TO JSMITH;

Here is the full list of possible privileges for an Oracle table.

Privilege Description
Select Ability to query the table.
Insert Ability to add new rows to the table.
Update Ability to update rows in the table.
Delete Ability to delete rows from the table.
References Ability to create a constraint that refers to the table.
Alter Ability to change the table definition.
Index Ability to create an index on the table.

Alternatively you could grant full privileges by using the following:

GRANT ALL ON test_table TO JSMITH;

You can of course do the opposite and revoke privileges.

Extract numbers from an alphanumeric string

In this example we have an alphanumeric string, and we want to extract only the numbers from it.

select regexp_substr(col1, '[0-9]*')
from table;

You could also use :digit: to represent numbers, but I prefer 0-9.

You might wonder why you would extract only numbers from a string. One example might be if you want to order by a house number. The presence of 1a,1b etc means that Oracle would order it as if it were a string, which is not ideal. Combining to_number with the regexp_substr will allow you to order the results in a much more sensible way.