Discussions
Categories
- 197K All Categories
- 2.5K Data
- 546 Big Data Appliance
- 1.9K Data Science
- 450.8K Databases
- 221.9K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 552 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 556 SQLcl
- 4K SQL Developer Data Modeler
- 187.2K SQL & PL/SQL
- 21.4K SQL Developer
- 296.4K Development
- 17 Developer Projects
- 139 Programming Languages
- 293.1K Development Tools
- 110 DevOps
- 3.1K QA/Testing
- 646.1K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 161 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.2K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 19 Java Essentials
- 162 Java 8 Questions
- 86K Java Programming
- 81 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 205 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 474 LiveLabs
- 39 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 175 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 233 Portuguese
How to get data set from Oracle SQL to Excel via VBA

Hi guys,
I have an SQL statement that works fine in Oracle SQL Developer
</code></p><pre __default_attr="sql" __jive_macro_name="code" class="jive_macro_code jive_text_macro _jivemacro_uid_14883816529872870" data-renderedposition="-348_8_1460_80" jivemacro_uid="_14883816529872870" modifiedtitle="true"><code><span class="str">select<br/> *<br/>from articles<br/>where<br/> artnr in (123, 234, 345)<br/></span>
I want to call this statement from VBA which works if I do it like this:
Sub ConnectToOracle<span class="typ"><code><span class="typ">Dim</span><span class="pln"> cn </span><span class="typ">As</span><span class="pln"> ADODB</span><span class="pun">.</span><span class="typ">Connection</span><span class="pln"><br/></span><span class="typ">Dim</span><span class="pln"> rs </span><span class="typ">As</span><span class="pln"> ADODB</span><span class="pun">.</span><span class="typ">Recordset</span><span class="pln"><br/></span><span class="typ">Dim</span><span class="pln"> arr </span><span class="typ">As</span><span class="pln"> </span><span class="typ">Variant</span><span class="pln"><br/><br/>connstr </span><span class="pun">=</span><span class="pln"> </span><span class="str">"Provider=msdaora;Data Source=###;User Id=###;Password=###;"</span><span class="pln"><br/><br/></span><span class="typ">Set</span><span class="pln"> cn </span><span class="pun">=</span><span class="pln"> </span><span class="typ">New</span><span class="pln"> ADODB</span><span class="pun">.</span><span class="typ">Connection</span><span class="pln"><br/></span><span class="typ">Set</span><span class="pln"> rs </span><span class="pun">=</span><span class="pln"> </span><span class="typ">New</span><span class="pln"> ADODB</span><span class="pun">.</span><span class="typ">Recordset</span><span class="pln"><br/><br/>cn</span><span class="pun">.</span><span class="typ">Open</span><span class="pln"> connstr<br/><br/>rs</span><span class="pun">.</span><span class="typ">CursorType</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> adOpenForwardOnly<br/>rs</span><span class="pun">.</span><span class="typ">Open</span><span class="pln"> </span><span class="pun">(</span><span class="str">"select * from articles where artnr in (123, 234, 345)"</span><span class="pun">),</span><span class="pln"> cn<br/><br/>arr </span><span class="pun">=</span><span class="pln"> rs</span><span class="pun">.</span><span class="typ">GetRows</span><span class="pln"><br/>arr </span><span class="pun">=</span><span class="pln"> transposeArray</span><span class="pun">(</span><span class="pln">arr</span><span class="pun">)</span><span class="pln"><br/><br/></span><span class="typ">Dim</span><span class="pln"> x </span><span class="typ">As</span><span class="pln"> </span><span class="typ">Long</span><span class="pln"><br/></span><span class="typ">Dim</span><span class="pln"> y </span><span class="typ">As</span><span class="pln"> </span><span class="typ">Long</span><span class="pln"><br/>x </span><span class="pun">=</span><span class="pln"> </span><span class="typ">UBound</span><span class="pun">(</span><span class="pln">arr</span><span class="pun">,</span><span class="pln"> </span><span class="lit">1</span><span class="pun">)</span><span class="pln"> </span><span class="pun">-</span><span class="pln"> </span><span class="typ">LBound</span><span class="pun">(</span><span class="pln">arr</span><span class="pun">,</span><span class="pln"> </span><span class="lit">1</span><span class="pun">)</span><span class="pln"> </span><span class="pun">+</span><span class="pln"> </span><span class="lit">1</span><span class="pln"><br/>y </span><span class="pun">=</span><span class="pln"> </span><span class="typ">UBound</span><span class="pun">(</span><span class="pln">arr</span><span class="pun">,</span><span class="pln"> </span><span class="lit">2</span><span class="pun">)</span><span class="pln"> </span><span class="pun">-</span><span class="pln"> </span><span class="typ">LBound</span><span class="pun">(</span><span class="pln">arr</span><span class="pun">,</span><span class="pln"> </span><span class="lit">2</span><span class="pun">)</span><span class="pln"> </span><span class="pun">+</span><span class="pln"> </span><span class="lit">1</span><span class="pln"><br/><br/></span><span class="typ">Worksheets</span><span class="pun">(</span><span class="lit">1</span><span class="pun">).</span><span class="typ">Activate</span><span class="pln"><br/></span><span class="typ">ActiveSheet</span><span class="pun">.</span><span class="typ">Range</span><span class="pun">(</span><span class="typ">Cells</span><span class="pun">(</span><span class="lit">1</span><span class="pun">,</span><span class="pln"> </span><span class="lit">1</span><span class="pun">),</span><span class="pln"> </span><span class="typ">Cells</span><span class="pun">(</span><span class="pln">x</span><span class="pun">,</span><span class="pln"> y</span><span class="pun">))</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> arr<br/><br/></span><span class="str">'Close connections<br/>Set rs = Nothing<br/>Set cn = Nothing<br/><br/>End Sub</span>
However, my actual SQL-code is much longer and I want to use a stored procedure to store the select-statement on the server and then call it from VBA.
How would I do this? I can't find any online documentation on how to call regular SQL statements from VBA and get a record set. Most of them only feature single variables.
Thanks!
Answers
-
-
Thanks for your reply, John.
This works from a technical perspective (e.g. like in the code above). However, my SQL code is 500+ lines long so I would like to avoid chopping my code into pieses with the "" & _" at the end of every line.
How would you go about this with such a long code?
-
Why don't you just create your statement as a view on the server, e.g.
create or replace view myview as
<your 500 line select statement here>;
and then in your VB code, have
select *
from myview
where ...
-
Thanks Jon,
That works perfectly!
For one of my codes (static one), that is exactly what I need. However, for another one, I need to pass certain parameters from my Excel to the SQL (e.g. a date to filter for). How would I go about this? Is this possible with a view as well or does the view only work with static queries?
-
2972c2ed-aca1-433f-90e2-eeb1d2851a2d wrote:Thanks Jon,That works perfectly!For one of my codes (static one), that is exactly what I need. However, for another one, I need to pass certain parameters from my Excel to the SQL (e.g. a date to filter for). How would I go about this? Is this possible with a view as well or does the view only work with static queries?
Can you expose the column in your view then filter on the column in your main query?
Aiming to put all queries in the database for the sake of it will just cause you headaches, yes store complex queries that can be reused by many code paths, but don't enforce a blanket rule that says all queries should be achieved purely by views.
BTW If columns can't be exposed because you do aggregation (or other complexities which require extra restrictions to predicate pushing) then you might be able to set a context that can be read by the view where necessary. Another option is having plsql that opens a ref cursor for the client to use. It may be easier to just write the full query in the client side - utilising bind variables where appropriate of course!
-
Thanks for your feedback, Andrew.
The parameters are actually used for more than just pure filtering. I would supply ~10 parameters, that are used for calculations, filtering, etc. (hence the code length of around 500 rows).
I know stored procedures from the MS SQL world and there it's rather simple. You supply the parameters via VBA, read them into the stored procedure and push your result set back to VBA. Hence, I was wondering, if there was such a straightforward method in the Oracle landscape as well.
-
2972c2ed-aca1-433f-90e2-eeb1d2851a2d wrote:Thanks for your feedback, Andrew.The parameters are actually used for more than just pure filtering. I would supply ~10 parameters, that are used for calculations, filtering, etc. (hence the code length of around 500 rows).I know stored procedures from the MS SQL world and there it's rather simple. You supply the parameters via VBA, read them into the stored procedure and push your result set back to VBA. Hence, I was wondering, if there was such a straightforward method in the Oracle landscape as well.
Sounds like you might find the ref cursor suggestion useful. I have no idea how to get it done with your VBA but I'm confident a small google will be fruitful.
A brief demo using sqlplus to do the fetching from the refcursor:[email protected]>create or replace procedure demo_prc (pnInput IN NUMBER, rcOut OUT SYS_REFCURSOR)
2 IS
3 sDummy varchar2(40);
4 BEGIN
5 sDummy := dbms_random.string('x',pnInput);
6 open rcOut for select sDummy string_val from dual;
7 END;
8 /Procedure created.
[email protected]>var rcReturn refcursor
[email protected]>exec demo_prc(10,:rcReturn)PL/SQL procedure successfully completed.
[email protected]>print :rcReturn
STRING_VAL
-----------------------------------------------------------------------------------------------
D6WWRABKER