ORA-00900: invalid SQL statement when using Perl DBI::Oracle module 
Author Message
 ORA-00900: invalid SQL statement when using Perl DBI::Oracle module

Hello,

    In my perl script, I have written the following:

[ successful connection to the database precedes ]

$sth=$dbh->prepare("describe mytablename");

$sth->execute;

and it throws invalid SQL statement!!! If you write the following:

$sth=$dbh->prepare("select * from tab");

$sth->execute;

it runs perfectly and I get the right data. I should say that "describe"
runs OK within SQLPlus (both in Windows and Linux) and SQLPlus
Worksheet. Our Oracle server is:

SQL*Plus: Release 8.1.6.0.0 - Production on Thu Jun 1 10:15:32 2000

(c) Copyright 1999 Oracle Corporation. All rights reserved.

Connected to:
Oracle8i Release 8.1.5.0.0 - Production
With the Java option
PL/SQL Release 8.1.5.0.0 - Production

on a Windows NT Server 4.0 (service pack 6). Perl version is:

Summary of my perl5 (5.0 patchlevel 5 subversion 3) configuration:
Platform:
 osname=linux, osvers=2.2.5-22smp, archname=i386-linux uname='linux
porky.devel.redhat.com 2.2.5-22smp #1 smp wed jun 2 09:11:51 edt 1999
i686 unknown '

The DBI module version 1.13 and DBD-Oracle 1.03 (compiled with Oracle
client 8.1.6).

    Could you please have any idea what is the problem about, since I
need describe SQL command in order to get dynamically the table's
fields. Is there any other way to do that? Thank you very much in
advance.

Panos Kavalagios



Mon, 18 Nov 2002 03:00:00 GMT  
 ORA-00900: invalid SQL statement when using Perl DBI::Oracle module

Quote:

> $sth=$dbh->prepare("describe mytablename");

    I've been told in a private mail that I can use the database object
all_tab_columns as an alternative solution to my problem. Thus, the
following:

$sth=$dbh->prepare("select column_name from all_tab_columns where
table_name='mytablename'");

gives me the desired result. However, I'm still curious how a trivial
describe SQL statement could fail. Anyway, thank you very much for your
time.

Panos Kavalagios



Mon, 18 Nov 2002 03:00:00 GMT  
 ORA-00900: invalid SQL statement when using Perl DBI::Oracle module
You can get the information you need from all_tab_columns, which is a real
database object
Quote:

>Hello,

>    In my perl script, I have written the following:

>[ successful connection to the database precedes ]

>$sth=$dbh->prepare("describe mytablename");

>$sth->execute;

>and it throws invalid SQL statement!!! If you write the following:

>$sth=$dbh->prepare("select * from tab");

>$sth->execute;

>it runs perfectly and I get the right data. I should say that "describe"
>runs OK within SQLPlus (both in Windows and Linux) and SQLPlus
>Worksheet. Our Oracle server is:

>SQL*Plus: Release 8.1.6.0.0 - Production on Thu Jun 1 10:15:32 2000

>(c) Copyright 1999 Oracle Corporation. All rights reserved.

>Connected to:
>Oracle8i Release 8.1.5.0.0 - Production
>With the Java option
>PL/SQL Release 8.1.5.0.0 - Production

>on a Windows NT Server 4.0 (service pack 6). Perl version is:

>Summary of my perl5 (5.0 patchlevel 5 subversion 3) configuration:
>Platform:
> osname=linux, osvers=2.2.5-22smp, archname=i386-linux uname='linux
>porky.devel.redhat.com 2.2.5-22smp #1 smp wed jun 2 09:11:51 edt 1999
>i686 unknown '

>The DBI module version 1.13 and DBD-Oracle 1.03 (compiled with Oracle
>client 8.1.6).

>    Could you please have any idea what is the problem about, since I
>need describe SQL command in order to get dynamically the table's
>fields. Is there any other way to do that? Thank you very much in
>advance.

>Panos Kavalagios



Mon, 18 Nov 2002 03:00:00 GMT  
 ORA-00900: invalid SQL statement when using Perl DBI::Oracle module

Quote:


> > $sth=$dbh->prepare("describe mytablename");

>     I've been told in a private mail that I can use the database object
> all_tab_columns as an alternative solution to my problem. Thus, the
> following:

> $sth=$dbh->prepare("select column_name from all_tab_columns where
> table_name='mytablename'");

> gives me the desired result. However, I'm still curious how a trivial
> describe SQL statement could fail. Anyway, thank you very much for your
> time.

'describe' is not a generic SQL command (at least in Oracle).  It is
available in sqlplus only (from what I know).

Jeff Long



Mon, 18 Nov 2002 03:00:00 GMT  
 ORA-00900: invalid SQL statement when using Perl DBI::Oracle module

Quote:

> 'describe' is not a generic SQL command (at least in Oracle).  It is
> available in sqlplus only (from what I know).

    Yes you're right. According to SQL documentation there is no such
command. It is mentioned only in SQL*Plus documentation and it works only in
sqlplus and SQL Plus Worksheet.

--
D? a??



Mon, 18 Nov 2002 03:00:00 GMT  
 ORA-00900: invalid SQL statement when using Perl DBI::Oracle module

Quote:

> There is a much better way to get the information on the columns of a table:
> use the view  DBA_TAB_COLUMNS. (OR DBA_TAB_COLS?).

> You can use a cursor to manipulate this info.

    Both views dba_tab_columns and all_tab_columns provides exactly the same
result. Now, I'm confused! What should I use? Is there any documentation about
these tables/views?

    Well, about the "describe" it appeared in the major revision of SQL standard
in 1992, also known as SQL2. All Oracle applications, support these extensions
up to the Entry SQL92 level.

Panos Kavalagios



Tue, 19 Nov 2002 03:00:00 GMT  
 ORA-00900: invalid SQL statement when using Perl DBI::Oracle module


Quote:
> Hello,

>     In my perl script, I have written the following:

> [ successful connection to the database precedes ]

> $sth=$dbh->prepare("describe mytablename");

describe is NOT a SQL command. It's a SQL*Plus command.

--
  P. Larsen



Sun, 08 Dec 2002 03:00:00 GMT  
 ORA-00900: invalid SQL statement when using Perl DBI::Oracle module
Try selecting from the user_source table instead
Quote:



>> Hello,

>>     In my perl script, I have written the following:

>> [ successful connection to the database precedes ]

>> $sth=$dbh->prepare("describe mytablename");

>describe is NOT a SQL command. It's a SQL*Plus command.

>--
>  P. Larsen



Sat, 14 Dec 2002 03:00:00 GMT  
 
 [ 8 post ] 

 Relevant Pages 

1. DBD::Oracle PL/SQL statement emulating a SQL statement

2. DBI error: ORA-06401: NETCMN: invalid driver designator

3. dbi:Oracle & SQL statement with date

4. Perl Oracle DBD with NLS_LANG UTF8 getting errors ORA-01406 ORA-24345

5. Using Embedded SQL vs Stored procedures in Perl DBI?DBD for Oracle

6. Problem with SQL update using Oracle & DBI

7. DBI/DBD/Oracle connect won't work (ORA-12154)

8. execute a Perl Programm with DBD:DBI - SQL-Statements in a file

9. ORA-01461 with DBI when using placeholders

10. using DBI , unreasonable conversion error - ORA-01460

11. using DBI,unreasonable conversion error-ORA 01460

12. Problem with SQL statement in DBI

 

 
Powered by phpBB® Forum Software