ORA-20999: Failed to parse SQL query! ORA-06550: line 4, column 82: ORA-00936: missing expression

Question

While trying to build a prompt context in Oracle APEX, I can't tell what's wrong with my code below:

SELECT
    'Overview of the product : '|| PRODUCT_DETAILS ||BLOB || BLOB||
    'This product cost the following price : '||UNIT_PRICE||NUMBER(10, 2) || NUMBER(10, 2)||
    'This product comes in the following colors: '||COLOR||VARCHAR2(0, 200) || VARCHAR2(0, 200)||
    'This product belongs to the following department: '||DEPARTMENT||VARCHAR2(0, 200) || VARCHAR2(0, 200)||
    'This product is part of the following clothing:  '|| CLOTHING|| VARCHAR2(0, 200) || VARCHAR2(0, 200)
    AS prompt_context
FROM 
    PRODUCTS 
WHERE 
    id = :P21_PRODUCT_ID;

I get the following errors:

ORA-20999: Failed to parse SQL query
ORA-06550: line 4, column 82:
ORA-00936: missing expression

Answer 1

It seems you simply copy-pasted column names with their types from the data dictionary, when you only wanted names.

The concatenation operator || concatenates strings.

• 'Overview of the product : ' is a string.
• PRODUCT_DETAILS, COLOR, DEPARTMENTS and CLOTHING are probably string columns.
• UNIT_PRICE is probably a numeric column that can be converted into a string.
• BLOB, NUMBER(10, 2), etc. are neither of these and result in the error.

You want something like this:

SELECT
    'Overview of the product : ' || product_details || ' ' ||
    'This product cost the following price : ' || unit_price || ' ' ||
    'This product comes in the following colors: ' || color || ' ' ||
    'This product belongs to the following department: ' || department || ' ' ||
    'This product is part of the following clothing:  ' || clothing
    AS prompt_context
FROM 
    PRODUCTS 
WHERE 
    id = :P21_PRODUCT_ID;