Home > Default > Oracle dequeue function is not working through different user.

Oracle dequeue function is not working through different user.

November 30Hits:0
Advertisement
I created an Oracle queue, an anonymous block to enqueue messages in it and a dequeue function. when i use the dequeue function with the same user i've created queue its working fine, but when i am executing same function with different user it is returning null.
Please see the code i've used ----
-- Create q_table
begin
DBMS_AQADM.create_queue_table (
queue_table => 'QUEUE_QTAB' , queue_payload_type => 'SYS.XMLTYPE'
,multiple_consumers => TRUE, message_grouping => DBMS_AQADM.none);
end;
--Create queue:-
begin
DBMS_AQADM.create_queue (
queue_name => 'QUEUE_Q', queue_table => 'QUEUE_QTAB');
end;
-- Start queue:-
begin
dbms_aqadm.start_queue (
queue_name => 'QUEUE_Q');
end;
--Add subscriber:-
begin
DBMS_AQADM.add_subscriber (
queue_name  => 'QUEUE_Q',
subscriber  => sys.aq$_agent ('B_USER', NULL, NULL));
end;
GRANT SELECT ON A_USER.QUEUE_QTAB  TO DBMONITOR;
GRANT SELECT ON A_USER.QUEUE_QTAB TO IDSCORE;
GRANT SELECT ON A_USER.QUEUE_QTAB TO IDSREAD;
GRANT SELECT ON A_USER.QUEUE_QTAB TO IDS_READ;
-- View Creation:-
CREATE VIEW QUEUE_QTAB _VW
AS
  SELECT * FROM QUEUE_QTAB
  WHERE Q_NAME = QUEUE_Q';
Grants:-
GRANT SELECT ON A_USER.QUEUE_QTAB _VW TO B_USER;
    --Enqueue
    DECLARE
   enqueue_options     dbms_aq.enqueue_options_t;
   message_properties  dbms_aq.message_properties_t;
   message_handle      RAW(16);
   message           XMLTYPE;
BEGIN
   message := XMLType('<?xml version="1.0"?><tns:ISO8583-87 xmlns:tns="http://www.tsys.com/prime/online/iso8583"><I000>0120</I000><I002>491693******9989</I002><I003>280000</I003><I004>000000003000</I004><I006>0000091050.23</I006><I007>0406131721</I007><I011>039622</I011><I012>094121</I012><I013>0224</I013><I018>5999</I018><I022>0000</I022><I032>06123496</I032><I033>06198745</I033><I037>123456787012</I037><I038>026446</I038><I039>00</I039><I041>A1234567</I041><I042>CARD ACCEPTER  </I042><I043>ACQUIRER NAME            CITY NAME    UK</I043><I048>1001O</I048><I049>978</I049><I051>826</I051><I056>37120300692340000012300004800456005600</I056><I102>0890220150</I102></tns:ISO8583-87>');
   dbms_aq.enqueue(queue_name => 'QUEUE_Q',          
         enqueue_options      => enqueue_options,      
         message_properties   => message_properties,    
         payload              => message,              
         msgid                => message_handle);
   COMMIT;
