Today I wrote a simple procedure for a friend:

create or replace procedure P_QN_SERVICE is
  l_table_name varchar2(100);
  no_table EXCEPTION;
  PRAGMA EXCEPTION_INIT(no_table, -942);
begin
  l_table_name := 'v_dat_sdr_' || to_char(sysdate - 1, 'yyyymmdd');
  execute immediate 'insert into t_qn_service_bak(stime,service_id,cnt) ' ||
                    'select :date,service_id,count(*) from USBOSS2.' ||
                    l_table_name ||
                    '@usboss where service_id in (''0575SXZQ'',''0575MiYu'',''0575News'',''0575SMS'',''0575Test'',''0575QYDT'',''05759004050101'') group by service_id'
    using trunc(sysdate - 1);
  commit;
exception
  when no_table then
    dbms_output.putline('The view USBOSS2.' || l_table_name ||
                        'does not exists!');
end;

 Here I use a bind variable ":date". But when I execute this procedure, it raise an error "ORA-01745".

Here is the introduction about this error:

ORA-01745: invalid host/bind variable name
Cause: A colon in a bind variable or INTO specification was followed by an inappropriate name, perhaps a reserved word.
Action: Change the variable name and retry the operation.

After I changed the bind variable ":date" to ":l_date", it worked well.

Blog this case here to remind myself do not use reserved word as a bind variable.