Re: Stupid trigger problem

The first one does not work because the only way to include DDL (like 'alter index...') in PL/SQL is using dynamic SQL (dbms_sql or EXECUTE IMMEDIATE). The second one does not work, because EXECUTE IMMEDIATE requires character string as a parameter.

So:

create trigger tr delete or insert on testtab declare sql_string varchar2(4000);
BEGIN
sql_string := 'alter index testtabidx rebuild online parameters(' || '''' || 'sync' || '''' || ')';
execute immediate sql_string;
end;
/

or:

create trigger tr delete or insert on testtab BEGIN
execute immediate 'alter index testtabidx rebuild online parameters(' || '''' || 'sync' || '''' || ')';
end;
/

It's a little complicated because of the quotes around 'sync'. IF it would be simple 'alter index' without "parameters('sync')", then you could do:

create trigger tr delete or insert on testtab BEGIN
execute immediate 'alter index testtabidx rebuild online'; end;
/

Igor Neyman, OCP DBA
Perceptron, Inc.
(734)414-4627
ineyman_at_perceptron.com

• Original Message ----- From: "Andreas Jung" <ajung_at_sz-sb.de> To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Friday, July 28, 2000 6:46 AM Subject: Stupid trigger problem

> The following trigger give me a compilation error but I don't know why:
>
> create trigger tr delete or insert on testtab
> BEGIN
> alter index testtabidx rebuild online parameters('sync');
> end;
> /
>
> also
>
> create trigger tr delete or insert on testtab
> BEGIN
> execute immediate alter index testtabidx rebuild online
parameters('sync');
> end;
> /
>
> did not work. Any idea ?
>
> Thanks,
> Andreas
> --
> Author: Andreas Jung
> INET: ajung_at_sz-sb.de
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
Received on Fri Jul 28 2000 - 07:27:27 CDT