SQL Transformation Dynamic Query Generation Limitation

0
votes

I'm using an expression transformation to dynamically generate a query which serves as the input to my SQL transformation. Since the query is already generated in the expression, i'm using String substitution.

Issue: My generated query is more than 4000 characters long and hence the query is truncated while being read in the SQL transformation(since the query port is varchar2 and it accepts a max of 4000 characters) and is throwing an error.

How do i overcome this issue?

Unfortunately, i cannot use parameter binding since the parameters can be used only in the WHERE clause of the query and my query is not very straightforward.

2
sqloracleinformatica-powercenterdynamicquery
Put the query in a stored procedure, and call that instead? - OldProgrammer
Have you tried using CLOBs instead of VARCHAR2(4000)? - Boneist
OldProgrammer - I will definitely try that and let you know on how it works. Boneist - I cannot use CLOB since i'm using string substitution. - rcmaiden
Stored procedure will not work since my query is dynamically generated - rcmaiden
Thanks rcmaiden. Can you include which database version you are using? There may be different options available, for example, after 12.2 than before it. - alexgibbs

2 Answers

0
votes

I came up with a workaround for this issue. After dynamically generating the query, i loaded the query onto a flat file which would serve as a parameter file for my subsequent batch. The next batch would then run the query based on the parameterized SQ query(which was parameterized in my previous workflow). This eliminated the possibility of the query being truncated since there is no 4000 character limit in the SQ transformation.

0
votes

String port in Informatica transformations can handle characters range from 1 to 104,857,600.
Why you don't increase size of your (generated) output port in Expression transformation and input port in SQL transformation to higher value?