| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Subqueries in Inserts in Triggers
This isn't a subquery but what is called an inline view.
Inline views where silently added to the SQL language in 7.1
They were documented in 7.2
Support for using them in PL/SQL started with version 7.3
Prior to 7.3 to make use of them in pl/sql you must either
On Wed, 05 Mar 1997 09:35:35 -0800, Tony Keller <EUS.EUSAJK_at_MEMOUSA.ERICSSON.SE> wrote:
>I'm new to Oracle but have been around the RDBMS world for a while
>so my question is more syntax than semantics, I think. Anyway here's
>the scoop:
>
> Oracle7 Server Release 7.1.4.1.10
> SQL*Plus Release 3.1.3.5.1
> I want the creation of a summary table to occur within a trigger, the
> error occurs when I use subqueries in my insert statement but only in
>the
> trigger if I run just the insert statement in SQL*Plus it runs fine.
>
> And here's the problem:
>
>create or replace trigger tr_the_load
> after update of second_is_loaded on the_load
> for each row
> when (new.first_is_loaded = 'Y' and new.second_is_loaded = 'Y')
>BEGIN
>
> insert into the_summary
> (select sq1.this,
> sq1.that,
> sq1.the_other_thing,
> to_char(sq2.a_date,'YYYYMM'),
> sq2.not_a_date,
> sum(sq2.totals)
> from (select t1.this this,
> decode(t1.a_code, 'CODE1', '1',
> 'CODE2', '2',
> '0') that,
> t1.the_other_thing the_other_thing
> from table1 t1
> where not exists (select 1
> from table2 t2
> where t1.this = t2.this)) sq1,
> (select a_date,
> not_a_date,
> count(*) totals,
> from table3
> where yada_yada_yada
> group by a_date,
> not_a_date) sq2
> where sq1.this = sq2.not_a_date
> group by sq1.this,
> sq1.that,
> sq1.the_other_thing,
> to_char(sq2.a_date,'YYYYMM'),
> sq2.not_a_date);
>
>END;
>
>and the error I get from SQL*Plus is:
>
>create or replace trigger tr_the_load
>*
>ERROR at line 1:
>ORA-06550: line 13, column 11:
>PLS-00103: Encountered the symbol "(" when expecting one of the
>following:
>a PL/SQL variable or double-quoted string
>an expanded name
>an expanded name link
>etc.
>Resuming parse at line 53, column 28.
>
>TIA
Thomas Kyte
Oracle Government
tkyte_at_us.oracle.com
 |
 |