I have a simple stored procedure that needs to do periodic polling of a table to determine if it has been refreshed or not, and I'm trying to figure out how to do a "wait" type operation for a specified number of seconds within this procedure.
Something similar to dbms_lock.sleep(60); in Oracle.
Could someone give me a quick example to work from if this is possible?
I suppose a UDF could be written to perform this function. However, your application might be better served using a queue table. A queue table is a table that can be used as a FIFO event queue. In one usage model, you have two client processes (or stored procedures): a producer and a consumer. The consumer executes a SELECT AND CONSUME statement on the queue table, which (assuming the table is initially empty) causes it to wait until the producer executes an INSERT on the same table.
The queue table feature requires release V2R6.0 or later. UDFs require release V2R5.1 or later.