APEX ORACLE - Insert multiple parameters into multiple columns from apex to database table

Question

- Structure of the APEX APP:

Apex Oracle > Page> Form> Content Body > Items > p14_surname,p14_name,p14_birth_date,p14_gender,p14_city,P14_check

pkg_cf.cod_fiscale is a pl/sql procedure.
pkg_cf = name of package.
cod_fiscale = name of procedure.
V_STR_C to V_comune = are functions and insert parameters.
V_controllo = function that will put together all the parameters and generate/create an ID based by parameters inserted.
employee_list = name of the table.
employee_list columns = surname,name,birth_date,gender,city, ID ( In this column, the automated generated/created IDs will be put into this column).

**What Im trying to do in an Apex Action - Execute Server-Side Code:

if the p14_check = 1 (true) will execute the package
else p14_check = 0(false) will return an error message, for example 
dbms_output.put_line("there is an error in the ID created/generated")**

Structure of the apex page :

REGION BUTTON > CREATE > DYNAMIC ACTIONS > CLICK_CREATE(NAME OF THE DYNAMIC ACTION) > TRUE > PL/SQL CODE:

begin
 pkg_cf.cod_fiscale ( V_STR_C    => :P14_surname,
                      V_STR_N    => :P14_name,
                      V_DATA     => :P14_birth_date,
                      V_SESSO    => :P14_gender,
                      V_COMUNE   => :P14_city,
                      v_controllo => :P14_check);   
if P14_CHECK = 1 then insert into employee_list(name of the table) set surname = :P14_surname,
                                            set name = :P14_name,
                                            set birth_date = :P14_birth_date,
                                            set gender = :P14_gender,
                                            set city = :P14_city
else P14_CHECK = 0 then dbms.output.put_line ("there is an error in the ID created/generated");

The code I have written is giving me an error which is : ORA-00926: missing VALUES keyword

Line: set surname= :P14_surname,

Koen Lostrie Koen Lostrie · Accepted Answer · 2021-06-03T09:40:05

Any reason you're not using the built-in form region functionality ? That is a lot simpler than creating your own package. It also has features that are a lot of work to code manually, like lost update detection. Looks like you're trying to re-invent the wheel.

The error you're seeing is because the syntax of your insert statement is incorrect. This is your code:

...
if P14_CHECK = 1 then insert into employee_list(name of the table) set surname = :P14_surname,
                                            set name = :P14_name,
                                            set birth_date = :P14_birth_date,
                                            set gender = :P14_gender,
                                            set city = :P14_city

But the syntax for an insert statement is

  INSERT INTO employee_list (surname, name, birth_date, gender, city) VALUES
    (:P14_surname,:P14_name,:P14_birth_date,:P14_gender,:P14_city);

Side not: only use dbms_output in your pl/sql if you're running it in a client tool like sqlcl, sqlplus or sqldeveloper (or workshop in apex). In an app that output cannot be used and could cause unexpected buffer overflow errors.

APEX ORACLE - Insert multiple parameters into multiple columns from apex to database table

1 Answers