ORA-00907 when dynamically creating a view in PL/SQL and using a CLOB

Question

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;

Answer 1

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.