PL / SQL过程未返回预期结果

安吉丽娜（Angelina）

我有一个正在Shell脚本中运行的PL / SQL过程。

我拉出了逻辑，但它没有运行内部的BEGIN-END；部分：

DECLARE
model_exists NUMBER(1);
BEGIN 
  SELECT COUNT(*) INTO model_exists from mdsys.rdf_model$ WHERE model_name='XCLOVER';
  BEGIN 
      EXECUTE IMMEDIATE 'CREATE table SEMANTIC.RDF_STAGE_TABLE_XCLOVER(RDF$STC_sub  VARCHAR(4000) not null,RDF$STC_pred VARCHAR(4000) not null,RDF$STC_obj VARCHAR(4000) not null)'; COMMIT;
      EXECUTE IMMEDIATE 'CREATE TABLE SEMANTIC.XCLOVER_TPL (TRIPLE SDO_RDF_TRIPLE_S)'; COMMIT;
      EXCEPTION
      WHEN OTHERS THEN
      IF SQLCODE != -955 THEN
        EXECUTE IMMEDIATE 'TRUNCATE TABLE SEMANTIC.XCLOVER_TPL';
        EXECUTE IMMEDIATE 'TRUNCATE TABLE SEMANTIC.RDF_STAGE_TABLE_XCLOVER';
      END IF;
  END; 
EXECUTE IMMEDIATE 'GRANT ALL ON SEMANTIC.XCLOVER_TPL to MDSYS'; 
IF model_exists = 0 THEN
    SEM_APIS.CREATE_SEM_MODEL('XCLOVER', 'XCLOVER_TPL','TRIPLE'); 
END IF;
COMMIT; 
END;
/

它在shell脚本中的编写方式如下：

#!/bin/bash
. /etc/profile.d/oracle.sh

MODEL=$1

echo "DECLARE" > createxmodel.tmp
echo "model_exists NUMBER(1);" >> createxmodel.tmp
echo "BEGIN" >> createxmodel.tmp
echo "SELECT COUNT(*) INTO model_exists from mdsys.rdf_model\$ WHERE model_name='$MODEL';" >> createxmodel.tmp
echo "BEGIN" >> createxmodel.tmp
echo "EXECUTE IMMEDIATE 'CREATE table SEMANTIC.RDF_STAGE_TABLE_$MODEL(RDF\$STC_sub  VARCHAR(4000) not null,RDF\$STC_pred VARCHAR(4000) not null,RDF\$STC_obj VARCHAR(4000) not null)';" >> createxmodel.tmp
echo "EXECUTE IMMEDIATE 'CREATE TABLE SEMANTIC.$MODEL""_TPL (TRIPLE SDO_RDF_TRIPLE_S)'; " >> createxmodel.tmp
echo "EXCEPTION" >> createxmodel.tmp
echo "WHEN OTHERS THEN" >> createxmodel.tmp
echo "IF SQLCODE != -955 THEN" >> createxmodel.tmp
echo "EXECUTE IMMEDIATE 'TRUNCATE TABLE SEMANTIC.$MODEL""_TPL';" >> createxmodel.tmp
echo "EXECUTE IMMEDIATE 'TRUNCATE TABLE SEMANTIC.RDF_STAGE_TABLE_$MODEL';" >> createxmodel.tmp
echo "END IF;" >> createxmodel.tmp
echo "END;" >> createxmodel.tmp
echo "EXECUTE IMMEDIATE 'GRANT ALL ON SEMANTIC.$MODEL""_TPL to MDSYS';" >> createxmodel.tmp 
echo "IF model_exists = 0 THEN" >> createxmodel.tmp
echo "SEM_APIS.CREATE_SEM_MODEL('$MODEL', '$MODEL""_TPL','TRIPLE');" >> createxmodel.tmp
echo "END IF;" >> createxmodel.tmp
echo "COMMIT;" >> createxmodel.tmp
echo "END;" >> createxmodel.tmp
echo "/" >> createxmodel.tmp
echo "exit;" >> createxmodel.tmp

sqlplus -S user/password < createxmodel.tmp

现在抛出的错误是：

DECLARE
*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at line 15

告诉我内部的BEGIN永远不会被执行...

这用于工作:(

有人看到我在做什么错吗？

鲍勃·贾维斯（Bob Jarvis）-恢复莫妮卡（Monica）

我认为最好将每个表的创建内容放在其自己的BEGIN ... END块中，如下所示：

echo "DECLARE" > createxmodel.tmp
echo "  model_exists NUMBER(1);" >> createxmodel.tmp
echo "  excp_table_exists EXCEPTION;" >> createxmodel.tmp
echo "  PRAGMA EXCEPTION_INIT(excp_table_exists, -955);" >> createxmodel.tmp
echo "BEGIN" >> createxmodel.tmp
echo "  SELECT COUNT(*) INTO model_exists from mdsys.rdf_model\$ WHERE model_name='$MODEL';" >> createxmodel.tmp

echo "  BEGIN" >> createxmodel.tmp
echo "    EXECUTE IMMEDIATE 'CREATE table SEMANTIC.RDF_STAGE_TABLE_$MODEL(RDF\$STC_sub  VARCHAR(4000) not null,RDF\$STC_pred VARCHAR(4000) not null,RDF\$STC_obj VARCHAR(4000) not null)';" >> createxmodel.tmp
echo "  EXCEPTION" >> createxmodel.tmp
echo "    WHEN excp_table_exists THEN" >> createxmodel.tmp
echo "      EXECUTE IMMEDIATE 'TRUNCATE TABLE SEMANTIC.RDF_STAGE_TABLE_$MODEL';" >> createxmodel.tmp
echo "  END;" >> createxmodel.tmp
echo "  BEGIN" >> createxmodel.tmp
echo "    EXECUTE IMMEDIATE 'CREATE TABLE SEMANTIC.$MODEL""_TPL (TRIPLE SDO_RDF_TRIPLE_S)'; " >> createxmodel.tmp
echo "  EXCEPTION" >> createxmodel.tmp
echo "    WHEN excp_table_exists THEN" >> createxmodel.tmp
echo "      EXECUTE IMMEDIATE 'TRUNCATE TABLE SEMANTIC.$MODEL""_TPL';" >> createxmodel.tmp
echo "  END;" >> createxmodel.tmp
echo "  EXECUTE IMMEDIATE 'GRANT ALL ON SEMANTIC.$MODEL""_TPL to MDSYS';" >> createxmodel.tmp 
echo "  IF model_exists = 0 THEN" >> createxmodel.tmp
echo "    SEM_APIS.CREATE_SEM_MODEL('$MODEL', '$MODEL""_TPL','TRIPLE');" >> createxmodel.tmp
echo "  END IF;" >> createxmodel.tmp
echo "  COMMIT;" >> createxmodel.tmp
echo "END;" >> createxmodel.tmp
echo "/" >> createxmodel.tmp
echo "exit;" >> createxmodel.tmp

希望这会有所帮助。

分享并享受。

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。