PL/SQL query via VBA

Question

I am trying to pass PL/SQL query from file via VBA to the Oracle DB.

Simple test query like SELECT * FROM DUAL works flawlessly.

However just a bit more complicated (even not yet PL/SQL) like

SELECT x, y, z FROM table WHERE DATE = '5may2017'

already fails (actually no explicit error, just empty result).

I'm connecting to Conn.Provider = "OraOLEDB.Oracle.1", apply user schema by ALTER SESSION SET CURRENT_SCHEMA=MyUserSchema. Finally my last code-snippet is:

With Cmd
    .Properties("PLSQLRSet") = True '(actually this line doesnt affect)'
    .ActiveConnection = Conn
    .CommandType = adCmdText
    .CommandText = SqlStatement
    Set rs = .Execute
End With

Sheets(1).Range("A1").CopyFromRecordset rs

So, excel sheet is populated with the 1st test simpliest query, however remains empty for the more complex part (I have no semicolon in the end :) ) However final sql-script will be 400+ lines long...

What am I missing at this step?

PS. I don't have any procedures in the DB, and the access is ReadOnly, so I'm not able to run PL/SQL from procedures — Shurov
– Shurov, Commented May 22, 2017 at 14:51
Quick look, if you are not getting an error and are getting an empty result set, that probably means the query worked and found nothing that met your specifications. Possibly you have no records with that date, or you specified the date in a format that it did not recognize. What happens if you run it without the WHERE clause? — SandPiper
– SandPiper, Commented May 22, 2017 at 14:57
For Toad users, check your NLS SELECT * FROM NLS_SESSION_PARAMETERS; ; your NLS_DATE_FORMAT is not default one DD-MON-RR. — philippeko
– philippeko, Commented May 22, 2017 at 16:09
I think it must be Set rs = .Open instead of Set rs = .Execute — Wernfried Domscheit
– Wernfried Domscheit, Commented May 22, 2017 at 16:15

philippeko · Accepted Answer · 2017-05-22 16:02:32Z

1

You might try instead ANSI Date 'YYYY-MM-DD' or ANSI Time 'HH:MM:SS' SQL formatting below for an implicit conversion with SQL ANSI DATE or SQL ANSI TIME operator.

SELECT x, y, z FROM table WHERE COLUMN_DATE = DATE '2017-05-05'
SELECT x, y, z FROM table WHERE COLUMN_TIME = TIME '23:59:59'
- according such "COL_" exists

A must-keep cheatsheet for quick reference PSOUG Datatypes

NB: For Toad users, check your NLS SELECT * FROM NLS_SESSION_PARAMETERS;; your NLS_DATE_FORMAT is not default one DD-MON-RR.

edited May 22, 2017 at 16:02

answered May 22, 2017 at 15:05

philippeko

5115 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

philippeko Over a year ago

Must-read Oracle ressources Working with Dates in PL/SQL by Steven Feuerstein

Shurov Over a year ago

Thanks, this helped indeed. Still strange for me as '5may2017' works in TOAD

Belayer Over a year ago

That most likely is due to TOAD not being just passive. It has an internal date setting and may have noticed the date format and either issued an alter session or modified the query to specify the format.

Collectives™ on Stack Overflow

PL/SQL query via VBA

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related