Hi,

I am fighting with PL/SQL and loosing...

I have a database that changes on a regular basis and has over 200
columns... and the data needs to be exported to a file.

My idea was to create the following cursors:

CURSOR cAnnonceFields IS SELECT column_name FROM SYS.USER_TAB_COLUMNS
where table_name = 'ANNONCE';
CURSOR cAnnonceData IS SELECT * FROM annonce where id_annonce =
s_id_annonce;

From there I wanted to get values from cAnnonceData cursor by
referencing it with the column names got from cAnnonceFields:

FETCH cAnnonceData INTO aFieldValues;
FETCH cAnnonceFields INTO aFieldNames;

Up to here, no problems, but from here things go downhill.
cAnnonceFields has 1 column (column_name) and cAnnonceFields has x
fields, but I *have* to provide a static text lable to aFieldNames to
get the value - and it seems impossible to get somthing like (in
pseudo-code):

put_line(aFieldValues.(aFieldNames.column_name))

I get all sorts of strange PL/SQL compilation errors.

So 3 questions:

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?

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?

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?

Cheers,
Daniel

On Mon, 11 Aug 2008 03:06:29 -0700 (PDT), bpfh <numerista (AT) gmail (DOT) com>
wrote:

You can use exp for this purpose.

If you want to dump it to ASCII or CSV, get the SQL Unloader (or
Dump2CSV) utility available on http://asktom.oracle.com

Will take any SQL statement as parameter, chew it, and sput out the
requested data

--
Sybrand Bakker
Senior Oracle DBA

On Mon, 11 Aug 2008 03:06:29 -0700 (PDT), bpfh <numerista (AT) gmail (DOT) com>
wrote:

You can use exp for this purpose.

If you want to dump it to ASCII or CSV, get the SQL Unloader (or
Dump2CSV) utility available on http://asktom.oracle.com

Will take any SQL statement as parameter, chew it, and sput out the
requested data

--
Sybrand Bakker
Senior Oracle DBA

On Mon, 11 Aug 2008 03:06:29 -0700 (PDT), bpfh <numerista (AT) gmail (DOT) com>
wrote:

You can use exp for this purpose.

If you want to dump it to ASCII or CSV, get the SQL Unloader (or
Dump2CSV) utility available on http://asktom.oracle.com

Will take any SQL statement as parameter, chew it, and sput out the
requested data

--
Sybrand Bakker
Senior Oracle DBA

On Mon, 11 Aug 2008 03:06:29 -0700 (PDT), bpfh <numerista (AT) gmail (DOT) com>
wrote:

You can use exp for this purpose.

If you want to dump it to ASCII or CSV, get the SQL Unloader (or
Dump2CSV) utility available on http://asktom.oracle.com

Will take any SQL statement as parameter, chew it, and sput out the
requested data

--
Sybrand Bakker
Senior Oracle DBA

bpfh <numerista (AT) gmail (DOT) com> wa:
<snip>
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):

Not in my experience (and I have tried).


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.


.... 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

bpfh <numerista (AT) gmail (DOT) com> wa:
<snip>
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):

Not in my experience (and I have tried).


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.


.... 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

bpfh <numerista (AT) gmail (DOT) com> wa:
<snip>
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):

Not in my experience (and I have tried).


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.


.... 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

bpfh <numerista (AT) gmail (DOT) com> wa:
<snip>
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):

Not in my experience (and I have tried).


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.


.... 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