9 Replies Latest reply: Jun 11, 2014 1:21 PM by Hoek RSS

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

    Bety

      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

       

      Bety