Administration Digression: My Favorite Kind of New Oracle Function, LISTAGG

With the administration of my Discoverer / Noetix environment, I find that sometimes I need to compose comma separated concatenated list of some text column (e.g. for the purpose of creating documentation).

In Oracle Database 11g release 2, the LISTAGG function was introduced which is well suited for this type of task (and no custom function needs to be created).


This function has two flavors, analytic (non-aggregate) and aggregate.Here is a description of the function (see link above for full description in 11g R2):


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

LISTAGG ( expression, [delimeter] ) WITHIN GROUP (ORDER BY  order_by_expression_list )

-expression is the expression one wishes to concatenate
-delimeter is self-explanatory (e.g. ',')
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The analytic flavor is invoked as follows:

LISTAGG ( expression, [delimeter] ) WITHIN GROUP (ORDER BY  order_by_expression_list )

 [OVER (PARTITION BY partition_columns)]


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


With some documentation I am creating, I need to create a concatenated list of Discoverer responsibiities.


Here is my invocation of this function using the aggregate version of this function:


 EUL5_US@erpdev> SELECT LISTAGG(responsibility_name, ', ') WITHIN GROUP (
  2  ORDER BY responsibility_name) Responsibility_list
  3  FROM applsys.fnd_responsibility_tl
  4  WHERE 1      =1
  5  AND language = SYS_CONTEXT ('USERENV', 'LANG')
  6  AND regexp_like( responsibility_name, 'Discoverer.+ - \w{2}')
  7  /

Responsibility_list
------------------------------------------------------------------------
Discoverer Commissions - CS, Discoverer Viewer - CA, Discoverer Viewer - CM, Discoverer Viewer - CS, Discoverer Viewer - CW

Concurrent Program Query to View Scheduled and Pending Noetix Concurrent Programs

Simple concurrent program query to view scheduled and pending Noetix concurrent programs:

SELECT fcr.*
FROM applsys.fnd_concurrent_requests fcr,
  applsys.fnd_application fa
WHERE 1                        =1
AND fcr.program_application_id = fa.application_id
AND fa.application_short_name  = 'XXNAO'
AND fcr.phase_code             = 'P'
AND fcr.status_code            = 'Q'

This is nice to check to monitor these programs through SQL.

Digression: Querying for Organizations, Operating Units, Legal Entities and Ledgers in an R12 Environment

Invariably in an Oracle Applications environment, one needs to be aware of and know the relationship between organizations, operating units, legal entities and ledgers.

This query is just a variation of the apps.org_organization_definitions Oracle Applications view whose results provide this insight.  I take no credit for this:

 select
 hou.organization_id organization_id,
    mp.organization_code organization_code,
    lgr.ledger_id,
    decode(hoi2.org_information_context, 'Accounting Information', to_number(hoi2.org_information3), to_number(null)) operating_unit,
    decode(hoi2.org_information_context, 'Accounting Information', to_number(hoi2.org_information2), null) legal_entity
  from hr.hr_all_organization_units hou,
    hr.hr_organization_information hoi1,
    hr.hr_organization_information hoi2,
    inv.mtl_parameters mp,
    gl.gl_ledgers lgr
  where hou.organization_id = hoi1.organization_id
  and hou.organization_id   = hoi2.organization_id
  and hou.organization_id   = mp.organization_id
  and hoi1.org_information1 = 'INV'
  and hoi1.org_information2 = 'Y'
  and ( hoi1.org_information_context || '') = 'CLASS'
  and ( hoi2.org_information_context || '') ='Accounting Information'
  and to_number(decode(rtrim(translate(hoi2.org_information1,'0123456789',' ')), null, hoi2.org_information1,-99999)) = lgr.ledger_id
  and lgr.object_type_code ='L'
  and nvl(lgr.complete_flag,'Y') ='Y'

Troublshooting The ORA-04021 Error in a Development Environment

I notice that my regeneration was stalling through the install4.sql script in a development environment. Finally, the regeneration timed out.

Next, I check for the last spool file and it is the file, ycrenvph.lst. This script creates the header for the package, noetix_env_pkg.

I notice the error, ORA-04021 being thrown. This is the timeout error.

I query the v$session sessions owned by apps or noetix_sys which could be locking this package. Nothing stands out.

Next, I query the V$access table and I notice that there is session that has a lock on this object.

SELECT * FROM V$ACCESS WHERE OBJECT = 'NOETIX_ENV_PKG';

It is important to be cognizant of what sessions you intend to kill or let run its course.  It is important to know the details regarding a specific session (who, what, where, how and why) before any course of action is taken.  No matter what environment one is in, it usually is better to let something run its course than to initiate terminating a session.   

