title: Cannot execute SELECT MAX(ORA_ROWSCN) from Embedded SQL
body: <p>Environment : HP-UX 11.x C++ (ProC &amp; aCC compiler) Oracle 10g )</p>
  
  <p>I am unable to execute the following command from my C++ Embedded SQL code</p>
  
  <pre><code>EXEC SQL SELECT MAX(ORA_ROWSCN) INTO :scn_timestamp
  FROM table_name ;
  </code></pre>
  
  <p>The changes i'm making are to an existing file. Following is an excerpt from the automated build script showing compiler error:</p>
  
  <pre><code>/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"
  </code></pre>
  
  <p>The contents of the default option value file /data/tsd_oracle/hpux/10.2.0.2/precomp/admin/pcscfg.cfg are as follows</p>
  
  <pre><code>sys_include=(/data/tsd_oracle/hpux/10.2.0.2/precomp/public,/usr/include)
  ltype=short
  define=ORASTDARG
  </code></pre>
  
  <p>I've also tried variations of that command such as</p>
  
  <pre><code>EXEC SQL SELECT MAX(A.ORA_ROWSCN) INTO :scn_timestamp
  FROM table_name A;
  </code></pre>
  
  <p>For which i get error saying that ORA_ROWSCN column doesn't exist.</p>
  
  <p>I've verified that this sql query works in the SQLPlus console. Any particular reason why this isn't working when embedded?</p>
  
  <p>---------------------------WORKAROUND-------------------------------</p>
  
  <p>Based on Dominic's suggestion i was successfully able to retrieve what i wanted by using the following </p>
  
  <pre><code>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;
  </code></pre>
  
  <p>I'm happy with this workaround, though i still wonder why it doesn't work the usual way. I had also tried using cursors before and had the same error stating "identifier 'ORA_ROWSCN' must be declared". </p>
  
  <p>Anyways, Thanks Dominic</p>