PLSQL - Cursor Liked For Loop Example

    BEGIN
        FOR RESULT IN (
            SELECT C.CASEKEY AS THECASEKEY, CE.CASEEVENTKEY AS THECASEEVENTKEY
            FROM CASES C
            JOIN CASEEVENT CE ON C.CASEKEY = CE.CASEKEY
            )
        LOOP
            UPDATE CASES
            SET CURRENTCASEEVENTKEY = RESULT.THECASEEVENTKEY
            WHERE CASEKEY = RESULT.THECASEKEY;
        END LOOP;
    END;
    

PLSQL - Drop Table if Exist

DECLARE 
C INT; 
BEGIN 
  SELECT COUNT(*) INTO C FROM USER_TABLES WHERE TABLE_NAME = UPPER('TEMPTABLE');
  IF C=1 THEN 
      EXECUTE IMMEDIATE 'DROP TABLE TEMPTABLE';
   END IF;
END;

PLSQL - SQLPlus Patch File Template

ACCEPT LOG_DIR PROMPT 'Please enter the full path to the log files  (default  C:\Temp\Log) : ' DEFAULT C:\Temp\Log
SPOOL &&LOG_DIR\MyProject_MyIssue_MyDate.log

/*
Author : 
Date   : 
Client : 
Comments: 

*/

Prompt #############################################################
Prompt                 Connection Details
Prompt #############################################################
set termout  off
define _USER
define _CONNECT_IDENTIFIER
define _PRIVILEGE
define _O_VERSION
define _DATE
set termout  on
Prompt #############################################################
Prompt 
Prompt #############################################################

SET TIMING ON
------------------Script Start--------------------------

select settingvalue ENVIRONMENT from globalsetting where settingkey = 94;


PROMPT
PROMPT
Prompt #############################################################
PROMPT  1.1: 
    
PROMPT
PROMPT
Prompt #############################################################
PROMPT  1.2: 
    
PROMPT
PROMPT
Prompt #############################################################
PROMPT  1.3 

------------------Script End-----------------------------

Prompt #############################################################
Prompt #############################################################
prompt

SPOOL OFF

HOST &&LOG_DIR\MyProject_MyIssue_MyDate.log

PLSQL - For Loop Example

CREATE TABLE TEMPTABLE
AS
SELECT ROW_NUMBER() OVER (ORDER BY L.IPCONTACTTYPEID) AS ID, R.RECORDID AS RECORDID, L.IPCONTACTTYPEID AS IPCONTACTTYPEID, I.LONGNAME AS NAME, GM.GLOBALMEDIAKEY AS GMKEY
FROM SRC_RECORD R
JOIN SRC_CONFLICT C ON R.RECORDID = C.CONFLICTID
JOIN SRC_LNKIPCONTACT L ON R.RECORDID = L.RECORDID 
JOIN SRC_USERINFORMATION I ON L.USERID = I.USERID 
JOIN GLOBALMEDIA GM ON GM.PARENTKEY = R.CASEKEY 
JOIN TABLEMEDIATYPE TMT ON TMT.MEDIATYPEKEY = GM.MEDIATYPEKEY
WHERE TMT.MEDIATYPEDESCRIPTION = 'Additional Notes' 
AND L.IPCONTACTTYPEID IN ( 65, 66, 57, 107 ) 
ORDER BY 1; 

DECLARE IFINAL NUMBER;
BEGIN        
SELECT MAX(ID) INTO IFINAL FROM TEMPTABLE;
FOR ICOUNT IN 1..IFINAL 
LOOP        
UPDATE GLOBALMEDIALOB R
SET R.LONGVALUE = ( SELECT C2B( BLOB_TO_CLOB( R.LONGVALUE ) ||
TO_CLOB ( CHR(13)||CHR(10)|| 
CASE TT.IPCONTACTTYPEID
WHEN 65 THEN 'Searcher - ' || TT.NAME 
WHEN 66 THEN 'Third Party Contact - ' || TT.NAME
WHEN 57 THEN 'Law Firm Contact - ' || TT.NAME
WHEN 107 THEN 'Patent Lead - ' || TT.NAME
END ))
FROM TEMPTABLE TT WHERE R.GLOBALMEDIAKEY = TT.GMKEY AND TT.ID = ICOUNT)
WHERE R.GLOBALMEDIAKEY = ( SELECT GMKEY FROM TEMPTABLE WHERE ID = ICOUNT );
END LOOP;
END;

