Fetch Into

[getjbb]

Using the command Fetch Into, is there a way to fetch selected fields into variables, when you do not know how may fields are in a select statement.
In the future I plan on using whatever script I create, so I am trying to get the Fetch Into statement as generic as possible. Could I do this using a Type Record:
TYPE rec1_t IS RECORD (field1 VARCHAR2(16), field2 NUMBER, field3 DATE);

But then again, the type record has a fixed number of fields. I will sometimes need more or less. I need something like field1.....fieldn, where n can be determined somehow.

For example my select could have field1, field2, field3 and
another time it could have field1, field2.

I plan on using the following loop:

OPEN myCursor;
LOOP
FETCH myCursor INTO ???????;
EXIT WHEN myCursor%NOTFOUND;
UTL_FILE.put_line(myFile,myFileLine);
END LOOP;
CLOSE myCursor;

Thanks.

getjbb

 

[carp]

You might want to look into REF cursors. I think they would offer the flexibility you are looking for.

 

[taupirho]

I think you need something called method4 dynamic SQL. You can find out more about it here:-

http://docstore.mik.ua/orelly/oracle/bipack/ch02_05.htm

In order to understand recursion, you must first understand recursion.

 

[getjbb]

I took a look at method4 dynamic SQL and it seems to be very complicated. I guess I will read up on it some more and try to understand it. I did a search on it and cannot seem to find a clear real world use of it.

getjbb

 

[Turkbear]

Hi,
as carp mentioned, a REF Cursor will handle that

Look at the Weak Ref Cursor - it does not need to have its type or contents pre-defined.

http://www.oradev.com/ref_cursor.jsp

Or

http://www.oracle.com/technology/oramag/code/tips2003/042003.html

To Paraphrase:"The Help you get is proportional to the Help you give.."