Home » SQL & PL/SQL » SQL & PL/SQL » convert cursor into a select into statement. (Oracle 10g)
convert cursor into a select into statement. [message #379479] Tue, 06 January 2009 13:17 Go to next message
decarlomw
Messages: 12
Registered: January 2009
Junior Member
is there an easy way to convert this cursor into a select into statement.
create or replace procedure p_print_row is
type text_nt is table of VARCHAR2(256);
v_ename_nt text_nt;
cursor c_emp is select first_name from per_all_people_f where employee_number in ('2404', '2405', '2406');

begin


open c_emp;
loop
fetch c_emp bulk collect into v_eName_nt limit 3;
if v_eName_nt.count=3 then
write_log(v_eName_nt(1)||' '||v_eName_nt(2)||' '||v_eName_nt(3));
elsif v_eName_nt.count=2 then
write_log(v_eName_nt(1)||' '||v_eName_nt(2));
elsif v_eName_nt.count=1 then
write_log(v_eName_nt(1));

end if;
exit when c_emp%NOTFOUND;


end loop;

close c_emp;

end;


call p_print_row();

here is the output.
0106:13:14:33.982|3815763|Decarlo Decarlo Decarlo
0106:13:14:33.983|3815763|Stephen Stephen Stephen
0106:13:14:33.984|3815763|Stephen Terrence Terrence
0106:13:14:33.985|3815763|Terrence Terrence
Re: convert cursor into a select into statement. [message #379483 is a reply to message #379479] Tue, 06 January 2009 13:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Still unreadable.
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel
Re: convert cursor into a select into statement. [message #379484 is a reply to message #379483] Tue, 06 January 2009 14:08 Go to previous messageGo to next message
decarlomw
Messages: 12
Registered: January 2009
Junior Member
is there an easy way to convert this cursor into a select into statement.?
/* Formatted on 2009/01/06 14:06 (Formatter Plus v4.8.8) */
CREATE OR REPLACE PROCEDURE p_print_row
IS
   TYPE text_nt IS TABLE OF VARCHAR2 (256);

   v_ename_nt   text_nt;

   CURSOR c_emp
   IS
      SELECT first_name
        FROM per_all_people_f
       WHERE employee_number IN ('2404', '2405', '2406');
BEGIN
   OPEN c_emp;

   LOOP
      FETCH c_emp
      BULK COLLECT INTO v_ename_nt LIMIT 3;

      IF v_ename_nt.COUNT = 3
      THEN
         write_log (   v_ename_nt (1)
                    || ' '
                    || v_ename_nt (2)
                    || ' '
                    || v_ename_nt (3)
                   );
      ELSIF v_ename_nt.COUNT = 2
      THEN
         write_log (v_ename_nt (1) || ' ' || v_ename_nt (2));
      ELSIF v_ename_nt.COUNT = 1
      THEN
         write_log (v_ename_nt (1));
      END IF;

      EXIT WHEN c_emp%NOTFOUND;
   END LOOP;

   CLOSE c_emp;
END;


here is the output.
0106:13:14:33.982|3815763|Decarlo Decarlo Decarlo
0106:13:14:33.983|3815763|Stephen Stephen Stephen
0106:13:14:33.984|3815763|Stephen Terrence Terrence
0106:13:14:33.985|3815763|Terrence Terrence
Re: convert cursor into a select into statement. [message #379498 is a reply to message #379479] Tue, 06 January 2009 17:01 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Define easy.

SELECT INTO is designed to return one row and always one row. Since you are intending to return multiple rows, then NO, there is no way at all to convert a cursor that returns multiple rows to a SELECT INTO that returns only one.

Easy is relative. If there is only one way to do it, then is that way easy or not?

If you don't like using a cursor and BULK FETCH, you have options.

1) use a FOR LOOP cursor instead. With the newer optimizing plsql compilers, these do their own bulk collect anyway, there is less coding and query logic is local to plsql logic.
2) use a collection type and select into the collection. this sounds most like what you are asking, but you will have to do some testing on your own to figure out if the rest of your plsql can use the collection the same way. If figure you can. Here is the basic syntax:

1) create a database type that maps to the items in your query
2) define a variable in your procedure based on the database type
3) do this

   select cast(multiset(select * from (
     <you select statement goes here>
                                      )
                       ) as <your type name goes here>
              )
   into <your collection variable goes here>
   from dual;


This satisfies your requirement of doing a select into to get multiple rows in one go.

Also

Good luck, Kevin
Previous Topic: How can we do it
Next Topic: alter sequence when an exception occurs in PL\SQL
Goto Forum:
  


Current Time: Mon Dec 05 15:01:56 CST 2016

Total time taken to generate the page: 0.13437 seconds