search and replace in an Oracle CLOB

Here comes a very useful function which replaces a string in a CLOB with another string the function has been taken out from http://www.astral-consultancy.co.uk/cgi-bin/hunbug/doco.cgi?11080

--      The function accepts three parameters, the same as the standard REPLACE function.
       --1.    p_clob: The CLOB data
       --2.    p_what: The search string
       --3.    p_with: The replacement string
       FUNCTION dfn_clobReplace
       ( p_clob          IN CLOB,
         p_what          IN VARCHAR2,
         p_with          IN VARCHAR2 ) RETURN CLOB IS
 
         c_whatLen       CONSTANT PLS_INTEGER := LENGTH(p_what);
         c_withLen       CONSTANT PLS_INTEGER := LENGTH(p_with);
 
         l_return        CLOB;
         l_segment       CLOB;
         l_pos           PLS_INTEGER := 1-c_withLen;
         l_offset        PLS_INTEGER := 1;
 
       BEGIN
 
         --It first checks that the p_what string is not null, to save time checking the CLOB.
         IF p_what IS NOT NULL THEN
           --The function then begins the first of two loops. The outer loop splits the main CLOB into segments of 32K, so that the inner loop can process the data.
           WHILE l_offset < DBMS_LOB.GETLENGTH(p_clob) LOOP
             l_segment := DBMS_LOB.SUBSTR(p_clob,32767,l_offset);
             LOOP --The inner loop then checks for the search string, p_what, in the segment of CLOB that is current being processed.
               l_pos := DBMS_LOB.INSTR(l_segment,p_what,l_pos+c_withLen);
               EXIT WHEN (NVL(l_pos,0) = 0) OR (l_pos = 32767-c_withLen);
               --If the search string is not found it exits the loop to process the next segment. 
               --If the search string is found at the very end of the string, it moves on to the next segment to be processed, 
               --as the **** segments overlap by the length of the search string ****, so a search string occurring over a segment break does not get missed.
               l_segment := TO_CLOB( DBMS_LOB.SUBSTR(l_segment,l_pos-1)
                                   ||p_with
                                   ||DBMS_LOB.SUBSTR(l_segment,32767-c_whatLen-l_pos-c_whatLen+1,l_pos+c_whatLen));
             END LOOP;
             --The segment is then rebuilt, from the data before the search string, the replacement string, then the data after the search string.
             l_return := l_return||l_segment;
             l_offset := l_offset + 32767 - c_whatLen;
             --The processed segment is then added to the return CLOB and the offset for the segment is increased by 32K minus the search string length.
           END LOOP;
         END IF;
 
         RETURN(l_return);
 
       END;

Multi-criteria search query in Oracle PL/SQL with dynamic SQL

How to implement a multi-criteria search query in PL/SQL with dynamic SQL.

It is very frequent to implement a search form with multiple criteria, where each criteria can be independently enabled by the user, according to her needs.
The application will then need to perform a query on the database, taking into account all the criteria choosen by the user, that is perform a dynamic query. Here comes an example of how to implement a dynamic query in PL/SQL, using dynamic SQL.

The query could be implemented also with a static query, which takes into account all the possible criteria, and switch them on according to the value of some input parameters.
This static query would sadly perform very bad, since it makes the optimizer choose access paths for unnecessary criteria.

For example, given the following table

CREATE TABLE SUBJECT
  (
    SUBJECTID   NUMBER(38,0) NOT NULL ENABLE,
    SURNAME NVARCHAR2(400),
    NAME NVARCHAR2(400)
    DATEOFBIRTH TIMESTAMP (6),
    CONSTRAINT PK_SUBJECT PRIMARY KEY (SUBJECTID) );

the following procedure performs a static search for an user taking into account her surname, name and birthdate and putting results into ResCursor1.
Each criteria is switching on in the query according to whether the corresponding input parameter is not null.

create or replace
PROCEDURE SEARCHPHYSICALPERSONS (Surname    in nvarchar2 default null,
                                                       Name       in nvarchar2 default null,
                                                       BirthDate  in date default null
                                                       ResCursor1 out sys_refcursor) is

  pSurname    Subject.Surname%TYPE := Surname;
  pName       Subject.Name%TYPE := Name;
  pBirthDate  Subject.Dateofbirth%TYPE := BirthDate;

begin

  open ResCursor1 for
    select distinct
           a.subjectid,
           surname,
           name,
      from Subject a
     where (pSurname is null or TRIM(upper(a.surname)) like TRIM(upper(pSurname||'%')))
       and (pName is null or TRIM(upper(a.name)) = TRIM(upper(pName)))
       and (pBirthDate is null or a.Dateofbirth = pBirthDate);

end SearchPhysicalPersons;

The following approach will instead optimize the search taking into account only the necessary criteria, building a dynamic sql query which is being parsed at runtime by the DB engine (the query will use binding variables, since they are necessary for optimal performances).

