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 ?
(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
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.
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...
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?
if ... something does not exist..
... Create the something...
... 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