| problem reg select into ....... [message #39839] |
Fri, 16 August 2002 02:58  |
Victoria
Messages: 152
Registered: July 2002
|
Senior Member |
|
|
Hi,
i have written my procedure like this........
FOR ARTS_rec IN ARTS_cursor LOOP
SELECT 1 FROM URT_CITY CITY
WHERE
.......
IF SQL%FOUND THEN
ARTS_rec.m25 := ARTS_rec.m25||RPAD('Y',31,' ');
ELSE
ARTS_rec.m25 := ARTS_rec.m25||RPAD('N',31,' ');
END IF;
UTL_FILE.PUT_LINE(v_file_handle,
ARTS_rec.a||ARTS_rec.b||ARTS_rec.m25 );
END LOOP;
here am getting an error
LINE/COL ERROR
-------- -----------------------------------------------------------------
318/7 PLS-00428: an INTO clause is expected in this SELECT statement
318/7 PL/SQL: SQL Statement ignored...
so i have used
something like this,
FOR ARTS_rec IN ARTS_cursor LOOP
SELECT count(*) into cnt FROM URT_CITY CITY
WHERE
.......
IF cnt >0 THEN
ARTS_rec.m25 := ARTS_rec.m25||RPAD('Y',31,' ');
ELSE
ARTS_rec.m25 := ARTS_rec.m25||RPAD('N',31,' ');
END IF;
UTL_FILE.PUT_LINE(v_file_handle,
ARTS_rec.a||ARTS_rec.b||ARTS_rec.m25 );
END LOOP;
can any one tell me whats wrong with the first one???i have been told not to use the second one...pls help me
Thanks
Victoria
|
|
|
|
| Re: problem reg select into ....... [message #39844 is a reply to message #39839] |
Fri, 16 August 2002 08:31  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
Any SELECT statement in PL/SQL must be INTO something - a scalar variable, a record variable, a collection, etc. That is why your first version is not compiling.
The reason that you probably have been told not to use the second option is that someone wants to avoid the overhead of counting all the rows that match your criteria when you are basically only interested in whether there is at least one row.
I would recommend a hybrid that is optimized to just check for the presence of matching data and will not count all of the matches.
select count(*)
into v_count
from dual
where exists (select null
from urt_city
where ...);
You will get a count back of either 1 or 0 from this query and, assuming you have the proper indexes on urt_city, it will be the best performing option available.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
