9 Replies Latest reply on Jun 11, 2014 6:21 PM by Hoek

    The source has correct syntax but ...... parsing error is showing up!


      Hello everyone,


      I need your help because a strange error is showing up in a procedure that has more than 3 months working without problem.


      The stored procedure GESTOR1.carga enters registers to some tables an suddenly the number of processed registers decrease.


      We put a trace with ALTER SESSION SET sql_trace = TRUE; and process it with tkprof.


      In the first lines we can see:



      The following statement encountered a error during parse:


      SELECT to_number(extractvalue(dbms_xmlgen.getXMLtype (:"SYS_B_0"||table_name),:"SYS_B_1")) FROM  USER_TABLES    WHERE table_name in (SELECT  upper(:"SYS_B_2"||a.puerto) puerto FROM puertos awhere a.central=LOWER(:"SYS_B_3"))


      Error encountered: ORA-00907



      The description of ORA-00907 says: OERR: ORA 907 missing right parenthesis


      But the query doesn´t have problems with parenthesis. Instead we can see this error:

      ..WHERE table_name in (SELECT upper(:"SYS_B_2"||a.puerto) puerto FROM puertos awhere a.cent

      It seems that is missing a space between the alias “a” and the instruction “where”.


      The problem is that the source looks good.


      We display de query with DBMS_OUTPUT.PUT_LINE and we execute it in sqlplus. The query was working without change ........


      In fact, we extract the rows of the dba_source and there, it is correct too!!! ......


      GESTOR1                        CARGA                          PROCEDURE           746                                                                                                                                                                       lt_query22:='SELECT sum(to_number(extractvalue(dbms_xmlgen.getXMLtype (''select count(*) cnt from ''||table_name),''/ROWSET/ROW/CNT'')))'||   GESTOR1                        CARGA                          PROCEDURE           747                                                                                                                                                                                         ' FROM  USER_TABLES   '||                                                                                                                                                         GESTOR1                        CARGA                          PROCEDURE           748                                                                                                                                                                                         '  WHERE table_name in (SELECT  upper(''t_''||a.puerto) puerto FROM puertos a WHERE a.central=LOWER('''||lt_central||'''))';                                                                                                                                                                                                                                                                        



      What do you think?


      Thanks for your help