Home > Archive > PERL Miscellaneous > August 2005 > desc $table in DBI Oracle
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
desc $table in DBI Oracle
|
|
|
| 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
| |
| Paul Lalli 2005-08-02, 9:07 am |
| ngoc wrote:
> 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
| |
|
| 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)
Paul Lalli wrote:
> ngoc wrote:
>
>
>
> "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?
>
>
>
>
> Please post a *short* but *complete* script that we can run by copy and
> pasting which illustrates the problem you are having.
>
> Paul Lalli
>
| |
|
| 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 wrote:
> ngoc wrote:
>
>
>
> "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?
>
>
>
>
> Please post a *short* but *complete* script that we can run by copy and
> pasting which illustrates the problem you are having.
>
> Paul Lalli
>
| |
| Brian Wakem 2005-08-02, 9:07 am |
| ngoc wrote:
> 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?
--
Brian Wakem
Email: http://homepage.ntlworld.com/b.wakem/myemail.png
| |
|
| $selected_table is the table name. I am sure table name is correct.
Brian Wakem wrote:
> ngoc wrote:
>
>
>
>
>
> 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?
>
>
| |
| Paul Lalli 2005-08-02, 9:07 am |
| ngoc wrote:
> 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
| |
|
| The second message is the correct one. I deleted the previous one. Maybe
you have to refresh your news program.
Paul Lalli wrote:
> ngoc wrote:
>
>
>
> 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 2005-08-02, 9:07 am |
| ngoc wrote:
> 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@gmail.com 2005-08-02, 9:07 am |
| ngoc <ngoc@yahoo.com> wrote:
> 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
--
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service $9.95/Month 30GB
| |
| xhoster@gmail.com 2005-08-02, 5:01 pm |
| "Paul Lalli" <mritty@gmail.com> wrote:
> Anno Siegel wrote:
>
> 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
--
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service $9.95/Month 30GB
| |
| Paul Lalli 2005-08-02, 5:01 pm |
| xhoster@gmail.com wrote:
> 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 2005-08-11, 4:00 am |
| xhoster@gmail.com wrote in news:20050801114637.177$AO@newsreader.com:
> ngoc <ngoc@yahoo.com> wrote:
>
> 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 2005-08-29, 3:56 am |
| 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: harrism@lucent.com .
.. Room #SE405G | Conf. Bridge: 800.450.3848, x322703 .
.. Cary, North Carolina 27511 | Lucent IM: harrism@im.lucent.com .
.. .
.. .
.. "If you can't be a good example, then you'll just have to be a .
.. horrible warning..." -- Catherine Aird .
.. .
..:::::::::::::::::::::::::::: Signature :::::::::::::::::::::::::::::::::.
|
|
|
|
|