Oracle create public synonym

CREATE or REPLACE PUBLIC SYNONYM user_app_priv
FOR app_user.user_app_priv
/

it is example of how to make public synonym

yasser

Oracle drop table cascade and release the space

DROP TABLE user_names CASCADE CONSTRAINTS PURGE


this to release the space of table when you drop it in one sentence


yasser

ORACLE TO GIVE GRANT ON TABLE TO PUBLIC

grant select on TABLE_NAME to public;

Oracle 11g

Hello dear readers
11g case sensitive and to avoid this

ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON = FALSE



Yasser

Oracle forms change label of Button

in when-new-form-instance
you can write this code to change the label

set_item_property('ctrl.PSH_SRCH', label, 'My label');



Yasser

Oracle minus datetimes

I have 2 different fields from a table and both have same format of timestamp.
7/18/2012 10:19:48 AM
and
7/18/2012 10:20:46 AM
How can I subtract two timestamp to get the duration in time.

now how to do it

SELECT (firstDateColumn - secondDateColumn) * 24 * 60 difference_in_minutes
FROM yourtable

If the dates are string then convert them first:

SELECT (TO_DATE(firstDateColumn, 'dd/mm/yyyy HH:MI:SS AM') - 
        TO_DATE(secondDateColumn, 'dd/mm/yyyy HH:MI:SS AM')) * 
        24 * 60 difference_in_minutes
FROM yourtable

 

 

 

best regards

Yasser

 

Oracle listagg function for 2 columns

select e.emp_no,
listagg (to_char(start_time,'dd-mm-yyyy hh24:mi'), ',')
WITHIN GROUP
(ORDER BY e.emp_no,c.work_date,start_time,end_time) start_times,
   listagg (to_char(end_time,'dd-mm-yyyy hh24:mi'), ',')
WITHIN GROUP
(ORDER BY e.emp_no,c.work_date,start_time,end_time) end_times
from emp e,come_leav c
where e.emp_no = c.emp_no
and   e.finshed = 2
GROUP BY
   e.emp_no,c.work_date 
/



Yasser