Re: Dynamic PL/SQL - Get values from one cursor from another or alternatives?

From: Aya the Vampire Slayer <ryoko_at_gatech.rmv.this.part.edu>
Date: Mon, 11 Aug 2008 14:48:31 +0000 (UTC)
Message-ID: <g7pjfv$2o0$1@news-int.gatech.edu>


bpfh <numerista_at_gmail.com> wa:
<snip>
>So 3 questions:

I am going to assume by "label" you mean column name.

In my experience, with 10g (don't know if they fixed any of this in 11):

>1) Can I access the values FETCHed from a cursor by an index value
>rather than a label so that I can control it from a loop?

Not in my experience (and I have tried).

>2) Is there a way of generating the label in a similar method as above
>in pseudo-code as to get the label out of one FETCH'ed array and
>passing it to another as an argument?

No. You can get the names of all of the columns of your table by querying USER_TAB_COLS (or ALL_TAB_COLS, depending on what you're working with), though. Unfortunately, as far as I know, as of 10g at least, you have to hard-code the column names.

>3) Is there a way of getting the field names out of the cAnnonceData
>cursor, so that in effect, executing a SELECT * from a table gets all
>data *and* field names out of a table?

... and, no on this one too.

However, assuming your table structure is not in flux, you can make gratuitous use of USER_TAB_COLS and some java on your oracle db to convert some cursor data into a useable array type that includes both col names and data (depending on how you want to do it). I have done this before on a previous project, and it can work, it's just a little more work and a lot less intuitive; pl/sql is limited in some ways that I find very unfortunate. At least, as of and including 10g.

-- 
"Care must be exorcised when handring Opiticar System as it is apts to
be sticked by dusts and hand-fat." --Japanese Translators

"Keep your fingers off the lens." --Elton Byington, English Translator
Received on Mon Aug 11 2008 - 09:48:31 CDT

Original text of this message