Oracle: ORA-14402: updating partition key column would cause a partition change

This can be circumvented with:

ALTER TABLE my_table ENABLE ROW MOVEMENT;

CyanogenMod 10.2 on HTC Explorer

• How To Root HTC Explorer Pico and Install CWM Recovery
• CyanogenMod 10.2 Weekly Builds
• Install Jelly Bean 4.3 Based CyanogenMod 10.2 Nightly Build ROM on HTC Explorer/Pico
• How To Increase Your HTC Pico's Internal Memory Using Link2SD

Perl: sort hash values by keys

my @sorted_values = @hash{sort {$a <=> $b} keys %hash};

Oracle: aggregate data from a number of rows into a single row

Base Data:

    DEPTNO ENAME
---------- ----------
        20 SMITH
        30 ALLEN
        30 WARD
        20 JONES
        30 MARTIN
        30 BLAKE
        10 CLARK
        20 SCOTT
        10 KING
        30 TURNER
        20 ADAMS
        30 JAMES
        20 FORD
        10 MILLER

SELECT deptno, LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename) AS employees
FROM   emp
GROUP BY deptno;

    DEPTNO EMPLOYEES
---------- --------------------------------------------------
        10 CLARK,KING,MILLER
        20 ADAMS,FORD,JONES,SCOTT,SMITH
        30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

jQuery: Have UI datetimepicker 'Now' button use UTC time?

This solution requires an update of the source code:
http://stackoverflow.com/a/17833188/732797

Windows 8: Could'nt upgrade to Windows 8.1

If ever you meet this stupid error in the process of upgrading to Windows 8.1:

"Could'nt update to Windows 8.1.
Sorry, we couldn't complete the update to Windows 8.1
We've restored your previous version of Windows to this PC.
ERROR CODE: 0x80070002 - 0x20009."

This is most likely because the HDD on which Windows has been installed in not the one you booted on.
In this case, change the options in the BIOS in order to boot on the Windows HDD, upgrade, then restore as before.

Oracle: IN vs. EXISTS

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::NO::P11_QUESTION_ID:442029737684

PL SQL: Convert array into SQL table

Use:

SELECT COLUMN_VALUE FROM TABLE(my_array)

Beware to use only SQL array types or you will get the following error a run time (at least with Oracle 11.2.0.1):

ORA-21700: object does not exist or is marked for delete

E.g.

CREATE OR REPLACE TYPE MBQT_DB.ID_ARRAY_TYPE IS TABLE OF VARCHAR2(20);
/

GRANT EXECUTE ON MBQT_DB.ID_ARRAY_TYPE TO PUBLIC;

PL SQL: open ref cursor from explicit cursor

PROCEDURE my_proc(x_ref_cursor OUT sys_refcursor) IS

    TYPE my_array_type IS TABLE of MY_TABLE%ROWTYPE;
    my_array my_array_type;
BEGIN
    OPEN x_ref_cursor FOR SELECT * FROM MY_TABLE;

    FETCH x_ref_cursor BULK COLLECT INTO my_array;

    -- Do something with my_array...
    FORALL i IN 1 .. my_array.COUNT
        INSERT INTO MY_TABLE2 (
            col1,
            col2,
            col3)
        VALUES (
            my_array(i).col1,
            my_array(i).col2,
            my_array(i).col3
        );
END;

Oracle: record vs object

Record version
In package:

TYPE PARAMETER_REC_TYPE IS RECORD (
    PARAMETER_ID CRM_DB.HT_CO_ADDON_PARAMETER.PARAMETER_ID%TYPE,
    PARAMETER_VALUE CRM_DB.HT_CO_ADDON_PARAMETER.PARAMETER_VALUE%TYPE
);
TYPE PARAMETER_ARRAY_TYPE IS TABLE OF PARAMETER_REC_TYPE;

In package body:

