Accessing data from a Stored Procedure in ODAC

[Horace]

Could anyone point me in a right direction how to access data generated within a Stored Procedure (executing on an Oracle server), and transfer it to a client via ODAC Net but WITHOUT using an intermediate temporary table to store the procedure's results ?

e.g. Oracle Collections to Client via ODAC Net, etc...


Any suggestions would be greatly appreciated.

Regards,
Horace

[Plash]

The first way:
Create a stored procedure that returns the data through REF CURSOR parameter.
The second way:
Create a stored procedure that returns the data through parameter of nested table type.

[Horace]

Thank you for your help!

Which ODAC component, would be suitable for transfering the data from a Ref Cursor or Nested Table, to the client ?

TOraQuery or some other one ?


Regards,
Horace

[Horace]

..also, what are the pros and cons of using the Ref Cursor versus the Nested Table method ?


Regards,
Horace

[Challenger]

You can use TOraQuery, TSmartQuery, TOraStoredProc, TOraSQL. In case of TOraStoredProc you only need to specify a stored procedure name. In other components you should set the SQL property (PL/SQL block with procedure call). If the procedure has parameters of type CURSOR, TOraQurye, TSmartQuery, TOraStoredProc components automatically open the first one.
REF Cursors is more suitable because you don't need to create any specific Oracle types.

[Horace]

Is there any way to pass Oracle collections or arrays (not tables) out of stored procedures into the client via ODAC using TOraStoredProc ?

I'm seeking an efficient way to transfer the output from a stored procedure (e.g a stored procedure that returns a collection or array representing arithmetic SUMs from 2 tables), without writing this collection or array into a temporary table.
Note that these arithmetic SUMs form a temporaty recordset that does not exist outside of the stored procedure.
Specificaly - this recordset does not exist in a table (it is not stored in a table).

Of course I could write the Oracle collection or array to a temporary table, and then transfer the recordset in this temporary table to the client, using a simple SELECT query via TOraQuery, BUT this is exactly what I am trying to avoid.

[Challenger]

Yes, ODAC supports such functionality. For more information please refer to ODAC help, the "Working with VARRAY data type" topic.

[Horace]

I am sorry to report that my developers do not understand this documentation.

Could I bother you a little more for specifics ? (we are a paying ODAC Pro user, and would like to ask for some personal support)

Since an example is worth a hundred words, I am enclosing a sample stored procedure below.

If this sample procedure executes on Oracle server, how would you transfer the resultset in p_array, to the client via ODAC ?
(without using a temporary intermediate table, of course)


Regards,
Horace

Code: Select all

create or replace package arrays is

  TYPE rec_nums IS RECORD (
    num1 number,
    num2 number);

  TYPE aarray_type IS TABLE OF rec_nums
    INDEX BY BINARY_INTEGER;

end arrays;


procedure array_samplep(p_param1 in varchar2, -- a parameter that might be used in the future, now is not used
                        p_param2 in varchar2, -- a parameter that might be used in the future, now is not used
                        p_array  in out aarray_type) is
  v_id       number;  
begin

  v_id := 0;
  for v_id in 1..10 loop
    p_array(v_id).num1 := v_id;
    p_array(v_id).num2 := round(dbms_random.value(1,1000));    
  end loop;
  
end array_samplep;

...for the sake of clarity I have omited the declaration of array_samplep

[Challenger]

ODAC doesn't have features to work with PL/SQL collections and records. You can use nested tables.

Code: Select all

Code: 
CREATE TYPE rec_nums AS OBJECT 
( 
    num1 number, 
    num2 number 
); 

CREATE TYPE aarray_type AS TABLE OF rec_nums;  

procedure array_samplep(p_param1 in varchar2, -- a parameter that might be used in the future, now is not used 
                        p_param2 in varchar2, -- a parameter that might be used in the future, now is not used 
                        p_array  in out aarray_type) is 
  v_id       number;  
begin 

  v_id := 0; 

  p_array := aarray_type();
  for v_id in 1..10 loop
    p_array.EXTEND;
    p_array(v_id).num1 := v_id; 
    p_array(v_id).num2 := round(dbms_random.value(1,1000));    
  end loop; 
  
end array_samplep;

For information on how to execute this procedure please refer to the following link http://crlab.com/forums/viewtopic.php?t=5808.

[Horace]

ODAC doesn't have features to work with PL/SQL collections and records.

