Recently I got a chance to automate some stuff in pl/sql . I am able to complete 99% of the program but stuck with ora-01422
01422, 00000, "exact fetch returns more than requested number of rows"
// *Cause: The number specified in exact fetch is less than the rows returned.
// *Action: Rewrite the query or change number of rows requested
I googled but I didnt find anything useful , most of the advise to use "rownum=1" or "Raise exception error – too many rows" but I want all the rows , I tried with cursor but its not effective.
Finally , I got a link to store more than one row that is BULK COLLECT . BULK COLLECT is very fast to store the result in output.
Syntax to use bulk collect
type inst_t is table of varchar(200); — A Table Type
<variable> inst_t;
SELECT INSTANCE_ID bulk collect into <variable > FROM <Table name>