Next, I find this session's detail:

SELECT * FROM V$SESSION WHERE SID = 1098;

and kill the session.

ALTER SYSTEM KILL SESSION '1098,61537';

The Lazy Noetix Developer: More on Quick Error Checking of XU2/XU4 Scripts

A lot of Noetix people have migrated to Noetix Workbench for the modification
of their Noetix View environment.  We have not. 

Anyways, I am a Vimmer and a Noetix dude (administration, support and developer) for a
6.0.1 environment and I have found that this process below to work quite well for
capturing errors with my customized xu2/xu4 scripts:

1. Type out xu2/xu4 script modifications.  Usually, I have an existing xu2/xu4
file.

2. With Vim, I just place a mark at the beginning of my new DML. Marking just is a
way to reference a row in a document for easy, quick access. Typically, I use
the mark, b, which I associated with the beginning of my noteworthy selection.

3. When I am done with my modifications to my xu2/xu4 script, I use the Vim ex
command:

:'b,$s/^COMMIT;/--COMMIT;/g

Explanation:
-'b,$ is a range for the s (substitute) command.
-^ is an anchor.  Anchors are used in regular expressions to identify the location.  In this context, the anchor, ^, is used to indicate the beginning of a line.
-'^COMMIT' is the searched for expression.
-'--COMMIT' is the replaced texts.

I perform this substitute for all of the commits so that I can rollback the command after it is executed. section.

4. Yank the text from this modification section using this Vim ex
command:

:'b,$y*

This just means, from the mark, b, to the end of the document, $, yank
(translation place contents in register).  With my Vim instance, *, refers to
the Windows clipboard.  Thus, this command moves the text from my selected
area and places this in the clipboard. 

5. Start-up Windows Powershell (which I have set my profile variable,
$profile, to automagically start-up SQL Plus). I issue the spool C:\temp.lst
command in SQL Plus.

6. Paste my new modification script to SQL Plus using the Noetix
System user account.

7. Issue the spool off command.

8. Undo the substitution command in step #3 using the u normal mode
command (undo).  This results in the COMMIT commands being non-commented.

9. Perform a rollback in SQL Plus so that my DML does not get committed.

9. Lastly, I peruse my spool file, C:\temp.lst, for errors using the Vim
normal mode search command:

 /ORA-[0-9]\+

In summary, this is a nice, efficient way to capture errors in new DML
xu2/xu4 scripts without needing to kick-off a regeneration.

Analyzing a View to Understand Unexpected/Erroneous Behavior

It is fairly common for me to analyze a single record where unexpected behavior is occurring with a particular view.

Here is a trivial view (this is not a Noetix View):

CREATE OR REPLACE VIEW ONTX0_ORDER_LINE
AS
SELECT
    HEAD.ORDER_NUMBER,
    ITEM.SEGMENT1 ITEM,
    ITEM.DESCRIPTION ITEM_DESCRIPTION,
    LINE.ORDERED_QUANTITY
FROM
    ONT.OE_ORDER_HEADERS_ALL HEAD,
    ONT.OE_ORDER_LINES_ALL LINE,
    INV.MTL_SYSTEM_ITEMS_B ITEM,
    NOETIX_SYS.ONTX0_OU_ACL_MAP_BASE XMAP
 WHERE 1=1
    AND LINE.ORG_ID=XMAP.ORG_ID
    AND LINE.HEADER_ID = HEAD.HEADER_ID
    AND LINE.INVENTORY_ITEM_ID = ITEM.INVENTORY_ITEM_ID ;


Now suppose through testing, we know that we receive multiple records in our results set with ORDER_NUMBER = '30066929', though we know there should only be one record.  We know this to be the case with most records, yet this record is the first one which comes back in our result set.


What is the most efficient approach to troubleshooting this problem?  Again, with our trivial view, we can find the solution quickly; yet there is value in thinking about this problem as a process and trying to refine/speed-up this process.

As with most problem solving, one should identify the problem precisely:

"For ORDER_NUMBER, '30066929', I am getting three records when I anticipate one record.  In fact, I see this triplication problem systemically".

Now that this problem has been identified, it is good to look at the most recent changes and rollback them (if the issue is a new issue).

