oracle - Oracle VBAscript 连接错误
问题描述
Dim strDBDesc As String
strDBDesc = "(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = ##)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = ##)))"
cn.Open "Provider=OraOLEDB.Oracle;Data Source=" & strDBDesc & ";User ID=##;Password=##;"
query1 = ""
query1 = ERM.Sheets("query").Range("A10")
query1 = Replace(query1, "v_job_name", v_field1)
'Set OraDynaSet = objdatabase.DBCreateDynaset(query1, 0&)
Dim OraDynaSet As ADODB.Recordset
Set OraDynaSet = CreateObject("ADODB.Recordset")
OraDynaSet.ActiveConnection = cn
OraDynaSet.Open query1, cn, adOpenStatic
我收到错误消息,因为 ORA-00933 sql 命令未正确结束
错误信息
查询 1
select
job_name,Status
from (
select Distinct a.job_name,
a.description description,Decode (job_type,98,'Box',99,'Command Job',102,'File watcher job',job_type) job_type,
substr(decode(d.status,1,'Running',
3,'Starting',
4,'Success',
5,'Failed',
6,'Terminated',
7,'On Ice',
8,'Inactive',
9,'Activated',
11,'On Hold',
12,'Que Wait',
d.status),1,9) status,
a.mach_name,a.owner,g.command,g.std_err_file,g.std_out_file,f.days_of_week,f.start_times,f.start_mins,f.run_calendar,f.max_run_alarm,profile
from AEDBADMIN.ujo_job a,
AEDBADMIN.ujo_job_runs c,
AEDBADMIN.ujo_job_status d,
(select joid,max(STARTIME) startime,
max(endtime) endtime
from AEDBADMIN.ujo_job_runs group by joid) e,
AEDBADMIN.ujo_command_job g,
AEDBADMIN.ujo_sched_info f
where a.joid = c.joid(+)
and a.joid = d.joid(+)
and a.joid = e.joid(+)
and a.joid = f.joid(+)
and a.joid = g.joid(+)
and (c.startime = e.startime or c.startime is null)
and job_name ='v_job_name'// job name replaces
and a.is_active =1
);
解决方案
我认为问题是这个
and job_name ='v_job_name'// job name replaces
在 SQL 中,注释由-- ...
(单行)或/* ... */
(多行)完成。
但是,也许 ADODB 根本不支持注释,我建议将其完全删除。
或者它是;
末尾的分号 - 删除它。
请注意,您应该重写查询并使用 ANSI 连接语法而不是旧的 Oracle 连接语法 - 特别是对于 OUTER JOINS。
推荐阅读
- mysql - 使用 Group by 获取表格的百分比
- python - Pandas:根据条件将数据从df提取到新的df
- serilog - Windows 8.1 (WinRT) 上的 Serilog.Sinks.File 支持
- apache-nifi - 集群Nifi运行不规律,经常出现问题
- css - 在智能手机中全屏打开 Leaflet Popup
- azure - 从 Azure 广告 B2C 帐户访问 Azure Blob 存储和 Key Vault
- excel - 在VBA中,如何从文本中提取数字前的字符串
- solr - SOLR 建议器中的上下文过滤
- php - Laravel Distinct 查询在我的项目中不起作用
- python - 无法制作计算树中非终端节点的程序