Search code examples
phpsqlcronclobvarchar2

Can Changing a SQL Table Column From VARCHAR2 to CLOB Negatively impact systems that use cron scripts or PHP web pages?

I'm working in a complex system that utilizes multiple SQL queries called by several cron.sh script files, printing the data onto php pages.

I will be changing a column from type VARCHAR2(4000) to CLOB, so that the field can expand past 4000 in size. (In Oracle)

I've been trying to understand as much of the code as possible to discern if changing VARCHAR2 to CLOB could have any negative or unanticipated side effects. There appears to be no down side to swapping to CLOB from VARCHAR2.

Any opinions or confirmation in this matter?

Solution

  • Yes. Changing from VARCHAR2 to CLOB in Oracle SQL can can and did have negative impacts on other systems. In a shell script relating to the changed field that generated an email, the following problem occured:

    Oracle's 'spool' command defaults to 80 characters when retrieving CLOB data. This resulted in the field being cut off at 80 characters in the email.

    To fix the problem, the SQL script line 'SET LONG 8000' needed to be added.