Thanks, Scott! I was not aware of TEXTJOIN, so thank you for that new tool in my toolbox.
Original Message:
Sent: 06-17-2025 11:24 AM
From: Scott Nishizaki
Subject: CLOB to CHAR length limited in query
Okay, so I realize I'm about to put an Excel/Google Sheets solution in the PS Query/SQL group, but, if you're combining fields in Excel after running your query anyway, you could utilize 2 functions to quickly put your SQL chunks back together.
If you had EQUATION_NAME in column A (for example) and EQTN_SQL_TEXT in column D (randomly chosen column) you could utilize
=TEXTJOIN(" ",TRUE,FILTER(D:D,A:A=A2))
in E2 (assuming it was empty). This would concatenate any cells in column D where the value in A matched your EQUATION_NAME. TEXTJOIN is available starting Excel 2019 and FILTER in Excel 2021.
------------------------------
Scott Nishizaki
Developer/Analyst
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: 06-17-2025 10:38 AM
From: Brian Beard
Subject: CLOB to CHAR length limited in query
Thank you! I'll spend some time digesting these.
------------------------------
Brian Beard
Associate Director of Systems and Technology
Student Financial Services
The School of the Art Institute of Chicago
Chicago, IL
Original Message:
Sent: 06-17-2025 10:34 AM
From: Dana Pawlowicz
Subject: CLOB to CHAR length limited in query
Vic's queries:
UCFA_QA_CALLABLE_SQL_CHUNKS (Checks to see if your Callable SQL is chunking correctly. If you are missing any of your SQL, then it's not.)
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
UCFA_RPT_XML_EQNDETAIL (Used to export a single Equation and Callable SQL to Excel.)
SELECT A.EQUATION_NAME, TO_CHAR(A.EFFDT,'YYYY-MM-DD'), A.DESCR, B.EQTN_ROW_SEQ/10, B.EQUATION_KEYWORD, B.EQTN_OPERAND_TYPE, Case
when B.EQTN_OPERAND_TYPE = 'N' then
to_char( B.EQTN_OPERAND_NUM)
else
trim(concat( B.EQTN_OPERAND_STR,
concat( TO_CHAR(B.EQTN_OPERAND_DT,'YYYY-MM-DD'),
concat( B.RECNAME,
concat( B.FIELDNAME,
concat( B.EQTN_OPERAND_VAL,
concat( B.EQTN_OPERAND_EQTN,
concat( B.EQTN_OPERAND_XTRN,
concat( B.EQTN_OPERAND_LOC,
concat( B.EQTN_OPERAND_GLB,
concat( B.EQTN_OPERAND_APPL, B.EQTN_OPERAND_SQL)))))))))))
end, B.EQTN_COMMENT, C.EQTN_SQL_TEXT, TO_CHAR(SYSDATE,'YYYY-MM-DD'), TO_CHAR(SYSDATE,'YYYY-MM-DD')
FROM PS_EQUATION_TBL A, (PS_EQUATION_DTL B LEFT OUTER JOIN PS_EQTN_SQL_TBL C ON C.EQTN_OPERAND_SQL = B.EQTN_OPERAND_SQL AND C.EFFDT =
(SELECT MAX(C_ED.EFFDT) FROM PS_EQTN_SQL_TBL C_ED
WHERE C.EQTN_OPERAND_SQL = C_ED.EQTN_OPERAND_SQL
AND C_ED.EFFDT <= SYSDATE) )
WHERE ( A.EFFDT =
(SELECT MAX(A_ED.EFFDT) FROM PS_EQUATION_TBL A_ED
WHERE A.EQUATION_NAME = A_ED.EQUATION_NAME
AND A_ED.EFFDT <= SYSDATE)
AND A.EFF_STATUS = 'A'
AND A.EQUATION_NAME = :1
AND B.EQUATION_NAME = A.EQUATION_NAME
AND B.EFFDT =
(SELECT MAX(B_ED.EFFDT) FROM PS_EQUATION_DTL B_ED
WHERE B.EQUATION_NAME = B_ED.EQUATION_NAME
AND B_ED.EFFDT <= SYSDATE))
ORDER BY 1, 4
This 2nd one has 2 expressions:
Original Message:
Sent: 6/17/2025 10:26:00 AM
From: Brian Beard
Subject: RE: CLOB to CHAR length limited in query
Thank you everyone for your suggestions! They got me to a workable solution.
To answer some questions - I did not have any aggregates, I did have the results set to Distinct, but changing that didn't resolve the issue for me, in this case. I did have the expression type set to character and I did have the length set to an amount greater than the what I was trying to pull. All good tips though, that I'll keep in mind for future issues!
What ultimately resolved it for me was a combination of what Jeffrie, Jason, and Lidia suggested. I was still being limited by the 4,000 character buffer limit, but I created two expressions and used both as fields:
dbms_lob.substr(I.EQTN_SQL_TEXT, 4000, 1)
dbms_lob.substr(I.EQTN_SQL_TEXT, 4000, 4001)
If I need to combine them into one field later, I can just concatenate the results in excel. That's good enough for this project. Thank you everyone for your help! I was trying a lot of things and getting nowhere. Everyone's insight was much appreciated.
On that note, Dana, if you wouldn't mind sharing the queries from Vic that you mentioned, I would love to see those!
------------------------------
Brian Beard
Associate Director of Systems and Technology
Student Financial Services
The School of the Art Institute of Chicago
Chicago, IL
Original Message:
Sent: 06-16-2025 04:19 PM
From: Brian Beard
Subject: CLOB to CHAR length limited in query
Hi all,
I'm getting the dreaded CLOB, and haven't been able to navigate around it. I'm hoping someone has a suggestion for me.
I'm trying to pull the EQTN_SQL_TEXT field from the EQTN_SQL_TBL record, in one of my queries.
Just selecting the field resulted in me getting the error:
A SQL error occurred. Please consult your system log for details.
Error in running query because of SQL Error, Code=932, Message=ORA-00932: inconsistent datatypes: expected - got CLOB (50,380)
So, I instead incorporated the field into an expression "TO_CHAR(I.EQTN_SQL_TEXT)".
This works for some SQL, but not others. Based on the error message I (sometimes) get below, it seems that some of the SQL is just too long, and when it is too long, I get this error:
A SQL error occurred. Please consult your system log for details.
Error in running query because of SQL Error, Code=22835, Message=ORA-22835: Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: 5289, maximum: 4000) (50,380)
Adjusting the expression length has no impact. Does anyone know a way around this, or just a better way to do this?
------------------------------
Brian Beard
Associate Director of Systems and Technology
Student Financial Services
The School of the Art Institute of Chicago
Chicago, IL
------------------------------