WM_CONCAT function - How to aggregate data from a number of rows into a single row

Term: WM_CONCAT

Definition:
The Oracle PL/SQL WM_CONCAT function is used to aggregate data from a number of rows into a single row, giving a list of data associated with a specific value. In effect, it cross-tabulates a comma delimited list.

Note that WM_CONCAT is undocumented and unsupported by Oracle, meaning it should not be used in production systems. The LISTAGG function, which can produce the same output asWM_CONCAT is both documented and supported by Oracle.


Example Usage:

CREATE TABLE test_wm (

column_1 VARCHAR2(5),

column_2 VARCHAR2(20));



INSERT INTO test_wm VALUES (111, 'This');

INSERT INTO test_wm VALUES (111, 'is');

INSERT INTO test_wm VALUES (111, 'a');

INSERT INTO test_wm VALUES (111, 'test');

INSERT INTO test_wm VALUES (222, 'This is not');



SELECT * FROM test_wm;



col concat format a40



SELECT column_1, wmsys.wm_concat(column_2) CONCAT

FROM test_wm

GROUP BY column_1;



SELECT column_1, TRANSLATE(wmsys.wm_concat(column_2), 'A,', 'A ') CONCAT

FROM test_wm

GROUP BY column_1;

Like operator with IN clause

  logon_triggers_exclude values:

MYDB

YDB%

SELECT DISTINCT a.DATABASE_ID,  FROM cp_database a  WHERE NOT EXISTS ( SELECT 1                        FROM logon_triggers_exclude lt                      WHERE a.database_name LIKE lt.database_name);

Example of DBMS_STATS

BEGIN

      DBMS_STATS.GATHER_TABLE_STATS ( ownname => 'MYUSR',

                                      tabname => 'MY_PART_TABLE',

                                      estimate_percent =>1,

                                      method_opt =>'FOR ALL INDEXED COLUMNS SIZE 1',

                                      granularity =>'PARTITION',

                                      DEGREE=>8,

                                      NO_INVALIDATE => TRUE,

                                      Cascade => TRUE);

END;

ANSWERS TO "WHY ARE MY JOBS NOT RUNNING ?"

http://forums.oracle.com/forums/thread.jspa?threadID=646581

This is one of the most common Scheduler questions asked. 
Here we list some of the common problems and their solutions.

1) job_queue_processes may be too low (this is the most common problem)
The value of job_queue_processes limits the total number of dbms_scheduler and dbms_job jobs that can be running at a given time.

To check whether this is the case check the current value of job_queue_processes with


SQL> select value from v$parameter where name='job_queue_processes';


Then check the number of running jobs


SQL> select count(*) from dba_scheduler_running_jobs;
SQL> select count(*) from dba_jobs_running;

If this is the problem you can increase the parameter using


SQL> alter system set job_queue_processes=1000;

How to stop DataPump Job 2

DECLARE
  n NUMBER:=0;
  errm VARCHAR2(2000);
BEGIN
FOR i IN (SELECT job_name, owner_name FROM dba_datapump_jobs ) LOOP
  n := DBMS_DATAPUMP.ATTACH( JOB_NAME => i.job_name, JOB_OWNER => i.owner_name );
  DBMS_OUTPUT.PUT_LINE( n );
  DBMS_DATAPUMP.STOP_JOB( n );
END LOOP;
EXCEPTION
WHEN OTHERS THEN
 BEGIN
      errm := SQLERRM;
   DBMS_OUTPUT.PUT_LINE( errm );
 END;
END;
/

How to stop Datapump Job

DECLARE

   n NUMBER := 0;

   errm VARCHAR2(2000);

BEGIN

   n := DBMS_DATAPUMP.ATTACH( job_name => 'MYJOB', job_owner => 'ADMIN_USR' );

   DBMS_OUTPUT.PUT_LINE( n );

   DBMS_DATAPUMP.STOP_JOB( n );

EXCEPTION

  WHEN OTHERS THEN

    BEGIN

       errm := SQLERRM;

       DBMS_OUTPUT.PUT_LINE( errm );

    END;

END;

Example of DBMS_SCHEDULER

alter session set time_zone = 'Asia/Jerusalem';


declare

start_date TIMESTAMP WITH TIME ZONE;

begin

start_date :=  TO_TIMESTAMP_TZ('2006/05/11 16:50:00.000000 '||TZ_OFFSET('Asia/Jerusalem'),'yyyy/mm/dd hh24:mi:ss.ff tzh:tzm');

--start_date :=  TO_TIMESTAMP_TZ('2006/05/11 16:40:00','yyyy/mm/dd hh24:mi:ss');

--start_date := systimestamp at time zone '+02:00'

DBMS_SCHEDULER.CREATE_JOB(

     job_name            =>'alex_test_10',

     job_type          => 'STORED_PROCEDURE',

     job_action          => 'alex_text',

     start_date          =>  start_date,    

     enabled => true );

end;

/

The WRAP Utility and the DBMS_DDL Package

On occasion it is necessary to hide (obfuscate) your PL/SQL source code. Traditionally this has been done using the WRAP utility, but Oracle 10g Release 2 also allows this to be done dynamically using the DBMS_DDLpackage. This article presents examples of both methods of PL/SQL source obfuscation.

The WRAP Utility

The wrap utility is a command line utility that obfuscates the contents of a PL/SQL source file. The syntax for the wrap utility is shown below.

wrap iname=input_file [oname=output_file]

The iname parameter specifies the source file, while the oname parameter specifies the destination file. If the destination file is not specified it defaults to the source file name with a ".pld" extension.

Using Ref Cursors To Return Recordsets

Since Oracle 7.3 REF CURSORS have been available which allow recordsets to be returned from stored procedures, functions and packages. The example below uses a ref cursor to return a subset of the records in the EMP table.

Is there a way to get a ref cursor from a PL/SQL table?

Is there a way to get a ref cursor from a PL/SQL table? I mean, I need to return a ref cursor from my Oracle function in order to see it as a recordset into VB. It is not a problem when the data I’m passing to the VB application comes from a query, even if the query has many tables involved. The problem appears when the data I need to pass is stored into a PL/SQL table in the form of a table of records.