Re: Cycle thru columns in table?

From: joel garry <joel-garry_at_home.com>
Date: Fri, 5 Mar 2010 13:33:26 -0800 (PST)
Message-ID: <5bd6c58b-fb6b-4420-9d4b-a6a0e02920d5_at_k36g2000prb.googlegroups.com>



On Mar 5, 1:14 pm, Rich <richma..._at_earthlink.net> wrote:
> On Mar 2, 4:57 pm, "Gerard H. Pille" <g..._at_skynet.be> wrote:
>
>
>
> > Rich wrote:
>
> > > 1 am using 10g.
>
> > > I need code that returns the column name of the table and the value of
> > > a row of code. For example, assume I have a table called Customer.
> > > Then imagine if there is a column called First_Name. My SQL will
> > > return one record. So that the output might look like
>
> > > First_Name = Fred
>
> > > The psudo code might look like
>
> > > For i = 1 to the last column in table
> > >     column_name := column(i)
> > >     record_value := cursor(i)
> > > Loop
>
> > > How can I do the above in Oracle?
>
> > An example using PHP, good for any database, I guess.  The column names are used as column
> > headers.  You can drop the rowcount and bail out after the first row.
>
> >         $tables =
> >           array( "users", "machines", "games", "runs_on", "owns", "likes");
>
> >         // loop over tables
> >         foreach( $tables as $table ) {
> >           // loop over rows of database table
> >           $sql = "SELECT count(*) aantal FROM $table";
> >           foreach ($db->query($sql) as $row) {
> >             $rowcount = $row[0];
> >             print "$table contains $row[0] row(s)<br>";
> >           }
>
> >           // print contents if any
> >           if ($rowcount > 0) {
> >             $sql = "SELECT * FROM $table";
> >             $stmt = $db->query($sql);
> >             print "<table><tr>";
>
> >             // show column names
> >             for ($i = 0; $i < $stmt->columnCount(); $i++) {
> >               $cmeta = $stmt->getColumnMeta($i);
> >               print "<th>" . $cmeta['name'];
> >             }
> >             print "</tr>";
>
> >             // print the rows
> >             foreach ($stmt as $row) {
> >               print "<tr>";
> >               // print column by column
> >               for ($i = 0; $i < $stmt->columnCount(); $i++) {
> >                 print "<TD>" . $row[$i];
> >               }
> >               print "</tr>";
> >             }
> >             print "</table>";
> >           }
> >         }- Hide quoted text -
>
> > - Show quoted text -
>
> Thanks for the response.
>
> Is this possible in PL/SQL?

Anything is possible in PL/SQL. Some things are even possible in SQL. I'm having trouble comprehending exactly what you want, but I'll say that there is select view_name from all_views where view_name like 'USER%TAB%COL%'; and you can find many examples of similar things at asktom.oracle.com, like http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1833453100346607073

In general, if you can do something in SQL rather than PL, it will work better. This is partly due to relational algebra working a lot better than procedural design for relational databases.

jg

--
_at_home.com is bogus.
http://www.computerworlduk.com/management/it-business/sme/news/index.cfm?newsid=19216
Received on Fri Mar 05 2010 - 15:33:26 CST

Original text of this message