Re: Dynamic SQL

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 <ORACLE-L_at_fatcity.com> 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: jonathan_at_jlcomp.demon.co.uk

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: ListGuru_at_fatcity.com (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).