ORA-00972 when trying to update field in table

Question

update t set command="select PIN_ID, PIN_STATUS from inventory where trunc(EXPIRY_DATE) <= trunc(sysdate) and pin_status in ('Delivered','GenHold')" where id='8';

im getting this error Error at Command Line : 2 Column : 32 Error report - SQL Error: ORA-00972: identifier is too long 00972. 00000 - "identifier is too long" *Cause: An identifier with more than 128 bytes was specified, or a password identifier longer than 30 bytes was specified. *Action: Specify at most 128 bytes for identifiers, and at most 30 bytes for password identifiers.

how do i update this field?

Answer 1

The problem in your query: You have used double quotes to represent the string value. You must use the single quotes to represent the string in oracle. Double quotes are used for giving alias or for object names

There are single quotes in your query so You need to use two single quotes in your string to represent a single quote for your actual string value as follows:

UPDATE T
   SET
    COMMAND = 'select PIN_ID, PIN_STATUS from inventory where trunc(EXPIRY_DATE) <= trunc(sysdate) and pin_status in (''Delivered'',''GenHold'')'
 WHERE ID = '8';

But there is an easy option available (quoted-string)

UPDATE T
   SET
    COMMAND = q'#select PIN_ID, PIN_STATUS from inventory where trunc(EXPIRY_DATE) <= trunc(sysdate) and pin_status in ('Delivered','GenHold')#'
 WHERE ID = '8';

Here, I have used q'#<your_string>#' to simply ignore all the single quotes to be considered as the end of the string from your actual string (otherwise you have to replace all the single quotes in your string with two single quotes). It is called quoted string and it is represented as:

q'<delimiter><your_string><delimeter>'

Answer 2

The issue is with the quotes, please use below statement,

update t set command= 'select PIN_ID, PIN_STATUS from inventory where trunc(EXPIRY_DATE) <= trunc(sysdate) and pin_status in (''Delivered'',''GenHold'')' where id='8';