CODEWITHSUNDEEP
jquerysqloracle-databaseoracle-apex
sersarino
sersarino

Reputation: 11

Oracle apex how to get values from checkbox and insert it into specific table

I have question about getting values from checkbox list called :P5_LIST and insert these values into specific table. I have table called ST_COURSE have the entity US_ID which is refer to user student id and C_CODE which is refers to Course code. The scenairo of the problem as follow :

The student check the courses that he want to register after that click button called "register" after he clicked register I want the ID of the user logged in and the selected courses fetched into the table ST_COURSE. I tried this query but it didn't work:

insert into ST_COURSE(US_ID,C_CODE)values(:APP_USER,:P5_LIST);

Any idea how it's gonna work?

thanks

Upvotes: 0

Views: 2936

Answers (3)

Akil_Ramesh
Akil_Ramesh

Reputation: 431

Use the function apex_string.split to achieve the desired result.

This function is used to to split input clob at separator.

BEGIN
  FOR rec IN (SELECT column_value 
                 FROM table(apex_string.split(:P5_LIST,':')))
  LOOP
    INSERT INTO ST_COURSE(US_ID
                         ,C_CODE)
                  VALUES(:APP_USER
                        ,rec.column_value); 
  END LOOP;
END;

To know more about apex_string.split, please visit the below link:

https://docs.oracle.com/database/apex-5.1/AEAPI/SPLIT-Function-Signature-1.htm#AEAPI-GUID-3BE7FF37-E54F-4503-91B8-94F374E243E6

Upvotes: 0

Koen Lostrie
Koen Lostrie

Reputation: 18665

P5_LIST will contain a colon separated list of selected values. That can be split into the individual values using the APEX_STRING api. You can loop through the values in a pl/sql block and insert one selected value per row:

BEGIN
  FOR r IN (SELECT column_value FROM table(apex_string.split(: P5_LIST,':')))
  LOOP
    insert into ST_COURSE(US_ID,C_CODE)values(:APP_USER,r.column_value); 
  END LOOP;
END;

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 142743

Koen said what P5_LIST looks like. Another option is to avoid a loop and use single INSERT statement whose SELECT part splits P5_LIST's values into rows.

insert into st_course (us_id, c_code) 
  select :APP_USER,
         regexp_substr(:P5_LIST, '[^:]+', 1, level)
  from dual
  connect by level <= regexp_count(:P5_LIST, ':') + 1;

Upvotes: 1

Related Questions