Returning data from an array table parameter from an Oracle SP

Question

I have this C# code to call an Oracle stored procedure:

using (OracleConnection oracleConnection = new OracleConnection(connectionString))
{
    oracleConnection.Open();
    OracleCommand oracleCommand = new OracleCommand();

    oracleCommand.Parameters.Add(new OracleParameter
    {
        ParameterName = "eventids",
        Direction = ParameterDirection.Input,
        CollectionType = OracleCollectionType.PLSQLAssociativeArray,
        Value = new string[] { "Test1", "Test2" },
        Size = 2,
        UdtTypeName = "T_STRING_TAB"
    });
    oracleCommand.Parameters.Add("p_cursor", OracleDbType.RefCursor).Direction = ParameterDirection.Output;

    oracleCommand.Connection = oracleConnection;
    oracleCommand.CommandText = "spTest";
    oracleCommand.CommandType = CommandType.StoredProcedure;

    using (OracleDataReader oracleDataReader = oracleCommand.ExecuteReader())
    {
        while (oracleDataReader.Read())
        {
            int fieldCount = oracleDataReader.FieldCount;
            string s = oracleDataReader.GetString(0);
        }
    }               
}

I have this in the database:

create or replace noneditionable package pp is
  type t_string_tab is table of varchar2(260) index by binary_integer;
end;

create or replace noneditionable procedure sptest(
  eventids in pp.t_string_tab,
  p_cursor in out sys_refcursor)
as
begin
  open p_cursor for 
    select p.column_value, 'Test1', 'Test2' from table(eventids) p;
end;

When I call this procedure from the code I get the following error:

Oracle.ManagedDataAccess.Client.OracleException:
ORA-21700: object does not exist or is marked for delete
ORA-06512: at "PPUSER.SPGETMETADATA", line 6 ORA-06512: at line 1'

This appears to be something to do with the T_STRING_TAB type not being accessible from the stored procedure?

I get this error even if I put the stored procedure in the PP package and call PP.spTest in the code. I also tried to use PP.T_STRING_TAB as the UdtTypeName in the code and this did not work. I cannot make the type global otherwise I get the following error:

PLS-00355: use of PL/SQL table not allowed in this context

Please note that I have added a new user so could it be to do with permissions?

I am not sure what I am doing wrong - I am not an Oracle expert!

This question follows on from How to pass an array of strings from C# to an Oracle stored procedure.

In this website (http://dbtricks.com/?p=216 ) it suggests granting execute on the type to the user. Have you tried that? — Francisco Sitja, Aug 16 '19 at 21:52
It's a PL/SQL associative array defined within a package, so that would need to be execute on the package. — William Robertson, Aug 16 '19 at 22:20
There is no type `t_string_tab`. There is only `pp.t_string_type`. I don't know C# so I can't tell if the value assignment is right, but support for this type of conversion between PL/SQL types and SQL types is highly version-dependent. What's your Oracle version? — William Robertson, Aug 16 '19 at 22:25
I have tried to grant execute permission on the package (which should do the type) and that did not work. I tried to add the sp to the package and that did not work either. The Oracle version is OraDB18Home1. — Sputnik, Aug 17 '19 at 07:08
It is rather pointless if you select data from value which you actually provided as input. If you just want to convert `string[]` into a `DataReader` or `DataTable` then you don't need to invoke Oracle for that. — Wernfried Domscheit, Aug 17 '19 at 12:00

Wernfried Domscheit · Answer 1 · 2019-08-17T17:31:06.563

You have to convert your Associative array (index-by table) to a nested table. Could be like this:

CREATE type string_table_type is table of varchar2(260);


create or replace package pp is
  type t_string_tab is table of varchar2(260) index by binary_integer;
  function sptest(eventids in t_string_tab) RETURN p_cursor sys_refcursor;
end;

create or replace package body pp is

function sptest(eventids in t_string_tab) RETURN sys_refcursor as    
   p_cursor sys_refcursor;
   string_tab := string_table_type();
begin
   for i in eventids.FIRST..eventids.LAST LOOP
       string_tab.EXTEND();
       string_tab(string_tab.LAST) := eventids(i);
   end loop;
  open p_cursor for 
    select p.column_value, 'Test1', 'Test2' from table(string_tab) p;
  RETURN p_cursor;
end sptest;

end;

If you have one return value then I prefer a FUNCTION rather than a PROCEDURE with one OUT parameter, but this is more a matter of taste.

Popeye · Answer 2 · 2019-08-17T18:52:01.007

0

You can try to create type at schema level.

CREATE type t_string_tab is table of varchar2(260);

Now you can use it in your select statement as following:

select p.column_value, 'Test1', 'Test2' from table(eventids) p;

Cheers!!

edited Aug 17 '19 at 18:52

answered Aug 17 '19 at 06:07

Popeye

35,427
4
10
31

As I mentioned, I cannot create a "global" type. I get the error:1/22 PLS-00355: use of pl/sql table not allowed in this context – Sputnik Aug 17 '19 at 07:10
You cannot create and Associative array (or index-by table) at schema level. It can be defined only in a PL/SQL block or package. You can create only VARRAY or nested tables at schema level, see [Collection Types](https://docs.oracle.com/database/121/LNPLS/composites.htm#GUID-7E9034D5-0D33-43A1-9012-918350FE148C) – Wernfried Domscheit Aug 17 '19 at 12:03
Did you test it? I doubt that you can run `table(cast eventids as t_string_tab)` - the syntax is wrong anyway. – Wernfried Domscheit Aug 17 '19 at 17:28
Yes, my previous answer was correct, just updated it wrongly in this version by mistake. Haha. Now updated the answer – Popeye Aug 18 '19 at 02:08
But you cannot bind nested table parameters in .NET, it supports only PLSQLAssociativeArray – Wernfried Domscheit Aug 18 '19 at 11:15

Returning data from an array table parameter from an Oracle SP

2 Answers2