2

Part of a stored procedure I'm writing (on an Oracle DB) will return an array of integer values to a c# app. I've never done this before and I can't find info online on how to do this inside of the stored procedure.

On the C# side, I've connected to the DB and created a stored procedure command. I'm using:

cmd.Parameters.Add("returnID", OracleDbType.Array, ParameterDirection.Output);

To grab the array.

Inside of the Stored Procedure, I have:

CREATE OR REPLACE PROCEDURE ODM(/* not relevant*/, returnIDs OUT ARRAY)
IS
BEGIN
...
END ODM;

Where returnIDs is the array I want to output, full of integers.

I need to be able to loop through a table, ORDERS, and grab all integer primary keys between two values, and add them into returnIDs.

I'm hoping theres soemthing similar to an insert into the array, where the primary key is between the min and max value, but I'm not sure.

What's the syntax to be able to declare those values, loop through the table and add into my output array?

EDIT: solution: Bulk Collect would work for this, but it's much easier just to return the min and max values to my program and then just do a separate select in there.

Improve this question

3 Answers 3

Reset to default
5

I'm not sure that you need to loop. Depending on the definition of the ARRAY type, you can probably just

SELECT primary_key
  BULK COLLECT INTO returnIDs
  FROM orders
 WHERE primary_key BETWEEN low_value AND high_value
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Thank you. Haven't heard of bulk collect before. I'll try that.
Just added it in. It looks like it will work, just have to begin testing it. Thanks!
3

So, what you need to know is:

  1. we can insert into an array using Oracle's bulk collect syntax
  2. we can give each row a unique number using the ROW_NUMBER() aggregate function.

Put them together into a PL/SQL function like this:

SQL> create or replace type numbers_nt as table of number
  2  /

Type created.

SQL> create or replace function get_range_of_numbers
  2    (p_start in pls_integer
  3      , p_end in pls_integer )
  4     return  numbers_nt
  5  is
  6    rv numbers_nt ;
  7  begin
  8      select empno
  9      bulk collect into rv
 10      from
 11          ( select empno
 12                   , row_number() over (order by empno asc) rn
 13            from emp )
 14      where rn between p_start and p_end;
 15      return rv;
 16  end;
 17  /

Function created.

SQL>

Let's rock!

    SQL> select *
      2  from table(get_range_of_numbers(5, 8))
      3  /

    COLUMN_VALUE
    ------------
            7654
            7698
            7782
            7788

    SQL>

Hmmm, I think I misread your question. You probably want to select records on the basis of key value rather than row position. In which case, the function should simply be

create or replace function get_range_of_numbers
  (p_start in pls_integer
    , p_end in pls_integer )
   return  numbers_nt 
is
  rv numbers_nt ;
begin
    select empno
    bulk collect into rv
    where emp between p_start and p_end;
    return rv;
end;
/
Improve this answer

1 Comment

I also misread your comments further down in this thread. No idea what I was thinking. This works much better.
-1

My db expertise is in sql server and firebird primarily and I am not a skilled oracle person. However, I was just curious, can't you just select the values and return them as a simple datatable or dataset to the C# application then you can either keep them in the datatable or convert them to an array or collection in the C# app however you like?

Improve this answer

5 Comments

Just to add to my answer. Basically what I am saying is why not return the values to the front end and then handle the array management up front? Would that solve the issue easily enough?
I'm managing a bunch of "orders" done on a specific date, (inputting the date into the procedure) and want a list of all the orders I just managed.
"why not return the values to the front end and then handle the array management up front?" Because if you want twnty rows out of a miilion row table, the impact on the b=network would be Teh Suck!
Nobody indicated that the developer should return a million rows or all records to the front end casing network latency. I was indicating that the developer could (as an alternative resolution) return the 20 rows to the front end (using 20 as an example). The data would be available in a simple datatable or dataset then that could easily be conerted to a c# array or collection in C# rather than returning an array from oracle directly to C#
Part of what I'm doing is moving rows from one table to another "where date = input_date" and it could be from 1 to not more than 20 rows. I was trying to grab those rows that I just moved and return them. If there is a better way let me know. I'm still new to this.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.