Re: Subquery not valid in a cursor?

This type of ref cursor will still be parsed via the pl/sql parser. What you need to do is change your cursor into a string and pass that to the open statement. Instead of OPEN curs FOR (your cursor here), do something like the following:

m_sql_string VARCHAR2(30000);
BEGIN
m_sql_string := 'SELECT ... (the rest of your select statement here)'; OPEN curs FOR m_sql_string;

This way the pl/sql parser won't try to parse the statement at compile time and it will simply pass the string to the sql engine at run time. A couple of things to note, if there is an error in your sql statment, it will be harder to track down since it will be a runtime error not a compile time error. One thing to look out for is placing single quotes around literals, you'll need to use chr(39) and the || operator to make that work. One last thought, couldn't your subquery be put into an inline view? I know those work in pl/sql version 8.1.6.

-- 
Posted via Mailgate.ORG Server - http://www.Mailgate.ORG