CODEWITHSUNDEEP
sqloracle-databaseexcel
GrumP
GrumP

Reputation: 1203

How to view the Oracle query which populates an Excel spreadsheet?

I have a spreadsheet that's connected to an Oracle database. It populates Excel using a query pulling specific fields from the database. I want to view the query that it is using so I can modify it.

It is using Office 2003 & Oracle 11g. At the moment, it refreshes automatically with the latest data. I need to reverse engineer the query so it gives me all the information from yesterday only.

Can anyone advise?

Upvotes: 0

Views: 2338

Answers (3)

Dennis
Dennis

Reputation: 31

In Excel 2013:

  1. Data > Existing Connections
  2. Double click on (one of) the connection(s)
  3. Properties > Definition

The SQL will be in the Command text

Upvotes: 0

steve healy
steve healy

Reputation: 11

We are still using 2003 Excel also. I never could see any icon like a hand holding a document but I found a way around it. When you choose edit the query and you just hit next until the button changes to finish you will see a save query button. I saved the query and then went into it with Notebook and it looked like this-

XLODBC
1
DBQ=J:\SHEALY.mdb;DefaultDir=J:\;Driver={Microsoft Access Driver (*.mdb)};DriverId=25;FIL=MS Access;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;SafeTransactions=0;Threads=3;UserCommitSync=Yes;
SELECT `Inventory by Class - Detail`.ITEMNO, `Inventory by Class - Detail`.CLASS, `Inventory by Class - Detail`.ONHAND, `Inventory by Class - Detail`.EPLANT_ID  FROM `J:\SHEALY`.`Inventory by Class - Detail` `Inventory by Class - Detail`  WHERE (`Inventory by Class - Detail`.ITEMNO Like 'I%') OR (`Inventory by Class - Detail`.ITEMNO Like 'UI%')  ORDER BY `Inventory by Class - Detail`.ITEMNO, `Inventory by Class - Detail`.EPLANT_ID


ITEMNO  CLASS   ONHAND  EPLANT_ID

My goal was to find the original source query. This told me what it was and then I could go back to the source and change the selection criteria.

Upvotes: 1

Mathew Frank
Mathew Frank

Reputation: 766

I don't a later version of Excel on my PC, so I can't give you an exact on the buttons to press/screens to look at, however I have done HEAPS with embedded data sources from various databases.

You need to look at "External Data" Connections. I think in 2003 you can still right click on the table with the data and access the external data properties from there (as well as select 'refresh')

Inside that you will find a button that leads to the definition: - a single table(/view) - or a SELECT statement

The select statement is what you need to look at.

NOTE: There are two ways of looking at he query - one loads the external MS Query tool - that you don't want - and the otehr will just display the raw query within Excel - this is what you want.

Upvotes: 0

Related Questions