COMMIT;

DROP TABLE TEMPTABLE;

Oracle - blob_to_clob

create or replace FUNCTION blob_to_clob (blob_in IN BLOB)
RETURN CLOB
AS
v_clob CLOB;
v_varchar VARCHAR2(32767);
v_start PLS_INTEGER := 1;
v_buffer PLS_INTEGER := 32767;
BEGIN
DBMS_LOB.CREATETEMPORARY(v_clob, TRUE);
FOR i IN 1..CEIL(DBMS_LOB.GETLENGTH(blob_in) / v_buffer)
LOOP
   v_varchar := UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(blob_in, v_buffer, v_start));
DBMS_LOB.WRITEAPPEND(v_clob, LENGTH(v_varchar), v_varchar);
v_start := v_start + v_buffer;
END LOOP;
RETURN v_clob;
END blob_to_clob;

Oracle - c2b

create or replace FUNCTION                c2b( c IN CLOB ) RETURN BLOB
IS
position PLS_INTEGER := 1;
buffer RAW( 32767 );
res BLOB;
lob_len PLS_INTEGER := DBMS_LOB.getLength( c );
BEGIN
DBMS_LOB.createTemporary( res, TRUE );
DBMS_LOB.OPEN( res, DBMS_LOB.LOB_ReadWrite );
LOOP
buffer := UTL_RAW.cast_to_raw( DBMS_LOB.SUBSTR( c, 10000, position ) );
IF UTL_RAW.LENGTH( buffer ) > 0 THEN
DBMS_LOB.writeAppend( res, UTL_RAW.LENGTH( buffer ), buffer );
END IF;
position := position + 10000;
EXIT WHEN position > lob_len;
END LOOP;
RETURN res;
END c2b;

Cumulative No. of Patents Filed and Grant per Year (1 Apr)

SELECT Y1.Myyear||'/'||SUBSTR(TO_NUMBER(Y1.MYYEAR)+1,1) AS YEAR, SUM(Y2.FILCOUNT) AS "Cumulative Filing Case", SUM(Y2.GRTCOUNT) As "Cumulative Granted Case"
FROM 
    (   SELECT   Myyear AS ID, Myyear, SUM(FILCOUNT) AS FILCOUNT, SUM(GRTCOUNT) AS GRTCOUNT
        FROM (  SELECT extract (year from add_months(NVL(FILL.eventdate,FIL.eventdate), -3)) as Myyear, COUNT (NVL(FILL.eventdate,FIL.eventdate)) AS FILCOUNT, NULL AS GRTCOUNT FROM CASES C
                LEFT JOIN CASEEVENT FIL ON C.CASEKEY = FIL.CASEKEY AND FIL.EVENTKEY = 10
                LEFT JOIN CASEEVENT FILL ON C.CASEKEY = FILL.CASEKEY AND FILL.EVENTKEY = 110 
                  JOIN CASENAME CN ON C.CASEKEY = CN.CASEKEY AND CN.NAMETYPEKEY = 206
                  JOIN NAME N ON CN.NAMEKEY = N.NAMEKEY AND ( N.NAME = 'xxxx' OR N.NAMECODE = 'xxxx' )
                WHERE FIL.EVENTDATE IS NOT NULL AND C.CASETYPEKEY = 1 
                GROUP BY extract (year from add_months(NVL(FILL.eventdate,FIL.eventdate), -3)) 
                UNION
                SELECT extract (year from add_months(GRT.eventdate, -3)) as Myyear, NULL AS FILCOUNT, COUNT (GRT.EVENTDATE) AS GRTCOUNT FROM CASES C
                LEFT JOIN CASEEVENT GRT ON C.CASEKEY = GRT.CASEKEY AND GRT.EVENTKEY = 25
                  JOIN CASENAME CN ON C.CASEKEY = CN.CASEKEY AND CN.NAMETYPEKEY = 206
                  JOIN NAME N ON CN.NAMEKEY = N.NAMEKEY AND ( N.NAME = 'xxxx' OR N.NAMECODE = 'xxxx' )
                WHERE GRT.EVENTDATE IS NOT NULL AND C.CASETYPEKEY = 1 
                GROUP BY extract (year from add_months(GRT.eventdate, -3)) ) X1 
        GROUP BY MYYEAR ORDER BY 1 ) Y1
