Information

Title	How to find the SQL query responsible for Oracle error ORA-00933:SQL "command not properly ended"

Summary	Steps for finding the query responsible for Oracle error ORA-00933:SQL command not properly ended, produced during a custom Data Collect

URL Name	ORA-00933-Error-on-data-collect

Resolution

1. The SQL Trace file location can be found by querying the DIAGNOSTIC_DEST database parameter. From SQL plus, connect as an admin user and run:
     Show parameters diagnostic_dest;

2. To start a database wide trace, from the same sqlplus session run:
  exec dbms_monitor.database_trace_enable;

3. Run the Data collect to reproduce the error.

4. It is important that the trace is disabled after reproducing the error.
exec dbms_monitor.database_trace_disable;

5. Go to the trace folder under the diagnostic destination for your database, and run the following command to find the sql trace file with the error.

grep err=933 *

6. Open the trace file and search for err=933 to find the problem SQL.

Products	Allocation

Module	Data Collect

Version	All

Error Message Code

"ORA-00933:SQL command not properly ended"

Cause

This error occurs if the SQL syntax is incorrect.

Additional Information

Example:
Bold section shows a missing comma as the reason for the ORA-9333 error.

PARSE ERROR #675563208:len=435 dep=1 uid=44 oct=2 lid=44 tim=955092337069 err=933 INSERT /* + append */ INTO BWS_PLAN_FORMAT (ALLOCATION_NBR, LOCATION_ID, ASRT_QTY) select:1,min(location_id),sum(assortment_qty) from bws_ep_assortment_prd a,bws_list_po b worklist w where a.item_code= 0031855 and a.aap_id=b.aap_id and a.asrt_key=b.assortment_key and trim(leading 0 FROM w.ITEM_CODE)=trim(leading 0 FROM a.ITEM_CODE) and w.AAP_ID=a.AAP_ID and w.ASRT_KEY=a.ASRT_KEY and w.ALLOC_NBR = 472253 group by location_id CLOSE #675563208:c=0,e=2,dep=1,type=0,tim=955092337426

Publication Status	Published

How to find the SQL query responsible for Oracle error ORA-00933:SQL "command not properly ended"

Information