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;