set serveroutput on;
declare
    my_param_array PROV_DB.PROV_API.PARAMETER_ARRAY_TYPE;
    my_param_record PROV_DB.PROV_API.PARAMETER_REC_TYPE;
    g_sysdate date := sysdate;
    CURSOR my_rz_addon_cursor(p_co_id CRM_DB.DT_CO.CO_ID%TYPE) is
        SELECT  'RZ_ID[]' PARAMETER_ID,
                HT_CO_ADDON_PARAMETER.PARAMETER_VALUE
        FROM    CRM_DB.HT_CO_ADDON,
                CRM_DB.HT_CO_ADDON_PARAMETER,
                CRM_DB.HT_CO_ADDON_STATUS
        WHERE   HT_CO_ADDON.CO_ID = p_co_id
            AND HT_CO_ADDON.ADDON_ID LIKE 'ROAMING_ZONE_%'
            AND HT_CO_ADDON.FROM_DATE <= g_sysdate AND HT_CO_ADDON.TO_DATE > g_sysdate
            AND HT_CO_ADDON_PARAMETER.CO_ADDON_ID = HT_CO_ADDON.CO_ADDON_ID
            AND HT_CO_ADDON_PARAMETER.FROM_DATE <= g_sysdate AND HT_CO_ADDON_PARAMETER.TO_DATE > g_sysdate
            AND HT_CO_ADDON_STATUS.CO_ADDON_ID = HT_CO_ADDON.CO_ADDON_ID
            AND HT_CO_ADDON_STATUS.STATUS_ID = CRM_DB.CO_API.STATUS_ADDON_ACTIVE
            AND HT_CO_ADDON_STATUS.FROM_DATE <= g_sysdate AND HT_CO_ADDON_STATUS.TO_DATE > g_sysdate;
begin
    OPEN my_rz_addon_cursor('000000000941');
    FETCH my_rz_addon_cursor BULK COLLECT INTO my_param_array;
    CLOSE my_rz_addon_cursor;

    my_param_record.parameter_id := 'TOTO';
    my_param_record.parameter_value := 'coin coin';
    my_param_array.extend;
    my_param_array(my_param_array.count) := my_param_record;

    my_param_record.parameter_id := 'pim';
    my_param_record.parameter_value := '1 2 3';
    my_param_array.extend;
    my_param_array(my_param_array.count) := my_param_record;

    for i in 1..my_param_array.count loop
        dbms_output.put_line(my_param_array(i).parameter_id||' = '||my_param_array(i).parameter_value);
    end loop;
end;

Object version
As PROV_DB user:

create TYPE PARAMETER_OBJ_TYPE AS object (
    PARAMETER_ID varchar2(20),
    PARAMETER_VALUE varchar2(256)
);
/
create TYPE PARAMETER_OBJ_ARRAY_TYPE IS TABLE OF PARAMETER_OBJ_TYPE;
/;

GRANT EXECUTE ON PARAMETER_OBJ_TYPE TO public;
GRANT EXECUTE ON PARAMETER_OBJ_ARRAY_TYPE TO public;

In package body:

set serveroutput on;
declare
    my_param_array PROV_DB.PARAMETER_OBJ_ARRAY_TYPE;
    g_sysdate date := sysdate;
    CURSOR my_rz_addon_cursor(p_co_id CRM_DB.DT_CO.CO_ID%TYPE) is
        SELECT  PROV_DB.PARAMETER_OBJ_TYPE('RZ_ID[]', HT_CO_ADDON_PARAMETER.PARAMETER_VALUE)
        FROM    CRM_DB.HT_CO_ADDON,
                CRM_DB.HT_CO_ADDON_PARAMETER,
                CRM_DB.HT_CO_ADDON_STATUS
        WHERE   HT_CO_ADDON.CO_ID = p_co_id
            AND HT_CO_ADDON.ADDON_ID LIKE 'ROAMING_ZONE_%'
            AND HT_CO_ADDON.FROM_DATE <= g_sysdate AND HT_CO_ADDON.TO_DATE > g_sysdate
            AND HT_CO_ADDON_PARAMETER.CO_ADDON_ID = HT_CO_ADDON.CO_ADDON_ID
            AND HT_CO_ADDON_PARAMETER.FROM_DATE <= g_sysdate AND HT_CO_ADDON_PARAMETER.TO_DATE > g_sysdate
            AND HT_CO_ADDON_STATUS.CO_ADDON_ID = HT_CO_ADDON.CO_ADDON_ID
            AND HT_CO_ADDON_STATUS.STATUS_ID = CRM_DB.CO_API.STATUS_ADDON_ACTIVE
            AND HT_CO_ADDON_STATUS.FROM_DATE <= g_sysdate AND HT_CO_ADDON_STATUS.TO_DATE > g_sysdate;
