cx-oracle-users

[cx-oracle-users] cx_Oracle4.3.1: - describe table query is not working!!!

[Piyush C. <piy...@tc...>]

Hi all...

Just have a look on the code below...

(We have 'sample database with one of the table 'tab1' & user 'demo')

#We are trying to fetch all the columns from the table specified & their 
type.
#This is done by using 'describe <table-name>' sql query in Oracle.

import cx_Oracle

connection = cx_Oracle.Connection("demo/demo@sample")
cursor = connection.cursor()
cursor.execute("describe tab1")
for row in cursor.description:
        print "Column Name is: ",row[0]
        print "Column Type is: ", row[1]
        print "------------------------------------------------"

#But this is giving error that is: - cx_Oracle.DatabaseError: ORA-00900: 
invalid SQL statement
#I got some other method to implement this, but that is a bit lengthy. 
(can put that also if required)
#My quetion is that does cx_Oracle supports 'describe <table-name>' query 
or not???

Regards,
cPiyush.
=====-----=====-----=====
Notice: The information contained in this e-mail
message and/or attachments to it may contain 
confidential or privileged information. If you are 
not the intended recipient, any dissemination, use, 
review, distribution, printing or copying of the 
information contained in this e-mail message 
and/or attachments to it are strictly prohibited. If 
you have received this communication in error, 
please notify us by reply e-mail or telephone and 
immediately and permanently delete the message 
and any attachments. Thank you

Re: [cx-oracle-users] cx_Oracle4.3.1: - describe table query is not working!!!

[Richard M. <ri...@we...>]

Piyush Chechani wrote:
> Hi all...
> 
> Just have a look on the code below...
> 
> (We have 'sample database with one of the table 'tab1' & user 'demo')
> 
> #We are trying to fetch all the columns from the table specified & their 
> type.
> #This is done by using 'describe <table-name>' sql query in Oracle.
> 
> import cx_Oracle
> 
> connection = cx_Oracle.Connection("demo/demo@sample")
> cursor = connection.cursor()
> cursor.execute("describe tab1")
> for row in cursor.description:
>         print "Column Name is: ",row[0]
>         print "Column Type is: ", row[1]
>         print "------------------------------------------------"
> 
> #But this is giving error that is: - cx_Oracle.DatabaseError: ORA-00900: 
> invalid SQL statement
> #I got some other method to implement this, but that is a bit lengthy. 
> (can put that also if required)
> #My quetion is that does cx_Oracle supports 'describe <table-name>' query 
> or not???

I don't think it does (it's not really a query). You can get the
same information using a query of the data dictionary. For example

select table_name from user_tables; -- Get a list of tables
select column_name, data_type from all_tab_columns where table_name = 
'RESELLER'; -- Get the names and types of the table RESELLER.

Cheers

Rich.


-- 
Richard Moore, Principal Software Engineer,
Westpoint Ltd,
Albion Wharf, 19 Albion Street, Manchester, M1 5LN, England
Tel: +44 161 237 1028
Fax: +44 161 237 1031

Re: [cx-oracle-users] cx_Oracle4.3.1: - describe table query is not working!!!

[Piyush C. <piy...@tc...>]

Hey Richard,

Thanks a lot man for quotin g that query, 
But I was asking specifically for that particular query, that 'describe 
<tabl-name>' runs when we execute this in SQL*Plus, but it doesn't from 
cx_Oracle interface. Why so.

Thanks for responding.

cPiyush.

cx-...@li... wrote on 05/09/2007 02:37:47 
PM:

> Piyush Chechani wrote:
> > Hi all...
> > 
> > Just have a look on the code below...
> > 
> > (We have 'sample database with one of the table 'tab1' & user 'demo')
> > 
> > #We are trying to fetch all the columns from the table specified & 
their 
> > type.
> > #This is done by using 'describe <table-name>' sql query in Oracle.
> > 
> > import cx_Oracle
> > 
> > connection = cx_Oracle.Connection("demo/demo@sample")
> > cursor = connection.cursor()
> > cursor.execute("describe tab1")
> > for row in cursor.description:
> >         print "Column Name is: ",row[0]
> >         print "Column Type is: ", row[1]
> >         print "------------------------------------------------"
> > 
> > #But this is giving error that is: - cx_Oracle.DatabaseError: 
ORA-00900: 
> > invalid SQL statement
> > #I got some other method to implement this, but that is a bit lengthy. 

> > (can put that also if required)
> > #My quetion is that does cx_Oracle supports 'describe <table-name>' 
query 
> > or not???
> 
> I don't think it does (it's not really a query). You can get the
> same information using a query of the data dictionary. For example
> 
> select table_name from user_tables; -- Get a list of tables
> select column_name, data_type from all_tab_columns where table_name = 
> 'RESELLER'; -- Get the names and types of the table RESELLER.
> 
> Cheers
=====-----=====-----=====
Notice: The information contained in this e-mail
message and/or attachments to it may contain 
confidential or privileged information. If you are 
not the intended recipient, any dissemination, use, 
review, distribution, printing or copying of the 
information contained in this e-mail message 
and/or attachments to it are strictly prohibited. If 
you have received this communication in error, 
please notify us by reply e-mail or telephone and 
immediately and permanently delete the message 
and any attachments. Thank you

Re: [cx-oracle-users] cx_Oracle4.3.1: - describe table query is not working!!!

[Richard M. <ri...@we...>]

Piyush Chechani wrote:
> Hey Richard,
> 
> Thanks a lot man for quotin g that query, 
> But I was asking specifically for that particular query, that 'describe 
> <tabl-name>' runs when we execute this in SQL*Plus, but it doesn't from 
> cx_Oracle interface. Why so.

As I said, describe isn't a real query. I doubt it will work. Describe
is implemented separately from queries (using OCIDescribeAny()), it's
not really part of SQL.

Cheers

Rich.

-- 
Richard Moore, Principal Software Engineer,
Westpoint Ltd,
Albion Wharf, 19 Albion Street, Manchester, M1 5LN, England
Tel: +44 161 237 1028
Fax: +44 161 237 1031

Re: [cx-oracle-users] cx_Oracle4.3.1: - describe table query is not working!!!

[Amaury F. d'A. <ama...@gm...>]

Hello,

Piyush Chechani wrote:
>
> Hey Richard,
>
> Thanks a lot man for quotin g that query,
> But I was asking specifically for that particular query, that 'describe
> <tabl-name>' runs when we execute this in SQL*Plus, but it doesn't from
> cx_Oracle interface. Why so.

SQL*Plus has features and commands that are not SQL queries, but are
useful in a command-line interface to the database.
For example, "connect",  "spool", "set linesize" are not SQL queries.
"describe" is such a command.

In cx_Oracle, you can implement the 'describe' command like this:

def describe(connection, tablename):
    cur =connection.cursor()
    cur.execute('select * from %s where 1=0' % tablename)
    for desc in cur.description:
        column_name = desc[0]
        nullable = desc[6]
        data_type = desc[1].__name__
        data_length  = desc[3]
        print column_name, nullable, data_type, data_length

-- 
Amaury Forgeot d'Arc

Re: [cx-oracle-users] cx_Oracle4.3.1: - describe table query is notworking!!!

[Rob D. <Rob...@5o...>]

select COLUMN_NAME,DATA_TYPE,DATA_LENGTH,NULLABLE from user_tab_columns
where table_name =3D 'BOB'

=20

________________________________

From: cx-...@li...
[mailto:cx-...@li...] On Behalf Of
Piyush Chechani
Sent: 09 May 2007 09:57
To: cx-...@li...
Subject: [cx-oracle-users] cx_Oracle4.3.1: - describe table query is
notworking!!!

=20


Hi all...=20

Just have a look on the code below...=20

(We have 'sample database with one of the table 'tab1' & user 'demo')=20

#We are trying to fetch all the columns from the table specified & their
type.=20
#This is done by using 'describe <table-name>' sql query in Oracle.=20

import cx_Oracle

connection =3D cx_Oracle.Connection("demo/demo@sample")
cursor =3D connection.cursor()
cursor.execute("describe tab1")
for row in cursor.description:=20
        print "Column Name is: ",row[0]=20
        print "Column Type is: ", row[1]=20
        print "------------------------------------------------"=20

#But this is giving error that is: - cx_Oracle.DatabaseError: ORA-00900:
invalid SQL statement=20
#I got some other method to implement this, but that is a bit lengthy.
(can put that also if required)=20
#My quetion is that does cx_Oracle supports 'describe <table-name>'
query or not???=20

Regards,=20
cPiyush.

=3D=3D=3D=3D=3D-----=3D=3D=3D=3D=3D-----=3D=3D=3D=3D=3D
Notice: The information contained in this e-mail
message and/or attachments to it may contain=20
confidential or privileged information. If you are=20
not the intended recipient, any dissemination, use,=20
review, distribution, printing or copying of the=20
information contained in this e-mail message=20
and/or attachments to it are strictly prohibited. If=20
you have received this communication in error,=20
please notify us by reply e-mail or telephone and=20
immediately and permanently delete the message=20
and any attachments. Thank you
=20
=20