PL/SQL cribsheet

PL/SQL merge

To merge a new value into a table, without causing constraint violations, you can do so in a single statement. This is also useful when you need to merge data from one table to another.

MERGE INTO ANDY
USING (
	SELECT 3 ID,'three' NAME FROM dual
) SRC
ON ( ANDY.ID = SRC.ID )
WHEN NOT MATCHED THEN INSERT (ID,NAME) VALUES (SRC.ID,SRC.NAME);

Batching updates

It is sometimes necessary to update a huge amount of data, and to so with with negligible impact on other users of the system. In this case, we split the updates into batches and commit them on the go. This example shows how updates are achieved in batches of 1,000. When it has no more to update, it will exit its loop and continue.

DECLARE
	BATCH_SIZE CONSTANT INT := 1000;
LOOP
	UPDATE PARCEL
	  SET UUID = RANDOMUUID()
	  WHERE UUID IS NULL
	  AND ROWNUM < BATCH_SIZE;
	EXIT WHEN SQL%ROWCOUNT = 0;
	COMMIT;
END LOOP;

DDL extraction

If you want to extract the DDL scripts for all indexes in the schema starting with "IDX_", you can use the dbms_metadata.GET_DDL package.

select dbms_metadata.GET_DDL('INDEX',object_name,'DM_1_1')
from user_objects
where object_name like 'IDX_%'
and object_type='INDEX';

Finding active SQL statements

Sometimes, it is useful to know what SQL statements are currently executing. This query lists the SQL currently "active". To use this, you need to be logged in with SYSDBA privileges.

SELECT VS.SID,SQL_TEXT
FROM GV$SESSION VS,GV$SQLTEXT ST
WHERE VS.STATUS = 'ACTIVE' AND VS.USERNAME IS NOT NULL
AND VS.INST_ID = ST.INST_ID AND VS.SQL_ADDRESS = ST.ADDRESS AND VS.SQL_HASH_VALUE = ST.HASH_VALUE
ORDER BY VS.SID,ST.SQL_ID,ST.PIECE;

There are other methods, and one can be found here.

Finding the number of active SQL statements

Oracle's performance is greatly affected by the number of active SQL statements. This query simply returns you the number of statements currently being executed. To use this, you need to be logged in with SYSDBA privileges.

SELECT COUNT(*)
FROM GV$SESSION VS
WHERE VS.STATUS = 'ACTIVE' AND VS.USERNAME IS NOT NULL;

New columns

To add a column to a table if it does not already exist, execute the following:

DECLARE
	theCount INT;
BEGIN
	SELECT COUNT(*) INTO theCount FROM user_tab_columns 
         WHERE TABLE_NAME='COUNTRY' AND COLUMN_NAME='INVOICE_COPIES';
	IF theCount = 0 THEN
		EXECUTE IMMEDIATE 'ALTER TABLE COUNTRY ADD (INVOICE_COPIES INT)';
	END IF;
END;
/

Search through procedure code

To look for all procedures, triggers and functions what mention a particular keyword, use the following:

SELECT * FROM USER_SOURCE WHERE UPPER(TEXT) LIKE '%KEYWORD%';

Lead and lag aggregation

In DM, it is sometimes useful to identify gaps in sequences of numbers (such as UPI usage). Oracle provides a LEAD and LAG construct to look at the next or previous rows. This example will identify the start and end of each gap of contiguous UPI usage for a carrier within a retailer.

SELECT RETAILERID,CARRIERID,UPI+1 GAP_START,UPI_NEXT-1 GAP_END
FROM
(
  SELECT RETAILERID,CARRIERID,UPI,
         LEAD(UPI,1,0) OVER (ORDER BY RETAILERID,CARRIERID,UPI) UPI_NEXT
  FROM PARCEL P,CONSIGNMENT C
  WHERE P.CONSIGNMENTID = C.CONSIGNMENTID
  AND C.CONSIGNMENTSTATUSID>0
  AND C.RETAILERID = 67
)
WHERE UPI_NEXT - UPI > 2
ORDER BY RETAILERID,CARRIERID,GAP_START,GAP_END;

Finding the first in a group

There are many occasions when you only want to consider the first row of each group in a GROUP BY clause. This example will return the name and code of the first carrier service (as ordered by it's ID) for each carrier in DM:

SELECT CS.CARRIERID,
  MAX(CS.NAME) KEEP (DENSE_RANK FIRST ORDER BY CS.CARRIERSERVICEID) AS SERVICE_NAME,
  MAX(CS.METAPACKCARRIERSERVICECODE) KEEP (DENSE_RANK FIRST ORDER BY CS.METAPACKCARRIERSERVICECODE) AS SERVICE_CODE
FROM CARRIER_SERVICE CS
GROUP BY CS.CARRIERID;

Concatenating aggregates

You can concatenate fields into a long string by using the LISTAGG function. This particular example will produce a comma-separated list of PARCELID associated with each consignment.

SELECT CONSIGNMENTID,LISTAGG(PARCELID, ',')  WITHIN GROUP (ORDER BY PARCELID) AS PARCELIDS
FROM PARCEL
WHERE ROWNUM<1000
GROUP BY CONSIGNMENTID
ORDER BY CONSIGNMENTID;

Another example of this will identify all the areas associated with a part of a postcode. This demonstrates how to use the call using a larger group-by clause. The ORDER BY identified at the WITHIN GROUP section defines the order in which the AREAIDs will be placed into the concatenated list.

SELECT COUNTRYCODE,POSTCODE,POSTCODELEVEL, LISTAGG(AREAID, ',') WITHIN GROUP (ORDER BY AREAID) AS AREAIDS
FROM AREA_POSTCODE
WHERE EXCLUDEFLAG = 0
GROUP BY COUNTRYCODE,POSTCODE,POSTCODELEVEL
ORDER BY 1,2,3;

Cubes

Cubing is a rather large subject, but this introduction to the CUBE clause is excellent, particularly when learning the basics.

Timezones

To convert a date/time between time zones is a little awkward, but here is an example of how to take the current system time (assuming it's in London), and outputting it as UTC:

SELECT
    SYSDATE NOW,
    FROM_TZ(CAST(SYSDATE AS TIMESTAMP),'Europe/London') NOW_AS_TIMESTAMP_WITH_TZ,
    FROM_TZ(CAST(SYSDATE AS TIMESTAMP),'Europe/London') AT TIME ZONE 'UTC' NOW_AS_TIMESTAMP_WITH_TZ_UTC
FROM DUAL;

Preventing triggers from firing

Sometimes you want to merge in a lot of data without triggers from firing. This statement will effectively disable all triggers for the current session:

exec sys.dbms_xstream_gg.set_foo_trigger_session_contxt(fire=>true);

For this to be accessible, the following needs to be executed in the SYS schema:

CREATE PUBLIC SYNONYM DBMS_SYSTEM_GG FOR DBMS_SYSTEM_GG;
GRANT EXECUTE ON DBMS_SYSTEM_GG TO <insert_schema_name_here>;