2

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;
TrojanName
  • 4,853
  • 5
  • 29
  • 41

1 Answers1

4

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.

Ollie
  • 17,058
  • 7
  • 48
  • 59
  • many thanks for the answer. However I already tried that but I get an "ORA-06502: PL/SQL: numeric or value error" error, which was why I switched to CLOB. – TrojanName Oct 03 '11 at 14:27
  • Did you use a SUBSTR when you were using the VARCHAR2 variable? – Ollie Oct 03 '11 at 14:29
  • No, just execute immediate lv_sql; I wonder does doing the execute immediate do some sort of context switching so that the PL/SQL limit of 32k no longer applies? – TrojanName Oct 03 '11 at 14:34
  • The AskTom Q&A would suggest not (also EXECUTE IMMEDIATE can be used to execute dynamic PL/SQL too). Could you post the exact SQL you were trying to run to get the error? – Ollie Oct 03 '11 at 14:38
  • BTW, I found an article where someone was having problems dynamically creating a view using EXECUTE IMMEDIATE and yet when they ran the create view code through SQL*Plus it was OK. They eventually solved the issue by removing all the unnecessary parenthesis from the view code and it worked fine. Is your view code as simple as it can be? – Ollie Oct 03 '11 at 14:47
  • Super, thanks for the info. I think I'm making some progress. My test code actually works now in TOAD when I use varchar2(32767), and it actually creates the view. Which is great. However, when I try the same thing in my PL/SQL package, it is still complaining (with an "ORA-06502: PL/SQL: numeric or value error" error) – TrojanName Oct 03 '11 at 14:56
  • Ok, now I feel like I'm going round in circles. That ORA-06502 is caused when I assign the long string into the lv_sql variable (even though lv_sql is defined as 32k in length). I know that string is only 14k long. This is the reason I assumed that VARCHAR2's couldn't be more than 4k in my version of Oracle, and why I switched to CLOBs. – TrojanName Oct 03 '11 at 15:06
  • 1
    Try daisy-chaining more than one VARCHAR2(32767) variables together and see if that works, you might be using a charset that uses more than one byte per character. – Ollie Oct 03 '11 at 15:17
  • Success! I declared lv_sql as VARCHAR2(32767 CHAR) and that did the job. Thanks for the help! – TrojanName Oct 03 '11 at 15:57
  • No problemo, glad it got fixed. – Ollie Oct 03 '11 at 18:07
  • If you use a wide character representation (like UTF8), there is a difference between VARCHAR2(32767) and VARCHAR2(32767 CHAR). The former will hold 32767 bytes while the latter will hold 32767 chars. – Klas Lindbäck Oct 04 '11 at 14:52
  • Klas, that's what the OP did to get around his error. I suspect that he is using UTF8 or a similar charset. – Ollie Oct 04 '11 at 15:32