PLS-00103使用动态SQL创建外部表 [英] PLS-00103 creating an external table with dynamic SQL
问题描述
我正在尝试动态创建外部表，但收到错误消息PLS-00103: Encountered the symbol "EXTERNAL".我还在外部表定义中使用了ora_hash.请让我知道这是获取ora_hash值的正确方法.
I'm trying to dynamically create an external table but I'm getting error message PLS-00103: Encountered the symbol "EXTERNAL". I'm also using ora_hash in the external table definition; please let me know it is the right way to get the ora_hash value.
create or replace procedure CHECKTABLEEXIST1 (p_tab_name in varchar2,DATAFILE in varchar2) --user_tables.table_name%type)
is
tab_name varchar2(100) := p_tab_name;
n Number(3);
ext_table varchar(100) := tab_name|| ' as select * from xyz WHERE 1=0';
begin
select count(*) into n from tab where TName=upper(tab_name);
--dbms_output.put_line(n);
if n=0 then
execute immediate 'create table ' || ext_table ;
else
execute immediate 'drop table ' || tab_name;
execute immediate 'create table ' || ext_table;
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY DE_DUBFILE
ACCESS PARAMETER
(
RECORDS DELIMITED BY NEWLINE
CHARACTERSET US7ASCII
BADFILE 'UPLOAD':'p_tab_name.bad'
DISCARDFILE 'UPLOAD':'p_tab_name.dis'
LOGFILE 'UPLOAD':'p_tab_name.log'
FILEDS TERMINATED BY ','
optionally enclosed by '"'
TRAILING NULLCOLS
MISSING FIELD VALUES ARE NULL
(
t1 ,t2,t3,t4,t5 date "YYYYMMDD" ,t6,t7,
t8 ,t9 ,
DETL_CLMNS_HASH "ORA_HASH( :t4||:t7 )",
KEY_CLMNS_HASH "ORA_HASH(:t1||:t2||:t5)", t10,t11)
)
LOCATION (DATAFILE)
);
end if;
end;
我收到错误消息:
LINE/COL ERROR
-------- -----------------------------------------------------------------
16/14 PLS-00103: Encountered the symbol "EXTERNAL" when expecting one o
f the following:
:= . ( @ % ;
推荐答案
从ORGANIZATION开始的所有内容都被视为PL/SQL代码，而不是动态SQL语句的一部分.您要将表名附加到create table，但不将其余部分作为该语句字符串的一部分附加.您需要执行以下操作:
Everything from ORGANIZATION onwards is being seen as PL/SQL code, not part of your dynamic SQL statement. You're appending the table name to the create table but then not appending the rest as part of that statement string. You need to do something like:
execute immediate 'create table ' || p_tab_name || '
( /* put column names and types here */ )
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY DE_DUBFILE
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE
CHARACTERSET US7ASCII
BADFILE UPLOAD:''' || p_tab_name || '.bad''
DISCARDFILE UPLOAD:''' || p_tab_name || '.dis''
LOGFILE UPLOAD:''' || p_tab_name || '.log''
FIELDS TERMINATED BY '',''
optionally enclosed by ''"''
MISSING FIELD VALUES ARE NULL
(
t1 ,t2,t3,t4,t5 date mask "YYYYMMDD" ,t6,t7,
t8 ,t9, t10,t11
)
LOCATION (''' || DATAFILE || ''')
)';
在第一行中，终止分号已替换为新字符串文字的串联.变量p_tab_name和DATAFILE的引用也必须从该文字中分解出来，这需要更多的单引号和串联.而实际上是该语句一部分的单引号需要通过加倍来进行转义.也缺少其他各种引号.显示的内容现在应该运行.
In the first line the terminating semicolon has been replaced with concatenation of a new string literal. The references to variables p_tab_name and DATAFILE have to be broken out from that literal too, requiring more single quotes and concatenation; and the single quotes that are actually part of the statement need to be escaped by doubling them up. There were various other quotes missing as well. What's shown should now run.
我还更改了仅用于p_tab_name的表名，但是您需要显式指定列名和数据类型.将as select * ...用于外部表没有任何意义.这不是合法的语法，在当前语句之前，在organization之前或之后.我想您可以从all_tab_columns中提取该信息并也动态地构建该部件，但是如果您将其基于固定的表，则无论如何都应该知道这些信息.
I've also change the table name being used to just p_tab_name, but you need to specify the column names and data types explicitly. It doesn't make sense to use as select * ... for an external table. That isn't legal syntax, either before organization or after the rest if the current statement. I suppose you could extract that information from all_tab_columns and build that part dynamically too, but if you're basing it on a fixed table you ought to know those anyway.
您放置/创建的逻辑也已关闭-我想您只是想要:
Your logic for dropping/creating is off too - I think you just want:
if n>0 then
execute immediate 'drop table ' || p_tab_name;
end if;
execute immediate 'create table ' || p_tab_name || '
...
...因此您不必在两个分支中都重复执行create语句.
... so you don't have to repeat the create statement in both branches.
我还纠正了其他一些错误； PARAMETERS而不是PARAMETER; FIELDS而不是FILEDS;删除了TRAILING NULLCOLS.尝试将命令作为静态SQL执行，然后再将其转换为动态.可能还有其他问题.
I've also corrected a couple of other mistakes; PARAMETERS rather then PARAMETER; FIELDS rather then FILEDS; removed TRAILING NULLCOLS. Try to execite the command as static SQL before converting it to dynamic. There may still be other issues.
我已经删除了最后两个计算列:
And I've removed the last two calculated columns:
DETL_CLMNS_HASH "ORA_HASH( :t4||:t7 )",
KEY_CLMNS_HASH "ORA_HASH(:t1||:t2||:t5)")
ORACLE_LOADER驱动程序没有不允许这样的操纵； SQL * Loader可以，但是它们并不完全相同.您也不能在外部表上定义虚拟列.如果您将此表用作将数据加载到另一个(实际)表中的暂存表，则可以在传输过程中计算这些哈希值.否则，您可以在包含已计算列的外部表上创建一个视图.
The ORACLE_LOADER driver doesn't allow manipulations like that; SQL*Loader does but they are not exactly the same. You also can't define virtual columns on an external table. If you're using this as a staging table to load data into another (real) table then you can calculate those hashes during the transfer; otherwise you can create a view over this external table which includes the calculated columns.
这篇关于PLS-00103使用动态SQL创建外部表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
