博客

plsql的execute immediate可以执行动态语句，今天在写一个简单的脚本的时候总是报错，说是column not allowed here，但是报错的位置在declare哪里，看declare这个关键字也没写错啊，想应该是在begin end的block里，最后排查出是这一句

execute immediate ‘insert into session_count values(m_session_time,m_session_count)’;，他认为这事先定义的变量m_session_time为column，因为我execute immediate的方法用错了，很多年不写plsql了，都忘了。

正确的写法有两种

一种是租sql字符串语句，不用变量


m_insert_statement := ‘insert into t_’||m_random_table||’(ncol1,col1) values(’||m_random_ncol1||’,’’’||m_random_col1||’’’)’;


像这样就没问题

还有一种就是execute immediate 的字句 use子句，像下面这样

execute immediate ‘insert into session_count values(:1,:2)’ using m_session_time,m_session_count;

SQL> @/u01/wgz/test_04031_20220107/record_session_count.sql;
1
declare
*
ERROR at line 1:
ORA-00984: column not allowed here
ORA-06512: at line 11


[oracle@slcm05adm01 test_04031_20220107]$ cat record_session_count.sql
set serveroutput on;
--create table session_count(session_time date,session_count number);

declare
  m_session_count number := 0;
  m_session_time date;
  m_session_time_char varchar2(30);
begin
  while 1<2 loop
  --for i in 1 .. 5 loop
    execute immediate 'select count(*) from v$session' into m_session_count;
    execute immediate 'select sysdate from dual ' into m_session_time;
    --execute immediate 'insert into session_count values(m_session_time,m_session_count)'; 注释掉的事出问题的语句，下面是正确的用法
    execute immediate 'insert into session_count values(:1,:2)' using m_session_time,m_session_count;
    commit;
    dbms_lock.sleep( 1);
  end loop;
end;
/