And taking the previous page a step further - the following code produces a page with embedded html links which can then call another function - in our case this was a list of messages on a queue - clicking on the link would then remove that single message from the queue (note the entmap off extra option to sqlplus - this stops special characters being interpreted)

.html content is

#!/bin/sh
echo Content-type: text/html
# Required blank line
echo
# Your ORACLE_HOME
ORACLE_HOME=/app/oracle/product/10.2.0.3.DB
export ORACLE_HOME
# SQL*Plus executable path
# and enable SQL*Plus markup feature and include Net8 service name
# and SQL script path and script name
$ORACLE_HOME/bin/sqlplus -s -M "html on entmap off" user/pass@db @queuefix.sql

and queuefix.sql contained

select message,event_type,entity_name,key_values,key_columns,to_char(enq_time,'dd-mon-yyyy hh24:mi:ss'),'<a href="http://servername:8035/cgi-bin/dba/delrec.html?TEST='||message||'">DEL</a>' DEL from apps_error_queue
order by 3,6
/
exit
/
delrec.html then contains

#!/bin/sh
echo Content-type: text/html
# Required blank line
echo
# Your ORACLE_HOME
ORACLE_HOME=/app/oracle/product/10.2.0.3.DB
export ORACLE_HOME
VAR=`echo $QUERY_STRING |cut -d'=' -f2`
#echo $VAR
$ORACLE_HOME/bin/sqlplus -s -M "html on entmap off" user/pass@db @delmsg.sql $VAR

and  delmsg.sql contains

ttitle "Message"
set pages 0
exec apps_delete_message('&1');
exit
/
For completeness this is the stored proc

create or replace procedure apps_delete_message (v_message in varchar2) as
  deq_msgid      RAW(40);
  my_payload     trade_data_exc_payload;
  deq_opt        dbms_aq.dequeue_options_t;
  msg_prp        dbms_aq.message_properties_t;
begin
  deq_opt.wait          := 1;  -- second
  deq_opt.dequeue_mode  := dbms_aq.REMOVE;
  deq_opt.msgid := v_message;

    BEGIN
    dbms_aq.dequeue(queue_name => 'DATA_EXC',
                    dequeue_options => deq_opt,
                    message_properties => msg_prp,
                    payload => my_payload,
                    msgid => deq_msgid);
--dbms_output.put_line ('Processed message');
    commit;
    END;
end apps_delete_message;
Once you know the tricks this is actually very simple to set up.

For reference here is a screenshot of the simple page - clicking on the del link then removes the message form the queue.


Comments