desc $table in DBI Oracle

[ngoc]

Hi
I use "my $header_sql = qq { desc $table };". It does not work.
But "select column_name from all_tab_columns where table_name =
\'$table\'" work.
My problem is "desc $table" matching which "select * from $table"
than
select column_name ...........
(I mean data and column name order matching).
Thanks

 

[ngoc]

Sorry. I was lazy to type, because I write to newsgroup on another
computer than my perl program computer.


Code:
my $header_sql = qq {
select
column_name
from
all_tab_columns
where
table_name = \'$selected_table\'
};

my $csr = $db_handle->prepare($header_sql)
or die("Could not prepare : $db_handle->errstr\n");
$csr->execute()
or die("Could not execute : $db_handle->errstr\n");
while (my $row = $csr->fetchrow()) {
push @header, $row;
};
$csr->finish;
NO PROBLEM

I TRIED

my $header_sql = qq { desc $sel_table };

my $csr = $db_handle->prepare($header_sql)
or die("Could not prepare : $db_handle->errstr\n");
$csr->execute()
or die("Could not execute : $db_handle->errstr\n");
while (my $row = $csr->fetchrow()) {
push @header, $row;
};
$csr->finish;


Error: Could not execute : ORA-00900: invalid SQL statement (DBD ERROR:
OCIStmtExecute)

 

[ngoc]

Sorry. I was lazy to type, because I write to newsgroup on another
computer than my perl program computer.


Code:
my $header_sql = qq {
select
column_name
from
all_tab_columns
where
table_name = \'$selected_table\'
};

my $csr = $db_handle->prepare($header_sql)
or die("Could not prepare : $db_handle->errstr\n");
$csr->execute()
or die("Could not execute : $db_handle->errstr\n");
while (my $row = $csr->fetchrow()) {
push @header, $row;
};
$csr->finish;
NO PROBLEM

I TRIED

my $header_sql = qq { desc $selected_table };

my $csr = $db_handle->prepare($header_sql)
or die("Could not prepare : $db_handle->errstr\n");
$csr->execute()
or die("Could not execute : $db_handle->errstr\n");
while (my $row = $csr->fetchrow()) {
push @header, $row;
};
$csr->finish;


Error: Could not execute : ORA-00900: invalid SQL statement (DBD ERROR:
OCIStmtExecute)

 

[Paul Lalli]

Hi
I use "my $header_sql = qq { desc $table };". It does not work.

"does not work" is a remarkably poor error description. How does it
not work? Compile error? Runtime error? Output you weren't
expecting?

Have you read the posting guildelines for this group?

But "select column_name from all_tab_columns where table_name =
\'$table\'" work.
My problem is "desc $table" matching which "select * from $table"
than
select column_name ...........
(I mean data and column name order matching).

Please post a *short* but *complete* script that we can run by copy and
pasting which illustrates the problem you are having.

Paul Lalli

 

[ngoc]

$selected_table is the table name. I am sure table name is correct.

 

[Brian Wakem]

my $header_sql = qq { desc $selected_table };

my $csr = $db_handle->prepare($header_sql)
or die("Could not prepare : $db_handle->errstr\n");
$csr->execute()
or die("Could not execute : $db_handle->errstr\n");
while (my $row = $csr->fetchrow()) {
push @header, $row;
};
$csr->finish;


Error: Could not execute : ORA-00900: invalid SQL statement (DBD ERROR:
OCIStmtExecute)

What does $selected_table contain? Have you checked to see if it contains
exactly what you thought?

I'm not familiar with oracle, is 'desc tablename' valid syntax? You need to
use "DESCRIBE tablename" in some DBs I believe?

 

[ngoc]

The second message is the correct one. I deleted the previous one. Maybe
you have to refresh your news program.

 

[Paul Lalli]

Sorry. I was lazy to type, because I write to newsgroup on another
computer than my perl program computer.

You just posted the same message twice, but with a significant
difference to the code. In your previous post, you were using
$selected_table in the first query, but $sel_table in the second.
Which is it?

Please post a short-but-*complete* script, so we can see exactly where
all of your variables are being assigned. Otherwise, we have no way of
verifying what you're telling us.

Paul Lalli

 

[Paul Lalli]

The second message is the correct one. I deleted the previous one. Maybe
you have to refresh your news program.

Maybe you need to learn how Usenet works. There is no "delete" of
posts. Once you've sent it, you sent it. You can request that servers
don't archive it, but they're under no obligation to follow that
request. Whatever program you're using that gave you the option of
"deleting" your post was flat out lying to you.

Paul Lalli

 

[xhoster]

Hi
I use "my $header_sql = qq { desc $table };". It does not work.
But "select column_name from all_tab_columns where table_name =
\'$table\'" work.
My problem is "desc $table" matching which "select * from $table"
than
select column_name ...........
(I mean data and column name order matching).
Thanks

I believe, but am not certain, that "desc" is not valid Oracle SQL syntax.
Rather, it is a command to the oracle tool SQLPLUS. So when you are using
SQLPLUS, it intercepts the desc command and processes it itself. Since
perl is connecting directly to Oracle, not via SQLPLUS, then the command
does not work from Perl.

Xho

 

[Anno Siegel]

Maybe you need to learn how Usenet works. There is no "delete" of
posts. Once you've sent it, you sent it. You can request that servers
don't archive it, but they're under no obligation to follow that
request. Whatever program you're using that gave you the option of
"deleting" your post was flat out lying to you.

Not so. Usenet messages can be cancelled, and some newsreaders allow
the user to do so.

Anno

 

