My first official LEFT JOIN

When I was at school I always was afraid of using LEFT OR RIGHT Joins, well, almost 6 years after I'm done with school, I had to do my first one: SELECT ACCT_ORDER.CUST_ID, ACCT_ORDER.ACCT_NUM, MTN_ORDER.mtn, MTN_ORDER.mtn_order_type, MTN_ORDER.mtn_order_state, MTN_ORDER.mtn_order_state_date, MTN_ORDER.bdy_mtn, MTN_ORDER.cidb_order_id, MTN_STATE.mtn_eligibility_state, MTN_STATE.mtn_eligibility_state_date, ACCT_ORDER.pos_order_id, ACCT_ORDER.pos_pre_order_id, ACCT_ORDER.pos_loc_cd, ACCT_ORDER.order_state, ACCT_ORDER.order_state_date FROM CUST_ACCT_ORDER ACCT_ORDER LEFT JOIN CUST_ACCT_MTN_ORDER MTN_ORDER ON ACCT_ORDER.acct_num = MTN_ORDER.acct_num AND ACCT_ORDER.cust_id = MTN_ORDER.cust_id AND ACCT_ORDER.cidb_order_id = MTN_ORDER.cidb_order_id LEFT JOIN CUST_ACCT_MTN_STATE MTN_STATE ON MTN_ORDER.cust_id = MTN_STATE.cust_id AND MTN_ORDER.acct_num = MTN_STATE.acct_num AND MTN_ORDER.mtn = MTN_STATE.mtn WHERE( (ACCT_ORDER.pos_order_id = p_pos_order_id AND ACCT_ORDER.pos_loc_cd = p_pos_loc_cd) OR ACCT_ORDER.pos_pre_order_id = p_pos_pre_order_id ) ORDER BY MTN_ORDER.mtn;

Add new parameter to Stored Procedure without impacting existing call

I have a Stored procedure already deployed in a production box, there is another porcess which wants to use it in a batch process, but the main batch process wants to control the transaction (commit/rollback). What can I do??
Add another parameter to my SP to handle the transaction or not, but If I do this the signature (DDL) of my SP is going to change!
There is one way to do this, something like adding polymorphism, on this case overload SP.
This is my original SP: CREATE OR REPLACE PROCEDURE dispo_pre_order ( p_cust_id IN NUMBER, p_acct_num IN NUMBER, p_pos_order_id IN NUMBER, p_pos_loc_cd IN VARCHAR2, p_mtn IN NUMBER ) On my iBatis side I'm calling this SP with this number of Params (5), If I add a new param, I'll have to add a new one to my DDL, right?
Not necessarily, because If you add another parameter but you don't declare it as IN neither OUT, it will be optional!!!
Then I can do this: CREATE OR REPLACE PROCEDURE dispo_pre_order ( p_cust_id IN NUMBER, p_acct_num IN NUMBER, p_pos_order_id IN NUMBER, p_pos_loc_cd IN VARCHAR2, p_mtn IN NUMBER, p_commit NUMBER DEFAULT 1 ) And nobody will notice that I change it, because you can call it with 5 params (The 6th param will be given by default in the signature: DEFAULT 1) or with 6 (assigning 0 for example to the 6th parameter)
Inside the body of my SP I'm doing something like this: IF p_commit = 1 THEN COMMIT; --Close Entire Tx END IF; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.put_line (SQLERRM); IF p_commit = 1 THEN ROLLBACK; --Roll back Tx END IF; RAISE; END;

Set Timeout when accessing Oracle with iBatis