If this approach does not work, I wanted to present my quick, brute force solution.


    1. Make a copy of the view definition (but just keep the SQL statement).


    SELECT
        HEAD.ORDER_NUMBER,
        ITEM.SEGMENT1 ITEM,
        ITEM.DESCRIPTION ITEM_DESCRIPTION,
        LINE.ORDERED_QUANTITY
    FROM
        ONT.OE_ORDER_HEADERS_ALL HEAD,
        ONT.OE_ORDER_LINES_ALL LINE,
        INV.MTL_SYSTEM_ITEMS_B ITEM,
        NOETIX_SYS.ONTX0_OU_ACL_MAP_BASE XMAP
     WHERE 1=1
        AND LINE.ORG_ID=XMAP.ORG_ID
        AND LINE.HEADER_ID = HEAD.HEADER_ID
        AND LINE.INVENTORY_ITEM_ID = ITEM.INVENTORY_ITEM_ID ;



    2. Next, comment all line except the SELECT, FROM and WHERE lines as follows:


    SELECT
--        HEAD.ORDER_NUMBER,
--        ITEM.SEGMENT1 ITEM,
--        ITEM.DESCRIPTION ITEM_DESCRIPTION,
--        LINE.ORDERED_QUANTITY
    FROM
--        ONT.OE_ORDER_HEADERS_ALL HEAD,
--        ONT.OE_ORDER_LINES_ALL LINE,
--        INV.MTL_SYSTEM_ITEMS_B ITEM,
--        NOETIX_SYS.ONTX0_OU_ACL_MAP_BASE XMAP
     WHERE 1=1
--        AND LINE.ORG_ID=XMAP.ORG_ID
--        AND LINE.HEADER_ID = HEAD.HEADER_ID
--        AND LINE.INVENTORY_ITEM_ID = ITEM.INVENTORY_ITEM_ID ;

      If you are using a text editor and it happens to be VIM, you can just invoke this EX command:

        %s/^/--/c

        Where % identifies the range of lines where I intend to substitute characters (all lines).
        The '^' character is an anchor conveying to VIM that I want to find the beginning of the line.
        The '--' characters are going to placed on all lines in this range of lines.
        The 'c' option allows me to confirm which lines I want to perform this replace.

    3.  Add a condition to the where clause to more closely examine the error/problem identified.

        In this case, it should be as follows, "AND HEAD.ORDER_NUMBER = '30066929'". Since our condition is on the header table, let us uncomment all lines associated with these two tables in both the from and where clause.


        Also, we should have visibility of the primary key of the driving table (here it is "LINE.LINE_ID").  Thus we add this line in the select clause.

      
    SELECT
--        HEAD.ORDER_NUMBER,
--        ITEM.SEGMENT1 ITEM,
--        ITEM.DESCRIPTION ITEM_DESCRIPTION,
--        LINE.ORDERED_QUANTITY
        LINE.LINE_ID
    FROM
        ONT.OE_ORDER_HEADERS_ALL HEAD,
        ONT.OE_ORDER_LINES_ALL LINE,
--        INV.MTL_SYSTEM_ITEMS_B ITEM,
--        NOETIX_SYS.ONTX0_OU_ACL_MAP_BASE XMAP
     WHERE 1=1
--        AND LINE.ORG_ID=XMAP.ORG_ID
        AND LINE.HEADER_ID = HEAD.HEADER_ID
--        AND LINE.INVENTORY_ITEM_ID = ITEM.INVENTORY_ITEM_ID
--      testing
        AND HEAD.ORDER_NUMBER = '30066929';

    Optimally, our condition should be on the driving table (LINE), and it should restrict the result set for the driving table to one record.  In this trivial case, we are not doing this (one could have multiple lines for a given header).



    4. Next, we uncomment tables in the From clause and their associated Where clause lines.  Here, we do this with the XMAP base view:


    SELECT
--        HEAD.ORDER_NUMBER,
--        ITEM.SEGMENT1 ITEM,
--        ITEM.DESCRIPTION ITEM_DESCRIPTION,
--        LINE.ORDERED_QUANTITY
        LINE.LINE_ID
    FROM
        ONT.OE_ORDER_HEADERS_ALL HEAD,
        ONT.OE_ORDER_LINES_ALL LINE,
--        INV.MTL_SYSTEM_ITEMS_B ITEM,
        NOETIX_SYS.ONTX0_OU_ACL_MAP_BASE XMAP
     WHERE 1=1
        AND LINE.ORG_ID=XMAP.ORG_ID
        AND LINE.HEADER_ID = HEAD.HEADER_ID
--        AND LINE.INVENTORY_ITEM_ID = ITEM.INVENTORY_ITEM_ID
--      testing
        AND HEAD.ORDER_NUMBER = '30066929';


    5. After the XMAP base view being uncommented, we procedd to uncomment the ITEM table.  Here we will find that we are missing the organization_id column in the join between LINE and ITEM.


I have found this approach can work fairly quickly (20 minutes to quickly assess/analyze a view).

While this is fairly common task, it is important to approach it with speed and precision and clarity.