Restrictions on the length of Oracle SQL statements and Solutions

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 limits 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 = '<Project>... n multiple strings <ProjectID>' 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.