SQL & PL/SQL

Oracle 11gR2

This is a small code block. I want to use a variable in the values clause of an Insert/Select statement using execute immediate.

g_sysdate date := sysdate

I cant' use sysdate directly because this is a long running process. We assign sysdate to g_sysdate at the beginning of the procedure and we store it in multiple tables. So we need it to have the same value everywhere.

I'm getting ORA-00904: "G_SYSDATE": invalid identifier

create table usa.work_time
( id number ,
  work_status_id number ,
  work_task_id number ,
  txt_msg varchar2(255)
)
/

create table usa.work_time_arc
( id number ,
  work_status_id number ,
  work_task_id number ,
  txt_msg varchar2(255),
  archived_dt date
)
/

insert into usa.work_time values(1,2,3,'hello');

commit ;

DECLARE
   v_query varchar2(2000);
   g_sysdate date := sysdate ;
   v_tbl_arc varchar2(61) := 'usa.work_time_arc';
   v_tbl_src varchar2(61) := 'usa.work_time';
   v_tab_cols varchar2(2000) := 'id,work_status_id,work_task_id,txt_msg';
   v_id number := 9521;
BEGIN
   v_query := 'insert /*+ append */ into '||v_tbl_arc || ' (' || v_tab_cols || ',archived_dt)' || 
                              'select ' || v_tab_cols || ',g_sysdate from ' || v_tbl_src || 
                           ' where id = :id';
   dbms_output.put_line(v_query);
   
   execute immediate v_query using v_id ;
END ;
/

ERROR at line 1:
ORA-00904: "G_SYSDATE": invalid identifier
ORA-06512: at line 14