Re: Error: ORA-00911: invalid character

From: Nigel Thomas <"Nigel>
Date: Fri, 11 Mar 2016 16:26:06 +0000
Message-ID: <CAGRZYUfhirCgJgCQ7fke3ZLcD+FPRNBOB99gbR6_uuXLdOVJiQ_at_mail.gmail.com>

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_at_yahoo.co.uk> 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> _at_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>
>

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

Received on Fri Mar 11 2016 - 17:26:06 CET