Restrictions on the length of OracleSql statements and Solutions

Recently when writing SQL statements, encountered two problems: 1) ORA-01795: maximumnumberofexpressionsinalistis1000 cause: Write such SQL statement:

Recently I encountered two problems when writing SQL statements: 1) ORA-01795: maximum number of expressions in a list is 1000 cause: I wrote such an SQL statement:

When I was writing SQL statements recently, I encountered two problems:

1) ORA-01795: maximum number of expressions in a list is 1000

Cause: the following SQL statement is written: SELECT PALLET_ID, BOX_ID, STATUS FROM SD_CURRENT_BOX WHERE PALLET_ID IN ('"+ pallets + "'); among them, pallets is a string composed of many pallet_ids.

Analysis: Obviously, according to the error prompt, we can know that there are 1000 items restrictions in.

Solution: replace pallets long strings with subqueries.

2) ORA-01704: string literal too long

Cause: the following SQL statement is written: UPDATE PDM_MEMBERLIST SET MEMBERS =' ... N multiple strings are omitted here '.

Analysis: The MEMBERS field in the database design is in xmltype format. data is stored as strings. In this way, when the xml data volume is large, the SQL statement is too long and the limit of 2 K has been tasted.

Solution: parameterized SQL statements.