Scenario:
There is a call which takes too much time, we don't want to wait until it's done, so in this case we can timeout our transaction and kill it on the Oracle side (well we don't kill/stop anything, we just abandon the process without any adverse impact);
Then we'll receive something like this from the DB:

ORA-01013: user requested cancel of current operation

How do you do that??
According to iBatis documentation, we can do it globally or for each statement/call we make by changing the value of defaultStatementTimeout
<settings cacheModelsEnabled="true" enhancementEnabled="true" lazyLoadingEnabled="true" maxRequests="128" maxSessions="10" maxTransactions="5" useStatementNamespaces="false" defaultStatementTimeout="5" /> defaultStatementTimeout This setting is an integer value that will be applied as the JDBC query timeout for all statements. This value can be overridden with the “statement” attribute of any mapped statement. If not specified, no query timeout will be set unless specified on the “statement” attribute of a mapped statement. The specified value is the number of seconds the driver will wait for a statement to finish. Note that not all drivers support this setting.

Or we can do it Specifically for each statement element by setting the attribute timeout, for example:
<procedure id="ShipPreOrder" parameterMap="paramsMapShipPreOrder" timeout="2"> {call ship_pre_order(?,?,?,?,?)} </procedure>

Create List of STRUCT based on a Query

This is my problem: I have a Stored Procedure which receives as parameter a List of Objects, this is my object
CREATE OR REPLACE TYPE CUST_ACCNT_MTN_PRE_ORDER AS OBJECT ( cust_id NUMBER, acct_num NUMBER, mtn NUMBER, cidb_order_id NUMBER, mtn_order_state VARCHAR(1), mtn_order_type VARCHAR(1), mtn_order_state_date DATE, bdy_mtn NUMBER )
In Oracle to create a new instance of this kind of object, the default Contructor is given by definition of the object, for example in this case to create a new instance of CUST_ACCNT_MTN_PRE_ORDER, my contructor will be something like this new CUST_ACCNT_MTN_PRE_ORDER(cust_id, acct_num, mtn, cidb_order_id, mtn_order_state, mtn_order_type, mtn_order_state_date, bdy_mtn)
And this is my Collection/List/Table of Objects
CREATE OR REPLACE TYPE CUST_ACCNT_MTN_PRE_ORDER_LIST AS TABLE OF CUST_ACCNT_MTN_PRE_ORDER
My SP is something like this (As you'll see I'm using the same objects I defined in that entry)

And my solution is something like this DECLARE p_list CUST_ACCNT_MTN_PRE_ORDER_LIST; BEGIN SELECT CUST_ACCNT_MTN_PRE_ORDER(CUST_ID, ACCT_NUM, NPA || NXX || TLN, -1, 'X', MTN_ORDER_TYPE, SYSDATE, NVL(EO_ALT_NPA||EO_ALT_NXX||EO_ALT_TLN,0)) BULK COLLECT INTO p_list FROM ( SELECT CUST_ID_NO AS CUST_ID, ACCT_NO AS ACCT_NUM, NPA , NXX , TLN, NVL(ESN_CHG_RSN_CD,'A') AS MTN_ORDER_TYPE, EO_ALT_NPA,EO_ALT_NXX,EO_ALT_TLN FROM TEST_INIT_LOAD WHERE CUST_ID_NO = my_cust_id AND ACCT_NO = my_acct_num AND EQ_ORD_NO = my_pos_order_id AND ESN_CHG_RSN_CD IS NULL UNION SELECT CUST_ID_NO AS CUST_ID, ACCT_NO AS ACCT_NUM, NPA , NXX , TLN, 'U' AS MTN_ORDER_TYPE, EO_ALT_NPA,EO_ALT_NXX,EO_ALT_TLN FROM TEST_INIT_LOAD WHERE CUST_ID_NO = my_cust_id AND ACCT_NO = my_acct_num AND EQ_ORD_NO = my_pos_order_id AND ESN_CHG_RSN_CD IS NOT NULL ); END;
The secret is in this line: SELECT CUST_ACCNT_MTN_PRE_ORDER(CUST_ID, ACCT_NUM, NPA || NXX || TLN, -1, 'X', MTN_ORDER_TYPE, SYSDATE, NVL(EO_ALT_NPA||EO_ALT_NXX||EO_ALT_TLN,0)) BULK COLLECT INTO p_list
I'm creating for each row an object of type CUST_ACCNT_MTN_PRE_ORDER and then the ResultSet is set INTO p_list variable using BULK COLLECT, which fetch all rows once


IF you want to see my Stored Proc, this is what I did

CREATE OR REPLACE PROCEDURE record_ship_temp_orders(   
 out_orders_processed   OUT   NUMBER
)
IS
my_cust_id    CUST_ACCT_ORDER.cust_id%TYPE; 
my_acct_num    CUST_ACCT_ORDER.acct_num%TYPE;
my_pos_order_id    CUST_ACCT_ORDER.pos_order_id%TYPE;
my_pos_pre_order_id    CUST_ACCT_ORDER.pos_pre_order_id%TYPE;
my_pos_loc_cd    CUST_ACCT_ORDER.pos_loc_cd%TYPE;
my_total_order_lines    CUST_ACCT_ORDER.total_order_lines%TYPE;
my_ord_add_lines_cnt    CUST_ACCT_ORDER.add_lines_cnt%TYPE;   
my_ord_upg_lines_cnt    CUST_ACCT_ORDER.upg_lines_cnt%TYPE;  
p_list  CUST_ACCNT_MTN_PRE_ORDER_LIST; 
OUT_CIDB_ORDER_ID NUMBER;

--This is the main cursor 
CURSOR cur_test_init_load
    IS
    SELECT CUST_ID_NO AS CUST_ID, 
            ACCT_NO AS ACCT_NUM, 
            EQ_ORD_NO AS POS_ORDER_ID, 
            NULL AS POS_PRE_ORDER_ID, 
            NETACE_LOC_ID AS POS_LOC_CD             
    FROM TEST_INIT_LOAD
    GROUP BY CUST_ID_NO, ACCT_NO, EQ_ORD_NO, NULL, NETACE_LOC_ID
    ORDER BY CUST_ID_NO, ACCT_NO, EQ_ORD_NO, NETACE_LOC_ID;
    
/******************************************************************************
   NAME:    record_ship_temp_orders
   PURPOSE:    Retrieve data loaded into TEST_INIT_LOAD to be processed by
     Record_Pre_Order and then shipped by Ship_Pre_Order.
   DATE:    Oct 2010
   Referenced by:    To be executed internally

******************************************************************************/    
    
BEGIN 
DBMS_OUTPUT.put_line('Starting prcess '||TO_CHAR(SYSTIMESTAMP,'hh:mi:ss.FF'));
OPEN cur_test_init_load;  
        IF cur_test_init_load%NOTFOUND THEN         
  DBMS_OUTPUT.put_line('No data found on TEST_INIT_LOAD');   
  CLOSE cur_test_init_load;
  raise_application_error(-20102, 'No data found on TEST_INIT_LOAD');
 END IF; 
 
 out_orders_processed := cur_test_init_load%ROWCOUNT; --Before the first fetch, %ROWCOUNT returns 0
 
 DBMS_OUTPUT.put_line('Start record '||TO_CHAR(SYSTIMESTAMP,'hh:mi:ss.FF'));
 LOOP --Iterate cursor
 FETCH cur_test_init_load INTO my_cust_id, my_acct_num, my_pos_order_id, my_pos_pre_order_id, my_pos_loc_cd;
 
 IF out_orders_processed = 0 THEN
  out_orders_processed := cur_test_init_load%ROWCOUNT; --Set a value to the OUT param
 END IF;
 
 EXIT WHEN cur_test_init_load%NOTFOUND;--Exits when process is completed

 DBMS_OUTPUT.put_line('Processing  '||my_cust_id||'-'||my_acct_num||', my_pos_order_id='||my_pos_order_id||', my_pos_pre_order_id='||my_pos_pre_order_id||', my_pos_loc_cd='||my_pos_loc_cd);
 
  --Calculate total_order_lines, add_lines_cnt & upg_lines_cnt
            SELECT count(1) AS TOTAL_ODER_LINES INTO my_total_order_lines
            FROM TEST_INIT_LOAD
            WHERE CUST_ID_NO = my_cust_id
              AND ACCT_NO =  my_acct_num
              AND EQ_ORD_NO = my_pos_order_id;
              
            SELECT count(1) AS ADD_LINES_CNT INTO my_ord_add_lines_cnt
            FROM TEST_INIT_LOAD
            WHERE CUST_ID_NO = my_cust_id
              AND ACCT_NO =  my_acct_num
              AND EQ_ORD_NO = my_pos_order_id
              AND ESN_CHG_RSN_CD IS NULL;
  
            SELECT count(1) AS UPG_LINES_CNT INTO my_ord_upg_lines_cnt
            FROM TEST_INIT_LOAD
            WHERE CUST_ID_NO = my_cust_id
              AND ACCT_NO =  my_acct_num
              AND EQ_ORD_NO = my_pos_order_id
              AND ESN_CHG_RSN_CD IS NOT NULL;
              

 DBMS_OUTPUT.put_line('my_total_order_lines  '||my_total_order_lines||', my_ord_add_lines_cnt='||my_ord_add_lines_cnt||', my_ord_upg_lines_cnt='||my_ord_upg_lines_cnt);
              
               --Generate List of Items to be inserted into CUST_ACCT_MTN_ORDER
            SELECT CUST_ACCNT_MTN_PRE_ORDER(CUST_ID, ACCT_NUM, NPA || NXX || TLN, -1, 'X', MTN_ORDER_TYPE, SYSDATE, NVL(EO_ALT_NPA||EO_ALT_NXX||EO_ALT_TLN,0)) BULK COLLECT INTO p_list
            FROM (  
                SELECT CUST_ID_NO AS CUST_ID, 
                            ACCT_NO AS ACCT_NUM,
                            NPA , NXX , TLN,
                            NVL(ESN_CHG_RSN_CD,'A') AS MTN_ORDER_TYPE,
                            EO_ALT_NPA,EO_ALT_NXX,EO_ALT_TLN 
                FROM TEST_INIT_LOAD
                WHERE CUST_ID_NO = my_cust_id
                  AND ACCT_NO =  my_acct_num
                  AND EQ_ORD_NO = my_pos_order_id
                  AND ESN_CHG_RSN_CD IS NULL
  
                  UNION
  
                SELECT CUST_ID_NO AS CUST_ID, 
                            ACCT_NO AS ACCT_NUM,
                            NPA , NXX , TLN,
                            'U' AS MTN_ORDER_TYPE,
                            EO_ALT_NPA,EO_ALT_NXX,EO_ALT_TLN
                FROM TEST_INIT_LOAD
                WHERE CUST_ID_NO = my_cust_id
                  AND ACCT_NO =  my_acct_num
                  AND EQ_ORD_NO = my_pos_order_id 
                  AND ESN_CHG_RSN_CD IS NOT NULL    
            ); 
            
     FOR i in 1 .. p_list.count
     LOOP
  DBMS_OUTPUT.put_line ('STRUCT '||i||'=> mtn='||p_list(i).mtn||', mtn_order_type='||p_list(i).mtn_order_type||', bdy_mtn='||p_list(i).bdy_mtn);
     END LOOP;  
     --Call SP's
      BEGIN 
   DBMS_OUTPUT.put_line('Calling Record_Pre_Order');
   RECORD_PRE_ORDER ( my_cust_id, my_acct_num, my_pos_order_id, my_pos_pre_order_id, my_pos_loc_cd, my_total_order_lines, my_ord_add_lines_cnt, my_ord_upg_lines_cnt, 0, p_list, OUT_CIDB_ORDER_ID );
   DBMS_OUTPUT.put_line('Calling Ship_Pre_Order');
   SHIP_PRE_ORDER ( my_cust_id, my_acct_num, my_pos_order_id, my_pos_loc_cd, OUT_CIDB_ORDER_ID ); 
   DBMS_OUTPUT.put_line('End record '||TO_CHAR(SYSTIMESTAMP,'hh:mi:ss.FF'));
   DBMS_OUTPUT.put_line('-------');
   p_list := NULL;
  EXCEPTION
      WHEN OTHERS
      THEN
      DBMS_OUTPUT.put_line ('***ERROR*** '||SQLERRM);
      out_orders_processed := out_orders_processed - 1; --Decrease failed orders
  END;
 END LOOP; 
DBMS_OUTPUT.put_line('Process completed');
DBMS_OUTPUT.put_line('End of process '||TO_CHAR(SYSTIMESTAMP,'hh:mi:ss.FF'));
CLOSE cur_test_init_load;
 
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line (SQLERRM);
      ROLLBACK;
      RAISE;
END;
/

SQL Joins explained with Venn's Diagrams

SQL Joins explicados de forma gráfica con diagramas Venn

Una simple ayuda

El lenguaje SQL es aveces el gran olvidado por los desarrolladores, cada vez abundan mas los frameworks que abstraen al desarrollador del contacto con el modelo de negocio.

He escrito este documento, basándome en otros similares para ayudar a entender con un diagrama de Vann, los diferentes tipos de Join's que SQL92 soporta.

Existen tecnologías que abstraen completamente del modelo de negocio, para el desarrollador funcionan creando una serie de clases de Dominio que define el modelo, sin importar qué base de datos o de qué fabricante sea esta. Por ejemplo Ruby on Rails, GRails, ... usando un conjunto de tecnologías, como Hibernate configuradas por convención dentro del propio framework.

También es muy extendido el uso de aplicaciones que permiten modelar el negocio de forma gráfica, ERM (como DBSchema), y normalmente después se usa un ORM's que les hacen el trabajo sucio de forma elegante y segura.

Todo vale, pero la realidad de las empresas TIC es que necesitan profesionales serios y conscientes que entiendan y controlen todas las capas para que un sistema funciona como se espera y está diseñado, y no se deje nada al azar.

El desarrollador, muy frecuentemente recurre a activar trazas o aplicaciones de monitorización de actividad que nos desvelen, qué está haciendo nuestro framework con nuestro modelo de negocio. Existen aplicaciones que esnifan directamente de la base de datos esta información para que podamos analizarla, el obsoleto Profile y Analyzer que incluía Microsoft SQL Server es un ejemplo de ellos.

Finalmente cuando tenemos delante la query sucede que hace tanto tiempo que no trabajamos con SQL que no entendemos qué hace exactamente, sobre todo si está trabajando con tablas relacionadas y nos encontramos con una consulta que afecta a varias entidades.

Espero que este documento ayude a reducir el tiempo invertido a descubrir porqué se comporta una SQL de un modo u otro.

Convenciones Este documento asume que siempre que la "TABLA A" esta a la izquierda y la "TABLA B" a la derecha de las sentencias. Para los ejemplos vamos a utilizar 2 tablas que van a contener los siguientes datos: id name | id name 1 Roberto | 1 Alex 2 Juan | 2 Carlos 3 Rubén | 3 Juan 4 Carlos | 4 Saúl
INNER JOIN SELECT * FROM TableA INNER JOIN TableB ON TableA.name = TableB.name id name | id name 2 Juan | 2 Carlos 4 Carlos | 3 Juan El resultado son solo el conjunto de registros que coinciden en ambas tablas.
FULL OUTER JOIN SELECT * FROM TableA FULL OUTER JOIN TableB ON TableA.name = TableB.name id name | id name 1 Roberto | null null 2 Juan | 3 Juan 3 Rubén | null null 4 Carlos | 2 Carlos null null | 1 Alex null null | 4 Saúl El resultado es el conjunto total de registros de ambas tablas, coincidiendo aquellos registros cuando sea posible. Si no hay conicidencias, se asignan nulos.
FULL OUTER JOIN WHERE SELECT * FROM TableA FULL OUTER JOIN TableB ON TableA.name = TableB.name WHERE TableA.id IS null OR TableB.id IS null id name | id name 1 Roberto | null null 3 Rubén | null null null null | 1 Alex null null | 4 Saúl El resultado es un conjunto de records únicos en la TablaA y en la TablaB, hacemos el Full Outer Join y excluimos los registros que no queremos con el Where
LEFT OUTER JOIN SELECT * FROM TableA LEFT OUTER JOIN TableB ON TableA.name = TableB.name id name | id name 1 Roberto | null null 2 Juan | 3 Juan 3 Rubén | null null 4 Carlos | 2 Carlos El resultado son todos los registros de la TablaA, y si es posible las coincidencias de la TablaB. Si no hay coincidencias, el lado derecho mostrará nulos.
LEFT OUTER JOIN WHERE SELECT * FROM TableA LEFT OUTER JOIN TableB ON TableA.name = TableB.name WHERE TableB.id IS null id name | id name 1 Roberto | null null 3 Rubén | null null El resultado es un conjunto de registros que solo están en la TablaA, no en la TablaB. Hacemos lo mismo que en un Left Outer Join, pero eliminamos los registros que no queremos de la TablaB con el Where
CROSS JOIN Existe también la posibilidad de cruzar todos los registros con todos (producto cartesiano), imposible de dibujar con un diagramas Venn SELECT * FROM TableA CROSS JOIN TableB Imaginarse el resultado de todos los registros por todos es muy fácil, si tenemos 4 registros en cada tabla 4 x 4 = 16.	¡Ojo al hacer esto en tablas con muchos registros!