Recreate GRANTS to user in oracle without dba privilege

If you want to get list of grants made to tables and you want to recreate the user permissions in a different environment, the easy way to generate a script is to use the dbms_metadata.get_granted_ddl function as described here. However, if you do not have DBA privilege on the database you will not be able to use that method. Here is a simple sql that you can use to generate a script that will work in most common situations. This just relies on the TABLE_PRIVILEGES table.

select replace('GRANT ' || decode(select_priv,'Y','SELECT','') || decode(insert_priv,'A',',INSERT','') || decode(delete_priv,'Y',',DELETE','') || decode(update_priv,'Y',',UPDATE','') || decode(references_priv,'Y',',REFERENCES','') || decode(alter_priv,'Y',',ALTER','') ||' ON '|| owner || '.' || table_name || ' TO ' || GRANTEE ||';','GRANT ,','GRANT ') from TABLE_PRIVILEGES where owner= 'OWNER' order by table_name, grantee;

Pagination using sql

If you have a complex query that returns lot of rows from the database and you are displaying the results in a table,  you may want to add some pagination logic to your application.  However, most people add pagination to the application layer, but retrieve all the rows back from the database.  Java programming frameworks/API such as IBatis, JPA etc provides a mechanism to limit the number of rows retrieved or skip a number of rows.   However, the implementation of those framework is left behind the jdbc driver and often results in the data still retrieved to the application server and then discarded.   You could avoid this resource wastage by changing your query to limit the rows.  The following is an example of how you can do just that.

    select * from ( select rownum rnum, a.* from (
         select columns from your_complex_table order by someField )  a 
            where rownum <  #UPPER_LIMIT )     where rnum >= #LOWER_LIMIT

In this example, you can change the values of #UPPER_LIMIT and #LOWER_LIMIT as parameters to your query limit the results to just the rows you want to deal with.  If you are using a  iBATIS you can make your actual query (the one does fetches all the records) in to an sql fragment and reuse it for the paginated query and get row counts.   It also helps to get all the records in case of exporting all the data to some external format.  The following example shows how you can reuse the sql fragment within your sqlMapping.

<sql id="selectItems">
select columns from your_complex_table order by someField
</sql>
<select id="selectItemCount" resultClass="int">
SELECT COUNT(*) AS total
<include refid="selectItems"/>
</select>
<select id="selectPaginated" resultClass="Item" parameterClass="map">
 select * from ( select rownum rnum, a.* from (
<include refid="selectItems"/>
    ) a  where rownum <  #UPPER_LIMIT )
   where rnum >= #LOWER_LIMIT
</select>

Oracle - Generate a list of dates with time

I had to create a report that had to have a line for each date/time within the given range, even if the actual data did not have value for that date. Say, web trafic count for each 5 minutes for a given day. The simple full join of the date will not work for this because, there might be periods during the day there was no user activity, and it is expected to have zeroes in that case.

A simple approach would be to use the hirerical query used in this blog if you only need one row per date. But if you need date entries for multiple times per day, say a row for every 5 minutes, the following function will come in handy. It uses a Pipelined Table Function.

CREATE OR REPLACE TYPE date_array AS TABLE OF DATE;
/
CREATE OR REPLACE FUNCTION MPI2.date_table(sdate DATE, edate DATE, mi_interval integer   )

RETURN date_array PIPELINED is
 fDate date ;
BEGIN
  fDate := sdate;
  while fDate < edate LOOP
    PIPE ROW(fDate );
    fDate := fDate +  mi_interval / 1440;  
  END LOOP;
  RETURN;
END date_table;

Now you may query this function just like you query a table, as follows

SELECT to_char(column_value,'DD-MON-YYYY HH24:MI')  FROM TABLE(CAST(MPI2.DATE_TABLE(trunc(sysdate-1),sysdate,5) AS date_array));

The above query will return a row for every 5 minutes. You can do a full join of this table with your actual tables to get the result you wanted.

Oracle function to aggregate a number by date

One of the common scenarios while working with relational database tables is to aggregate a column on a table and find out the corresponding value of a different column. For example, if you have a table that has the name, location and checkin time of all employees in a table and and you want to find out the location from which each employee ckecked in last, the sql will involve creating a inner query. For most cases, an inner query is fine. However, if the table has a huge number of rows querying the table twice might be expensive. That is when a custom aggregate function might be helpful.
Here is the sql that represents the table:

CREATE TABLE EMP_TIME_LOG
(
REPORT_ID NUMBER(5),
LOCATION_ID NUMBER(2),
LOGIN_DTG DATE DEFAULT sysdate,
EMP_NAME VARCHAR2(50 BYTE)
)
/

SET DEFINE OFF;
Insert into EMP_TIME_LOG
(REPORT_ID, LOCATION_ID, LOGIN_DTG, EMP_NAME)
Values
(1, 1, TO_DATE('02/13/2008 13:35:59', 'MM/DD/YYYY HH24:MI:SS'), 'emp1');
Insert into EMP_TIME_LOG
(REPORT_ID, LOCATION_ID, LOGIN_DTG, EMP_NAME)
Values
(2, 1, TO_DATE('02/13/2008 13:36:23', 'MM/DD/YYYY HH24:MI:SS'), 'emp2');
Insert into EMP_TIME_LOG
(REPORT_ID, LOCATION_ID, LOGIN_DTG, EMP_NAME)
Values
(3, 2, TO_DATE('02/13/2008 13:36:50', 'MM/DD/YYYY HH24:MI:SS'), 'emp1');
Insert into EMP_TIME_LOG
(REPORT_ID, LOCATION_ID, LOGIN_DTG, EMP_NAME)
Values
(4, 2, TO_DATE('02/13/2008 13:37:32', 'MM/DD/YYYY HH24:MI:SS'), 'emp3');
Insert into EMP_TIME_LOG
(REPORT_ID, LOCATION_ID, LOGIN_DTG, EMP_NAME)
Values
(5, 4, TO_DATE('02/13/2008 13:45:48', 'MM/DD/YYYY HH24:MI:SS'), 'emp3');
COMMIT;

In this example we need to get group the rows by employee name but we want to get the latest checkin time and the location_id of the row corresponding to the latest checkin time. Without using any custom function here is how one might achieve this.

select t1.emp_name , t1.location_id from emp_time_log T1 ,
(select location_id, emp_name, max(login_dtg) max_login_dt from emp_time_log group by emp_name,location_id ) T2
where t1.emp_name = t2.emp_name and t2.max_login_dt = t1.LOGIN_DTG

Oracle supports a feature called User-Defined Aggregate Functions that makes this very easy to implement. If we had a custom aggregate function that can take both the location id and the login_dtg and returned the location_id of the row corresponding to the max(login_dtg) of each group, we could eliminate the inner query. However, for aggregate functions there is limit on the number of arguments that can be passed in. Fortunately, we could create a custom data type that can hold both the values and use that custom type as a parameter to the aggregate function. With a custom function, the query to achieve the same result would look like the following.

select emp_name , MAX_DATES_NUMBER (NUMBER_DATE (location_id, login_dtg)) as location_id from emp_time_log group by emp_name

Here NUMBER_DATE is the custom data type that I created to hold both the number and a date, and MAX_DATES_NUMBER is the custom aggregate function.
The function definition is as follows:

CREATE OR REPLACE FUNCTION MAX_DATES_NUMBER
( idDtVal Number_Date
) RETURN NUMBER
PARALLEL_ENABLE
AGGREGATE USING Number_Date;
/

The code for creating the custom data type and the the logic for implementing the aggregation function is given below. Jonathan Gennick has written a nice article "Build Custom Aggregate Functions" that explains who the custom aggregation function works. I encourage you to read that article.

CREATE OR REPLACE TYPE Number_Date
AS OBJECT (

maxId NUMBER,
maxDt DATE,

STATIC FUNCTION ODCIAggregateInitialize
( actx IN OUT Number_Date
) RETURN NUMBER,

MEMBER FUNCTION ODCIAggregateIterate
( self IN OUT Number_Date,
idDtVal IN Number_Date
) RETURN NUMBER,

MEMBER FUNCTION ODCIAggregateTerminate
( self IN Number_Date,
returnValue OUT NUMBER,
flags IN NUMBER
) RETURN NUMBER,

MEMBER FUNCTION ODCIAggregateMerge
(self IN OUT Number_Date,
ctx2 IN Number_Date
) RETURN NUMBER

);
/

CREATE OR REPLACE TYPE BODY Number_Date AS

STATIC FUNCTION ODCIAggregateInitialize
( actx IN OUT Number_Date
) RETURN NUMBER IS
BEGIN
IF actx IS NULL THEN
actx := Number_Date (0,'01-JAN-1900');
ELSE
actx.maxDt := '01-JAN-1900';
actx.maxId := 0;
END IF;
RETURN ODCIConst.Success;
END;

MEMBER FUNCTION ODCIAggregateIterate
( self IN OUT Number_Date,
idDtVal IN Number_Date
) RETURN NUMBER IS
BEGIN
IF idDtVal.maxDt > self.maxDt THEN
self.maxId := idDtVal.maxId;
self.maxDt := idDtVal.maxDt;
END IF;
RETURN ODCIConst.Success;
END;

MEMBER FUNCTION ODCIAggregateTerminate
( self IN Number_Date,
ReturnValue OUT NUMBER,
flags IN NUMBER
) RETURN NUMBER IS
BEGIN
returnValue := self.maxId;
RETURN ODCIConst.Success;
END;

MEMBER FUNCTION ODCIAggregateMerge
(self IN OUT Number_Date,
ctx2 IN Number_Date
) RETURN NUMBER IS
BEGIN
IF ctx2.maxDt > self.maxDt THEN
self.maxId := ctx2.maxId;
self.maxDt := ctx2.maxDt;
END IF;

RETURN ODCIConst.Success;
END;

END;
/

What is going on with DATE and TIMESTAMP?

I have ran in to developers using java.sql.Date object in Oracle and wondering why the date object does not contain the time information. The short answer is java.sql.Date and you need to use the java.sql.TimeStamp object that contains both Date and timestamp information.

A detailed explanation of the issue can be found in the Oracle web site . I am copying the text here for a quick reference.

Prior to 9.2, the Oracle JDBC drivers mapped the DATE SQL type to java.sql.Timestamp. This made a certain amount of sense because the Oracle DATE SQL type contains both date and time information as does java.sql.Timestamp. The more obvious mapping to java.sql.Date was somewhat problematic as java.sql.Date does not include time information. It was also the case that the RDBMS did not support the TIMESTAMP SQL type, so there was no problem with mapping DATE to Timestamp.

In 9.2 TIMESTAMP support was added to the RDBMS. The difference between DATE and TIMESTAMP is that TIMESTAMP includes nanoseconds and DATE does not. So, beginning in 9.2, DATE is mapped to Date and TIMESTAMP is mapped to Timestamp. Unfortunately if you were relying on DATE values to contain time information, there is a problem.

There are several ways to address this problem:

Alter your tables to use TIMESTAMP instead of DATE. This is probably rarely possible, but it is the best solution when it is.

Alter your application to use defineColumnType to define the columns as TIMESTAMP rather than DATE. There are problems with this because you really don't want to use defineColumnType unless you have to (see What is defineColumnType and when should I use it?).

Alter you application to use getTimestamp rather than getObject. This is a good solution when possible, however many applications contain generic code that relies on getObject, so it isn't always possible.

Set the V8Compatible connection property. This tells the JDBC drivers to use the old mapping rather than the new one. You can set this flag either as a connection property or a system property. You set the connection property by adding it to the java.util.Properties object passed to DriverManager.getConnection or to OracleDataSource.setConnectionProperties. You set the system property by including a -D option in your java command line.

java -Doracle.jdbc.V8Compatible="true" MyApp