| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Help! Calling a PL/SQL procedure with a trigger ... just isn't working!
A copy of this was sent to jeff grant <nospam_at_nettwerk.com>
(if that email address didn't require changing)
On Mon, 04 Jan 1999 07:16:14 -0800, you wrote:
>Thanks for the prompt reply Thomas...
>
>Not the answer I was looking for, but what can you do? ;)
>
>I'll probably end up invoking sqlplus through a shared library I've
>installed to do the DDL stuff, though, due to the possible lack of
>availability or wait for the job queue.
>
you can use the job queue today. dbms_jobs were introduced into the database in v7.1. Basically, if a trigger wants to do ddl, it can use the job queues to do that. you'll use dbms_job.submit() to submit a stored procedure that will execute right after you commit. What I think you might want to do is simply code:
create or replace trigger dates_trig
before insert or delete on dates
for each row
declare
l_job number;
begin
if INSERTING then
dbms_job.sumit( l_job, 'do_partitions(' || :new.date_id || ',''create'');' );
dbms_output.put_line('INSERTING'); else
dbms_job.sumit( l_job, 'do_partitions(' || :old.date_id || ',''create'');' );
dbms_output.put_line('DELETING');
end if;
end;
/
if your date includes a time component -- you might need to pass that as a VARCHAR with an explicity to_char() on it and let do_partitions do an explicity to_date() on it (not a bad idea given the Y2K anyway) to avoid losing any component of it.
that way, do_partitions will run almost immediately after you commit. just make sure to set the job_* parameters in your init.ora to permit jobs to run.
In Oracle8i, you'll be able to have do_partitions execute immediately as an autonomous transaction (a transaction inside a transaction that can commit independently of the other transaction)
>thanks again...
>
>
>...jeff
>
>
>
>Thomas Kyte wrote:
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA
--
http://govt.us.oracle.com/ -- downloadable utilities
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Mon Jan 04 1999 - 12:24:06 CST
 |
 |