在PL/SQL中动态创建视图并使用CLOB时，ORA-00907 [英] ORA-00907 when dynamically creating a view in PL/SQL and using a CLOB

问题描述

这是从Oracle获得无用的错误消息的情况之一.

我的情况如下:我正在PL/SQL中动态创建视图.我建立一个字符串，并使用EXECUTE IMMEDIATE创建视图.该字符串很长，以至于我使用CLOB来存储它.

当我在TOAD中运行以下代码时，我将无济于事

ORA-00907: missing right parenthesis error. 

在TOAD中手动创建视图(不执行EXECUTE IMMEDIATE)不会有任何问题.我的感觉是字符串的长度是这里的一个因素，因为我已经成功创建了较短字符串的视图(也可以通过使用to_char()而不是dbms_lob.substr()，但是to_char()仅适用于较小的Clob).

字符串的总长度为13775.(很明显，我在下面构建字符串的行中进行了编辑.)这是Linux上的Oracle 10g数据库.

declare
lv_sql CLOB;

begin
lv_sql := ' CREATE OR REPLACE FORCE VIEW my_view.....';

EXECUTE IMMEDIATE dbms_lob.substr(lv_sql, 14765, 1 );    
end;

解决方案

就像Klas所说的那样，您应该能够使用VARCHAR2(32767)进行变量声明，但是如果您发现这还不够的话，您可以使用多个VARCHAR2变量来保存view语句的各个部分，然后将它们发布到EXECUTE IMMEDIATE语句.

此处的AskTom答案表明:

http://asktom .oracle.com/pls/asktom/f?p = 100:11:0 ::::: P11_QUESTION_ID:6161200355268

说:

您已指示立即执行的最大字符串长度 是32K.

我们正在使用立即执行来创建生成的包，我们正在 当前通过它> 35000个字符

通过立即执行v_myvc1 || my_vc2

vc1和vc2是32 k varchar2 vars.当前的总长度

35000

全部于8.1.7

我的问题是立即执行的最大长度是多少 字符串原因，我担心这是32k，我们已经超过了， 我不确定何时要碰壁.

汤姆·凯特(Tom Kyte)回应:

后续行动2003年3月5日-中央时区下午6点:

有趣-从来没有想过这样做.

这似乎可行-会撞到墙吗?不确定，我永远不会 已经超过32k.

看起来可能很大:

ops$tkyte@ORA817DEV> declare   
2          l_str1 long := 'select /* ' || rpad( '*', 20000, '*' ) || ' */ * '; 
3          l_str2 long := 'from /* '   || rpad( '*', 15000, '*' ) || ' */ dual';
4          l_str3 long := '/* '   || rpad( '*', 32000, '*' ) || ' */ ';   
5          l_result dual.dummy%type;   
6  begin   
7          execute immediate l_str1||l_str2||l_str3||l_str3||l_str3||' d' into l_result; 
8          dbms_output.put_line( l_result );   
9  end;  
10  / 

PL/SQL过程成功完成.

尽管这是在Oracle 8i数据库实例上，但是如果在以后的版本中放弃了以菊花链形式连接VARCHAR2变量的功能，我会感到非常惊讶.不幸的是，我目前无法提供10g实例，因此无法对其进行测试.

This is one of those situations where you get an unhelpful error message back from Oracle.

My situation is as follows: I'm dynamically creating a view in PL/SQL. I build a string and use EXECUTE IMMEDIATE to create the view. The string is so long that I use a CLOB to store it.

When I run the code below in TOAD I get the unhelpful

ORA-00907: missing right parenthesis error. 

Manually creating the view in TOAD (without the EXECUTE IMMEDIATE) gives no problems. My feeling is that the length of the string is a factor here as I've successfully created views with shorter strings (and also by using to_char() instead of dbms_lob.substr(), however to_char() only works with smaller clobs).

The total string length is 13775. (Obviously I've edited the line below where I build the string.) This is an Oracle 10g database on Linux.

declare
lv_sql CLOB;

begin
lv_sql := ' CREATE OR REPLACE FORCE VIEW my_view.....';

EXECUTE IMMEDIATE dbms_lob.substr(lv_sql, 14765, 1 );    
end;

解决方案

As Klas has said, you should be able to use VARCHAR2(32767) for your variable declaration but if you find that this is not quite enough, you could just use more than one VARCHAR2 variable to hold the various parts of the view statement and then issue them to the EXECUTE IMMEDIATE statement.

An AskTom answer here demonstrates:

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:6161200355268

Says:

You have indicated that the max string length for execute immediate is 32K.

We are using execute immediate to create generated packages and we are currently passing it > 35000 chars

by execute immediate v_myvc1 || my_vc2

vc1 and vc2 are 32 k varchar2 vars. whose combined length is currently

35000

All on 8.1.7

My Question is what is the maximum length for the execute immediate string cause I was worried it was 32k and we are already over it, and I'm not sure when I'm going to hit the wall.

Tom Kyte responds:

Followup March 5, 2003 - 6pm Central time zone:

interesting -- never would have thought to do it that way.

That appears to work -- will it hit a wall? not sure, I would never have gone over 32k.

looks like it can go pretty large:

ops$tkyte@ORA817DEV> declare   
2          l_str1 long := 'select /* ' || rpad( '*', 20000, '*' ) || ' */ * '; 
3          l_str2 long := 'from /* '   || rpad( '*', 15000, '*' ) || ' */ dual';
4          l_str3 long := '/* '   || rpad( '*', 32000, '*' ) || ' */ ';   
5          l_result dual.dummy%type;   
6  begin   
7          execute immediate l_str1||l_str2||l_str3||l_str3||l_str3||' d' into l_result; 
8          dbms_output.put_line( l_result );   
9  end;  
10  / 

PL/SQL procedure successfully completed.

Though this was on an Oracle 8i database instance I would be very surprised if the ability to daisy-chain the VARCHAR2 variables had been dropped in later revisions. Unfortunately I can't test it as I don't have a 10g instance available to hand at the moment.

这篇关于在PL/SQL中动态创建视图并使用CLOB时，ORA-00907的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！