Patrick_in_MD
asked on
Limit portion of bulk collect not compiling
In PL/SQL this compiles
select unique t1.id bulk collect into v2
from table t1, table t2
where t2.id(+) = t1.id and t2.id is null;
but adding a limit statement (as shown below) produces an error (also shown below)
select unique t1.id bulk collect into v2
from table t1, table t2
where t2.id(+) = t1.id and t2.id is null limit 1000;
error
PL//SQL ORA-00933: SQL command not properly ended.
I have moved the "limit 1000" all over the statement trying to get it to compile. No luck. Any ideas?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>>limit is MySQL syntax not supported by oracle
You sure about that? You just need to use FETCH and a cursor.
http://download.oracle.com/docs/cd/B12037_01/appdev.101/b10807/12_tune.htm#i48876
Limiting the Rows for a Bulk FETCH Operation with the LIMIT Clause
The optional LIMIT clause, allowed only in bulk FETCH statements, limits the number of rows fetched from the database.
You sure about that? You just need to use FETCH and a cursor.
http://download.oracle.com/docs/cd/B12037_01/appdev.101/b10807/12_tune.htm#i48876
Limiting the Rows for a Bulk FETCH Operation with the LIMIT Clause
The optional LIMIT clause, allowed only in bulk FETCH statements, limits the number of rows fetched from the database.
bulk collect is oracle
limit is MySQL syntax not supported by oracle