2 Replies Latest reply: Apr 13, 2007 1:26 PM by 572380 RSS

    Problems with "desc <table>" usind OLEDB

      Dear Oracle Experts,

      I have a problem with getting table descriptions.
      I use an Oracle 10g database and try to access it with Excel-VBA using the original OLEDB driver.

      If I perform an describe EMSADM.Calculation on the iSWL*Plus I get the table description.
      If I try to do the same using an ADO/OLEDB connection out of an Excel VBA script I get
      the ORA-00900 error (the code below).

      Is there a general problem with that driver ?
      What can I do to get it running ?

      Code :

      (Dialog has been declared in an external routine)

      Dim Rec As New ADODB.Recordset

      Dim SQLAbfrage As String
      Dim SQLResult As New Collection
      Dim dataset As Variant

      'SQLResult Array()

      'SQLAbfrage = "desc '" & DBOwner & "." & SheetName & "'"
      'SQLAbfrage = "desc " & DBOwner & "." & SheetName
      SQLAbfrage = "desc EMSADM.Calculations"

      Rec.Open SQLAbfrage, Dialog

      If Rec.RecordCount = 1 Then

      While Not Rec.EOF
      SQLResult.Add ("Type")

      Result :

      I get the error at the Rec.Open line.

      Thanks for any help,

      Daniel Wetzler
        • 1. Re: Problems with "desc <table>" usind OLEDB

          Hopefully, you've already figured this out for yourself by now ... :-)

          DESC (DESCRIBE) is a SQL*Plus command, not a SQL verb. SQL*Plus is a front-end tool for SQL, so it adds some commands of its own, and this is one of those. You have to stick with SQL when using OLE DB.

          If you want this data in a client program, you might want to try something like:
          select * from all_tab_columns where table_name = '<your table name>'

          This is pure (Oracle) SQL, which you can experiment with in SQL*Plus until you get the set of columns that you need.

          • 2. Re: Problems with "desc <table>" usind OLEDB
            Pardon me, I have a similar problem.

            I'm trying to get Oracle via ADO and ODBC to launch a query that builds a whole schema. The query would test for the existance of tables, users, column in tables, stored procedure, triggers, and then some; and create/modify them as need be. In the 10g PL/SQL web page, one can do things like this:

            set serveroutput on;
            if .... stuf exists...
            . Create/Modify stuff...
            end if;

            But to ADO and ODBC, the set serveroutput on alone is evidently a command, which causes ORA-00922.

            We need to do this, as we deal with many databases, and we want the query to do the unique logic to apply our business rules, rather than build tons of ADO/ODBC code to launch each query seperately. And we can not just create a stored procedure, as we must check the database 1st for safety reasons. Now, if we can somehow create a store dprcoedure, or package, or SOMETHING, on the fly that will then run itself, and let us get standard rowsets back where we define the columns of the rows (including multiple recordset), then that would be wonderful.

            How can we execute such complicated queries and get results back? Is there anything like this?

            . complex queries

            if ... something does not exist..
            ... Create the something...
            end if;
            ... Somehow return a record set with 3 columns like this:
            select 'Schema completed' "RET", 'No errors found' ERR, 'finish schema completiong' LA from SOMETHING_ADO_ODBC_ORACLE_WILL_UNDERSTNAD