Re: Help! Calling a PL/SQL procedure with a trigger ... just isn't working!

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.

thanks again...

...jeff

Thomas Kyte wrote:
>
> A copy of this was sent to jeff <nospam_at_nettwerk.com>
> (if that email address didn't require changing)
> On Mon, 04 Jan 1999 05:25:14 -0800, you wrote:
>
> >Hello-
> >
> >I am trying to call a stored procedure using a trigger, and it doesn't
> >seem to want to work for some reason.
> >
> >Current system: OS = Windows NT 4 (sp3), 8.0.4 production
> >Eventually: OS = solaris 2.6, 8.0.5 production
> >
> >I have written a simple stats program that uses 6 different tables, and
> >all are partitioned using the date column (which is actually a number in
> >the form 'YYYYMMDD'). In order to help with the administration of these
> >tables, I have written some dynamic sql that will create (by adding or
> >splitting) or drop partitions for all of the tables involved. This
> >procedure works just fine when called by sqllpus or from another PL/SQL
> >procedure, and is called DO_PARTITIONS.
> >
> >DO_PARTITIONS has 2 parameters, the date (which is actually a number in
> >the form 'YYYYMMDD') and the action (varchar2 of 'create' or 'drop').
> >
> >I then created this trigger to hopefully automate the job even further,
> >so that when a new date is added to the DATE table, the partitions would
> >be created automatically. Similarly, when a date is deleted, the
> >appropriate table partitions would be dropped.
> >
> >Here is the trigger:
> >
> > create or replace trigger dates_trig
> > before insert or delete on dates
> > for each row
> > begin
> > if INSERTING then
> > do_partitions(:new.date_id,'create');
> > dbms_output.put_line('INSERTING');
> > else
> > do_partitions(:old.date_id,'drop');
> > dbms_output.put_line('DELETING');
> > end if;
> > end;
> > /
> >
> >
> >When I insert into the table, the appropriate "INSERTING" output is
> >generated, and "DELETING" appears when a date is being deleted, but the
> >partitions are not being added or dropped.
> >
>
> making some assumptions here:
>
> - do_partitions has an exception handler, probably a WHEN OTHERS, and hence
> always appears to succeed (even when it fails).
>
> - do_partitions is trying to do DDL (partition management DDL).
>
> If partitions has an exception handler that just catches and ignores errors, get
> rid of it.
>
> If partitions is doing DDL, you cannot call it from a trigger. DDL commits
> automagically and since you are midway through an insert (insert isn't over yet)
> you cannot commit.
>
> If you really really need to do DDL in a trigger you can use dbms_job today to
> schedule the ddl to occur shortly after the transaction commits. In Oracle8i,
> you'll be able to use what is known as an autonomous transaction to do the same.
>
> >I'm at a loss, and any help would be GREATLY appreciated.
> >
> >
> >
> >
> >....jeff
> >
> >jeff grant, it manager, nettwerk productions
> >http://www.nettwerk.com http://www.nettweb.com
> >mailto:jeff_at_nettwerk.com
>
>
> Thomas Kyte
> tkyte_at_us.oracle.com
> Oracle Service Industries
> Reston, VA USA
>
> --
> http://govt.us.oracle.com/ -- downloadable utilities
>
> ----------------------------------------------------------------------------
> Opinions are mine and do not necessarily reflect those of Oracle Corporation
>
> 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 - 09:16:14 CST