Oracle: record vs object

Record version
In package:

1 2 3 4 5

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:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38

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:

1 2 3 4 5 6 7 8 9 10

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:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31

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

1	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

1 2 3 4 5 6 7 8 9 10

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:

1 2 3

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

Number of opened sessions:

1 2	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

1 2 3 4 5 6 7 8 9 10

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:

1 2 3

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:

1 2 3

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

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

Show why a job failed:

1 2 3 4

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

1 2 3 4 5 6 7 8

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

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

Then remove this job

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

Remove program

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

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

Oracle: Read some columns from returned ref cursor

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28

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;