Help with %rowtype

Hello all!  I'm new to Oracle and trying to teach myself it through various resources; mostly online!  We don't have an oracle DBA on staff, so I'm assuming this role.

I'm trying to figure out the how to use the %rowtype attribute to acquire all the data per row.  I created a test database called erictest with two fields (firstname, lastname).  I have about 10 records in this test database.

Here is my test code...

SET SERVEROUTPUT ON
declare
  type eric_test_table is table of erictest%ROWTYPE
  index by binary_integer;
  erictemptable eric_test_table;
  ericcounter number(2);
  lowernumber number := 1;
begin
  select count(*) into ericcounter from erictest;
  FOR i IN lowernumber..ericcounter LOOP
    select * into erictemptable from erictest;
  END LOOP;
  FOR i IN 1..ericcounter LOOP
    DBMS_OUTPUT.PUT_LINE (erictemptable(i));
  END LOOP;
end;
/

This thing doesn't work.

Now, if I change the above code to just return all FirstNames by altering the "is table of" and "select" statements accordingly, it works fine.  I just can't return all the data (firstname and lastname) for each row.

Any ideas???
NEARNGAsked:
Who is Participating?
 
schwertnerConnect With a Mentor Commented:
May be you should use index of the row:


for eric in (select *  from erictest) loop

erictemptable(lowernumber) := eric ;

lowernumber := lowernumber + 1;

end loop;
0
 
Mark GeerlingsDatabase AdministratorCommented:
Yes, that is correct.  You can use DBMS_OUTPUT.PUT_LINE to display individual values or variables and you can concatenate them together and still have DBMS_OUTPUT.PUT_LINE display them, but there are big limitations on what you can do with a rowtype variable - one of them is that you cannot display it with DBMS_OUTPUT.PUT_LINE.
0
 
NEARNGAuthor Commented:
I appreciate your responses and I'm headed in the right direction I believe!

How in the world do I resolve this?

exact fetch returns more than requested number of rows
0
 
plamen73Commented:
SET SERVEROUTPUT ON
declare
  cursor c_Test_Table is  select * into erictemptable from erictest;
  type eric_test_table is table of c_Test_Table%ROWTYPE index by binary_integer;
  erictemptable eric_test_table;

  --ericcounter number(2); -- you do not need this
  --lowernumber number := 1; -- you do not need this
begin
  --- select count(*) into ericcounter from erictest; -- do not need
  open c_Test_Table;
  -- fetch it in one pass
  fetch c_Test_Table bulk collect into erictemptable;
  close c_Test_Table; -- do not forget to close it in the exception handler
 
  FOR i IN 1..erictemptable.count LOOP
     DBMS_OUTPUT.PUT_LINE (erictemptable(i).firstname || ', ' || erictemptable(i).lastname);  -- be careful what you are referencing
  END LOOP;
exception
  when others then
     if(c_Test_Table%isopen)then
       close c_Test_Table;
     end if;
     raise;
end;
/
0
 
Mark GeerlingsDatabase AdministratorCommented:
I just noticed another major flaw in the procedure you posted: you have two separate loops, rather than just the one loop that I believe you want, if you do want a value displayed for each record retrieved.  I don't normally use "for ..." loops or rowtype variables, but I think your procedure should be more like this:

  FOR i IN lowernumber..ericcounter LOOP
    select * into erictemptable from erictest;
    DBMS_OUTPUT.PUT_LINE (erictemptable(i));
  END LOOP;
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.