Re: Trigger row as parameter for procedure call?

> Mark, let us see if I understand correctly: if you call the procedure
> from the trigger using a variable it fails, but the procedure will
> work when you pass a constant; however, you made the trigger into a
> statement level trigger for that test. If you leave in the 'for each
> row' parameter does the procedure work twice in a row for the
> constant? If it fails then perhaps the logic in the procedure fails
> to close a cursor, file, etc...
>
> The other possibility is to make sure the data in the table looks
> exactly the way you think and more importantly the way the procedure
> expects, that is, correct column name being passed, trailing nulls or
> carriage returns not stored in varchar2 column, data value case
> sensitivity problem ruled out .... The dump(column,base) function can
> help verify the data.
>
> I would suspect there is something wrong in the procedure rather than
> with the trigger, but you could try variable := :new.column and use
> the variable in the call just to verify that if makes no difference.
>
> Are you getting any Oracle error messages? Does the procedure attempt
> to read the table the trigger is on? I think this could lead to a
> mutating table error being received. I am not an XML knowledgable
> person so you may need to post your procedure or at least a
> description of what it does so someone more knowledgable can recognize
> probable problem areas.
>
> IMHO
> -- Mark D Powell --

 Thanks for the advice Mark, I figured out in the end that the cursor wasn't working as the table it was using hadn't been committed yet as it was an update or insert that was firing the trigger in the first place, settled in the end for writing to a temp table from the trigger and used the Oracle Scheduler to read these row and pass them as parameters to the XML procedure.

Regards Mark Nyhan Received on Wed Feb 20 2002 - 02:27:09 CST