PL/SQL: using INSTR and SUBSTR is a lot faster than REGEXP_SUBSTR

Given this "labels":
"InterfaceID=Common_NCRS;TechnicalMessageID=Common_NCRS^ACMEPreOrder.quote_order^5505352925343722746--4337ac89.1404f3a3d07.-8bc;EventType=InvokedACME;PathOfService=Commons_NCRS/ProxyServices/Common_NCRS_PS;EventOccuredOn=2013-08-05T18:17:59.470+02:00;BusinessID=DE11PC089^d3a59751-38e4-eed5-7bf7-4f45592a9d18;ServerName=osbpr1ms2" and this labelname "EventOccuredOn", the following function extracts the value "2013-08-05T18:17:59.470+02:00" :

create or replace 
FUNCTION ACME_findLabelValue 
  (labels IN VARCHAR2, labelname IN VARCHAR2 )  return VARCHAR2
is 
begin
  return  REPLACE(REGEXP_SUBSTR(labels, labelname || '=[^;]+'), labelname || '=', '');
end;

Performance was abysmal. I have replaced with this much faster function:

create or replace 
FUNCTION ACME_findLabelValue 
  (labels IN VARCHAR2, labelname IN VARCHAR2 )  return VARCHAR2
is 
v_delimpos1 PLS_INTEGER;
v_delimpos2 PLS_INTEGER;
labels2 VARCHAR2(4000);
begin
 
  v_delimpos1 := INSTR(labels, labelname || '=' );
  if v_delimpos1 > 0 then
    labels2 := SUBSTR(labels, v_delimpos1 + 1 + LENGTH(labelname));
    v_delimpos2 := INSTR(labels2, ';' );
    return SUBSTR(labels2, 1, v_delimpos2 - 1);  
  else
    return '';
  end if;
end;

Associative Arrays in PL/SQL

When splitting/parsing strings in PL/SQL, using REGEXP can bee too computationally expensive.
Parsing a CSV string can be done more effectively with SUBSTR, INSTR and associative arrays.
Most of my inspiration is coming from this post.

SET SERVEROUTPUT ON;

DECLARE
  TYPE MSG_LABELS_TYPE IS TABLE OF VARCHAR2(400) INDEX BY VARCHAR2(50);
  v_delimpos1 PLS_INTEGER;
  v_delimpos2 PLS_INTEGER;
  p_delim1 VARCHAR2(1);
  p_delim2 VARCHAR2(1);
  INPUT_STRING VARCHAR2(500);
  v_label varchar(50);
  v_value varchar(400);
  v_result MSG_LABELS_TYPE;
  
BEGIN
  INPUT_STRING := 'InterfaceID=ACMEPIPPOConnector;TechnicalMessageID=ACMEPIPPOConnector^INVOICE^f6de3e52000001404d914d04ffff847a^7-382734;EventType=ACMEMessage For PIPPO Posted;PathOfService=ACME_CommonServices/ProxyServices/ACMECommonServices_NESOA_to_PIPPO_PS;EventOccuredOn=2013-08-05T10:22:11.765+02:00;BusinessID=7-382734;ServerName=osbpr1ms3';
  p_delim1  := ';';
  p_delim2  := '=';
  INPUT_STRING := INPUT_STRING || ';';
  v_delimpos1 := INSTR(INPUT_STRING, p_delim1);
  while v_delimpos1 > 0 and LENGTH(INPUT_STRING) > 1
  loop
    v_delimpos2 := INSTR(INPUT_STRING, p_delim2);
    v_label := SUBSTR(INPUT_STRING, 1, v_delimpos2 - 1);
    v_value := SUBSTR(INPUT_STRING, v_delimpos2 + 1, v_delimpos1 - v_delimpos2 - 1);
    v_result(v_label) := v_value;
    INPUT_STRING := SUBSTR(INPUT_STRING, v_delimpos1 + 1);
    v_delimpos1 := INSTR(INPUT_STRING, p_delim1);
  END LOOP;
  
  dbms_output.put_line('InterfaceID ' || v_result('InterfaceID'));
  dbms_output.put_line('TechnicalMessageID ' || v_result('TechnicalMessageID'));
  dbms_output.put_line('EventType ' || v_result('EventType'));
  dbms_output.put_line('PathOfService ' || v_result('PathOfService'));
  dbms_output.put_line('EventOccuredOn ' || v_result('EventOccuredOn'));
  dbms_output.put_line('BusinessID ' || v_result('BusinessID'));
  dbms_output.put_line('ServerName ' || v_result('ServerName'));
  