[Paul Lalli]

Not so. Usenet messages can be cancelled, and some newsreaders allow
the user to do so.

http://www.faqs.org/faqs/usenet/cancel-faq/part1/ II. D.

There is no deletion of Usenet posts. There are "cancel messages",
which are a misnomer. They are requests for cancelation at best.
Individual sites are not in any way forced to cancel a message.

Paul Lalli

 

[xhoster]

http://www.faqs.org/faqs/usenet/cancel-faq/part1/ II. D.

There is no deletion of Usenet posts. There are "cancel messages",
which are a misnomer. They are requests for cancelation at best.
Individual sites are not in any way forced to cancel a message.

In that case, posts are not actually posts, they requests to post.

Individual sites are free to do whatever they want, including replace every
capital letter Q with a random quote from Shakespeare.


Xho

 

[Paul Lalli]

In that case, posts are not actually posts, they requests to post.

Individual sites are free to do whatever they want, including replace every
capital letter Q with a random quote from Shakespeare.

Sigh. The point was that once you have sent your message, there is no
method which guarantees people around the world will not be able to see
it, as is evidenced by the fact that the dupliate posts by the OP are
still visible in, for example, Google Groups.

And we have drifted seriously off-topic now. I apologize for steering
us there in the first place.

Paul Lalli

 

[Eric J. Roode]

I believe, but am not certain, that "desc" is not valid Oracle SQL
syntax. Rather, it is a command to the oracle tool SQLPLUS. So when
you are using SQLPLUS, it intercepts the desc command and processes it
itself. Since perl is connecting directly to Oracle, not via SQLPLUS,
then the command does not work from Perl.

Xho

You are correct, Xho.

--
Eric
`$=`;$_=\%!;($_)=/(.)/;$==++$|;($.,$/,$,,$\,$",$;,$^,$#,$~,$*,$:,@%)=(
$!=~/(.)(.).(.)(.)(.)(.)..(.)(.)(.)..(.)......(.)/,$"),$=++;$.++;$.++;
$_++;$_++;($_,$\,$,)=($~.$"."$;$/$%[$?]$_$\$,$:$%[$?]",$"&$~,$#,);$,++
;$,++;$^|=$";`$_$\$,$/$:$;$~$*$%[$?]$.$~$*${#}$%[$?]$;$\$"$^$~$*.>&$=`

 

[Marcus Eric Harris]

Hello,

I was wondering if you ever got an answer to your question?

If not, I may have a solution for you. I was trying to
resolve the very same issue a couple of years ago and
wrote this quick script as a test to see if it could
be done using the 'table_info()' method provided by
the DBI module.

Here is the script:

<Start>
#! /usr/bin/perl

use DBI;

#
# Create the database handle. Be sure to replace everything
# between the '<>' (not including them) with the values which
# are specific to your system.
#
$DBHandle = DBI->connect(
"dbi:Oracle:host=<HostName>;sid=<DBName>",
"<UserID>",
"<Password>"
);

#
# Fill-in all or part of your table schema name here.
# If you use the entire name, you may remove the wildcard (%).
#
%Attributes = (
TABLE_SCHEM => "<SchemaID>%",
);

#
# Define the statement handle to get the table metadata.
#
$SQLStatement = $DBHandle->table_info(\%Attributes);


#
# This part is not really necessary. It just allows
# for an orderly display of the output in tabular
# format.
#
open(HEADER);
$~ = "HEADER";
write();
select(STDOUT);
close(HEADER);

open(TABLE_INFO);
$~ = "TABLE_INFO";

while (($Catalog, $Owner, $TableName, $Type, $Remarks) = $SQLStatement->fetchrow_array())
{
foreach ($Catalog, $Owner, $TableName, $Type, $Remarks)
{
$_ = "N/A" unless defined($_);
}
write();
}

select(STDOUT);
close(TABLE_INFO);


#
# Define the header and body formats for the output.
#
format HEADER =
Catalog Owner Table Name Type
---------- ----------- ---------------------------- --------------------
..

format TABLE_INFO =
@<<<<<<<<< @<<<<<<<<<< @<<<<<<<<<<<<<<<<<<<<<<<<<<< @<<<<<<<<<<<<<<<<<<<
$Catalog, $Owner, $TableName, $Type
..

</End>


There are other values availble to you via 'table_info()', only
a few of which are displayed in the table above.

Once I got this issue answered, I was able to quickly
code something more specific to the work I was doing
at the time.

If you have other questions, let me know.

Marcus E. Harris


Aug 1, ngoc said:

n| Hi
n| I use "my $header_sql = qq { desc $table };". It does not work.
n| But "select column_name from all_tab_columns where table_name = \'$table\'"
n| work.
n| My problem is "desc $table" matching which "select * from $table"
n| than
n| select column_name ...........
n| (I mean data and column name order matching).
n| Thanks
n|

--

..:::::::::::::::::::::::::::: Signature :::::::::::::::::::::::::::::::::.
.. .
.. Marcus E. Harris [Engineer] | Work Phone: 919.463.3162 .
.. Lucent Technologies | FAX: 919.463.4479 .
.. 200 Lucent Lane | E-Mail: (e-mail address removed) .
.. Room #SE405G | Conf. Bridge: 800.450.3848, x322703 .
.. Cary, North Carolina 27511 | Lucent IM: (e-mail address removed) .
.. .
.. .
.. "If you can't be a good example, then you'll just have to be a .
.. horrible warning..." -- Catherine Aird .
.. .
..:::::::::::::::::::::::::::: Signature :::::::::::::::::::::::::::::::::.