Re: Error: ORA-00911: invalid character

On 2016/03/11 09:38, Zabair Ahmed wrote:

> CREATE OR REPLACE PACKAGE BODY Purge_Data AS
>
> PROCEDURE pr_Purge_Data
> ( P_Days IN Number
> ) IS
> L_Error_Count PLS_INTEGER := 0;
> BEGIN
> for L_Row in (
> select * from (
> select 1 as order_tname
> , tname as object_name
> , 'select count(*) from '||tname||' where last_modified < sysdate - ' ||P_Days|| ';'
> as STMT
> from tab
> )
> where object_name like 'CT%'
> order by order_tname, object_name
> ) LOOP
> BEGIN
> dbms_output.put_line(L_Row.Stmt);
> execute immediate L_Row.Stmt;
> EXCEPTION
> WHEN OTHERS THEN
> dbms_output.put_line('Error: ' || sqlerrm);
> L_Error_Count := L_Error_Count + 1;
> END;
>
> END LOOP;
> _[snip]_
> SQL>
>
> Any pointers on the error, greatly appreciated.
>
> TIA
My kneejerk is that there's implicit casting going on with P_Days, and also that SELECTs would generally require an corresponding INTO clause within PL/SQL.

You might also want to try a different approach to the above, using XML, something along the lines of:

SELECT
 TNAME,
 TO_NUMBER(
 EXTRACTVALUE(
 XMLTYPE(
 dbms_xmlgen.getxml('SELECT COUNT(*) c from '||tname||' WHERE last_modified < sysdate - '||P_Days)
 )
 ,'/ROWSET/ROW/C')
 ) COUNT
FROM
 tab
WHERE tname LIKE 'CT%'
ORDER BY 1,2; Try this from the command line first, and if it works, include the INTO clause in your package body.

HTH! GL! Rich

--
http://www.freelists.org/webpage/oracle-l