Archive

Archive for the ‘plsql’ 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;

Advertisements
Categories: Mini-tips, Oracle, plsql, 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

Remove Item from Apex Collection

April 5, 2016 Leave a comment
declare
    l_collection_name varchar2(200) := 'MY_COLLECTION';
    l_seq_id number;
begin
    select seq_id
    into l_seq_id
    from apex_collections
    where collection_name = l_collection_name
    and c001 = :ID_SAVED_IN_THAT_COLUMN;
    
    apex_collection.delete_member( 
        p_collection_name => l_collection_name,
        p_seq => l_seq_id
    );
exception
    when no_data_found then
        null; -- The element was already deleted, dont worry about it! :)
end;
Categories: Beyond, Oracle, plsql, SQL