Apex insert row from form using a ODBC driver with PL/SQL

Question

I’m currently using Apex 20.4 and tries to insert a row based on variables from another page (Wizard) Since I’m using a Snowflake ODBC I cannot just use the standard DML submit action. The query I’ve made, note that the part @snowflakecomputing is correct, I can do a insert in an interactive grid with a similar query.

begin declare
v_current_time date := sysdate;
v_user_id varchar(50) := :APP_USER;
v_type varchar(20) := 'CLIENT';
--v_status varchar(1) := :APEX$ROW_STATUS;

v_continent varchar(50) := :P11_continent;
v_country_code varchar(2) := :P11_country_code;
v_region varchar(20) := :P11_region;
v_province varchar(50) := :P11_province;
v_city varchar(50) := :P11_city;
v_postalcode varchar(15) := :P11_postalcode;
v_address_line_1 varchar(50) := :P11_address_line_1;
v_address_line_2 varchar(50) := :P11_address_line_2;
v_latitude number := :P11_latitude;
v_longitude number := :P11_longitude;
begin
execute immediate
     'begin 
        insert into dwh_pl.geographic@snowflakecomputing (continent, country_code, region, province, city, postalcode, address_line_1, address_line_2,
                        latitude, longitude, type, creation_date, user_id)
         values (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13);
         commit;
     end;'
     using v_continent,
           v_country_code,
           v_region, 
           v_province, 
           v_city, 
           v_postalcode, 
           v_address_line_1, 
           v_address_line_2, 
           v_latitude, 
           v_longitude, 
           v_type, 
           v_current_time, 
           v_user_id;
end;
end;

The Error I receive : Hiding error additional_info, as it contains ORA error message: ORA-01403: no data found

Which is strange, I display the same variables in the form that’s why I’m sure the parameters contain the data that I need. My question is, is there something I’m missing like a variable that needs to be included

Thank you.

Answer 1

Apparently you can use the submit action when just inserting data, there is no need to use PL/SQL. I think its just a bit buggy to use custom drivers in Apex, 'sometimes it works, sometimes without any changes it does not work'