无法从嵌入式SQL执行SELECT MAX(ORA_ROWSCN)

Question

环境：HP-UX 11.x C++（ProC&aCC编译器）Oracle 10g）

我无法从我的C++嵌入式SQL代码执行以下命令

EXEC SQL SELECT MAX(ORA_ROWSCN) INTO :scn_timestamp
FROM table_name ;

我正在对现有文件进行更改。以下是显示编译器错误的自动构建脚本的摘录：

/data/tsd_oracle/hpux/10.2.0.2/bin/proc +z ireclen=200 ORECLEN=200 LRECLEN=208 code=cpp cpp_suffix=C lines=yes sqlcheck=semantics maxopencursors=10 parse=PARTIAL unsafe_null=yes mode=oracle dbms=V8 sys_include='(/opt/aCC,/opt/aCC/include/iostream,/opt/aCC/include/rw,/usr/include)' include=/data/tsd_oracle/hpux/10.2.0.2/precomp/public include=/usr/include include=/opt/aCC/include include=/opt/aCC/include/SC iname=FILENAME.PC;
Pro*C/C++: Release 10.2.0.2.0 - Production on Fri Feb 15 12:48:35 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
System default option values taken from: /data/tsd_oracle/hpux/10.2.0.2/precomp/admin/pcscfg.cfg
Error at line 81, column 5 in file FILENAME.PC
EXEC SQL SELECT MAX(ora_rowscn) INTO :scn_timestamp
....1
PLS-S-00201, identifier 'ORA_ROWSCN' must be declared
Error at line 81, column 5 in file FILENAME.PC
EXEC SQL SELECT MAX(ora_rowscn) INTO :scn_timestamp
....1
PLS-S-00000, SQL Statement ignored
Semantic error at line 81, column 5, file FILENAME.PC:
EXEC SQL SELECT MAX(ora_rowscn) INTO :scn_timestamp
....1
PCC-S-02346, PL/SQL found semantic errors
*** Error code 1
clearmake: Error: Build script failed for "FILENAME.C"

默认选项值文件/data/tsd_oracle/hpux/10.2.0.2/procop/admin/pcscfg.cfg的内容如下

sys_include=(/data/tsd_oracle/hpux/10.2.0.2/precomp/public,/usr/include)
ltype=short
define=ORASTDARG

我也尝试过这种命令的变体，比如

EXEC SQL SELECT MAX(A.ORA_ROWSCN) INTO :scn_timestamp
FROM table_name A;

为此，我得到了一个错误，说ORA_ROWSCN列不存在。

我已经验证了这个sql查询可以在SQLPlus控制台中工作。有什么特别的原因可以解释为什么在嵌入时不起作用？

---------------------------变通方法------------------------------

根据多米尼克的建议，我成功地使用以下检索到了我想要的东西

EXEC SQL PREPARE timestamp_stmt FROM 'SELECT MAX(A.ora_rowscn) FROM table_name A';
EXEC SQL DECLARE timestamp_cur CURSOR FOR timestamp_stmt;
EXEC SQL OPEN timestamp_cur;    
EXEC SQL FETCH timestamp_cur INTO :scn_timestamp;   
EXEC SQL CLOSE timestamp_cur;

我对这种变通方法很满意，尽管我仍然想知道为什么它不能以通常的方式工作。我以前也尝试过使用游标，但遇到了同样的错误，即"必须声明标识符'ORA_ROWSCN'"。

无论如何，感谢多米尼克

Answer 1

您是否尝试过将其封装在动态sql块中，以向pro*c编译器隐藏它，看看这是否有效？

即使用类似EXEC SQL PREPARE sql_stmt FROM :select_stmt;的东西，其中select_stmt是包含select max(a.ora_rowscn) from table_name a 的char数组