END;
/

More info on Collections here.

java.sql.SQLException: ORA-01403: no data found

In a PL/SQL function, I do:

SELECT ISRETRIABLE INTO isRetriable
from ACME_ERRORCODES
where ERRORCODE=theERRORCODE;

If theERRORCODE is not found, I get a "ORA-01403: no data found".
In reality, in this case I would like to return 0.
This can be achieved by adding the clause:

  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      return 0;
  WHEN OTHERS THEN
    RAISE;

Oracle DB PL/SQL package example

I have always considered packages as a nuisance, as an extra, useless thing. In fact, their syntax is highly redundant (why do I have to declare twice the signature of a procedure???).
But they help keep your PL/SQL organized.
Here is an example:

CREATE TABLE ACME_ALERTS (
ERRORCODE VARCHAR2(100) NOT NULL,
INTERFACEID VARCHAR2(100) NOT NULL,
CREATIONDATE DATE NOT NULL,
ISACTIVE NUMBER
);

CREATE INDEX "ACME_ALERTS_INDEX1" ON "ACME_ALERTS" ("ERRORCODE", "INTERFACEID") ;

create or replace package PKG_ACME_ALERTS
IS
 FUNCTION ACME_findAlertInstance (theERRORCODE IN VARCHAR2, theINTERFACEID IN VARCHAR2 )  return NUMBER;
 PROCEDURE ACME_insertAlertInstance (theERRORCODE IN VARCHAR2, theINTERFACEID IN VARCHAR2 );
 PROCEDURE ACME_resetAlertInstance (theERRORCODE IN VARCHAR2, theINTERFACEID IN VARCHAR2 );
END PKG_ACME_ALERTS;
/


 
CREATE OR REPLACE PACKAGE BODY PKG_ACME_ALERTS is

FUNCTION ACME_findAlertInstance 
  (theERRORCODE IN VARCHAR2, theINTERFACEID IN VARCHAR2 )  return NUMBER
is 
numberOfAlerts number;
begin
 SELECT COUNT(*) INTO numberOfAlerts
 from ACME_ALERTS
 where ERRORCODE=theERRORCODE
 and INTERFACEID=theINTERFACEID
 and ISACTIVE=1
 and CREATIONDATE < (SYSDATE - 4/24);
 
   return  numberOfAlerts;
end ACME_findAlertInstance;



PROCEDURE ACME_insertAlertInstance 
  (theERRORCODE IN VARCHAR2, theINTERFACEID IN VARCHAR2 )
as
begin
INSERT INTO ACME_ALERTS (
ERRORCODE, INTERFACEID, CREATIONDATE, ISACTIVE
) VALUES (
theERRORCODE, theINTERFACEID, SYSDATE, 1
);
end ACME_insertAlertInstance;


PROCEDURE ACME_resetAlertInstance 
  (theERRORCODE IN VARCHAR2, theINTERFACEID IN VARCHAR2 )
as
begin
UPDATE ACME_ALERTS set ISACTIVE = 0 where ERRORCODE=theERRORCODE and INTERFACEID=theINTERFACEID;
end ACME_resetAlertInstance;


end PKG_ACME_ALERTS;
/

PL/SQL to transfer files in small chunks

When confronted to copying large amounts of data in a DB, normally you don't want to do it in a mammoth transaction, since this could severely damage running application, and even fill your redo logs.

This is a sample anonymous block to run the transfer in small chunks (in the example, 7 days worth of data are transferred at 600 seconds blocks)

declare v_now  date;
v_delay_in_seconds number;

begin 

v_now := sysdate;

for thedelay in reverse 1.. 7 * 24 * 6 loop
  v_delay_in_seconds := thedelay * 600;

  insert into WLI_REPORTING_ARCHIVE
