Re: Oracle to Excel? How?

In article <1993Feb27.232549.13259_at_oracle.us.oracle.com> skanga_at_us.oracle.com (Shiraz Kanga) writes:
>In article <dtb.730518319_at_otto> dtb_at_otto.bf.rmit.oz.au writes:
>>I know Excel comes with Q&E that can hit Oracle tables.
>>Does anyone know how to get the data from there into an Excel
>>spreadsheet ? Preferaly using Excel macros? Or can I get
>>the data straight into excel from SQL*Net ?
>>
>
>I saw a lot of replies to this but none of them suggested the easiest method
>which IMHO is to use Oracle's DDE Manager. DDE manager allows any DDE compliant
>windows app to talk directly to the RDBMS. The product even comes with samples
>for excel macros talking to ORACLE - exactly what was requested.
>
>Just my (obviously biased) opinion
>
>Shiraz
>
>
>--
>Shiraz Kanga (skanga_at_oracle.com) Tel: (415)506-5412 Fax: (415)506-7822
>Analyst -- Desktop Tools Group -- Worldwide Technical Support -- 3OP11
>Oracle Corporation 500 Oracle Pkwy, MS 659311, Redwood Shores CA 94065
>"I work for Oracle - I speak and type (over here at least) for myself"

Yep, that's a biased opinion :-)

I tried to create a major application using Excel and DDEMGR and failed miserably. Why? Simple - the longest string that you can pass to DDEMGR is the longest string stat will fit in a SINGLE cell in Excel, namely 255 characters.

For simple selects/inserts/updates/whatever, like those used in the classic ORACLE examples, everything is just fine. BUT, if you cant contain your whole SQL statement within the 255-character limit, you're just plain SOL.

Q+E (the Microsoft version that comes with Excel), Q+E Database Editor 5.0 (the "retail" version from Pioneer Software who originated Q+E), Q+E Database Library (forget the DDE channel - call DLL's directly) and probably others support a mechanism that allows you to build up a large, complex SQL statement in an internal buffer by sending it in pieces whach are concatenated in the buffer and then treated as a single unit for execution. (Check out the "command" command in the Q+E docs).

It really doesn't take much to get a SQL statement over 255 characters if there are joins involved, if the table names are relatively long and descriptive, if you have ORDER BY and GROUP BY clauses and if you can't do a "select (*)" but have to name specific fields.

The only down side is that Q+E (either Microsoft or Pioneer version) does not let you trap the ORACLE return code fom statements, hence you can't trap out and do something smart if the return code indicates a problem. Q+E Database Library, however, does provide for getting the actual ORACLE return code, if you need it in your application.

[Disclaimer: I have no connection with Pioneer Software except as a satisfied customer!]

Good luck!

-- 
Bill Meahan			|EFHD Information Systems Staff
Plant Floor Systems Specialist	|Ford Motor Company
wwm_at_ef5003.efhd.ford.com      	| +1 313 487 6122
..!fmsrl7!pmsmam!wwm           |I'm not paid to speak for Ford!