Execute Multiple Queries in Oracle Apex Pl/Sql

Question

i have button with dynamic action execute pl/sql Code

on button click i have to execute two queries, initially insert in a table and then update in another table

INSERT INTO student (student_name,
                     student_father,
                     student_dob,
                     student_gender,
                     country,
                     email_id,
                     whatsapp_number,
                     good_time_to_contact,
                     time_requested,
                     mobile_number,
                     state_province,
                     city,
                     zip_code,
                     skype_id,
                     street_adress,
                     course,
                     language_required,
                     class_days,
                     application_id,
                     updated_by)
   SELECT first_name || ' ' || last_name AS StudentName,
          father_name,
          date_of_birth,
          gender,
          country_id,
          email,
          whatsapp_number,
          time_to_contact,
          learning_time,
          mobile_number,
          state_province,
          city,
          zip_code,
          skype_id,
          street_address,
          course_id,
          language,
          class_days,
          :P164_APP_ID,
          :App_user
     FROM student_app
    WHERE app_id = :P164_APP_ID;



UPDATE student_app
   SET gr_number =
          (SELECT gr_number
             FROM student
            WHERE application_id = :P164_APP_ID),
       updated_by = :App_user,
       app_status = '6-STUDYING',
       updated_ts = CURRENT_TIMESTAMP
 WHERE app_id = :P164_APP_ID;

i am using these pl/sql code but does insert in the table nor update either.

while these code execute fine when i execute from oracle sql developer

please help me out i am using oracle apex 18.2

Littlefoot · Accepted Answer · 2019-02-06 08:01:00Z

1

It seems that you didn't commit, did you?

Also, check whether you put page item(s) you use (P164_APP_ID) into the Items to submit dynamic action property (it is right below the PL/SQL code).

answered Feb 6, 2019 at 8:01

Littlefoot

144k15 gold badges41 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Scott Over a year ago

I would clarify - this was fixed with Items to Submit, not by adding a commit.

AWildmann · Accepted Answer · 2019-02-06 09:57:10Z

0

If your items do contain the values in the session (that you have to submit to the page, as Littlefoot said) and it still doesn't work, try adding your query to a Process in the Processing tab, with the server-side condition set to pressing the button you're pressing. I can't remember if you can set multiple statements to be run in a single process, but it's worth a try.

Something like:

Processing Tab

Code Section

Condition Section

answered Feb 6, 2019 at 9:57

AWildmann

1891 gold badge5 silver badges22 bronze badges

Collectives™ on Stack Overflow

Execute Multiple Queries in Oracle Apex Pl/Sql

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related