We have several databases that just contain views of view/table objects in other databases. I am trying to create a stored procedure to refresh the views in these databases so they are updated when the underlying object is changed. One step in the SP is to query the DBC.Tables and insert all the view definitions into a work table. When I highlight this code in the SP and run it manually, the code completes successfully and my work table is populated. When I CALL the SP this step fails with a 3798 error (A column or character expression is larger than the max size). What could make a statement that works ad hoc fail inside an SP. I have made sure all datatypes are unicode. The longest view definition 8103 characters. Any help and suggestions are greatly appreciated. Here is the problem code:
ROW_NUMBER() OVER(PARTITION BY DatabaseName ORDER BY TableName) AS ViewCount
,CAST(REGEXP_REPLACE(CAST(RequestText AS CLOB),'CREATE','REPLACE',1,1,'i') AS VARCHAR(12500) CHARACTER SET UNICODE) AS RequestText
AND TableKind = 'V';
Since TD12 you should be using "native" views such as DBC.TablesV instead of backward-compatible views like DBC.Tables that convert/truncate names to CHAR(30) and translate to LATIN.
Not certain that will resolve the issue here, but would remove some unnecessary complexity.
If you are compiling the stored procedure via ODBC (especially an older version), try using BTEQ or a .NET or JDBC connection instead.
I suppose you could also create a string variable containing the insert statement and EXECUTE IMMEDIATE as dynamic SQL.
Thank you Fred.
As you suspected changing to TablesV did not resolve the issue, but thanks for the info.
I am using a JDBC connection so I don't think the issue is there.
Interestingly when I remove the REGEXP_REPLACE and the casting and just use RequestText it works fine in the SP. However, then I potentially have CREATE views that when I try to refresh will fail.
I have partially resolved the issue. It appears that REGEXP_REPLACE and OREPLACE have character processing limits of 8000 characters. Does anyone have comments or more information on this issue?
I don't know if it can solve your issue, but maybe it's worth to work with CLOB everywhere.
For REGEXP_REPLACE, return of VARCHAR value is limited to 16000 bytes (so 8000 characters if CHARACTER SET UNICODE). But CLOB source / return should allow up to 16MB.
You may want to open an incident reporting this as a bug.
Besides the EXECUTE IMMEDIATE workaround, if you know that your views have only LATIN characters in their definitions, another workaround might be to explicitly make everything LATIN so you invoke the LATIN version of the function. (You have to translate the literals, too, because they will be UNICODE; and if any of the arguments is UNICODE there will be an implicit translation of all LATIN arguments to UNICODE.):
,REGEXP_REPLACE(TRANSLATE(RequestText USING UNICODE_TO_LATIN),TRANSLATE('CREATE' USING UNICODE_TO_LATIN),TRANSLATE('REPLACE' USING UNICODE_TO_LATIN),1,1,
TRANSLATE('i' USING UNICODE_TO_LATIN)) AS RequestText