Taking multiple value from Oracle Apex page and store it in an table

0
votes

I have developed 1 apex page where I have one text field. I will insert multiple values by separating it through a comma. I want that when I hit the submit button then it should separate the values on the basis of comma and should insert individually in different rows. For example if I pass "abc,cde,efgh,ijhk,gygg" in my textfield then it should insert "abc" in one row,"cde" in another row and so on. and also it should not through any error when I insert only one value.

I am able to store the value for only one data as I have created a procedure and that procedure takes only one data but I am not getting an idea of if I pass multiple values by separating it via comma then it should insert. I am posting my procedure here.

 create or replace procedure Exclude_items(
 p_analyze_name in varchar2,
 p_material_number in varchar2
 )
as
 p_analyze_id number;

begin

select analyze_id
into p_analyze_id
from analyzes
where table_name = p_analyze_name;

insert into p20da.test_vishal(ANALYZE_ID,MATNR,) values(p_analyze_id,p_material_number)
end;

Here matnr will be having multiple values separated by comma as it is a text field in apex page but analyze_id will be constant.

I want to write the procedure so that it could separate whenever a comma comes and should not through any error if I only insert one value.

2
oraclestored-proceduresoracle-apex
It's a social responsibilty for everyone on SO to educate others about SO etiquette, so please read this stackoverflow.com/help/someone-answers - XING

2 Answers

0
votes

How about something like:

create or replace procedure exclude_items( p_analyze_name in varchar2, p_material_number in varchar2 )
as
  comma_pos number;
  sub_name varchar2(4000);
  temp_name varchar2(4000);

   p_analyze_id number;
begin

select analyze_id
into p_analyze_id
from analyzes
where table_name = p_analyze_name;

  temp_name := p_material_number;
  LOOP
    comma_pos := instr( temp_name, ',' );
    exit when comma_pos = 0;
    sub_name := substr( temp_name,1,comma_pos-1 );

    insert into p20da.test_vishal(ANALYZE_ID,MATNR,) values(p_analyze_id,sub_name)
    temp_name := substr( temp_name, comma_pos + 1 );

  END LOOP;

  insert into p20da.test_vishal(ANALYZE_ID,MATNR,) values(p_analyze_id,temp_name)

end;
/
0
votes

You should take advantage of STRING_TO_TABLE function of APEX_UTIL package. Add below code to Submit process, your exclude_items procedure stays the same.

DECLARE
  L_MATERIAL_NUMBER APEX_APPLICATION_GLOBAL.VC_ARR2;
BEGIN
  L_MATERIAL_NUMBER := APEX_UTIL.STRING_TO_TABLE(:P2_MATERIAL_NUMBER,',');
  FOR I IN 1..L_MATERIAL_NUMBER.COUNT
  LOOP
    EXCLUDE_ITEMS('tablea', L_MATERIAL_NUMBER(I));
  END LOOP;
END;

More Information on API Package Function --> http://docs.oracle.com/cd/E11882_01/appdev.112/e12510/apex_util.htm#CHDFEEJD