JOIN
    (   SELECT   Myyear AS ID, Myyear, SUM(FILCOUNT)  AS FILCOUNT, SUM(GRTCOUNT) AS GRTCOUNT
        FROM (  SELECT extract (year from add_months(NVL(FILL.eventdate,FIL.eventdate), -3)) as Myyear, COUNT (NVL(FILL.eventdate,FIL.eventdate)) AS FILCOUNT, NULL AS GRTCOUNT FROM CASES C
                LEFT JOIN CASEEVENT FIL ON C.CASEKEY = FIL.CASEKEY AND FIL.EVENTKEY = 10 
                LEFT JOIN CASEEVENT FILL ON C.CASEKEY = FILL.CASEKEY AND FILL.EVENTKEY = 110 
                  JOIN CASENAME CN ON C.CASEKEY = CN.CASEKEY AND CN.NAMETYPEKEY = 206
                  JOIN NAME N ON CN.NAMEKEY = N.NAMEKEY AND ( N.NAME = 'xxxx' OR N.NAMECODE = 'xxxx' )
                WHERE FIL.EVENTDATE IS NOT NULL AND C.CASETYPEKEY = 1 
                GROUP BY extract (year from add_months(NVL(FILL.eventdate,FIL.eventdate), -3)) 
                UNION
                SELECT extract (year from add_months(GRT.eventdate, -3)) as Myyear, NULL AS FILCOUNT, COUNT (GRT.EVENTDATE) AS GRTCOUNT FROM CASES C
                LEFT JOIN CASEEVENT GRT ON C.CASEKEY = GRT.CASEKEY AND GRT.EVENTKEY = 25
                  JOIN CASENAME CN ON C.CASEKEY = CN.CASEKEY AND CN.NAMETYPEKEY = 206
                  JOIN NAME N ON CN.NAMEKEY = N.NAMEKEY AND ( N.NAME = 'xxxx' OR N.NAMECODE = 'xxxx' )
                WHERE GRT.EVENTDATE IS NOT NULL AND C.CASETYPEKEY = 1 
                GROUP BY extract (year from add_months(GRT.eventdate, -3)) ) X2 
        GROUP BY MYYEAR ORDER BY 1 ) Y2  ON Y1.ID >= Y2.ID
GROUP BY Y1.ID, Y1.Myyear        
ORDER BY 1 

Cumulative No. of Patents Filed and Grant per Year (1 July)

