Re: Error: ORA-00911: invalid character
- From: Rich J <rjoralist3@xxxxxxxxxxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Fri, 11 Mar 2016 10:32:50 -0600
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
Other related posts:
