Looking at Raj's post I seem to have made at least two errors in my original reply - the size of the varchar2s in the dbms_sql array and the question of whether a varchar2 can be 32K, 32,000 bytes, or 4,000 bytes in pl/sql. Perhaps there are a couple of version-dependent details that need to be checked.
Having said that, when you say the error is | I get an exception and the error is: sqlstr _IS_NULL_ what exactly is reporting the error in that format; it doesn't appear to be a normal exception message, and isn't one of the standard exceptions. I have tried to reproduce your problem, but only have 8.1.7.3. If the sql_str variable is declared large enough to hold the incoming string it works as expected, if the variable is larger than the declared length of the variable the error is the usual PL/SQL 6502 numeric or value error. Jonathan Lewis http://www.jlcomp.demon.co.uk Next Seminar - UK, April 3rd - 5th http://www.jlcomp.demon.co.uk/seminar.html Host to The Co-Operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html Author of: Practical Oracle 8i: Building Efficient Databases -----Original Message----- To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: 26 March 2002 20:30 |Yes, I know it's 32767. |My code: |PROCEDURE ... |sqlstr VARCHAR2(4000); |.... |sqlstr := my_pack.GETSQL( sql_id ... ); -- function | |OPEN my_cursor FOR sqlstr; |... |END; | |If the length of sqlstr is > 4000, | I get an exception and the error is: sqlstr _IS_NULL_ |Why? |I'm using 8.1.7.0.3 on Linux. | |JP | -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).