Home » SQL & PL/SQL » SQL & PL/SQL » BULK COLLECT and return a RECORD as OUT param (9.2)
BULK COLLECT and return a RECORD as OUT param [message #580510] Mon, 25 March 2013 09:31 Go to next message
raghx
Messages: 28
Registered: March 2006
Location: Bangalore
Junior Member
Hi All,
The following is the Record type and table type created. It is called in the below procedure. The procedure will be called in another procedure to return the records. But the count is showing as 0. Can you please help so that I get an OUT param with records which I can use in the calling procedure.

-- Record type
  type t_ein_cmpl_rec IS RECORD(
    IN_req_param_id       t_IN_req_param_id,
    IN_call_request_id    t_IN_call_request_id,
    IN_action             t_IN_action,
    IN_event_id           t_event_id,
    IN_ported_nbr         t_IN_ported_nbr,
    ported_nbr_range_stop t_ported_nbr_range_stop,
    IN_routing_nbr        t_IN_routing_nbr,
    IN_request_nbr        t_IN_request_nbr);

  type t_ein_cmpl_tab is table of t_ein_cmpl_rec index by binary_integer;


PROCEDURE Get_EIN_Completed_Record(p_event_id          IN t_event_id,
                                     p_ein_completed_tab OUT t_ein_cmpl_tab) IS
  
    k_module_name constant t_module_name := 'Get_EIN_Completed_Record';
    --
    --l_ein_cmpl_tab t_ein_cmpl_tab;
    --
    CURSOR c_get_ein_cmpl_rec IS
    
      SELECT unique(t186.t186_in_req_param_id),
             t184.t184_in_call_request_id,
             t184.t184_in_action,
             t184.t184_event_id,
             t186.t186_ported_nbr,
             t186.t186_ported_nbr_range_stop,
             t186.t186_routing_nbr,
             t186.t186_in_request_nbr
        FROM t184_in_call_request  t184,
             t186_in_req_parameter t186,
             t188_in_req_result    t188,
             t112_event            t112
       WHERE t112.t112_event_status_code = bl_event.k_ema_dual_es_open
         AND t112.t112_event_id = t184.t184_event_id
         AND t112.t112_event_id = p_event_id
         AND t184.t184_send_to_in = k_received
         AND t188.t188_request_status = k_in_complete
         AND t184.t184_in_call_request_id = t188.t188_in_call_request_id
         AND t186.t186_in_call_request_id = t184.t184_in_call_request_id
         AND t186.t186_in_call_request_id = t188.t188_in_call_request_id
         AND t186.t186_in_req_param_id = t188.t188_in_req_param_id;
    --
   TYPE ein_cmpl_tab IS TABLE OF c_get_ein_cmpl_rec%ROWTYPE
        INDEX BY PLS_INTEGER;
      l_ein_cmpl_tab ein_cmpl_tab;  
  BEGIN
    UT_DEBUG.enable;
    UT_DEBUG.Start_Module(k_package_name, k_module_name);
    --
    OPEN c_get_ein_cmpl_rec;
    LOOP
      FETCH c_get_ein_cmpl_rec BULK COLLECT
        INTO l_ein_cmpl_tab LIMIT 1000;
     /* FOR i IN 1 .. l_ein_cmpl_tab.count LOOP
      
        UT_DEBUG.Display('FNN', l_ein_cmpl_tab(i).IN_ported_nbr);
        UT_DEBUG.Display('IN_CALL_REQUEST_ID',
                         l_ein_cmpl_tab(i).IN_CALL_REQUEST_ID);
      END LOOP;*/
      EXIT WHEN l_ein_cmpl_tab.count = 0 OR l_ein_cmpl_tab.count = 1000;
      ut_debug.display('count l_ein_cmpl_tab*** = ', l_ein_cmpl_tab.count);
      
    END LOOP;
    Close c_get_ein_cmpl_rec;
   -- p_ein_completed_tab := l_ein_cmpl_tab;
    ut_debug.display('count l_ein_cmpl_tab = ', l_ein_cmpl_tab.count);
    /*ut_debug.display('count', p_ein_completed_tab.count);
    UT_DEBUG.Display('FNN', p_ein_completed_tab(1).IN_ported_nbr);
    UT_DEBUG.Display('IN_CALL_REQUEST_ID',
                     p_ein_completed_tab(1).IN_CALL_REQUEST_ID);*/
  
  EXCEPTION
    WHEN OTHERS THEN
      IF c_get_ein_cmpl_rec%ISOPEN THEN
        CLOSE c_get_ein_cmpl_rec;
      END IF;
      RAISE;
  END Get_EIN_Completed_Record;
Re: BULK COLLECT and return a RECORD as OUT param [message #580511 is a reply to message #580510] Mon, 25 March 2013 09:43 Go to previous messageGo to next message
BlackSwan
Messages: 23134
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/


How can we reproduce what you report?

>But the count is showing as 0.

what count?
showing 0 to who & when?
Re: BULK COLLECT and return a RECORD as OUT param [message #580512 is a reply to message #580510] Mon, 25 March 2013 10:02 Go to previous message
Michel Cadot
Messages: 59970
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do not use a collection for this, return a REF CURSOR.

Regards
Michel
Previous Topic: SQL Issue to generate report for shopping store.
Next Topic: ORA-01704 IN ORACLE
Goto Forum:
  


Current Time: Thu Dec 18 01:46:55 CST 2014

Total time taken to generate the page: 0.13092 seconds