How to get data set from Oracle SQL to Excel via VBA

2972c2ed-aca1-433f-90e2-eeb1d2851a2d

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!

John Thorton

https://www.google.com/search?q=how+to+call+regular+SQL+statements+from+VBA

2972c2ed-aca1-433f-90e2-eeb1d2851a2d

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?

JonWat

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 ...

2972c2ed-aca1-433f-90e2-eeb1d2851a2d

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?

AndrewSayer

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!

2972c2ed-aca1-433f-90e2-eeb1d2851a2d

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.

AndrewSayer

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