Oracle SQL - SELECT with Variable arguments stored procedure

Question

I'm struggling with a variable argument stored procedure that has to perform a SELECT on a table using every argument passed to it in its WHERE clause.

Basically I have N account numbers as parameter and I want to return a table with the result of selecting three fields for each account number.

This is what I've done so far:

function sp_get_minutes_expiration_default(retval IN OUT char, gc IN OUT GenericCursor,
        p_account_num IN CLIENT_ACCOUNTS.ACCOUNT_NUM%TYPE) return number
        is
    r_cod integer := 0;
    begin
        open gc for select account_num, concept_def, minutes_expiration_def from CLIENT_ACCOUNTS
            where p_account_num = account_num;  -- MAYBE A FOR LOOP HERE?
        return r_cod;
    exception

        -- EXCEPTION HANDLING

    end sp_get_minutes_expiration_default;

My brute force solution would be to maybe loop over a list of account numbers, select and maybe do a UNION or append to the result table?

Jeff Holt · Accepted Answer · 2017-07-07 15:27:04Z

1

If you cast your input parameter as a table, then you can join it to CLIENT_ACCOUNTS

select account_num, concept_def, minutes_expiration_def
from CLIENT_ACCOUNTS ca, table(p_account_num) a
where a.account_num = ca.account_num

But I would recommend you select the output into another collection that is the output of the function (or procedure). I would urge you to not use reference cursors.

ADDENDUM 1

A more complete example follows:

create or replace type id_type_array as table of number;
/

declare
   ti id_type_array := id_type_array();
   n number;
begin
   ti.extend();
   ti(1) := 42;
   select column_value into n from table(ti) where rownum = 1;
end;
/

In your code, you would need to use the framework's API to:

create an instance of the collection (of type id_type_array)
populate the collection with the list of numbers
Execute the anonymous PL/SQL block, binding in the collection

But you should immediately see that you don't have to put the query into an anonymous PL/SQL block to execute it (even though many experienced Oracle developers advocate it). You can execute the query just like any other query so long as you bind the correct parameter:

select account_num, concept_def, minutes_expiration_def
from CLIENT_ACCOUNTS ca, table(:p_account_num) a
where a.column_value = ca.account_num

edited Jul 7, 2017 at 15:27

answered Jul 6, 2017 at 13:36

Jeff Holt

3,2623 gold badges27 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Franch Over a year ago

@jeff5times7 In this short example p_account_num should be of what type, a SQL LIST?? I've been reading about the TABLE() function but I don't really understand what argument it takes. Could you expand a little further please?

Jeff Holt Over a year ago

Please see ADDENDUM 1.

Collectives™ on Stack Overflow

Oracle SQL - SELECT with Variable arguments stored procedure

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related