Cycle thru columns in table?

Prev: Comparing two rows
Next: Spying on queries from application?

From: Rich on 2 Mar 2010 16:09

---

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?

From: Gerard H. Pille on 2 Mar 2010 16:57

---

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>";
}
}

From: Rich on 5 Mar 2010 16:14

---

On Mar 2, 4:57 pm, "Gerard H. Pille" <g...(a)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?

From: joel garry on 5 Mar 2010 16:33

---

On Mar 5, 1:14 pm, Rich <richma...(a)earthlink.net> wrote:
> On Mar 2, 4:57 pm, "Gerard H. Pille" <g...(a)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
--
@home.com is bogus.
http://www.computerworlduk.com/management/it-business/sme/news/index.cfm?newsid=19216

From: Kay Kanekowski on 9 Mar 2010 09:35

---

Hi,
try this:

create table kk_cdom
( first_name varchar2(30),
last_name varchar2(30),
birthday date,
children number);

insert into kk_cdom values ( 'fred', 'flintstone', sysdate-30000,
2);
insert into kk_cdom values ( 'barnie','gröllheimer', sysdate-29900,
3);

select * from kk_cdom;
declare
cursor col is
select COLUMN_NAME
from user_tab_columns
where table_name = 'KK_CDOM'
order by COLUMN_id;
inhalt varchar2(30);
--
begin
for spalte in col loop
execute immediate ( 'select to_char(' || spalte.column_name || ')
from KK_CDOM where rownum < 2' ) into inhalt;
dbms_output.put_line ( spalte.column_name || ' : ' || inhalt);
end loop;
end;
/

And that is the result:

FIRST_NAME : fred
LAST_NAME : flintstone
BIRTHDAY : 19.01.28
CHILDREN : 2

hth
Kay

 | 
Pages: 1
Prev: Comparing two rows
Next: Spying on queries from application?