begin
    OPEN my_rz_addon_cursor('000000000941');
    FETCH my_rz_addon_cursor BULK COLLECT INTO my_param_array;
    CLOSE my_rz_addon_cursor;

    my_param_array.extend;
    my_param_array(my_param_array.count) := PROV_DB.PARAMETER_OBJ_TYPE('TOTO', 'coin coin');
    my_param_array.extend;
    my_param_array(my_param_array.count) := PROV_DB.PARAMETER_OBJ_TYPE('pim', '1 2 3');

    for i in 1..my_param_array.count loop
        dbms_output.put_line(my_param_array(i).parameter_id||' = '||my_param_array(i).parameter_value);
    end loop;
end;

Update from Moutain Lion to Mavericks

1. Create a bootable USB flash drive with Mavericks like explained here: UniBeast: Install OS X Mavericks on Any Supported Intel-based PC
2. Boot on USB flash drive: <TAB>, then select USB and use GraphicsEnabler=Yes PCIRootUID=1
3. Install Mavericks
4. Reboot on USB flash drive: <TAB>, then select Mavericks disk and use GraphicsEnabler=Yes
5. Complete install and run MultiBeast 6 like what follows:













6. Reboot, no option
7. Et voila

NB: Using iMac13,1 instead of the default Mac Pro 3,1 in the system Definitions is crucial so that EIST (Enhanced Intel Stepper Technology) be enabled in the BIOS, otherwise, unless EIST is disabled, the following error will prevent from booting:

P-state stepper error 18 at step 2 in context 2 on CPU 0

Oracle: convert timestamp with timezone into date type

Simply add 0

select current_timestamp+0 from dual;

Virtualbox: use host VPN

Configuration: Windows 7 host, Fedora guest
Configure a new NAT adapter in Virtualbox network settings.
Start the VPN on Windows.
Start the VM.

> cd /etc/sysconfig/network-scripts
> sudo vi ifcfg-eth1

DEVICE=eth1
NM_CONTROLLED=no
ONBOOT=yes
BOOTPROTO=dhcp
DNS1=8.8.8.8
DNS2=8.8.4.4
TYPE=Ethernet
USERCTL=no
PEERDNS=yes
IPV6INIT=no

> sudo ifup eth1

Test connection to remote machine only available through the VPN

> ping engine1

Git: install git 1.8.4 on Fedora 14

cd /usr/local/src/
sudo wget https://www.kernel.org/pub/software/scm/git/git-1.8.4.tar.gz
sudo tar xvfz git-1.8.4.tar.gz
cd git-1.8.4
sudo yum install libcurl-devel.x86_64
sudo ./configure --prefix=$HOME --without-tcltk
sudo yum install gettext
sudo make prefix=/usr/local all
sudo make prefix=/usr/local install

Oracle: maximum number of allowed connections

Number of sessions allowed by DB:

SELECT name, value 
FROM v$parameter
WHERE name = 'sessions'

Number of opened sessions:

SELECT COUNT(*)
FROM v$session

Oracle: create job to be run @ now +10 sec.

NB: Beware to not use SYSTIMESTAMP because start_date is a TIMESTAMP WITH TIME ZONE

set serveroutput on;
begin
    dbms_output.put_line(CURRENT_TIMESTAMP);
    dbms_scheduler.create_job (
        job_name            => 'TEST_JOB',
        job_type            => 'STORED_PROCEDURE',
        job_action          => 'PROV_DB.PROV_API.testtesttest',
        start_date          => CURRENT_TIMESTAMP + 10/86400,
        enabled             => TRUE);
end;

Oracle: display jobs from scheduler

Show past jobs:

select log_date, job_name, status, owner from dba_scheduler_job_log
where job_name like 'TEST_JOB%'
order by log_date desc;

NB: STATE will be set to 'RUNNING' is the job is currently running

