Is there a size limit for the SQL text in a PeopleSoft App Engine SQL Step/Action?

I'm 开发者_开发百科getting the following error: AeSymResolveStatement [775] ... Meta-SQL error at or near position 34338 in statement (108,512). The SQL statement itself is over 40,000 chars long, hence the question.

The DB is oracle. Running on Tools 8.49.24.

I know that there is a limit on the size of the SQL used in an Application Engine (SQL Step). I had once recieved a similar error while trying to use an exceptionally long SQL in an Application Engine.

I wouldn't be surprised if that same limit applies to SQL Objects.

To fix the problem, I was able to split the SQL into 2 (was an update statement). Hopefully that's possible in your case as well.

There is no such limit.

You can confirm this yourself by creating an SQL like:

select 'x' from PS_INSTALLATION where 
      1 = 1 and 
      1 = 1 and
      1 = 1 and
      1 = 1 and
      /* ... copy paste '1 = 1 and' 90000 times or so times more */
      1 = 1

Although it makes pside quite slow, It saves and validates just fine.

There are limits within PeopleCode, mostly due to the limits on string length, however I have never found a limit on stored SQL statements.

Personally I'd look at breaking the statement into pieces in some way.

You could:

• Using the inbuilt looping mechanism of App Engines
• Use a mixture of SQL and PeopleCode
• Use a temporary table and perform intermediate SQLs, storing in the temp table

Apart from giving your database a heart seizure, not the mention the DBA when he sees the statement in the SQL monitor. You are saving yourself a world of pain if you ever have to look at the statement again.

I think the SQLs in App Engines are stored as longs, so it would be 4GB under Oracle, something similarly huge under DB2.