Beefy Boxes and Bandwidth Generously Provided by pair Networks
Come for the quick hacks, stay for the epiphanies.

'Describe table' doesn't work (Oracle/dbiproxy)

by Anonymous Monk
on Jan 17, 2004 at 13:35 UTC ( #322031=perlquestion: print w/replies, xml ) Need Help??

Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

Dear Monks,

My Setup:
I have got a Linux (RH7.2) with perl 5x, and I am connecting to an Oracle 8 database
which is on a Win 2k machine using DBIproxy (DBI + DBD::ODBC, want to stick to DBD::ODBC as it allows me code portability to a great extent). Perl and the DBI and the other required modules are installed on the Win 2K too. ODBC's been configured on the WIN 2K to allow me to connect to the Oracle DB.

Via DBIproxy I am able to execute commands like select, insert, update and delete, but I am not able to "describe ". Here's where I need your help. Does anyone know why I am not able to execute the "describe table" command and what should I do to get it working by hook or crook :) (I haven't tried commands like create table or drop table etc. don't expect I'll need to do that.) Below is a snippet of my code with the error that it returns
# use DBI; use Data::Dumper; # declare variables my ($dbh, $sth, $row); $dbh= DBI->connect("DBI:Proxy:hostname=;port=3522;debug +=1;dsn=dbi:ODBC:dbtest",'test','test') or die $DBI::errstr; $sth = $dbh->prepare("select f1,f2,f3 from table1 where f1=1"); $sth->execute;
$VAR1 = { 'f1' => '1', 'f2' => 'A123', 'f3' => 'Vista' };
The code and output of the desc command:
$sth = $dbh->prepare("desc table1"); $sth->execute;
DBD::Proxy::st execute failed: Server returned error: Failed to execut +e method CallMethod: DBD::ODBC::st execute failed: [Oracle][ODBC][Ora +]ORA-00900: invalid SQL statement (SQL-42000)(DBD: st_execute/SQLExecute err=-1) at C:/Perl/site/lib/DB +I/ line 344.
Any help will be greatly appreciated. (Sorry for the long post)


Replies are listed 'Best First'.
Re: 'Describe table' doesn't work (Oracle/dbiproxy)
by perrin (Chancellor) on Jan 17, 2004 at 16:45 UTC
    It's because "describe" is not a part of SQL. It is something that Oracle's sqlplus shell implements, not the server. There are other ways to get a list of columns in a table, if that's what you want. Check the DBI docs.
Re: 'Describe table' doesn't work (Oracle/dbiproxy)
by graff (Chancellor) on Jan 18, 2004 at 02:57 UTC
    To expand a bit on perrin's reply, seek into the DBI docs for the section titled "Database Handle Methods", and probe down there quite a ways to find "table_info", "column_info" and the next few methods after that. If it seems kind of complicated, or if you're concerned by the opening statement in that part of the docs:
    Warning: This method is experimental and may change.
    don't worry -- there's a reasonable alternative using a query to oracle's own "data dictionary" tables. Here's a query that I've used to get something pretty similar to the output of "describe" -- just do your normal "prepare, execute, fetch_row" sequence using this query, and the results will be the list of columns and some of their attributes:
    my $sql = "select column_name, data_type, data_length, nullable from a +ll_tab_columns where table_name = ?";
    (Just have a scalar variable that holds the table name in ALL_CAPS, and provide that variable when you "execute" the prepared statement.) That query might not tell you everything you want to know about the definitions of the columns -- in which case, just consult a good oracle book about how to query for the attributes you want.

Log In?

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://322031]
Approved by b10m
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others studying the Monastery: (4)
As of 2023-12-02 11:52 GMT
Find Nodes?
    Voting Booth?
    What's your preferred 'use VERSION' for new CPAN modules in 2023?

    Results (16 votes). Check out past polls.