CODEWITHSUNDEEP
oracle-databaseoracle11goracle-sqldeveloperoracle-apexoracle-apex-5.1
AbdulRaheem
AbdulRaheem

Reputation: 115

Execute Multiple Queries in Oracle Apex Pl/Sql

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

Upvotes: 0

Views: 1972

Answers (2)

AWildmann
AWildmann

Reputation: 189

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

enter image description here

Code Section

enter image description here

Condition Section

enter image description here

Upvotes: 0

Littlefoot
Littlefoot

Reputation: 142720

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).

Upvotes: 1

Related Questions