CODEWITHSUNDEEP
plsqlsubqueryinsert-into
user3138025
user3138025

Reputation: 825

How to add local variable to PL/SQL INSERT INTO using subquery

I have an Oracle PL/SQL question. I have an INSERT INTO that works fine:

DECLARE
   l_filename   VARCHAR2 (1024);      
BEGIN
   l_filename := 'some value';
     INSERT INTO aff_attach
        (file_id, file_name, mime_type, attachment, file_size)
        SELECT ID, l_filename, mime_type, blob_content,DOC_SIZE
          FROM apex_application_files
          WHERE filename = l_filename AND created_by = :app_user;
END:

Now in the same INSERT INTO statement I'd like to include another field into aff_attach (AFF_EVENT_FKEY). That value will not come from the SELECT statement. If I define l_aff_event_fkey, and prior to the Insert statement I assign l_aff_event_fkey a value of 9, How can I include that in the Insert Into statement? Thanks for looking at this.

Upvotes: 0

Views: 3901

Answers (1)

Glenn
Glenn

Reputation: 9170

It can be added to the SELECT statement (as a constant in the SELECT):

DECLARE

   l_filename   VARCHAR2 (1024);
   v_aff_event_fk NUMBER;

BEGIN
   l_filename := 'some value';
   v_aff_event_fk := 9;

     INSERT INTO aff_attach
        (file_id, file_name, mime_type, attachment, file_size, AFF_EVENT_FKEY)
        SELECT ID, l_filename, mime_type, blob_content,DOC_SIZE, v_aff_event_fk
          FROM apex_application_files
          WHERE filename = l_filename AND created_by = :app_user;
END:

Upvotes: 3

Related Questions