Archive

Archive for the ‘Oracle’ Category

April 26, 2017 Leave a comment

Create a synonym in oracle database.
create or replace synonym my_schema_granted.my_db_object_synonym for schema_that_grants.db_object;
commit;

Categories: Mini-tips, Oracle, plsql, SQL

Get all days between two dates in oracle sql

March 30, 2017 Leave a comment
select to_date('30-03-2017','dd-mm-yyyy') + rownum -1
from all_objects
where rownum <= to_date('30-04-2017','dd-mm-yyyy')-to_date('30-03-2017','dd-mm-yyyy')+1;
Categories: Oracle, Programación, SQL

Split string in sql query – oracle

October 3, 2016 Leave a comment

select regexp_substr('MyString,otherString,lastOneStr','[^,]+', 1, level) from dual
connect by regexp_substr('MyString,otherString,lastOneStr', '[^,]+', 1, level) is not null;

–Result :
———-
–MyString
–otherString
–lastOneStr

Categories: Beyond, Mini-tips, Oracle, SQL

Simple way to enable a job in oracle scheduler.

September 29, 2016 Leave a comment

BEGIN
DBMS_SCHEDULER.ENABLE (‘MY_AWESOME_JOB’);
END;
/

Categories: Beyond, Mini-tips, Oracle, plsql

Split string in oracle sql query

August 1, 2016 Leave a comment

select regexp_substr('MyString,otherString,lastOneStr','[^,]+', 1, 2) from dual; 
-- Returns 'otherString' String
Categories: Beyond, Mini-tips, Oracle, plsql, SQL, Tips

Get TZH:TZM from TZR in oracle sql query

July 18, 2016 Leave a comment

SELECT TZ_OFFSET('US/Pacific') FROM DUAL;

Categories: Mini-tips, Oracle, plsql

Check when deleting, inserting or updating in trigger oracle db

June 6, 2016 Leave a comment

— Inside your trigger

IF DELETING THEN
— do your thing when deleting
END IF;

IF INSERTING THEN
— do your thing when inserting
END IF;

IF UPDATING THEN
— do your thing when updating
END IF;

Categories: Mini-tips, Oracle, plsql, SQL, Tips