dimanche 18 décembre 2022

Oracle APEX looping through CheckBox Group elements

I need to make the function of inserting data into several tables at once in the ORACLE APEX App Builder. Interactive report displays information from the View collected from the tables "Book", "Autors_list", "Autor", where "Autors_list" is needed to reveal the many-to-many relationship between the author and the book.

So, on an automatically created form, when I click on the CREATE button, using a dynamic event, I try to add data to the "Book" and "Autors_list" tables.

The data I'm trying to add:

P15_ID_BOOK - book id - add to "Book" and to "Autors_list"

P15_BOOK_NAME - book name - add to "Book"

P15_AUTORS - authors - are represented using the Checkbox Group - I need to go through each of its elements and for each element add an entry to the "Autors_list" in the form (P15_BOOK_NAME, author id from P15_AUTORS)

P15_PUBLICATION_DATE - publication date - add to "Book"

P15_PRICE - book's price - add to "Book"

The CheckBox Group is set using the following List of Values

`

select "id_autor", (CONCAT(CONCAT(autor."Name", ' '),autor."Surname")) as "Autors" from "Autor" autor;

`

I wrote the following PL/SQL code to add the above values to the tables:

begin
insert into "Book"
("Name", "Publication_date", "Price")
values
(:P15_BOOK_NAME, :P15_PUBLICATION_DATE, :P15_PRICE);

FOR i IN (select * from table(apex_string.split(:P15_AUTORS, ',')))
LOOP
 insert into "Autors_list"
 values (:P15_ID_BOOK, i);
END LOOP;
end ;

However, it gives me an error

ORA-01008: not all variables bound

Please tell me how to cycle through the elements of the Checkbox Group correctly and get the values from there




Aucun commentaire:

Enregistrer un commentaire