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