Boo ooooooooooooooooooo... weep, weep...:(

Does this limitation also apply to PL/SQL functions that return collections or arrays ?

Is it possible to add support for collections and records to ODAC in the future ?

What about the method suggested above by the user "plash" using the REF CURSOR parameter ?
Is that method workable ? (any examples anywhere...)


Thanks for your help,
Horace

[Challenger]

There is no sense to use REF CURSORs in this situation. Another way is to create function:

Code: Select all

function array_samplep3(p_param1 in varchar2, -- a parameter that might be used in the future, now is not used 
                        p_param2 in varchar2 -- a parameter that might be used in the future, now is not used 
                        ) return aarray_type
is                        
  v_id       number;  
  p_array aarray_type;
begin 
  v_id := 1; 
  p_array  :=  aarray_type();
   for v_id in 1..10 loop 
    p_array.EXTEND;
    p_array(v_id) := round(dbms_random.value(1,1000));    
 end loop;
  return p_array;
end array_samplep3;

Then you can use the following SQL statement to execute this function:

Code: Select all

select * from Table(arrays.array_samplep3('',''))

[alo88]

Dear devart Team,

I have nearly the same problem like Horace...
I am using the TOraStoredProc component for calling an oracle stored procedure with a PL/SQL-table-type as output parameter.
Your statement so far is that parameters of those type are not supported.

But when I enter the procedure name in the Stored Proc Editor of TOraStoredProc component at design time then I get my PL/SQL-table-type-values displayed in the "Value"-textfield after pressing the "execute" button. You see it at the image below...



So it seems that the TOraStoredProc component can definitely handle this PL/SQL-table-type...

So my question is when I see the component is able to fetch these PL/SQL-table-type-data in its Stored Proc Editor how can I fetch these data in my delphi source code?? Does the TOraStoredProc component also use that nested table internally to fetch these data into its "Value"-textfield?? Or how does that work in the component??

I would be very very grateful for your information!

Best reagards,
Andreas Lohrer

Here the code i tried to fetch that "Value"-textfield-content...

first SQL code:

Code: Select all

create or replace package DAP_contactOverview is
  
  
  -- types for input parameters...

  SUBTYPE dcot_name_t    IS dap_contacttype.dcot_name%TYPE ;
  SUBTYPE dcon_name_t    IS dap_contact.dcon_name%TYPE     ;
  SUBTYPE dcon_city_t    IS dap_contact.dcon_city%TYPE     ;
  SUBTYPE dcon_country_t IS dap_contact.dcon_country%TYPE  ;

  -- table type for output parameter...

  TYPE dcot_name_tt        IS TABLE OF dap_contacttype.dcot_name%TYPE       INDEX BY BINARY_INTEGER ;


procedure DAP_getContacts (i_dcot_Name        in   dcot_name_t,
                           i_dcon_Name        in   dcon_name_t,
                           i_dcon_City        in   dcon_city_t,
                           i_dcon_Country     in   dcon_country_t,
                           o_dcot_Name        out  dcot_name_tt
)  ;

end DAP_contactOverview;


create or replace package body DAP_contactOverview is


cursor csGetContacts (i_dcot_Name    in   dcot_name_t,
                      i_dcon_Name    in   dcon_name_t,
                      i_dcon_City    in   dcon_city_t,
                      i_dcon_Country in   dcon_country_t
)
is
  select dcot.dcot_name
    from dap_contact dcon,
         dap_contacttype dcot
   where dcon.dcot_ref = dcot.dcot_ref
     and dcot.dcot_name like i_dcot_Name
     and dcon.dcon_name like i_dcon_Name
     and dcon.dcon_city like i_dcon_City
     and dcon.dcon_country like i_dcon_Country
  order by dcot.dcot_name, dcon.dcon_name, dcon.dcon_country, dcon.dcon_city, dcon.dcon_street ;


procedure DAP_getContacts (i_dcot_Name        in   dcot_name_t,
                           i_dcon_Name        in   dcon_name_t,
                           i_dcon_City        in   dcon_city_t,
                           i_dcon_Country     in   dcon_country_t,
                           o_dcot_Name        out  dcot_name_tt
)  
is

i integer := 0 ;
cs_rt csGetContacts%rowtype ;
begin

  if not csGetContacts%ISOPEN then
    OPEN csGetContacts (i_dcot_Name, i_dcon_Name, i_dcon_City, i_dcon_Country) ;
  end if;
  
  loop
    FETCH csGetContacts
     INTO cs_rt ; 

    if csGetContacts%ROWCOUNT <= 0 or csGetContacts%NOTFOUND = TRUE then
      exit ;
    end if;

    i := i+1 ;
    
    o_dcot_Name(i) := cs_rt.dcot_name ; 
     
  end loop ; 
  
  if csGetContacts%ISOPEN then
    CLOSE csGetContacts ;
  end if;
    
end;

end DAP_contactOverview;

then my delphi source code...

Code: Select all

procedure TfrmDAP_contactOverview.FillGrdContact();
var
o_cotName : string ;
v: TOraArray;
begin

  with dmDAP_dataModule.OraStoredProc1 do begin

    StoredProcName := 'DAP_CONTACTOVERVIEW.DAP_GETCONTACTS' ;

    PrepareSQL() ;

    Params.ParamByName('i_dcot_Name').value := '%' ;
    Params.ParamByName('i_dcon_Name').value := '%' ;
    Params.ParamByName('i_dcon_City').value := '%' ;
    Params.ParamByName('i_dcon_Country').value := '%' ;

    Params.ParamByName('o_dcot_Name').Length := 5 ;

    Execute ;

    //this line caused a compile error...
    v := Params.ParamByName('o_dcot_Name').value ;
  end;

end;

[Horace]

Very good question !