(
   MSG_GUID, HOST_NAME, MSG_LABELS, ERRORCODE, ERRORDESCRIPTION,  
 INTERFACEID, BUSINESSID, BUSINESSUNIQUEID, EVENTTYPE, 
 EVENTOCCUREDON, TECHNICALMESSAGEID, PATHOFSERVICE, 
 FILENAME, ISERROR, EVENTOCCUREDON_EPOCH, DB_TIMESTAMP, DATA_VALUE
)
select A.MSG_GUID, A.HOST_NAME, A.MSG_LABELS, A.ERRORCODE, A.ERRORDESCRIPTION,  
 A.INTERFACEID, A.BUSINESSID, A.BUSINESSUNIQUEID, A.EVENTTYPE, 
 A.EVENTOCCUREDON, A.TECHNICALMESSAGEID, A.PATHOFSERVICE, 
 A.FILENAME, A.ISERROR, A.EVENTOCCUREDON_EPOCH, A.DB_TIMESTAMP, c.data_value 
from WLI_QS_REPORT_VIEW A, WLI_QS_REPORT_ATTRIBUTE B, WLI_QS_REPORT_DATA C
where A.MSG_GUID = B.MSG_GUID and C.MSG_GUID = B.MSG_GUID  and ( v_now - B.DB_TIMESTAMP) * 24 * 3600 > v_delay_in_seconds;

WLI_REP_ARCHIVE_LOG_INS('1:INSERT', To_char( SQL%ROWCOUNT ) || ' records inserted into WLI_REPORTING_ARCHIVE older than ' || v_delay_in_seconds || ' seconds');

delete from WLI_QS_REPORT_ATTRIBUTE B where ( v_now - B.DB_TIMESTAMP) * 24 * 3600 > v_delay_in_seconds ;


WLI_REP_ARCHIVE_LOG_INS('2:DELETE', To_char( SQL%ROWCOUNT ) || ' records deleted from WLI_QS_REPORT_ATTRIBUTE older than ' || v_delay_in_seconds || ' seconds');

commit;
end loop;

end;
/

Oracle DB: deleting a HUGE table a chunk at a time in a loop

This is just an example, of course TRUNC will be much faster... but if you need to select specific records, you have no choice.....

DECLARE
  nCount  NUMBER; 
  sql1 VARCHAR2(2000);
BEGIN
  
  nCount := 0;
  sql1 := 'delete from WLI_QS_REPORT_DATA where rownum < 10000';
  LOOP
    
    EXECUTE IMMEDIATE sql1;
    nCount := sql%rowcount;
    DBMS_OUTPUT.PUT_LINE('deleted records: ' || to_char(ncount) );
    commit;    

    EXIT WHEN nCount = 0;

  END LOOP;
end;
/

PL-SQL logging info with DBMS_OUTPUT.PUT_LINE in SQL Developer

unless you explicitly enable the logging, you will never see anything...

SET SERVEROUTPUT ON;

BEGIN

  DBMS_OUTPUT.ENABLE;
 DBMS_OUTPUT.PUT_LINE('Hello...');

 DBMS_OUTPUT.PUT_LINE('Goodbye.');
 END;
 /

in a stored procedure, just do

dbms_output.enable (1000000);

PL-SQL difference of dates in seconds

this expression, as a difference of 2 dates:

TO_DATE('2012-08-13 11.20.00', 'YYYY-MM-DD HH24:MI:SS') - max(CREATIONTIME)

is a "INTERVAL DAY TO SECOND", not a "number".


This function provided by asktom

create or replace function datediff( p_what in varchar2, 
                                        p_d1   in date, 
                                        p_d2   in date ) return number 
   as 
       l_result    number; 
   begin 
       select (p_d2-p_d1) * 
              decode( upper(p_what), 
                      'SS', 24*60*60, 'MI', 24*60, 'HH', 24, NULL ) 
        into l_result from dual; 
  
       return l_result; 
   end; 
 

is very useful, you can do this:


select trunc(datediff('ss', TO_DATE('2012-08-13 11.20.00', 'YYYY-MM-DD HH24:MI:SS'), max(A.CREATIONTIME) ))

and you get the result as a numeric value!

PL-SQL function, the basics

There are Procedures and Functions.

A Function is declared like this:

CREATE OR REPLACE FUNCTION SHOULDALERT 
(
  p_technicalmessageid IN VARCHAR2  
) RETURN NUMBER IS 
v_count integer;
BEGIN
  select count(*) into v_count from nesoa2_alert_instances where technicalmessageid = p_technicalmessageid 
   and status in ('CREATED', 'PROCESSED') ;
  return v_count;
END SHOULDALERT;

and can be invoked as

select SHOULDALERT('BLABLA^PuO20111212_164122_1640.txt^AVE^1344962989697') from dual;