create or replace
TYPE physicalPersonsType AS OBJECT
    ( subjectid NUMBER(38,0),
      surname VARCHAR2(400 CHAR),
      name VARCHAR2(400 CHAR),
      dateofbirth TIMESTAMP(6),
    );

 
CREATE OR REPLACE TYPE physicalPersonsTableType AS TABLE OF physicalPersonsType;

create or replace
FUNCTION readSearchPhysicalPersons(cur_hdl int) RETURN physicalPersonsTableType
PIPELINED IS
  subjectid SUBJECT.SUBJECTID%type;
  surname SUBJECT.SURNAME%type;
  name SUBJECT.NAME%type;
  dateofbirth SUBJECT.DATEOFBIRTH%type;
BEGIN

      dbms_sql.define_column(cur_hdl, 1, subjectid);
      dbms_sql.define_column(cur_hdl, 2, surname, 400);
      dbms_sql.define_column(cur_hdl, 3, name, 400);
      dbms_sql.define_column(cur_hdl, 4, dateofbirth);

  LOOP
  -- fetch a row
  IF dbms_sql.fetch_rows(cur_hdl) > 0 then

      -- fetch columns from the row
      dbms_sql.column_value(cur_hdl, 1, subjectid);
      dbms_sql.column_value(cur_hdl, 2, surname);
      dbms_sql.column_value(cur_hdl, 3, name);
      dbms_sql.column_value(cur_hdl, 4, dateofbirth);
   
   PIPE ROW (physicalPersonsType(subjectid, surname, name, dateofbirth));
  ELSE
        EXIT; 
  END IF;
  END LOOP;
  
  RETURN;
END;

create or replace
PROCEDURE SEARCHPHYSICALPERSONS (Surname    in nvarchar2 default null,
                                                       Name       in nvarchar2 default null,
                                                       BirthDate  in date default null,
                                                       ResCursor1 out sys_refcursor) is

                                                       pSurname    Subject.Surname%TYPE := Surname;
                                                       pName       Subject.Name%TYPE := Name;
                                                       pBirthDate  Subject.Dateofbirth%TYPE := BirthDate;

  stmt_str varchar2(4000);
  cur_hdl int;
  rows_processed int;
  commaFlag boolean := false;
begin

cur_hdl := dbms_sql.open_cursor; -- open cursor
stmt_str := 'SELECT DISTINCT A.SUBJECTID, SURNAME, NAME, DATEOFBIRTH'
   ||' FROM SUBJECT A'
   ||' WHERE 1=1';

if pSurname is not null then
  stmt_str := stmt_str || ' AND (TRIM(UPPER(A.SURNAME)) LIKE TRIM(UPPER(:pSurname ||''%'')))';
end if;

if pName is not null then
  stmt_str := stmt_str || ' AND (TRIM(UPPER(A.NAME)) = TRIM(UPPER(:pName)))';
end if;

if pBirthDate is not null then
  stmt_str := stmt_str || ' AND (A.DATEOFBIRTH = :pBirthDate)';
end if;

--uncomment this line to see the generated sql
--dbms_output.put_line(stmt_str);

dbms_sql.parse(cur_hdl, stmt_str, dbms_sql.native);

-- supply binds (bind by name)
if pSurname is not null then
  dbms_sql.bind_variable(cur_hdl, ':pSurname', pSurname);
end if;

if pName is not null then
  dbms_sql.bind_variable(cur_hdl, ':pName', pName);
end if;

if pBirthDate is not null then
  dbms_sql.bind_variable(cur_hdl, ':pBirthDate', pBirthDate);
end if;

rows_processed := dbms_sql.execute(cur_hdl); -- execute

open ResCursor1 for
select * from table(readSearchPhysicalPersons(cur_hdl));

END;

Oracle, get the current DB instance

--this is a useful query to get the DB instance on which the current session is running, useful especially in RAC mode
SELECT sys_context('USERENV', 'INSTANCE') AS instance#, sys_context('USERENV', 'INSTANCE_NAME') AS instance_name FROM dual

Oracle export/import

here comes an example of data export:

the export must be performed on the source db server, the import on the target db server, since a local directory is required for the Oracle db server to export/import data

-- Create datapump directory
CREATE OR REPLACE DIRECTORY DATA_PUMP_DIR AS 'D:\Backup\Oracle\DataPumpDir';
GRANT READ,WRITE ON DIRECTORY DATA_PUMP_DIR TO myorauser;

-- Check datapump directory
SELECT * FROM ALL_DIRECTORIES;

expdp myorauser/password@SID directory=DATA_PUMP_DIR DUMPFILE=myorauserDump.dmp SCHEMAS=myorauser

impdp DIRECTORY=DATA_PUMP_DIR DUMPFILE=myorauserDump.dmp SCHEMAS=myorauser

when you are asked credentials, don't use the SYS users, use another user with powerful privileges (as SYSTEM)
-always check that indexes have been correctly created in the target db
-the option CONTENT=DATA_ONLY imports only data
-in the target db must be created a tablespace with the same name of the tablespace containing the Oracle schema in the source db