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?
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=19216Received on Fri Mar 05 2010 - 15:33:26 CST