Re: Executing Stored Procedures from end-user tools

In article <445362.94Apr13133317_at_aisserver2.llnl.gov>, 445362_at_aisserver2.llnl.gov (Grant Johnson) says:
>
>In article <2of882$ec7_at_mailhost.interaccess.com> dmausner_at_interaccess.com (Dave Mausner) writes:
>
> Recently, Steve Stansfield posted:
> : However, I haven't had any luck executing this procedure from an end-user tool
> : such as Q+E 5.0 or Microsoft Query. I get an invalid SQL
> : statement.
> : Is there a trick in executing an Oracle stored procedure from a external
> : application????
>
>
>While not particularly elegant (how many work-arounds are?), I have
>thought about doing this:
>
> Create dummy table, with one column, action.
> Create a trigger on the table (insert or update).
> Have the trigger check the value of action and then invoke the
> appropriate db-procedure.
>
>So in practice, from the client tool you would issue:
>
>update proxy_proc
>set action='REDUCE_DEBT';
>
>Which would fire the on-update trigger:
>
>BEGIN
> IF :NEW.ACTION='REDUCE_DEBT' THEN
> REDUCE_SPENDING;
> END IF;
>END;
>
>One footnote, I haven't had a chance to try this... But it should
>work.
>

It does work. I've done something similar, but using a pre-delete trigger. You can also add additional fields to the table for any parameters that you may want to pass.

Thierry Lach                           | Sufficiently superior technology |
Home: thierry_at_eccdb1.pms.ford.com      | is indistinguishable from magic. |
Work: curlie!thierry_at_sycom.mi.org      |==================================|
#include <std.disclaimer>              |         We Do Magic Here!        |
===========================================================================