Extract duplicate keywords from string

SQL example using wm_concat function:

 

select wm_concat(distinct keyword) keywords
  from (
        SELECT LEVEL                                 AS keyword_no
           ,      REGEXP_SUBSTR(keywords, '[^,]+', 1, LEVEL) AS keyword
           --, str     
           FROM  (
                  SELECT ROWNUM AS id
                  ,      'SCOTT,ALLEN,KING,SCOTT,12345,SCOTT,12345,ALLEN,SCOTT' as keywords
                  FROM   dual
                 )
           CONNECT BY
                  -- INSTR for compatibility with Oracle 10g 
                  --INSTR(keywords, ',', 1, LEVEL-1) > 0
                  -- From Oracle 11g may used regexp_count 
                  LEVEL <= regexp_count(keywords,',') + 1
                  AND id = PRIOR id
                  AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL
  )          
;
 
KEYWORDS                  
--------------------------------------------
12345,ALLEN,KING,SCOTT                      
1 row selected.
 

SQL example using listagg function:

 

select listagg(keyword, ',') WITHIN GROUP (ORDER BY keyword) keywords 
  from (
        select distinct keyword
          from (
                SELECT LEVEL                                 AS keyword_no
                   ,      REGEXP_SUBSTR(keywords, '[^,]+', 1, LEVEL) AS keyword
                   --, str     
                   FROM  (
                          SELECT ROWNUM AS id
                          ,      'SCOTT,ALLEN,KING,SCOTT,12345,SCOTT,12345,ALLEN,SCOTT' as keywords
                          FROM   dual
                         )
                   CONNECT BY
                          -- INSTR for compatibility with Oracle 10g 
                          --INSTR(keywords, ',', 1, LEVEL-1) > 0
                          -- From Oracle 11g may used regexp_count 
                          LEVEL <= regexp_count(keywords,',') + 1
                          AND id = PRIOR id
                          AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL
          )
       )                
;
 
KEYWORDS                                                                        
--------------------------------------------------------------------------------
12345,ALLEN,KING,SCOTT                                                          
1 row selected.

Using PL/SQL function example:

 

DECLARE
    l_keywords varchar2(32767) := '10,20,30,40, 10, 20,SCOTT,ALLEN,KING,SCOTT,12345,SCOTT,12345,ALLEN,SCOTT';
    
    function extract_duplicate_keywords(p_keywords varchar2) return varchar2
    is   
       l_result varchar2(32767);
       cnt pls_integer;
       
       --l_str varchar2(4000);
       TYPE   nested_nbr_type IS TABLE OF varchar2(4000);
       nnt2   nested_nbr_type := nested_nbr_type();
       --nnt3   nested_nbr_type;
       answer nested_nbr_type;
       j pls_integer;
    BEGIN
       nnt2 := nested_nbr_type();
       
       cnt := regexp_count(p_keywords, '[^,]+');
       --dbms_output.put_line('Count: ' || cnt);
       for i in 1 .. cnt + 1
       loop
         nnt2.extend;
         nnt2(nnt2.last) := trim(REGEXP_SUBSTR(p_keywords,'[^,]+', 1, i));
         --dbms_output.put_line(i || ': ' || l_str);
       end loop;
       --nnt3 := nnt2;
       answer := nnt2 MULTISET INTERSECT DISTINCT nnt2;
       
       j := answer.first;
       while j is not null
       loop
         if answer(j) is not null then
           l_result := l_result || ',' ||answer(j);
           --dbms_output.put_line(answer(j));
         end if;
         j := answer.next(j);
       end loop;
       return ltrim(l_result, ',');
    END;
BEGIN
   l_keywords := extract_duplicate_keywords(l_keywords);
   dbms_output.put_line(l_keywords);
END;

How to connect SQLPlus without tnsnames.ora

Connect using one of service_name:

sqlplus username/password@//host[:port][/service_name]

where service_name can be one of simple name or domain name specified in service_names parameter:

show parameter service_names
ALTER SYSTEM SET service_names = 'ORCL', 'us.acme.com' COMMENT='Add new services' SCOPE=BOTH;

Procedure to write to alert.log

Link to original article Stored Proc to write to database alert logs author Charles Kim.

From the application or from database triggers, if I want to record anything to the alert log, I can simply invoke this stored procedure and write error messages to the alert log.

Most of the DBAs have implemented alert log scanners and will send alert notifications if new ORA- error messages pop up in the alert log file.
 

create or replace procedure write_msg_to_alert_log (
  p_message varchar2,
  p_oranum number default 20001
)
is
/*
  create procedure under system user
*/
begin
  sys.dbms_system.ksdddt;
  sys.dbms_system.ksdwrt(2, 'ORA-' || to_char(p_oranum) || ': ' || p_message);
end;
/
  
--grant execute on write_msg_to_alert_log to public;
grant execute on write_msg_to_alert_log to scott;
 
create public synonym write_msg_to_alert_log for system.write_msg_to_alert_log;