Show current jobs:

select start_date, job_name, job_action, job_class, owner, state, repeat_interval from dba_scheduler_jobs
where job_name like 'TEST_JOB%'
order by start_date desc;

Stop a job: always use owner.job_name

exec DBMS_SCHEDULER.stop_job('PROV_DB.MBQT_RZ_SET$1386', true);

Show why a job failed:

 SELECT LOG_DATE, OWNER, JOB_NAME, STATUS, ADDITIONAL_INFO FROM DBA_SCHEDULER_JOB_RUN_DETAILS
WHERE JOB_NAME LIKE 'MBQT_%'
AND STATUS='FAILED'
ORDER BY LOG_DATE DESC;

Show MBQT_SUSPEND jobs + 000 like argument

 select dba_scheduler_jobs.start_date, dba_scheduler_jobs.job_name, dba_scheduler_jobs.job_action, dba_scheduler_jobs.job_class, dba_scheduler_jobs.owner, dba_scheduler_jobs.state, dba_scheduler_jobs.repeat_interval,
    dba_scheduler_job_args.value
from dba_scheduler_jobs,
    dba_scheduler_job_args
where dba_scheduler_jobs.job_name like 'MBQT_SUSPEND%'
and dba_scheduler_jobs.job_name = dba_scheduler_job_args.job_name
and dba_scheduler_job_args.value like '000%'
order by dba_scheduler_jobs.start_date desc;

Run scheduled job immediately

 EXEC dbms_scheduler.run_job('RAF_CONS_DB.MBQT_SUSPEND_CO$15390405');

Then remove this job

 EXEC dbms_scheduler.drop_job('RAF_CONS_DB.MBQT_SUSPEND_CO$15390405', true);

Remove program

 SELECT * FROM DBA_SCHEDULER_PROGRAMS where program_name like '%MBQT_%';

 EXEC dbms_scheduler.drop_program('PROV_DB.MBQT_CO_POST');

Oracle: Read some columns from returned ref cursor

SET SERVEROUTPUT ON SIZE 1000000;
DECLARE
    my_rating_list DWH_RATED_USAGE.RATED_USAGE_API.cursor_type;
    rc NUMBER;
    rd VARCHAR2(200);
    my_margin_h DWH_RATED_USAGE.RATED_USAGE_API.ID_ARRAY_TYPE;
    TYPE rec_type IS RECORD (
        VPLMN_COUNTRY_NAME DWH_RATED_USAGE.RM_VPLMN_CLIENT.VPLMN_COUNTRY_NAME%TYPE,
        H_NETWORK_ID DWH_RATED_USAGE.RM_VPLMN_CLIENT.H_NETWORK_ID%TYPE
    );
    TYPE rec_array IS TABLE OF rec_type;
    my_rec_array rec_array;
BEGIN
    my_margin_h(1) := 'VPLMN_COUNTRY_NAME';
    my_margin_h(2) := 'H_NETWORK_ID';
    rc := DWH_RATED_USAGE.RATED_USAGE_API.get_margin_list(my_margin_h, '01/09/2013', '01/10/2013', my_rating_list, rd);
    IF 1 != rc THEN DBMS_OUTPUT.put_line(rd); RETURN; END IF;
    IF my_rating_list IS NULL THEN DBMS_OUTPUT.put_line('Empty cursor'); RETURN; END IF;

    SELECT  t.COLUMN_VALUE.EXTRACT('ROW/VPLMN_COUNTRY_NAME/text()').getstringval(),
            t.COLUMN_VALUE.EXTRACT('ROW/H_NETWORK_ID/text()').getstringval()
    BULK COLLECT INTO my_rec_array
    FROM TABLE(XMLSEQUENCE(my_rating_list)) t;

    FOR i IN 1..my_rec_array.COUNT LOOP
        DBMS_OUTPUT.put_line(my_rec_array(i).VPLMN_COUNTRY_NAME||' - '||my_rec_array(i).H_NETWORK_ID);
    END LOOP;
END;

Oracle: Test whether string is numeric

LENGTH(TRIM(TRANSLATE(string1, ' +-.0123456789', ' '))) IS NULL

