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

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.

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

Select historical data from a table using flashback

Select from a table at a certain point in history. This can be useful if you have made a mistake and edited or deleted data. Be careful though, because you have to have flashback enabled, and also set with enough space to capture all the changes that you need to recover.

Any DDL on the table will reset the ability to flashback.

example time_units
Day 1
Hour 1/24
Minute 1/1440
Second 1/86400

SELECT *
FROM TABLE_NAME
AS OF TIMESTAMP SYSDATE - time_unit;

Populating an ASP.Net drop down list using LINQ to SQL with C#

This example shows how to populate an ASP.Net drop down list control using LINQ to SQL with C#.  This example uses a stored procedure called from within the code behind  of the ASP.Net page. A sample method passes in a user ID which is then passed to the stored procedure.

Once you have created the datacontext diagram for your application, simply create an instance of it and populate the control as so:

protected void sampleMethod(int userId)
{
     using (var dc = new MyApplicationDataContext())
     {
          ddlMyDropDownList.DataSource = dc.SP_myStoredProc(userId);
          ddlMyDropDownList.DataTextField = "USER_NAME";
          ddlMyDropDownList.DataValueField = "USER_ID";
          ddlMyDropDownList.DataBind();
     }
}

You can also add an inline value to be shown in the drop down list and insert it at any point in the list at runtime, like so:

protected void sampleMethod(int userId)
{
     using (var dc = new MyApplicationDataContext())
     {
          ddlMyDropDownList.DataSource = dc.SP_myStoredProc(userId);
          ddlMyDropDownList.DataTextField = "USER_NAME";
          ddlMyDropDownList.DataValueField = "USER_ID";
          ddlMyDropDownList.DataBind();
          ListItem li = new ListItem("Inline Value", "78");
          ddlMyDropDownList.Items.Insert(0, li);
     }
}

The above code creates a list item and assigns some text and a value to it. The last line then inserts the new item into the drop down list at the first index in the list (i.e. the top).