Assign value to array from plsql block

Question

I have the following code in Oracle Forms detail block

BEGIN
v_product_no := :detail_block.product_no;
Go_block('detail_block');
first_record;
--some if condition
WHILE :SYSTEM.last_record != 'TRUE' LOOP        
next_record;     
if(:detail_block.product_no = v_product_no) then  
 -- other condtions

end if;

END LOOP;

I would like to store v_product_no into a some kind of collection object so that I could compare with value of :detail_block.product_no.

How can I do this?

Edit 1

product_no will have values such as K1BATTERY, K2BATTERY, ZCATBATEERY etc. So if K1BATTERY is same as :detail_block.product_no then proceed with next condition

Edit 2

Go_block('detail_block');
v_product_no := :detail_block.product_no;
v_products(v_product_no) := 1;

first_record;

WHILE :SYSTEM.last_record != 'TRUE' LOOP            

    if(v_products.exists(v_product_no)) then
        alert('duplicate');  
            end if;     
    END LOOP;

END if;

Edit 3

Go_block('detail_block');
v_product_no := :detail_block.product_no;
v_products(v_product_no) := 1;

    first_record;

    -- condition 


    WHILE :SYSTEM.last_record = 'FALSE' LOOP    
        next_record;        
        v_product_no := :detail_block.product_no;
        if(v_products.exists(v_product_no)) then
            alert('duplicate'); 
             else
      v_products(v_product_no) := 1; 

            end if;

        END LOOP;

    END if;

Do you really need a collection object? You can create a package spec and have it store the value if it's a scalar value. Also why not just store it into local block variable? — Sathyajith Bhat
– Sathyajith Bhat, Commented Apr 23, 2015 at 8:37
@Sathya My intention is if product_no is getting repeated, then I would like to have another if condition to be evaluated in same detail block. Having v_product_no stored in varchar2 is not helping to compare values in multi record block. Thanks — Jacob
– Jacob, Commented Apr 23, 2015 at 8:39
You don't need a collection type to store a scalar value. Declare another variable locally and assign the value, it could be used in the scope of the session, as it is a local variable. — Lalit Kumar B
– Lalit Kumar B, Commented Apr 23, 2015 at 8:41
@LalitKumarB If I declare as a local variable, how can I compare all the items in multi record block against current record's item value? — Jacob
– Jacob, Commented Apr 23, 2015 at 8:42
Perhaps it is not clear. What kind of values assigned by :detail_block.product_no? Please show an example, I will try to reproduce what you want. — Lalit Kumar B
– Lalit Kumar B, Commented Apr 23, 2015 at 8:44

Pavel Gatnar · Accepted Answer · 2015-04-23 10:15:19Z

3

Use pl/sql associative array to store already processed values.

declare
  type t_processed is table of number(1) index by varchar2(100);
  v_product_no varchar2(100); --hold the current value
  v_products t_processed;     --hold all processed values as keys
begin
  v_product_no := :detail_block.product_no;
  v_products(v_product_no) := 1; --create entry (v_product_no, 1)
  ...
  --later in while
  v_product_no := :detail_block.product_no;
  if(v_products.exists(v_product_no)) then --entry exists
  -- other conditions
  else
      v_products(v_product_no) := 1; --create entry (v_product_no, 1)
  end if;
...

edited Apr 23, 2015 at 10:15

answered Apr 23, 2015 at 9:08

Pavel Gatnar

4,0532 gold badges22 silver badges29 bronze badges

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

6 Comments

Jacob Over a year ago

I have tried this, however it inside while loop for checking exists condition, it goes into a infinite loop.

Pavel Gatnar Over a year ago

@Polppan this is an issue in your loop, not in this general construct

Jacob Over a year ago

I have included my code snippet in my question as Edit 2

Jacob Over a year ago

Yes, it does require an user action.

Jacob Over a year ago

I have cases where I there are no duplicate entry of prod_no, still it shows duplicate record as alert. I have included code snippet as Edit 3.

|

Lalit Kumar B · Accepted Answer · 2015-04-23 09:01:36Z

1

I think you are having Varying IN list of values in v_product_no variable.

You could do it in following way,

Test# 1

SQL> var product_no VARCHAR2(1000)
SQL> exec :product_no := 'K1BATTERY'

PL/SQL procedure successfully completed.

SQL>
SQL> SET SERVEROUTPUT ON
SQL>
SQL> DECLARE
  2    v_product_no VARCHAR2(1000);
  3  BEGIN
  4    v_product_no := 'K1BATTERY, K2BATTERY, ZCATBATEERY';
  5    IF :product_no IN (trim(regexp_substr(v_product_no, '[^,]+'))) THEN
  6      DBMS_OUTPUT.PUT_LINE('FOUND A MATCH');
  7    ELSE
  8      DBMS_OUTPUT.PUT_LINE('NO MATCH FOUND');
  9    END IF;
 10  END;
 11  /
FOUND A MATCH

PL/SQL procedure successfully completed.

Test# 2

SQL> var product_no VARCHAR2(1000)
SQL> exec :product_no := 'ABCD'

PL/SQL procedure successfully completed.

SQL>
SQL> SET SERVEROUTPUT ON
SQL>
SQL> DECLARE
  2    v_product_no VARCHAR2(1000);
  3  BEGIN
  4    v_product_no := 'K1BATTERY, K2BATTERY, ZCATBATEERY';
  5    IF :product_no IN (trim(regexp_substr(v_product_no, '[^,]+'))) THEN
  6      DBMS_OUTPUT.PUT_LINE('FOUND A MATCH');
  7    ELSE
  8      DBMS_OUTPUT.PUT_LINE('NO MATCH FOUND');
  9    END IF;
 10  END;
 11  /
NO MATCH FOUND

PL/SQL procedure successfully completed.

SQL>

answered Apr 23, 2015 at 9:01

Lalit Kumar B

49.5k13 gold badges104 silver badges131 bronze badges

1 Comment

Pavel Gatnar Over a year ago

this is a risk because you do not know the count of the records and you can run out of variable size

Collectives™ on Stack Overflow

Assign value to array from plsql block

2 Answers 2

6 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related