desc $table in DBI Oracle

Discussion in 'Perl Misc' started by ngoc, Aug 1, 2005.

  1. ngoc

    ngoc Guest

    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, Aug 1, 2005
    #1
    1. Advertising

  2. ngoc

    ngoc Guest

    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:
    >
    >>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, Aug 1, 2005
    #2
    1. Advertising

  3. ngoc

    ngoc Guest

    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:
    >
    >>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, Aug 1, 2005
    #3
  4. ngoc

    Paul Lalli Guest

    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
    Paul Lalli, Aug 1, 2005
    #4
  5. ngoc

    ngoc Guest

    $selected_table is the table name. I am sure table name is correct.
    Brian Wakem wrote:
    > 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?
    >
    >
    ngoc, Aug 1, 2005
    #5
  6. ngoc

    Brian Wakem Guest

    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
    Brian Wakem, Aug 1, 2005
    #6
  7. ngoc

    ngoc Guest

    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:
    >
    >>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
    >
    ngoc, Aug 1, 2005
    #7
  8. ngoc

    Paul Lalli Guest

    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
    Paul Lalli, Aug 1, 2005
    #8
  9. ngoc

    Paul Lalli Guest

    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
    Paul Lalli, Aug 1, 2005
    #9
  10. ngoc

    Guest

    ngoc <> 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
    , Aug 1, 2005
    #10
  11. ngoc

    Anno Siegel Guest

    Paul Lalli <> wrote in comp.lang.perl.misc:
    > 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.


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

    Anno
    --
    If you want to post a followup via groups.google.com, don't use
    the broken "Reply" link at the bottom of the article. Click on
    "show options" at the top of the article, then click on the
    "Reply" at the bottom of the article headers.
    Anno Siegel, Aug 2, 2005
    #11
  12. ngoc

    Paul Lalli Guest

    Anno Siegel wrote:
    > Paul Lalli <> wrote in comp.lang.perl.misc:
    > > ngoc wrote:
    > > 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.


    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
    Paul Lalli, Aug 2, 2005
    #12
  13. ngoc

    Guest

    "Paul Lalli" <> wrote:
    > Anno Siegel wrote:
    > > Paul Lalli <> wrote in comp.lang.perl.misc:
    > > > ngoc wrote:
    > > > 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.

    >
    > 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
    , Aug 2, 2005
    #13
  14. ngoc

    Paul Lalli Guest

    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
    Paul Lalli, Aug 2, 2005
    #14
  15. wrote in news:20050801114637.177$:

    > ngoc <> 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
    >


    You are correct, Xho.

    --
    Eric
    `$=`;$_=\%!;($_)=/(.)/;$==++$|;($.,$/,$,,$\,$",$;,$^,$#,$~,$*,$:,@%)=(
    $!=~/(.)(.).(.)(.)(.)(.)..(.)(.)(.)..(.)......(.)/,$"),$=++;$.++;$.++;
    $_++;$_++;($_,$\,$,)=($~.$"."$;$/$%[$?]$_$\$,$:$%[$?]",$"&$~,$#,);$,++
    ;$,++;$^|=$";`$_$\$,$/$:$;$~$*$%[$?]$.$~$*${#}$%[$?]$;$\$"$^$~$*.>&$=`
    Eric J. Roode, Aug 11, 2005
    #15
  16. 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: .
    .. Room #SE405G | Conf. Bridge: 800.450.3848, x322703 .
    .. Cary, North Carolina 27511 | Lucent IM: .
    .. .
    .. .
    .. "If you can't be a good example, then you'll just have to be a .
    .. horrible warning..." -- Catherine Aird .
    .. .
    ..:::::::::::::::::::::::::::: Signature :::::::::::::::::::::::::::::::::.
    Marcus Eric Harris, Aug 29, 2005
    #16
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. ulloa
    Replies:
    1
    Views:
    525
    Juha Laiho
    Jul 22, 2004
  2. Henri =?ISO-8859-15?Q?Schom=E4cker?=
    Replies:
    1
    Views:
    295
    Henri =?ISO-8859-15?Q?Schom=E4cker?=
    Dec 17, 2005
  3. Tim Haynes
    Replies:
    3
    Views:
    140
    Ron Reidy
    Sep 13, 2003
  4. Vincent Le-Texier
    Replies:
    1
    Views:
    148
    Paul Lalli
    Dec 3, 2004
  5. Feyruz
    Replies:
    4
    Views:
    2,157
    Sherm Pendley
    Oct 14, 2005
Loading...

Share This Page