See http://www.techonthenet.com/oracle/questions/isnumeric.php

Oracle: How to display information about network and ACL assignments

SELECT host, lower_port, upper_port, acl FROM dba_network_acls;

See Fine-Grained Access to Network Services in Oracle Database 11g

Oracle: show indexes status

select index_name, status from all_indexes where status != 'VALID';

OSX 10.8: Connect to Windows 8 host on LAN

Oracle: explain plan how to

SELECT * FROM TABLE(dbms_xplan.display);
EXPLAIN PLAN FOR SELECT ...

How to print an MP3 CD insert

Front cover: use iTunes print CD insert functionality
Back cover: use MACCC + cdlabelgen

MACCC does not work under OSX 10.8 or Linux (no GUI), so you need to use Windows.
NB: Use it fullscreen or tables remain empty (don't know why).
The options to be selected are as follows:

Then you can use the txt output file into cdlabelgen, as shown below:

And the PS output file can directly be printed on OSX.

OSX iTunes 11: How to burn a MP3 CD with folders

If you want to use an iTunes playlist composed of multiple albums and create a folder for each of these albums onto the disk to burn, you can do it by clicking on the Album column header.

If you don’t click on the Album column then the MP3 CD will be created with all of the songs in one folder.
Beware to not select Album by artist while clicking on the Album column header, or you might get some crap, e.g. folder 1 Will.i.am , folder 2 Will.i.am Featuring Britney Spears, etc. for the same album.

OSX: use TAB to switch between buttons in dialogs

• System Preferences
• Keyboards
• In the bottom of the dialog, in Full Keyboard Access section, check the All controls radio button

OSX screen capture

Shift + Command + 4

Oracle Enterprise Linux: add oracle user to sudoers

su - root
oracle
/usr/sbin/visudo

Add

oracle ALL=(ALL) ALL

or if no password prompt wanted

oracle ALL=(ALL) NOPASSWD:ALL

Change to UK keyboard on X86 Linux

setxkbmap gb

Show Oracle current jobs which name starts with

SELECT COUNT(*) FROM DBA_SCHEDULER_JOBS WHERE JOB_NAME LIKE 'MBQT_DEQ%';

How to find id of enclosing tr

var myId = $(this).closest('tr').attr('id');

How to post without redirecting / reloading the page

$.post(action, $('#MY_FORM').serialize());

How to deal with dots in HTML id

/* "." and ":" are problematic within the context of a jQuery selector
 * because they indicate a pseudo-class and class, respectively */
myId = myId.replace(/(:|\.)/g, "\\$1");

PL/SQL: sum per month

SELECT TO_CHAR(CDR_DATE, 'mm/yyyy') MONTH, SUM(CHARGED_VOL) CHARGED_VOL FROM...GROUP BY TO_CHAR(CDR_DATE, 'mm/yyyy')

PL/SQL: date to epoch

SELECT (TO_DATE('26/10/2012 12:34:14', 'dd/mm/yyyy hh24:mi:ss') - TO_DATE('01/01/1970', 'dd/mm/yyyy')) * 86400 FROM DUAL;

Diff 2 XML files

Let's say we have XML files stripped on 1 line and we want to compare them.
We just need to re-format them, then use whatever tool to compare them (e.g. diff or p4merge)

xmllint --format /tmp/GET_SUB_INFO.log > /tmp/13.xml

Perl: Date to epoch

perl -MDate::Parse -e 'print str2time("01/25/2013 19:23:22 UTC"), "\n"'

PL/SQL: epoch to date

SELECT TO_CHAR(TO_DATE('19700101','YYYYMMDD') + (1364986608/(60*60*24)),'DD:MM:YYYY HH24:MI:SS') my_date FROM dual;

Git: How to retrieve a single file from specific revision?

> git show $REV:$FILE

E.g.

> git show 4b286e8:./src/com/mobiquithings/gprstrial/ConnectivityService.java

NB: use a ./ syntax or an absolute path

PL / SQL UPDATE + JOIN

MERGE INTO T1
USING T2
ON (T1.PK = T2.PK)
WHEN MATCHED THEN 
UPDATE SET T1.VAL = T2.VAL;

Exit AirDroid

Disconnect, then press Back, and the application will ask if you want to exit

Remove empty Google+ album

There is only one way to achieve this: from Picasa3

Good bye stupid Flash ads

A native setting of Firefox prevents any plugin from being fired automatically:

This holds for Flash plugin, Java plugin, etc.

Remove Git tag

 git tag -d EXTRANET_v2.1

Check invalid packages

SELECT * FROM ALL_OBJECTS WHERE STATUS!='VALID' and owner not in ('SYS', 'PUBLIC', 'SYSTEM', 'WMSYS', 'SYSMAN', 'APEX_030200', 'VAS_P2P_DB', 'VAS_INDICATOR_DB', 'VAS_MULTIMSI_DB', 'VAS_PROPERTY_DB', 'VAS_ACK_DB', 'VAS_LOG_DB', 'VAS_TOEMAIL_DB');

Then to recompile invalid packages:

EXEC UTL_RECOMP.recomp_serial('CRM_DB');
EXEC UTL_RECOMP.recomp_serial('PROV_DB');
EXEC UTL_RECOMP.recomp_serial('RAF_CONS_DB');
EXEC UTL_RECOMP.recomp_serial('RATING_DB');
EXEC UTL_RECOMP.recomp_serial('RESOURCE_DB');
EXEC UTL_RECOMP.recomp_serial('SHARED_DB');

Or to perform the same task in parallel using the specified number of threads

EXEC UTL_RECOMP.recomp_parallel(4, 'PROV_DB');

or at database level:

EXEC UTL_RECOMP.recomp_parallel(4);

See http://www.oracle-base.com/articles/misc/recompiling-invalid-schema-objects.php for more details

Toad: Find Backward does not work, the Direction Forward and Backward are greyed out

Toad For Oracle 11

• Description

  In Find dialog, the Direction Forward and Backward are greyed out.
  Only the Forward option is selected.
  Thus, only F3 works but not Shift+F3.
  
  

• Cause

  User Setting file may have been corrupted.

• Resolution

  As a test to localize and confirm the issue, doing the following will give you a clean User Files.
  
  Here are steps to get a clean User Files folder…
  1) Select Utilities | Copy User Settings.
  2) Select “Create a clean set of User Files from the base installation” and click OK.
  3) Toad will automatically restart. Once restarted, test issue.
  You will temporarily lose your connections settings.
  (If error no longer persist, with Toad for Oracle closed: The user can copy their connections.ini, connectionpwds.ini and savedsql.dat files from the 11.5_bak folder into their newly created User Files Folder.)
  Here are steps to get old settings back…
  1) Close Toad.
  2) Delete the 11.5 folder in the C:\Users\<User>\AppData\Roaming\Quest Software\Toad for Oracle directory.
  3) Rename the 11.5_bak folder, in the same directory, to 11.5.
  4) Launch Toad.

