How to handle/use multiple values in variable PL/SQL

Question

I am new to PL/SQL

I have a code like this

SELECT multiple_string(exclude_acct(5,'ACCOUNT')) ACCT5 INTO v_acct5  from dual;

this v_acct5, output is

('0001','0002','0003')

Then I use this function like this in my procedure

--This code below is part of my procedure
SELECT multiple_string(exclude_acct(5,'ACCOUNT')) ACCT5 INTO v_acct5  from dual;

select ACCOUNT_NUM
FROM account
where account NOT IN v_acct5

I assume that I could exclude these 3 accounts, but when I tested I notice that I can still see these 3 accounts in the result,

how can I appropriately assign variable in PL/SQL so I can exclude these 3 accounts from the result?

It's hard to say with so little details on your datatypes and structures. If v_acct5 is a collection, I would say you should try to replace the variable name with (SELECT COLUMN_VALUE FROM TABLE(v_acct5)). If it is a string, you should rethink the approach, because you'll have to use dynamic SQL and concatenate the strings. — Chris Hep
– Chris Hep, Commented Sep 2, 2016 at 21:48

XING · Accepted Answer · 2016-09-05 13:27:00Z

Assuming your function returning a collection of arrays, I tried to simulate your scenario. Please see below how you can achieve your results.

Created a test table similar on line of your 'Account' table

CREATE TABLE TEST_ARRAY  
(
  A  NUMBER,
  B  VARCHAR2(30 CHAR)
)

Inserting some sample records:

Insert into TEST_ARRAY (A, B) Values  (1, '0001');
Insert into TEST_ARRAY (A, B) Values  (6, '0002');
Insert into TEST_ARRAY (A, B) Values  (7, '0003');
Insert into TEST_ARRAY (A, B) Values  (4, 'ABC');
Insert into TEST_ARRAY (A, B) Values  (2, 'YRI_1');
Insert into TEST_ARRAY (A, B) Values  (4, 'ABC');

Creating a function which returns an array of varchar2

create or replace function ret_strng (x  sys.odcivarchar2list) --used oracle defined collection for varchar2. 
return sys.odcivarchar2list
as
begin

return x;
end;

Procedure Starting

create or replace procedure disp_func_array_list
as
--- using oracle defined collection for varchar2 to store the result from the function
v_acct5  sys.odcivarchar2list; 

cursor cur_Var(v_acct sys.odcivarchar2list) is
        select * 
        from test_array 
        where B not in ( select column_value from table(v_acct));

begin

--Calling function where my array is getting passed

select ret_strng(sys.odcivarchar2list('0001','0002','0003'))
into v_acct5   --- Storing the value returned by the function
from dual;

for i in cur_Var(v_acct5) -- Passing the resultset(array list) from the function to the Cursor.
 loop

   dbms_output.put_line(i.a ||'<-->'|| i.b);

 end loop;

end;

Executing Procedure:

execute disp_func_array_list;

Result Set:

2<-->YRI_1
4<-->ABC
4<-->ABC
19<-->BCG

PL/SQL procedure successfully completed.

Shivam Mathur · Accepted Answer · 2016-09-03 01:07:20Z

1

Use as sub query

select ACCOUNT_NUM
FROM account
where account IN(SELECT multiple_string(exclude_acct(5,'ACCOUNT')) ACCT5 INTO          
v_acct5 from dual)

edited Sep 3, 2016 at 1:07

Shivam Mathur

2,73322 silver badges29 bronze badges

answered Sep 2, 2016 at 21:47

Parwinder K

133 bronze badges

1 Comment

XING Over a year ago

It will not work. Remove into clause from the subquery.

Collectives™ on Stack Overflow

How to handle/use multiple values in variable PL/SQL

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related