oracle procedure执行问题

Question

本人写了一段sql语句，其中包含大量的DDl操作，直接在pl/sql可以正常执行，大约半个小时吧。然后换成procedure编译后执行就有问题，不知道是什么原因。ps：procedure使用了动态sql

Answer 1

脚本比较长
create or replace procedure vwjz2011 is
begin
  ddl('DROP TABLE chenyr_group_data');
  ddl('CREATE TABLE chenyr_group_data TABLESPACE cnt_tablespace01 AS
SELECT *
FROM xzzw05.crm_group_member_xz@to_xzzw05 
WHERE prodid=''1110000026''
AND memtype=1
AND (enddate IS NULL OR to_char(enddate,''yyyymm
    '')>=to_char(SYSDATE,''yyyymm'') AND startdate<enddate)');
  ddl('CREATE  INDEX chenyr_group_data ON chenyr_group_data(subsid) TABLESPACE cnt_indexspace01');
  ddl('CREATE  INDEX chenyr_group_data1 ON chenyr_group_data(custid) TABLESPACE cnt_indexspace01');
  ddl('DROP TABLE chenyr_temp');
  ddl('CREATE TABLE chenyr_temp TABLESPACE cnt_tablespace01 AS
SELECT subsid,brand,servnumber,status,et_name,prematch_flag,startdate,actdate,statusdate
FROM xzzw05.CRM_PERSON_SUBSCRIBER_XZ@TO_XZZW05 ');
  ddl('CREATE INDEX chenyr_temp ON chenyr_temp(subsid) TABLESPACE cnt_indexspace01');
  ddl('ALTER TABLE chenyr_group_data ADD state VARCHAR2(32)');
  ddl('ALTER TABLE chenyr_group_data ADD statedate DATE');
  ddl('UPDATE chenyr_group_data a
SET (state,statedate)=(SELECT status,statusdate FROM chenyr_temp b WHERE a.subsid=b.subsid)');
  ddl('ALTER TABLE chenyr_group_data ADD statedesc VARCHAR2(32)');
  ddl('UPDATE chenyr_group_data a
SET statedesc=(SELECT b.dictname FROM xzzw05.sa_db_dictitem@to_xzzw05 b WHERE groupid=''US
      '' AND a.state=b.dictid)');
  ddl('DROP TABLE chenyr_customer');
  ddl('CREATE TABLE chenyr_customer AS
SELECT * FROM xzzw05.customer@to_xzzw05 WHERE custtype=2');
  ddl('CREATE INDEX chenyr_customer on chenyr_customer(custid) TABLESPACE cnt_indexspace01');
  ddl('DROP TABLE chenyr_group_customer');
  ddl('CREATE TABLE chenyr_group_customer AS
SELECT * FROM xzzw05.crm_group_customer_xz@to_xzzw05');
  ddl('CREATE INDEX chenyr_group_customer ON chenyr_group_customer(custid) TABLESPACE cnt_indexspace03');
  ddl('UPDATE chenyr_group_customer a
SET a.countyid=(SELECT b.countyid FROM chenyr_customer b WHERE a.custid=b.custid)');
  ddl('ALTER TABLE chenyr_group_customer ADD khyhs NUMBER(10)');
  ddl('UPDATE chenyr_group_customer a
SET a.khyhs=(SELECT COUNT(DISTINCT subsid) FROM chenyr_group_data b
WHERE a.custid=b.custid
AND state IN (''US10'',''US30'',''US31'')
AND to_char(startdate,''yyyymm'')<=to_char(SYSDATE,''yyyymm'')
AND (to_char(startdate,''yyyy'')<''2011'' OR to_char(startdate,''yyyy'')=''2011'' AND shortnumber IS NOT NULL)
)');
  ddl('ALTER TABLE chenyr_group_customer ADD fzyhs NUMBER(10)');
  ddl('UPDATE chenyr_group_customer a
SET a.fzyhs=(SELECT COUNT(DISTINCT subsid) FROM chenyr_group_data b
WHERE a.custid=b.custid AND state IN (''US10'',''US30'',''US31'')
AND enddate IS NULL)');
  ddl('ALTER TABLE chenyr_group_customer ADD xzyhs NUMBER(10)');
  ddl('UPDATE chenyr_group_customer a
SET a.xzyhs=(SELECT COUNT(DISTINCT subsid) FROM chenyr_group_data b
WHERE a.custid=b.custid AND state IN (''US10'',''US30'',''US31'')
AND to_char(startdate,''yyyymmdd'')>''20110101''
AND shortnumber IS NOT NULL)');
  ddl('ALTER TABLE chenyr_group_customer ADD tdyhs NUMBER(10)');
  ddl('UPDATE chenyr_group_customer a
SET a.tdyhs=(SELECT COUNT(DISTINCT subsid) FROM chenyr_group_data b
WHERE a.custid=b.custid AND state IN (''US10'',''US30'',''US31'')
AND to_char(enddate,''yyyymm'')=to_char(enddate,''yyyymm''))');
  ddl('DROP TABLE chenyr_cl_zb');
  ddl('CREATE TABLE chenyr_cl_zb TABLESPACE otherspace AS
SELECT *
FROM xzzw05.crm_group_member_xz@to_xzzw05 a
WHERE prodid=''1100006018''
AND enddate IS NULL');
  ddl('CREATE INDEX chenyr_cl_zb ON chenyr_cl_zb(custid) TABLESPACE cnt_indexspace01');
  ddl('DROP TABLE chenyr_cl_td');
  ddl('CREATE TABLE chenyr_cl_td AS
SELECT *
FROM xzzw05.crm_group_member_xz@to_xzzw05 a
WHERE prodid=''1100006018''
AND to_char(a.enddate,''yyyymm'')=''201102''
AND to_char(a.startdate,''yyyymm'')<''201102''');
  ddl('CREATE INDEX chenyr_cl_td ON chenyr_cl_td(custid) TABLESPACE cnt_indexspace01');
  ddl('ALTER TABLE chenyr_group_customer ADD cl_cnt NUMBER(10)');
  ddl('UPDATE chenyr_group_customer a
SET a.cl_cnt=(SELECT COUNT(DISTINCT subsid) FROM chenyr_cl_zb b WHERE a.custid=b.custid)');
  ddl('ALTER TABLE chenyr_group_customer ADD clxz_cnt NUMBER(10)');
  ddl('UPDATE chenyr_group_customer a
SET a.clxz_cnt=(SELECT COUNT(DISTINCT subsid) FROM chenyr_cl_zb b WHERE a.custid=b.custid AND to_char(b.startdate,''yyyymm'')=TO_char(SYSDATE,''yyyymm''))');
  ddl('ALTER TABLE chenyr_group_customer ADD cltd_cnt NUMBER(10)');
  ddl('UPDATE chenyr_group_customer a
SET a.cltd_cnt=(SELECT COUNT(DISTINCT subsid) FROM chenyr_cl_td b WHERE a.custid=b.custid)');
  
  ddl('ALTER TABLE chenyr_group_customer ADD hxzd_flag NUMBER(2)');
  ddl('UPDATE chenyr_group_customer a
SET a.hxzd_flag=1
WHERE address IS NULL 
OR linkphone IS NULL 
OR (departtype IS NULL or departtype=''other'')
OR (vocaionkind1 IS NULL OR vocaionkind1=8599)
OR ((vocaionkind2 IS NULL AND vocaionkind1<>85100) OR vocaionkind2=859999)
OR nvl(EMPLYEENUM,0)=0
OR nvl(annual_sales,0)=0');
  
  ddl('ALTER TABLE chenyr_group_customer ADD departtype_desc VARCHAR2(80)');
  ddl('UPDATE chenyr_group_customer a
SET a.departtype_desc=(SELECT b.dictname FROM xzzw05.sa_db_dictitem@to_xzzw05 b WHERE a.departtype=b.dictid AND groupid=''GroupcustProperty'')');
  
  ddl('ALTER TABLE chenyr_group_customer ADD departtype_pz VARCHAR2(80)');
  ddl('UPDATE chenyr_group_customer a
SET a.departtype_pz=(SELECT TRIM(b.departtype_desc) FROM chenyr_departtype b WHERE a.custid=b.custid)');  -end vwjz2011;
/

Answer 2

ddl过程就是简单写了执行ddl的sql

Answer 3

整个procedure执行下来跟手动一条一条执行的不一样，其中procedure ddl我还将错误信息专门存储在一个表中，可是这个表也没有什么错误信息。ddl过程是使用dbms_sql.parse执行,exception 存储在表中