PS Query & SQL

Good morning,

We recently had all of our packaging plans stop functioning. There were no changes to any of the equations and we were stumped. It turned out to be a SQL that all of the plan equations call. This SQL identified students in academic programs not eligible to receive aid. We found out that one of our schools added a certificate program, so we added that to the SQL in list.  It turns out that the SQL gets broken into 254-character chunks. Our new plan put us over the 254-character limit and our not inserting a space between the last character of the acad plan and the close parenthesis caused the error. Here is the text our IT was given from Oracle (I think):

Problem: When you enter SQL in the 'Equation Callable Sql Text' field on the 'Equation SQL Routines' page, the data is stored in the EQTN_SQL_TBL table in the EQTN_SQL_TEXT field. There is no limit on this field size. It is also stored in the EQTN_SQL_CHUNKS table in the EQTN_SQL_CHUNK field. This field has a length of 254, so if the SQL text is longer than 254 characters, it is split into multiple fields. When SQL ends with ')' and there is no space before it, the sentence is cut before the space.
Solution: Leave a space before   ')'  when SQL text ends with ).

I just thought I would share for those who were unaware.

Aaron

------------------------------
Aaron Tobul
Systems Analyst
University of Pittsburgh
------------------------------

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: 8/19/2026 8:25:00 AM
From: Jeffrie Brooks
Subject: RE: Fun SQL size limit Fact

Aaron,

Thanks for sharing this!

We ran into this issue a few years back with a callable SQL and somehow stumbled on the solution that we needed to take our single IN() statement and break it into 4 different IN() statements in order to get it to work. Its good to know the concrete reason why that was happening.

Jeffrie

--

Jeffrie Brooks | BUSINESS SYSTEM ANALYST UNIVERSITY OF MICHIGAN | INFORMATION AND TECHNOLOGY SERVICES 734-647-8763 | jedobr@umich.edu

Original Message:
Sent: 8/19/2026 8:44:00 AM
From: Dana Pawlowicz
Subject: RE: Fun SQL size limit Fact

I had this a few years back and Vic Goldberg gave me this query.  And now, whenever I create a new callable SQL, I run it against the query.  If anything is missing, I add "AND 1 = 1" to the bottom and that fixes it.  Works every time.  You have to pay attention to your parenthesis of course.

 

SELECT DISTINCT A.EQTN_OPERAND_SQL, TO_CHAR(A.EFFDT,'YYYY-MM-DD'), A.EQTN_SQL_CHUNK_SEQ, A.EQTN_SQL_CHUNK, A.SCC_DATA_TYPE_CD
  FROM PS_EQTN_SQL_CHUNKS A
  WHERE ( A.EFFDT =
        (SELECT MAX(A_ED.EFFDT) FROM PS_EQTN_SQL_CHUNKS A_ED
        WHERE A.EQTN_OPERAND_SQL = A_ED.EQTN_OPERAND_SQL
          AND A_ED.EFFDT <= SYSDATE)
     AND A.EQTN_OPERAND_SQL = :1)
  ORDER BY 3

 

 

Thanks!

Dana Pawlowicz

Business Systems Analyst Sr - ERP

Digital Platforms & Innovation
Digital Technology Solutions

University of Cincinnati

51 Goodman Dr.

Cincinnati, Oh 45221