The following error might occur when you use some date and time functions with SAS/ACCESS Interface to Oracle:

ERROR: ORA-30076: invalid extract field for extract source

For example, the error occurs if SAS passes the HOUR(column) function to Oracle, where column is an Oracle DATE data type.

You can successfully use the DAY, MONTH, and YEAR functions on Oracle DATE data types.

You can also successfully use DATE, MONTH, YEAR, HOUR, MINUTE and SECOND functions on Oracle TIMESTAMP data types.

Operating System and Release Information

libname o oracle user=scott pw=tiger path=orcl SQL_FUNCTIONS_COPY=sasuser.orafuncs92 ;
proc sql;
   delete from sasuser.orafuncs92 where sasfuncname='HOUR';
quit;
libname o oracle user=scott pw=tiger path=orcl SQL_FUNCTIONS="external_replace=sasuser.orafuncs92";