end;
Dequeuing is actually done through two function   fn_dequeue function calls fn_dequeue_payment_msg, both are under different packages
fn_dequeue---
FUNCTION fn_dequeue (p_consumer_i IN VARCHAR2 DEFAULT USER)
      RETURN CLOB
   AS
      v_msg          xmltype;
      v_msg_found    BOOLEAN := FALSE;
      v_return       clob;
      c_procedure_name constant varchar2 (100) := 'FN_DEQUEUE';
      v_locn         idsc_globals_pck.styp_locn;
      v_q_name_i varchar2(35) := 'QUEUE_Q';
   begin
        v_locn := '80';
      idsc_lib_pck.pr_debug (
                             p_program_name => c_procedure_name, p_ids_req_id => NULL
                            ,p_locn => v_locn, p_debug_message => 'Function called by ' || USER
       v_msg_found :=
         idsph_queues_pck.fn_dequeue_payment_msg (
                                                  p_q_name_i => v_q_name_i , p_message_o => v_msg, p_consumer_i => p_consumer_i
         v_locn := '90';
      if v_msg_found then
         v_locn := '100';
         idsc_lib_pck.pr_debug (
                                p_program_name => c_procedure_name, p_ids_req_id => NULL
                               ,p_locn => v_locn, p_debug_message => 'Message found'
         v_return := v_msg.getstringval ();
      end if;
         v_locn := '110';
      idsc_lib_pck.pr_debug (
                             p_program_name => c_procedure_name, p_ids_req_id => NULL
                            ,p_locn => v_locn, p_debug_message => 'Returning'
      RETURN v_return;
   EXCEPTION
      WHEN OTHERS THEN
         idsc_errors_pck.pr_raise_app_error (
                                             p_proc_name_i => c_procedure_name, p_locn_i => v_locn, p_err_msg_i => SQLERRM
   END fn_dequeue_iso8583;
fn_dequeue_payment_msg-----
FUNCTION fn_dequeue_payment_msg (
                                    p_q_name_i IN VARCHAR2
                                   ,p_consumer_i IN VARCHAR2 DEFAULT USER
                                   ,p_message_o   OUT XMLTYPE
      RETURN BOOLEAN
   IS
      v_dequeue_options DBMS_AQ.dequeue_options_t;
      v_message_properties DBMS_AQ.message_properties_t;
      v_message_handle RAW (16);
      v_message      XMLTYPE;
      e_no_messages exception;
      PRAGMA EXCEPTION_INIT (e_no_messages, -25228);
      c_procedure_name CONSTANT VARCHAR2 (100) := 'IDSPH_QUEUES_PCK.FN_DEQUEUE_PAYMENT_MESSAGE';
      v_locn         idsc_globals_pck.styp_locn;
   BEGIN
      v_locn := '10';
      idsc_lib_pck.pr_debug (
                             p_program_name => c_procedure_name, p_ids_req_id => NULL
                            ,p_locn => v_locn, p_debug_message => 'Setting dequeue options'
      v_dequeue_options.wait := dbms_aq.no_wait;      
      v_locn := '20';
      v_dequeue_options.navigation := DBMS_AQ.first_message;
      v_locn := '30';
      v_dequeue_options.consumer_name := p_consumer_i;
      v_locn := '40';
      idsc_lib_pck.pr_debug (
                             p_program_name => c_procedure_name, p_ids_req_id => NULL
                            ,p_locn => v_locn, p_debug_message => 'Dequeuing next message'
                            idsc_lib_pck.pr_debug (
                             p_program_name => c_procedure_name, p_ids_req_id => NULL
                            ,p_locn => v_locn, p_debug_message => 'p_consumer_i' || p_consumer_i                         
      DBMS_AQ.dequeue (
                       queue_name => p_q_name_i, dequeue_options => v_dequeue_options, message_properties => v_message_properties
                      ,payload => v_message, msgid => v_message_handle
      v_locn := '50';
      idsc_lib_pck.pr_debug (
                             p_program_name => c_procedure_name, p_ids_req_id => NULL
                            ,p_locn => v_locn, p_debug_message => 'Dequeue successful'
      p_message_o := v_message;
      RETURN TRUE;
   EXCEPTION
      WHEN e_no_messages THEN
         -- Not an error just no messages currently in queue so return success
         v_locn := '160';
         idsc_lib_pck.pr_debug (
                             p_program_name => c_procedure_name, p_ids_req_id => NULL
                            ,p_locn => v_locn, p_debug_message => SQLERRM
         RETURN FALSE;
      WHEN OTHERS THEN
         idsc_errors_pck.pr_raise_app_error (
                                             p_proc_name_i => c_procedure_name, p_locn_i => v_locn, p_err_msg_i => SQLERRM
   END fn_dequeue_payment_msg;
fn_dequeue function returns null while executing from B_USER , while same functions works fine for A_USER, please suggest

Answers

I got the error , we need append schema name where queue is created before assigning it to a variable, this problem is now resolved.
fn_dequeue---
FUNCTION fn_dequeue (p_consumer_i IN VARCHAR2 DEFAULT USER)
      RETURN CLOB
   AS
      v_msg          xmltype;
      v_msg_found    BOOLEAN := FALSE;
      v_return       clob;
      c_procedure_name constant varchar2 (100) := 'FN_DEQUEUE';
      v_locn         idsc_globals_pck.styp_locn;
      v_q_name_i varchar2(35) := 'A_USER.QUEUE_Q';
   begin
        v_locn := '80';
      idsc_lib_pck.pr_debug (
                             p_program_name => c_procedure_name, p_ids_req_id => NULL
                            ,p_locn => v_locn, p_debug_message => 'Function called by ' || USER

Read other 2 answers

Tags:

Related Articles

  • Oracle dequeue function is not working through different user.November 30

    I created an Oracle queue, an anonymous block to enqueue messages in it and a dequeue function. when i use the dequeue function with the same user i've created queue its working fine, but when i am executing same function with different user it is re

  • Oracle Spatial functionality with WFSOctober 11

    Hi all My first post here so be gentle on me ;-) This may be an obvious question but here goes ... When using a WFS client can you access the full SDO_... SQL functions within Oracle Spatial? I've had a read of the WFS spec and it is all a bit vague

  • How to Oracle Database Function in OBIEE Administration ToolOctober 11

    Hi All, I want to use a Oracle Database Function in as a column in OBIEE (BM, Presentation Model). Can anybody has done this inOBIEE. Any pointers to this is much appreicated. Thanks,Hi user7417334, You cannot use any formulas in the presentation lay

  • Execute Oracle Package Function call from FORTENovember 30

    Has anyone EVER successfully execute an Oracle Package Function call from FORTE via " sql execute procedure "? Here's my question, I am able to execute a stored procedure but hasn't figured out a way to execute a function which defined in a pack

  • Oracle Asset (Functional) Practichttps:/e Questions for Interviews and ExamNovember 30

    https://www.createspace.com/3495382 http://www.amazon.com/Functional-Questions-Interviews-Certification-Examination/dp/1456311581/ref=sr_1_4?ie=UTF8&s=books&qid=1289178586&sr=8-4 List Price: $29.99 Add to Cart Oracle Asset (Functional) Practic

  • Oracle Asset (Functional) Practice Questions for Interviews and ExamNovember 30

    https://www.createspace.com/3495382 http://www.amazon.com/Functional-Questions-Interviews-Certification-Examination/dp/1456311581/ref=sr_1_4?ie=UTF8&s=books&qid=1289178586&sr=8-4 List Price: $29.99 Add to Cart Oracle Asset (Functional) Practic

  • Oracle Asset (Functional) Practice Questions for Interviews and CertificatiNovember 30

    https://www.createspace.com/3495382 List Price: $29.99 Add to Cart Oracle Asset (Functional) Practice Questions for Interviews and Certification Examination (Release 11i and 12) Functional Consultant Authored by Erp Gold This book contains 150 Oracle

  • Using of Oracle Text functions not working in JDevNovember 30

    Hi, I'm confused about using Oracle Text functions in JDeveloper. It's not possible for me to pass the sql syntax test while creating a view object with a custom query: SELECT score (1) myScore, dokumente.titel, ctx_doc.snippet ('idx_dokumente_titel'

  • Oracle stored functions and where clausesNovember 30

    Hello everybody, I need to call an Oracle stored function and at the same time do a select on the result of the query, it works well except when I use a where clause. I am connecting to Oracle 8.1.7 through OLEDB using the Oracle provider for OLEDB d

  • Oracle 9i function to perform data manipulationNovember 30

    Hi everyone, I have two scenarios. I would like to write one Oracle 9i function that can accommodate both these scenarios, using PL/SQL developer. I intend passing a varchar2 parameter to this function. Within the function, I perform a select on a ta

Copyright (C) 2019 wisumpire.com, All Rights Reserved. webmaster#wisumpire.com 15 q. 1.204 s.