Home » SQL & PL/SQL » SQL & PL/SQL » How to process every line of a table (iSQL*Plus, v10, Windows)
How to process every line of a table [message #395252] Tue, 31 March 2009 22:33 Go to next message
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 #395254 is a reply to message #395252] Tue, 31 March 2009 22:48 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>so I was thinking it would need to loop through.
Yes a LOOP is needed.
>I'm just not sure how to do that.
When all else fail, you could always try Read The Fine Manual.
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/loop_statement.htm#sthref2861


alternatively another site has many fine coding examples
http://asktom.oracle.com
Re: How to process every line of a table [message #395335 is a reply to message #395252] Wed, 01 April 2009 03:03 Go to previous message
pablolee
Messages: 2834
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
Previous Topic: external tables
Next Topic: SQL - Escape character
Goto Forum:
  


Current Time: Sun Dec 04 00:43:30 CST 2016

Total time taken to generate the page: 0.21039 seconds