Re: Dynamic PL/SQL - Get values from one cursor from another or alternatives?
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 TranslatorReceived on Mon Aug 11 2008 - 09:48:31 CDT