?
Solved

Help with %rowtype

Posted on 2005-04-26
5
Medium Priority
?
1,387 Views
Last Modified: 2008-03-04
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???
0
Comment
Question by:NEARNG
5 Comments
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 13866736
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
 
LVL 48

Accepted Solution

by:
schwertner earned 600 total points
ID: 13866873
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
 

Author Comment

by:NEARNG
ID: 13867350
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
 
LVL 4

Expert Comment

by:plamen73
ID: 13867390
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
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 13868276
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

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question