SELECT Y1.Myyear||'/'||SUBSTR(TO_NUMBER(Y1.MYYEAR)+1,1) AS YEAR, SUM(Y2.FILCOUNT) AS "Cumulative Filing Case", SUM(Y2.GRTCOUNT) As "Cumulative Granted Case"
FROM
    (   SELECT   Myyear AS ID, Myyear, SUM(FILCOUNT) AS FILCOUNT, SUM(GRTCOUNT) AS GRTCOUNT
        FROM (  SELECT extract (year from add_months(NVL(FILL.eventdate,FIL.eventdate), -6)) as Myyear, COUNT (NVL(FILL.eventdate,FIL.eventdate)) AS FILCOUNT, NULL AS GRTCOUNT FROM CASES C
                LEFT JOIN CASEEVENT FIL ON C.CASEKEY = FIL.CASEKEY AND FIL.EVENTKEY = 10
                LEFT JOIN CASEEVENT FILL ON C.CASEKEY = FILL.CASEKEY AND FILL.EVENTKEY = 110
                  JOIN CASENAME CN ON C.CASEKEY = CN.CASEKEY AND CN.NAMETYPEKEY = 206
                  JOIN NAME N ON CN.NAMEKEY = N.NAMEKEY AND ( N.NAME = 'xxxx' OR N.NAMECODE = 'xxxx' )
                WHERE FIL.EVENTDATE IS NOT NULL AND C.CASETYPEKEY = 1
                GROUP BY extract (year from add_months(NVL(FILL.eventdate,FIL.eventdate), -6))
                UNION
                SELECT extract (year from add_months(GRT.eventdate, -6)) as Myyear, NULL AS FILCOUNT, COUNT (GRT.EVENTDATE) AS GRTCOUNT FROM CASES C
                LEFT JOIN CASEEVENT GRT ON C.CASEKEY = GRT.CASEKEY AND GRT.EVENTKEY = 25
                  JOIN CASENAME CN ON C.CASEKEY = CN.CASEKEY AND CN.NAMETYPEKEY = 206
                  JOIN NAME N ON CN.NAMEKEY = N.NAMEKEY AND ( N.NAME = 'xxxx' OR N.NAMECODE = 'xxxx' )
                WHERE GRT.EVENTDATE IS NOT NULL AND C.CASETYPEKEY = 1
                GROUP BY extract (year from add_months(GRT.eventdate, -6)) ) X1
        GROUP BY MYYEAR ORDER BY 1 ) Y1
JOIN
    (   SELECT   Myyear AS ID, Myyear, SUM(FILCOUNT)  AS FILCOUNT, SUM(GRTCOUNT) AS GRTCOUNT
        FROM (  SELECT extract (year from add_months(NVL(FILL.eventdate,FIL.eventdate), -6)) as Myyear, COUNT (NVL(FILL.eventdate,FIL.eventdate)) AS FILCOUNT, NULL AS GRTCOUNT FROM CASES C
                LEFT JOIN CASEEVENT FIL ON C.CASEKEY = FIL.CASEKEY AND FIL.EVENTKEY = 10
                LEFT JOIN CASEEVENT FILL ON C.CASEKEY = FILL.CASEKEY AND FILL.EVENTKEY = 110
                  JOIN CASENAME CN ON C.CASEKEY = CN.CASEKEY AND CN.NAMETYPEKEY = 206
                  JOIN NAME N ON CN.NAMEKEY = N.NAMEKEY AND ( N.NAME = 'xxxx' OR N.NAMECODE = 'xxxx' )
                WHERE FIL.EVENTDATE IS NOT NULL AND C.CASETYPEKEY = 1
                GROUP BY extract (year from add_months(NVL(FILL.eventdate,FIL.eventdate), -6))
                UNION
                SELECT extract (year from add_months(GRT.eventdate, -6)) as Myyear, NULL AS FILCOUNT, COUNT (GRT.EVENTDATE) AS GRTCOUNT FROM CASES C
                LEFT JOIN CASEEVENT GRT ON C.CASEKEY = GRT.CASEKEY AND GRT.EVENTKEY = 25
                  JOIN CASENAME CN ON C.CASEKEY = CN.CASEKEY AND CN.NAMETYPEKEY = 206
                  JOIN NAME N ON CN.NAMEKEY = N.NAMEKEY AND ( N.NAME = 'xxxx' OR N.NAMECODE = 'xxxx' )
                WHERE GRT.EVENTDATE IS NOT NULL AND C.CASETYPEKEY = 1
                GROUP BY extract (year from add_months(GRT.eventdate, -6)) ) X2
        GROUP BY MYYEAR ORDER BY 1 ) Y2  ON Y1.ID >= Y2.ID
GROUP BY Y1.ID, Y1.Myyear       
ORDER BY 1

Create Dummy Big File

File Size :

1M = 1024 x 1024 = 1048576

10M = 1024 x 1024 x 10 = 10485760

fsutil file createnew BigFile10M.txt 10485760

Gen PCT-National Case Details

-- PCT Details

