Don't include the final semicolon ';' in the dynamic SQL. It's not needed.
, 'select count(*) from '||tname||' where last_modified <
sysdate - ' ||P_Days as STMT
Semicolon terminates the statement in SQL*Plus and other clients but isn't
actually part of the SQL syntax.
Regards Nigel
On 11 March 2016 at 15:38, Zabair Ahmed <roon987@xxxxxxxxxxx> wrote:
Oracle 11.2.0.4 on Redhat 5.9
Am getting an ORA-00911 in sqlplus when running the below piece of SQL:-
SQL> !cat z.sql
CREATE OR REPLACE PACKAGE Purge_Data
AUTHID CURRENT_USER
AS
PROCEDURE pr_Purge_Data
( P_Days IN Number
);
END Purge_Data;
/
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;
IF L_Error_Count > 0 THEN
raise_application_error(-20001, 'Error count is ' ||
L_Error_Count || '. See DBMS_Output for details');
END IF;
END pr_Purge_Data;
END Purge_Data;
/
SQL> @z
Package created.
Package body created.
SQL> exec purge_data.pr_purge_data(7);
select count(*) from CT_ADT_HL7_JOB_SEGMENT where last_modified < sysdate
- 7;
Error: ORA-00911: invalid character
select count(*) from CT_ASR_ASSESS_PARAMS where last_modified < sysdate -
7;
<snip>
