Solved

Referncing Columns In a Cursor By Index

Posted on 2009-07-15
5
542 Views
Last Modified: 2013-12-07
Is there a way to reference columns in a cursor by index rather than by column name?  For instance,

DECLARE
  CURSOR myCursor IS
  SELECT first_name,
    last_name
  FROM customer;
BEGIN
  FOR curRecord IN myCursor LOOP
    DBMS_OUTPUT.PUT_LINE(curRecord(1));
    DBMS_OUTPUT.PUT_LINE(curRecord(2));
  END LOOP;
END;
0
Comment
Question by:ccovell
  • 2
5 Comments
 
LVL 47

Expert Comment

by:schwertner
ID: 24861676

So far only this can work

DECLARE
  CURSOR myCursor IS
  SELECT first_name,
    last_name
  FROM employees;
BEGIN
  FOR curRecord IN myCursor LOOP
    DBMS_OUTPUT.PUT_LINE(curRecord.first_name));
    DBMS_OUTPUT.PUT_LINE(curRecord.last_name));
  END LOOP;
END;
0
 

Author Comment

by:ccovell
ID: 24861730
ty schwertner,
I've come to the conclusion that this functionality is definitely no available.  Kind sad if your in a situation like me where you have a multi union query and there is no consistent name for the same column in each of the union components.  I've resided to giving them all the same alias and just using the alias reference in the cursor.
0
 
LVL 20

Accepted Solution

by:
flow01 earned 250 total points
ID: 24861846
Using same alias everywhere in the union is neat programming .
Until know however (in my experience) you can reference the columns of the union by the columns_names of the first union part.
0
 
LVL 47

Assisted Solution

by:schwertner
schwertner earned 250 total points
ID: 24862188
In UNION query the column names of the first query are used in the set statement. Also aliases could be given.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

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 article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.

803 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