Toad: Objects within Package Body or Spec are not sorted alphabetically in Schema Browser

Toad For Oracle 11

1. Double click a package in the Schema Browser
2. Once the package has been opened, right click then Desktop | Navigator
   
3. Right click in navigator and select "Sort and Group option
   
4. Come back to the Schema Browser and refresh

Check last PL/SQL package compile time

SELECT OWNER, OBJECT_NAME, LAST_DDL_TIME FROM ALL_OBJECTS WHERE OBJECT_TYPE='PACKAGE' AND OWNER LIKE '%_DB';

PL/SQL function: raise exceptions and fill in output variables

If we use "x_out_param OUT VARCHAR2", and an exception is raised in the function, the parameter won't be filled in.
The trick consists in using the NOCOPY keyword to pass variables by reference and not by copy.

PROCEDURE PROC(x_out_param OUT NOCOPY VARCHAR2) IS
BEGIN
    ...
    x_out_param := 'some value';
    RAISE MY_ERROR_EXCEPTION;
END PROC;

PROCEDURE MAIN(x_out_param OUT VARCHAR2) IS
BEGIN
    ...
    PROC(x_out_param);
EXCEPTION
    WHEN MY_ERROR_EXCEPTION THEN RETURN 1;
    WHEN OTHERS THEN RETURN 2;
END MAIN;