The trigger below will only allow user A, B or C create up to 10 objects within the database. Once the 11th object is created then the database will throw up the raise application error. create or replace trigger no_more_than_10
How to perform case insensitive searches
If you want to search for the following words: ‘Hello’,’hello’,’HEllo’ then you may think about using UPPER or LOWER, but there is an easier way. All you have to do is create an index on your column you want to
How to select the last Saturday in every month
I need to develop a query to return the date of the last Saturday in any given month. How can I do that? Code Listing 1: Find the last Saturday of each month for a year. SQL> select next_day( 2
How to drop a column from a table
It is possible to drop any column from an Oracle table by using the following SQL alter table command: alter table <tablename> drop column <columnname> If you need to rename a column you can do this as well by using
Rename a Column in a Table
If you need to rename a column you can do this as well by using the following command: alter table <tablename> rename column <columnold> to <columnnew> It is possible to drop any column from an Oracle table by using the
How to Find Years of Service of Employee
If you need to find the number of years someone has worked for you then the following type of query should do the trick: SELECT EMPNO, ENAME, TRUNC((SYSDATE-HIREDATE)/365) as YEARS_OF_SERVICE FROM EMP
How to find the exact difference between dates
If you need to find the exact difference between two dates worked out to the number of years and the number of days as separate columns then try the following SQL: SELECT DOB, DOD, ABS(years) years, GREATEST(DOB,DOD) – ADD_MONTHS(LEAST(DOB,DOD),ABS(12*years)) days
How to return errors when converting floating point numbers into number types
By default if you define something as NUMBER(3), the Oracle database will round the value, if possible to make the number fit, if the number cannot fit after rounding only then will it be rejected. If you enforce the NUMBER(3)
How to remove leading zeros from a string using SQL
If you want to remove leading zero’s or any other character from a string, or really do the SQL opposite of LPAD then use LTRIM. SELECT ltrim(text_string,’0′) FROM <tablename>