Hi Vic -
Note on dbms_lob.substr – whoever created this function hated users...
I feel your pain! One of my peeves about some Oracle functions, they don't follow conventions. Start with the start position, why don'tcha!
------------------------------
Ray Martin
Enterprise Applications Engineer
Azusa Pacific University
------------------------------
Message from the HEUG Marketplace:------------------------------
Find, Review, and Engage with Higher Education-focused solution providers, products, and services using the
HEUG Marketplace.
------------------------------
Original Message:
Sent: 08-18-2025 05:09 PM
From: Vic Goldberg
Subject: Listagg with PSMSGCATDEFN.MESSAGE_TEXT
Thanks, Jeffrie – my "changing just one thing at a time" protocol seems to have failed me. Turns out that it wasn't the listagg that caused the issue – there was another CLOB in the fields, and putting in a "distinct" checkbox screwed the pooch. In this case, I had to use a DBMS_LOB.SUBSTR to get it to work, a regular substr didn't do the trick.
Note on dbms_lob.substr – whoever created this function hated users – they put the length and start position parameters opposite the normal substr function call. So the args to it are dbms_log.substr (field, length, startpos)
Thanks!
Vic
---
Vic Goldberg
Retiree, Temporary Employee
University of Colorado Boulder
Independent PeopleSoft FA Consultant
Original Message:
Sent: 8/18/2025 5:01:00 PM
From: Jeffrie Brooks
Subject: RE: Listagg with PSMSGCATDEFN.MESSAGE_TEXT
Hey Vic,
I have run into similar errors, even if the CLOB is just a field otherwise returned in the SELECT statement. If you need that CLOB as a returned field, you can try a SUBSTR() expression, and make it a CHAR field.
If you don't need it, just try deleting it from the fields output and see what happens!
Thanks,
Jeffrie
On Mon, Aug 18, 2025 at 4:46 PM Vic Goldberg via Higher Education User Group <
Mail@heug.org> wrote:
Hi all - I'm trying to use one of my favorite aggregate functions, listagg, to concatenate some messages texts (disbursement messages in... -posted to the "PS Query & SQL" group
| Listagg with PSMSGCATDEFN.MESSAGE_TEXT | | | | | | Hi all - I'm trying to use one of my favorite aggregate functions, listagg, to concatenate some messages texts (disbursement messages in particular). I'm doing what I normally do with listagg, setup an appropriately sized expression, check the aggregate box in the expression, and pop in the listagg statement. It looks like this: listagg (distinct J.MESSAGE_TEXT,';'). When I try running the query with the expression as a field, I get the following error. Error in running query because of SQL Error, Code=932, Message=ORA-00932: inconsistent datatypes: expected - got CLOB (50,380) I checked, message_text is a char100 field, and listagg is defined as returning varchar2. No CLOBS in sight. There is a descrlong, which is a clob, in PSMSGCATDEFN, but I'm not referencing it in any way. Any answers?
------------------------------
Vic Goldberg
University of Colorado System
goldbergvictor@gmail.com
------------------------------
| | | Reply to Community Reply to Sender via Email View Thread Recommend |
| |
-- Jeffrie Brooks | BUSINESS SYSTEM ANALYST UNIVERSITY OF MICHIGAN | INFORMATION AND TECHNOLOGY SERVICES | |
Original Message:
Sent: 8/18/2025 4:47:00 PM
From: Vic Goldberg
Subject: Listagg with PSMSGCATDEFN.MESSAGE_TEXT
Hi all -
I'm trying to use one of my favorite aggregate functions, listagg, to concatenate some messages texts (disbursement messages in particular). I'm doing what I normally do with listagg, setup an appropriately sized expression, check the aggregate box in the expression, and pop in the listagg statement. It looks like this: listagg (distinct J.MESSAGE_TEXT,';'). When I try running the query with the expression as a field, I get the following error.
Error in running query because of SQL Error, Code=932, Message=ORA-00932: inconsistent datatypes: expected - got CLOB (50,380)
I checked, message_text is a char100 field, and listagg is defined as returning varchar2. No CLOBS in sight. There is a descrlong, which is a clob, in PSMSGCATDEFN, but I'm not referencing it in any way.
Any answers?
------------------------------
Vic Goldberg
University of Colorado System
goldbergvictor@gmail.com
------------------------------