SELECT P.CASEREFERENCE AS PCTCase, C.CASEREFERENCE AS NationalCase, PCT.EVENTNUMBER AS "PCT filing number", PCT.EVENTDATE AS "PCT filing date",
FLG.EVENTNUMBER AS "Filing Number", FLG.EVENTDATE AS "Filing Date",
DEPL.EVENTNUMBER AS "Local filing Number", DEPL.EVENTDATE AS "Local filing Date",
DES.EVENTNUMBER AS "Designated Number", DES.EVENTDATE AS "Designated Date"
FROM CASES P 
LEFT JOIN CEIRELATION CR ON CR.PARENTKEY = P.CASEKEY AND CR.RELATIONTYPE = 105
    LEFT JOIN CASEEVENT PCT ON P.CASEKEY = PCT.CASEKEY AND PCT.EVENTKEY = 10
LEFT JOIN CASES C ON CR.CASEKEY = C.CASEKEY 
    LEFT JOIN CASEEVENT FLG ON FLG.CASEKEY = C.CASEKEY AND FLG.EVENTKEY = 10
    LEFT JOIN CASEEVENT DEPL ON DEPL.CASEKEY = C.CASEKEY AND DEPL.EVENTKEY = 110
    LEFT JOIN CASEEVENT DES ON DES.CASEKEY = C.CASEKEY AND DES.EVENTKEY = 103
WHERE P.CASEREFERENCE LIKE '%WO%' 
ORDER BY 1,2

List all Tab

SELECT TC.TABCONTROLNO, C.CRITERIANO, C.CASEOFFICEID, O.DESCRIPTION AS OFFICE, CT.CASETYPE, CT.CASETYPEDESC, PT.PROPERTYTYPE, PT.PROPERTYNAME, CTY1.COUNTRYCODE, CTY1.COUNTRY, CTY2.COUNTRY AS VALIDCOUNTRY, CC.CASECATEGORY, CC.CASECATEGORYDESC, ISNULL(VC1.CASECATEGORYDESC, VC2.CASECATEGORYDESC) AS VALIDCASECATEGORYDESC,
       C.PROGRAMID, C.PROPERTYTYPE, C.PROPERTYUNKNOWN, C.COUNTRYCODE, C.COUNTRYUNKNOWN, C.CASECATEGORY, C.CATEGORYUNKNOWN, WC.WINDOWCONTROLNO, WC.WINDOWNAME, WC.ISEXTERNAL, TC.TABCONTROLNO, TC.TABNAME, TC.DISPLAYSEQUENCE, TC.TABTITLE
FROM CRITERIA C JOIN WINDOWCONTROL WC ON C.CRITERIANO = WC.CRITERIANO
LEFT JOIN TABCONTROL TC ON WC.WINDOWCONTROLNO = TC.WINDOWCONTROLNO
LEFT JOIN OFFICE O ON C.CASEOFFICEID = O.OFFICEID
LEFT JOIN CASETYPE CT ON C.CASETYPE = CT.CASETYPE
LEFT JOIN PROPERTYTYPE PT ON C.PROPERTYTYPE = PT.PROPERTYTYPE
LEFT JOIN CASECATEGORY CC ON CC.CASETYPE = C.CASETYPE AND CC.CASECATEGORY = C.CASECATEGORY
LEFT JOIN COUNTRY CTY1 ON C.COUNTRYCODE = CTY1.COUNTRYCODE
LEFT JOIN VALIDCATEGORY VC1 ON VC1.CASETYPE = C.CASETYPE AND VC1.PROPERTYTYPE = C.PROPERTYTYPE AND VC1.CASECATEGORY = C.CASECATEGORY AND VC1.COUNTRYCODE = C.COUNTRYCODE
LEFT JOIN VALIDCATEGORY VC2 ON VC2.CASETYPE = C.CASETYPE AND VC2.PROPERTYTYPE = C.PROPERTYTYPE AND VC2.CASECATEGORY = C.CASECATEGORY AND VC2.COUNTRYCODE = 'ZZZ'
LEFT JOIN COUNTRY CTY2 ON CTY2.COUNTRYCODE = ISNULL(VC1.COUNTRYCODE, VC2.COUNTRYCODE)
WHERE TABTITLE LIKE '%Case Billing info%'
ORDER BY C.CRITERIANO, TC.DISPLAYSEQUENCE