How to process every line of a table [message #395252] |
Tue, 31 March 2009 22:33  |
mcollins
Messages: 5 Registered: March 2009
|
Junior Member |
|
|
Hello,
I'm trying to work out a way of processing each line of a table...
For example, I have the data listed below in a Table called Table1 (is this the best way to describe the data? - I read the Forum Guide but coudn't see anything to with data):
cDATE | cNAME | cAGE
01-APL-09 | Bob | 33
01-APL-09 | Mary | 27
02-APL-09 | John | 33
I then want to print out Bob, Mary and John.
I was thinking of some code like this:
SET SERVEROUTPUT ON
DECLARE
vName VARCHAR(20);
BEGIN
SELECT cName
INTO vName
FROM Table1;
DBMS_OUTPUT.PUT_LINE(vName);
END;
When I run this code on the data I get the following error:
DECLARE
*
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 6
I understand that it saying it can't add multiple names to vName, so I was thinking it would need to loop through. I'm just not sure how to do that.
Any suggestions?
|
|
|
|
Re: How to process every line of a table [message #395335 is a reply to message #395252] |
Wed, 01 April 2009 03:03  |
pablolee
Messages: 2882 Registered: May 2007 Location: Scotland
|
Senior Member |
|
|
BlackSwan has given you a couple of good pointers. To add to his post, you asked:
Quote: | is this the best way to describe the data?...
|
In order to give a sample of data, by far the best (and most appreciated) way, ios to supply a test case consisting of DDL (create tabl;e statements) and DML (INSERT statements) (Note DDL and DML are not limited to those statements, but they are the relevant ones for this point)
An example, using your data:
CREATE TABLE table1 (cDATE DATE, cNAME VARCHAR2(20), cAGE NUMBER(3,0));
INSERT INTO table1 VALUES(TO_DATE('01-04-2009','DD-MM-YYYY'),'Bob',33);
INSERT INTO table1 VALUES(TO_DATE('01-04-2009','DD-MM-YYYY'),'Mary',27);
INSERT INTO table1 VALUES(TO_DATE('02-04-2009','DD-MM-YYYY'),'John',33);
Cheers
|
|
|